SQL Database - Oracle
This guide assumes you have some basic knowledge of databases, have read the Oracle Policy and are familiar with either CGI/PERL or PHP.
If your request to add Oracle database interactivity to your site has been granted, you will be given 2 accounts (a read-only and an admin account). You will want to verify that you are able to login to both accounts. To test your Oracle account first login to UBUnix.
To connect to Oracle once logged into UBUnix, enter the following in the terminal:source /util6/bin/coraenv
setenv TWO_TASK wings
sqlplus
You will be prompted for your Oracle Username and Password, enter the information you have received from CIT staff. You can now change the passwords for these accounts using the "password" command at the sqlplus "SQL>" prompt.
If your username/passwords work you are ready to setup your tables to function with your "reader" account. First login to UBUnix and connect to Oracle with your Oracle "admin" account. Now you can create the tables needed interactively using "sqlplus". You must then "grant select" on all the tables that the "reader" account should have access to. Remember that the "admin" account should never be used to access Oracle from a web based application like your website. Re-read the Oracle Policy if you missed this bit of information.
For additional information on Oracle, such as creating tables and inserting data, you can visit the University's docs located at http://wings.buffalo.edu/computing/documentation/unix/oracle.html or the Oracle Users guide located at http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/toc.htm
.
Some Campus users also utilize the product TOAD by QUEST SOFTWARE to administer their databases. If you are relatively new to databases this may be helpful for you.
After creating the neccesary tables and populating them with some data you will want to create a web application written in either CGI/PERL or PHP using the "reader" account to access your data. Remember that the password for this account should never be available to the public. This means that passwords should never be stored in a file that is viewable by the outside world. If you have selected Option1 for securing your passwords (from the Oracle Policy) then CIT should have sent you the path of an include file outside the root of the webserver. Below is a code snippet, written in PHP, which illustarates how a connection is made to Oracle.
< ?php
putenv("ORACLE_HOME=/oracle/product/10.2.0/db_1");
putenv("ORACLE_SID=wings.buffalo.edu");
$connection= OCILogon("username","password","wings.buffalo.edu");
if ($connection == false){
echo Ora_ErrorCode($connection).": ".Ora_Error($connection)."< BR>";
exit;
}
?>
It is also Important that you logoff from your Oracle connection after you are done with each session. Failure to successfully logoff after each connection can result is data loss and interuption of services. Each page utilizing Oracle should also call the ocilogoff() function after it has retrieved all the data needed by that page. Make sure each page makes this call after its interaction with Oracle is complete or you may experience problems accessing your data.
Other functions of interest to you would most likely include:
ociparse
ociexecute
ocicommit
An example of using the functions above to insert some data into a table using PHP. First we parse the SQL Statement "insert into MYTABLE values('sample', 'data')" which returns a FALSE value if the query is invalid. Next call ociexecute to actually execute your SQL statement and ocicommit to execute any statements outstanding :
< ?php
$stmt = ociparse($connection, "insert into MYTABLE values('sample', 'data')");
ociexecute($stmt, OCI_DEFAULT);
ocicommit($connection);
?>
For further information on using Oracle functions with PHP visit php.net