Oracle Server
The FeeNIX system runs Oracle 12c Release 1.
Connecting to the Server
You can connect to your Oracle database using the following clients.
- iSQL JunioR - Web client
- SQL*Plus
- Oracle SQL Developler (Windows, macOS and Linux)
Host Name: feenix-oracle.swin.edu.auUsername
A lowercase 's' followed by your student number.
Password
Initially this will be your six digit date of birth in ddmmyy format. You will be prompted to change this when you log in.
Service Names
Your database will exist under one of four service names. Service names are determined the unit you are studying. For example, Database Analysis and Design has the service name of 'DAD'.
Connections can be established from both SID and Service Names, the suffix '.swin.edu.au' is no longer needed when connecting
- DAD
- DMS
- DBA
- Projects
Client Programs
iSQL JunioR
iSQL JunioR is a web interface which can be accessed at https://feenix-isqljr.swin.edu.au/.
SQL*Plus
SQL*Plus is a command line tool. You can install SQL*Plus from Oracle.
If you have access to Mercury use the following command:
sqlplus s[student id]@[oracle database name]For more information on SQL*Plus, please see SQL*Plus Quick Start.
SQL Developer
Oracle SQL Developer is available in computer labs. For home use it can be downloaded from the Oracle SQL Developer website for use on Windows, OS X and Linux.
- When encountering error message 'ORA-28001: the password has expired', attempt to log in via iSQL JunioR.
- Connection to SQL Developer from home must be via Swinburne VPN.
- SQL Developer is available via Apps on Demand. Apps on Demand support here.
The connection settings for SQL Developer must be configured manually as below:
- Right click on Connections
- Select + New Connection...
- Enter the connection details
- Connection Name: Make this the same as the Service Name, for example; 'DAD'.
- Username: (your oracle username)
- Password: (your oracle password)
- Connection type: Service Name or SID
- Role: default
- Connect Identifier: (as per subject notes, eg: dad)
- Once the fields are completed, click on Test.
- If you see Status: Success then the details are correct.
- Click Save.
- Click Connect.
Connecting to Oracle from off campus
You must be using the Swinburne VPN client in order to connect directly to Oracle from off campus locations.
Manual
A copy of the manual is available on the Oracle website.
Advanced usage
TNS for Remote Access
TNS (Transparent Network Substrate) contains information which is used to connect to Oracle Server. The information is provided by TNS file.
The value of $ORACLE_HOME will vary from host to host. Windows users may be using C:\OraHome1\
TNSNAMES
TNS connections are controlled by two files, SQLNET.ORA and TNSNAMES.ORA, both of which should be located in $ORACLE_HOME/network/admin
Periodical checking for updates is recommended when using TNSNAMES as connection details can change.
Programming
Java
import java.sql.*;
/**
* Sample Oracle JDBC code
* Written By John Newbigin
*
* To compile this test code on mercury
* $ javac Test.java
*
* To run this test code on mercury
* $ java -classpath /usr/lib/oracle/10.2.0.3/client/lib/ojdbc14.jar:. Test
* - or -
* $ java -classpath ojdbc5.jar:. Test
* To run this test code on EL5 lab machines use
* $ java -classpath /usr/lib/oracle/11.1/client/lib/ojdbc5.jar:. Test
*
*
* You will have to change the getConnection parameters as appropriate
*/
public class Test
{
public static void main(String [] args)
{
String username = "test";
String password = "XXXXXX";
String database = "projects";
String sql = "SELECT owner, table_name, tablespace_name FROM all_tables ORDER BY owner, table_name";
try
{
System.out.println("FICT Oracle sample code");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Connecting to database....");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ldap://feenixldap.swin.edu.au/cn=OracleContext,dc=swin,dc=edu,dc=au/" + database, username, password);
Statement select = conn.createStatement();
ResultSet result = select.executeQuery(sql);
ResultSetMetaData rsMetaData = result.getMetaData();
int columns = rsMetaData.getColumnCount();
for(int i=1; i <= columns; i++)
{
System.out.print(rsMetaData.getColumnName(i));
if(i == columns)
{
System.out.println("");
}
else
{
System.out.print(", ");
}
}
while (result.next())
{
for(int i=1; i <= columns; i++)
{
String val = result.getString(i);
System.out.print(val);
if(i == columns)
{
System.out.println("");
}
else
{
System.out.print(", ");
}
}
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}PHP
<?
echo "OCI Test<br>";
$sid = 'projects';
$ds=ldap_connect("ldaps://feenixldap.swin.edu.au");
$r=ldap_bind($ds);
$sr=ldap_search($ds, "cn=OracleContext,dc=swin,dc=edu,dc=au", "(cn=$sid)");
if(ldap_count_entries($ds, $sr) > 0)
{
$info = ldap_get_entries($ds, $sr);
$tns = $info[0]['orclnetdescstring'][0];
}
else
{
echo "Can't find tns for $sid";
}
ldap_close($ds);
echo "<pre>$tns</pre>\n";
$username = "hitXXXX_NN";
$password = "XXXXXX";
$db = @ocilogon($username, $password, $tns);
if($db === false)
{
$error = OCIError();
if($error['code'] == 28001)
{
echo "You must change your password";
oci_password_change($tns, $username, $password, $newpassword); // Does not work
$db = @ocilogon($username, $newpassword, $tns);
}
else
{
echo "Logon error ";
print_r(OCIError());
}
}
if($error = OCIError($db))
{
echo "Error connecting to database<br>";
echo $error["code"]." ".$error["message"];
die;
}
$sql = "SELECT * FROM all_tables";
$stmt = OCIParse($db, $sql);
if(OCIExecute($stmt))
{
while(OCIFetchInto($stmt, $row, OCI_RETURN_NULLS))
{
echo $row[0]."<br>";
}
}
OCIFreeStatement($stmt);
?>