Please note that the Support HOWTO should be read before reading this guide, and before reporting problems to the ITEE Student IT Helpdesk.
Specifically, (and we only mention this because so many people fail to understand this from reading the clearly defined section on what the helpdesk does do and what it does do not do:) the ITEE Student IT Helpdesk does not teach you how to use Oracle, and we do not help you with your Oracle assignments/coursework. This is what your tutors and lecturers are for.
If the Oracle server is down, or something is not behaving as it should and you can prove it is not as a result of your code (and reading and following the advice in this good practice guide is the best way to prove that), then we can help. For anything else, you should ask your tutors, and if they can't help you, you should as your lecturer.
This guide outlines how to correctly use the ITEE Oracle server. If you are having a problem with the Oracle server, please refer to this document first to ensure your code follows all the information and advice contained within. If you report a problem to the student-helpdesk regarding the Oracle server, we will not look into it unless you have also indicated you have tried to confirm your code conforms to this guide (and provide some sort of evidence you have done this.)
Step 1. Specifics (or "Make It Work")
This details some basic information you'll need to get your Oracle database access up and running.
1.1 User Details
Oracle isn't given by default to every student of ITEE. If your course requires Oracle access though, you will have an account generated for you. The username you will use is your UQ username, and the password will be a random password. This password can be reset by you at http://studenthelp.itee.uq.edu.au/oracle/resetpw.php. Note though, unlike most other ITEE systems, the Oracle password is NOT synchronized with the rest of ITEE. This means if you change your password on the lab PCs it will NOT change the password you use for Oracle. This information is also available at http://studenthelp.itee.uq.edu.au/faq/#winsqlplus.
1.2 Oracle Connection Details
There's plenty of different ways to connect to Oracle. For each method, you'll need the correct details for that method. You should not assume that the details for one method will work for another. Below lists the methods we've encountered (so far - though each year students find more and more interesting ways to connect to the Oracle server) and the correct connection details you'll need for each:
- For SQLPlus, the Host String you'll need is TEACH This information is also available at http://studenthelp.itee.uq.edu.au/faq/#winsqlplus.
- For JDBC thin drivers (Tomcat, Webapps, etc.), the Connection String is:
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo", "login", "password");
This information is also available at http://studenthelp.itee.uq.edu.au/faq/#JDBCC. Note you should also read the note under Section 2.1 on the correct driverClassString to use if you are using JDBC thin drivers. - For Visual Studio applications (including ASP, VB,
C#, etc), you should NOT use the default Microsoft Oracle drivers
- these are for a much older version of Oracle. You need to be using the
newer Oracle supplied drivers/controls. There's two places
you need to change settings to be sure of this:
- You need to add a reference to your project to the official Oracle drivers, which are Oracle.DataAccess (verison 10.x) for .NET projects, or any control that has a path of C:\Oracle or C:\Program Files\Oracle for COM projects (Project -> Add reference in both cases.) You should be careful to ensure that you are not referencing the default Microsoft System.Data.OracleClient namespace which has almost identical function names and which is added in as a reference usually by default. Recommend: removing the reference to System.Data.OracleClient from your project.
- You should also ensure that any data access controls you are using in your project come from the Oracle.DataAccess.Client namespace (the Oracle one), and NOT the System.Data.OracleClient namespace (the Microsoft one). On the Data tab of the Toolbox, right click and select Choose items (VS2005) or Add/Remove items (VS2003). Remove any tick boxes next to the controls from the Microsoft namespace identified above, and tick the boxes next to the controls you need that are part of the Oracle namespace identified above.
1.3 Oracle Reports and Forms
The Oracle Reports and Forms features that are part of the Oracle Application Server series are installed locally on the lab PCs. Specifically, we do not run a central Reports Server (for performance and security reasons) so you need to start your own instance to enable reports and forms to work. To make this easier, there's a menu option under Start -> Programs -> Oracle -> Reports Developer -> Start OracleAS Reports Server.
NOTE: As you will no doubt be aware, Oracle reports servers must have a unique name over the entire network. To make the naming unique and consistent, the above reports server instance started from the shortcut icon will have the same name as your username (eg sXXXXXXX.) If you prefer to start the reports server yourself manually, please use that convention for naming your instance as well.
This information is also available at http://studenthelp.itee.uq.edu.au/faq/#orarepfrm
1.4 Changing Oracle Passwords
Once you've reset your Oracle Password via the webpage http://studenthelp.itee.uq.edu.au/oracle/ the easiest way of changing your password to something you can remember is to connect via the sqlplus interface (in the Oracle menu of the Start menu) using the password provided and the command string ALTER USER Your_Login IDENTIFIED BY Your_Password; where Your_login is the account name you wish to set the password for and Your_Password is the password you wish to use.
Step 2. Technicalities (or "Make It Work Correctly")
This details some finer points of Oracle usage. In a lot of cases there is more than one way to do something, and whilst all ways may work in your particular circumstance, some ways are recommended as the correct way and others are not.
2.1 JDBC Driver Strings
The driverClassString usually specified in conjunction with JDBC drivers is oracle.jdbc.driver.OracleDriver. This works even with the current Oracle 10g server, however Oracle has stated that this form of driverClassString was deprecated as of Oracle 8, and discontinued as of Oracle 9i. The correct way to specify the driverClassString is now String driverClassString="oracle.jdbc.OracleDriver";. Therefore, even though the old way still works, it probably won't for much longer, so you're better of using the correct way from the start.
Step 3. Practicalities (or "Make It Work Correctly AND Quickly")
This section deals with higher level concepts such as program design, availability, and resource usage. You should still read this section, as not writing your programs correctly for the shared Oracle environment at ITEE will result in you running into mandatory limits which will prevent your application from working. The solutions this section offers to the problems it highlights mostly involve requiring you to THINK about what you're doing and convince yourself that your code does not do something stupid.
3.1 One Connection To Rule Them All
A lot of code we have seen sets up a connection, fetches a single piece of data, then closes the connection. This is fine if all you want is one piece of information, but we've seen people generate entire tables of data by iterating through a dataset one item at a time, each time using the open, fetch, close method to retrieve a single piece of data. This is poor program design.
You should open a single connection at the start of your program and reuse it for subsequent queries, only closing it at the end of your application run (or page generation for ASP web services.)
The reason using multiple connections is a bad idea is that each connection you open consumes resources in terms of memory usage for local and remote connection information, network traffic to setup and tear down each connection, and also creates unnecessary work for network infrastructure that has to track connections. In fact, to stop people writing code that creates such a drain on the ITEE infrastructure, you can make at most 3 connections in a 2 second period before further connection attempts are blocked (if in the course of development this happens to you, you should close down your application, and any support environment such as Tomcat it uses, to reset the block.)
You should think hard about your programs' execution and convince yourself that you are not opening more than one (or at most two) connections to the oracle server during the entire lifetime of the program.
3.2 Locking: Good When You Don't Lock Yourself Out
A common mistake students make is not thinking about cleanup when a program exits, or not checking after an unhandled bad program termination to ensure there are no problems as a result. This is very closely related to another common problem which is locking a table and then forgetting to unlock it before exiting, or worse, locking a table in one connection to the database, and trying to access the same locked table in another connection to the database. This last problem is another great reason to read Section 3.1 above and make sure you only have one connection to the server at once.
If your programming language allows for easy Exception handling, check for connections and locks open and close them. If not, you'll need to manually check for them at either the next login, or via the SQLPlus interface. To avoid locking yourself out of your own database, you'll once again need to think about your programs' execution and convince yourself that you are not leaving tables locked or doing queries in a dangerous order.
If you have accidentally locked yourself out of Oracle by writing poor code that left sessions logged in or locked tables but didn't unlock them (ORA-00054) you can visit this web site to reset your Oracle sessions: http://studenthelp.itee.uq.edu.au/oracle/. This information was also available in an announcement here: http://studenthelp.itee.uq.edu.au/announcements/announcement.php?tag=oraclekilll.