Oracle

From Helpme

Jump to: navigation, search

Contents

Preparation

Account Creation

To use Oracle, you must first have an Oracle account created for you. (Your professor may have submitted a class roster to have your account created automatically, check with them about this first) This account is separate from your normal DC account. To request one, please e-mail support@engr.scu.edu

Your Oracle username will be identical to your DC username, but the password is independent. (By default your password is set to your ACCESS Card/Student ID number with *ALL* the leading zeroes. The one on the front of your card, not the back)


Tablespace (Database) Name

Your database name will be your username in all caps. For example, if your username was "testuser" your Oracle tablespace name would be "TESTUSER".


Database Server

The database backend runs on a single server. Its hostname is:

dbserver.engr.scu.edu

Running

  • To setup Oracle in your environment, type:
setup oracle
  • Run SQL*Plus (the Oracle command-line client) by executing:
sqlplus <username>@db11g
This will prompt you for your Oracle password. (note: this might be different from your DC account password)
Once authenticated, you will be left at the SQL> prompt.

Changing your password

Your oracle password is independent of your DC Unix and DC Windows passwords. To change it, login via SQL*Plus (see above) and run:

SQL> password
Changing password for TESTUSER
Old password: <Enter your existing password here, nothing will show up as you type>
New password: <Enter your new password here>
Retype new password: <Enter your new password again>
Password changed
SQL> 

NOTE: You must use 'sqlplus' to login to your Oracle account once to go through the password change process before you can use PHP or JDBC to access your Oracle account.

NOTE: You must NOT use the '@' character in your password. Oracle will allow you to do so, but will then be confused by it when you next try to log in and you'll be unable to access your account.

JDBC/Web Access

The following parameters should be used when setting up JDBC or other web-based access to the Oracle database:

  • Driver type: Thin
  • Host: dagobah.engr.scu.edu
  • TCP Port: 1521
  • SID: db11g
  • DB name: <your oracle username>
  • DB password: <your oracle password>

To open a connection to database, use the static getConnection() method of the JDBC DriverManager class. This method returns an object of the JDBC Connection class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.

Connecting to a database is a step where you must enter Oracle JDBC driver-specific information in the getConnection() method.

Specifying a Database URL, Userid, and password:
getConnection(String URL, String user, String password);
where the URL is of the form:
jdbc:oracle:<drivertype>:@<database>

The following example connects user scott with password tiger to a database with SID db9i through port 1521 of host dagobah.engr.scu.edu, using the Thin driver:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =
  DriverManager.getConnection ("jdbc:oracle:thin:@dagobah.engr.scu.edu:1521:db11g",
    "scott", "tiger");

NOTE: When building/testing your JDBC application from the command-line, you must first type 'setup jdk' to put the proper JDK (with Oracle JDBC driver support) into your PATH. If you do NOT run 'setup jdk' first, you will get the locally installed jdk which does NOT have the JDBC Oracle driver.

SECURITY NOTE: For security reasons, it is not possible to establish a JDBC connection to Oracle from outside the School of Engineering network. (The wireless network is logically external to the Engineering network)

PHP

It is possible to access Oracle via PHP from the student webserver. (http://students.engr.scu.edu/)

Information necessary to connect to the DB from PHP: (remember the user/pass are your Oracle user/pass, which may differ from your Unix or Windows user/pass)

<?php
$conn = oci_connect('username', 'password', '//dbserver.engr.scu.edu/db11g');
if($conn) {
	print "<br> connection successful";
} else {
	$e = oci_error;
	print "<br> connection failed:";
	print htmlentities($e['message']);
	exit;
}
?>

For further information on actually using Oracle data from PHP, refer to the OCI8 documentation.

NOTE: For PHP to process your code, you need to give your file a ".php" extension.

SQL Developer

You can run the SQL Developer GUI on a DC Linux system thusly:

$ setup jdk
$ setup oracle
$ sqldeveloper.sh

It also works remotely via FreeNX.

NOTE: For security reasons it is not possible to run SQL Developer on a system outside of the Engineering network and connect back to the DC Oracle server. Attempts to do so will fail with a network connection issue. The SCU wireless network is NOT part of the Engineering network. If you want to run SQL Developer when outside of the Engineering network, connect to a DC Linux workstation via FreeNX and run SQL Developer from there.

Personal tools