Contents

Oracle Database Information

Oracle Server

The server runs Oracle 11g Release 2 (11.2). Connections to the server must be made by an Oracle client and must use the correct TNS information.

Username

For students, the username is s followed by the student ID. eg:

s100123456

For staff, the username is your normal SIMS username.

Password

For students, your password is your date of birth in the format ddmmyy. You will be force to change your password on first login. If you forget your password, contact the service desk who can reset it for you.

Staff, please contact the service desk to have your password reset.

TNS

The TNS information tells oracle clients how to connect to the oracle server. ICT provide the information in two different formats. The prefered format is LDAP and the alternate format is tns file.

The value of $ORACLE_HOME will vary from host to host. Windows users may be using C:\OraHome1\

LDAP

LDAP connections are controlled by two files, SQLNET.ORA and LDAP.ORA, both of which live in $ORACLE_HOME/network/admin

When using LDAP you do not need to make any changes to your client if the server details change.

The contents of SQLNET.ORA is

NAMES.DEFAULT_DOMAIN = ict.swin.edu.au
NAMES.DIRECTORY_PATH = (LDAP,TNSNAMES)

The contents of LDAP.ORA is

DIRECTORY_SERVERS = (ldap.ict.swin.edu.au:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=ict,dc=swin,dc=edu,dc=au"
DIRECTORY_SERVER_TYPE = OID

You can download these files from [1] and [2]

This source of TNS data also includes the connections from the O: drive.

You must be on the Swinburne network or using the Swinburne VPN client in order to connect to LDAP and Oracle.

TNSNAMES

TNS connections are controlled by two files, SQLNET.ORA and TNSNAMES.ORA, both of which live in $ORACLE_HOME/network/admin

When using TNSNAMES you must check periodically for new versions of the files. The connection details can change from time to time.

The contents of SQLNET.ORA is

NAMES.DEFAULT_DOMAIN = ict.swin.edu.au
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES)

You can download these files from [3] and [4]

This source of TNS data does not include the connections from the O: drive.

You must be on the Swinburne network or using the Swinburne VPN client in order to connect to Oracle.

Client Programs

sqlplus

mercury and ICT computer labs have sqlplus configured and ready for use.

sqlplus s1234567@projects.ict.swin.edu.au

SQL Developer

ICT computer labs have Oracle SQL Developer available. You can also download this from the oracle sql-developer download website for use on Windows, OS X and Linux.

Note: SQL Developer does not have a change password function. If you receive the error 'Account is locked' then try logging in via iSQLjr first.

Note: If you want to use SQL Developer from home, you must use the Swinburne VPN.

Before using SQL Developer, you will also need to install Oracle instant client version 11.2.0.2.0 (instantclient-basic-nt-11.2.0.2.0.zip). Download instant client from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. Once you have download the zip file, extract it to the location of your "sqldeveloper" folder.

Note: You must use instant client version 11.2.0.2.0.

In order to use SQL Developer you must supply the connection details manually.

  • Right click on Connections
  • Select + New Connection...
  • Enter the connection details
    • Connection Name: dms (recommended value)
    • Username: (your oracle username)
    • Password: (your oracle password)
    • Connection type: TNS
    • Role: default
    • Connect Identifier: (as per subject notes, eg: dms)
  • Once the fields are completed, click on Test
  • If you see Status: Success then the details are correct
  • Click Save
  • Click Connect

Web interface

There is a web interface called iSQLJunioR

Manuals

Local manuals can be found http://manuals.it.swin.edu.au/oracle10.2/

Programming

Java

  1. import java.sql.*;
  2.  
  3. /**
  4. * Sample Oracle JDBC code
  5. * Written By John Newbigin
  6. * For more information please use the following URL's
  7. * http://manuals.it.swin.edu.au/oraclejdbc/new
  8. *
  9. * To compile this test code on mercury
  10. * $ javac Test.java
  11. *
  12. * To run this test code on mercury
  13. * $ java -classpath /usr/lib/oracle/10.2.0.3/client/lib/ojdbc14.jar:. Test
  14. * - or -
  15. * $ java -classpath ojdbc5.jar:. Test
  16. * To run this test code on EL5 lab machines use
  17. * $ java -classpath /usr/lib/oracle/11.1/client/lib/ojdbc5.jar:. Test
  18. *
  19. * You can get ojdbc5.jar file from otn or here:
  20. * http://manuals.it.swin.edu.au/oraclejdbc/ojdbc5.jar
  21. * http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
  22. *
  23. * You will have to change the getConnection parameters as appropriate
  24. */
  25.  
  26. public class Test
  27. {
  28. public static void main(String [] args)
  29. {
  30. String username = "test";
  31. String password = "XXXXXX";
  32. String database = "projects";
  33. String sql = "SELECT owner, table_name, tablespace_name FROM all_tables ORDER BY owner, table_name";
  34. try
  35. {
  36. System.out.println("FICT Oracle sample code");
  37. Class.forName("oracle.jdbc.driver.OracleDriver");
  38. System.out.println("Connecting to database....");
  39.  
  40. Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ldap://ldap.ict.swin.edu.au/cn=OracleContext,dc=ict,dc=swin,dc=edu,dc=au/" + database, username, password);
  41.  
  42. Statement select = conn.createStatement();
  43. ResultSet result = select.executeQuery(sql);
  44. ResultSetMetaData rsMetaData = result.getMetaData();
  45. int columns = rsMetaData.getColumnCount();
  46. for(int i=1; i <= columns; i++)
  47. {
  48. System.out.print(rsMetaData.getColumnName(i));
  49. if(i == columns)
  50. {
  51. System.out.println("");
  52. }
  53. else
  54. {
  55. System.out.print(", ");
  56. }
  57. }
  58. while (result.next())
  59. {
  60. for(int i=1; i <= columns; i++)
  61. {
  62. String val = result.getString(i);
  63. System.out.print(val);
  64. if(i == columns)
  65. {
  66. System.out.println("");
  67. }
  68. else
  69. {
  70. System.out.print(", ");
  71. }
  72. }
  73. }
  74. }
  75. catch(Exception e)
  76. {
  77. e.printStackTrace();
  78. }
  79. }
  80. }

PHP

  1. <?
  2. echo "OCI Test<br>";
  3.  
  4. $sid = 'projects';
  5.  
  6. $ds=ldap_connect("ldap.ict.swin.edu.au", 389);
  7. $r=ldap_bind($ds);
  8. $sr=ldap_search($ds, "cn=OracleContext,dc=ict,dc=swin,dc=edu,dc=au", "(cn=$sid)");
  9. if(ldap_count_entries($ds, $sr) > 0)
  10. {
  11. $info = ldap_get_entries($ds, $sr);
  12. $tns = $info[0]['orclnetdescstring'][0];
  13. }
  14. else
  15. {
  16. echo "Can't find tns for $sid";
  17. }
  18. ldap_close($ds);
  19.  
  20. echo "<pre>$tns</pre>\n";
  21.  
  22. $username = "hitXXXX_NN";
  23. $password = "XXXXXX";
  24.  
  25. $db = @ocilogon($username, $password, $tns);
  26. if($db === false)
  27. {
  28. $error = OCIError();
  29. if($error['code'] == 28001)
  30. {
  31. echo "You must change your password";
  32.  
  33. oci_password_change($tns, $username, $password, $newpassword); // Does not work
  34.  
  35. $db = @ocilogon($username, $newpassword, $tns);
  36. }
  37. else
  38. {
  39. echo "Logon error ";
  40. print_r(OCIError());
  41. }
  42. }
  43. if($error = OCIError($db))
  44. {
  45. echo "Error connecting to database<br>";
  46. echo $error["code"]." ".$error["message"];
  47. die;
  48. }
  49.  
  50.  
  51. $sql = "SELECT * FROM all_tables";
  52. $stmt = OCIParse($db, $sql);
  53. if(OCIExecute($stmt))
  54. {
  55. while(OCIFetchInto($stmt, $row, OCI_RETURN_NULLS))
  56. {
  57. echo $row[0]."<br>";
  58. }
  59. }
  60. OCIFreeStatement($stmt);
  61.  
  62. ?>