1. Obtain your account on ORACLE 7 DBMS.
2. Login to ORACLE server machine.
3. Set up user enviroment. (e.g. you can enter source ~oracle/.cshrc on osprey7.npac.syr.edu)
4. Enter sqlplus and followed by userid and passwd, or enter sqlplus userid/passwd on the server machine.
5. Alter your passwd for security.
   ALTER USER userid IDENTIFIED BY password;
6. Enter help to check online menu.
7. Enter exit or quit to logoff.

CREATE TABLE person_info_table
       (person_id       NUMBER(6) CONSTRAINT pk_person_info PRIMARY KEY,
        last_name       VARCHAR2(15),
        first_name      VARCHAR2(15),
        title           VARCHAR2(8) CONSTRAINT lower_title
                                    CHECK (title = LOWER(title)),
        user_name       VARCHAR2(8),
        address         VARCHAR2(12),
        url             VARCHAR2(20));

CREATE TABLE phone_list_table
       (person_id       NUMBER(6) CONSTRAINT fk_phone_list
                                  REFERENCES person_info_table(person_id),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

1. In SQL*Plus you can insert the data to a table by entering

   
   INSERT INTO person_info_table 
       VALUES('99', 'LIN', 'FRANK', 'GRA', 'thlin', '3-211', NULL);
   COMMIT;
   

   This will allow you to add a personal record into person_info_table. Another way to insert a small amount of data into a table is to use OraPerl to accomplish.

   OraPerl example

   For a large amount of data, The previous method can not be effeciently used. ORACLE DBMS provides a fast way to load a huge data set by using SQL*LOADER. The following steps explains how to use SQL*LOADER to load the data into table.

   1. Create a control file.
      If we have a data set named phone.db and in the following format,

      1|3154238422|home
      2|3154432483|office
      3|3154432083|office
      4|3154433544|office
      5|3154433857|office
      6|3154433686|office
      

      then the control file phone.ctl can be created as follow:
      

      LOAD DATA
      INFILE 'phone.db'
      BADFILE 'phone.bad'
      DISCARDFILE 'phone.dsc'
      REPLACE
      INTO TABLE phone_list_table
      FIELDS TERMINATED BY '|'
      (person_id, phone_no, phone_type)
      

      • LOAD DATA: define to load the data into database.
      • INFILE: define the data file will be loaded into database.
      • BADFILE: define the file will contains bad data.
      • DISCARDFILE: define the discard file.
      • REPLACE: define to replace the data in database by current data.
      • INTO TABLE table_name: define the the table you want to load the data into.
      • FIELDS TERMINATED BY 'seperator' (column name...): define the seperator between fields in data file and the column name.
      After the control file is created, user can enter sqlload userid=userid/passwd control=phone.ctl on the server (e.g., osprey7.npac.syr.edu). (SQL*LOADER runs at UNIX command line not SQL*Plus) The system will response commited messages or error messages to you, or you can check the phone.log file.

      Drop Tables

      You can use DROP command to remove tables from Oracle Database.
      

      
      SQL>DROP TABLE person_info_table;
      

      Check created Tables, Packages, etc.

      If you would like to check tables, views, triggers, packages, procedures, etc, you have created, you can use the following SQL code to generate the report to the file objects.rpt.

      SPOOL objects.rpt;
      SELECT SUBSTR(OBJECT_NAME,1,30), OBJECT_TYPE, CREATED, last_ddl_time
      FROM user_objects
      ORDER by object_type, timestamp;
      spool off;
      

      Or you can check the information in SQL*Plus by

      SELECT SUBSTR(OBJECT_NAME,1,30), OBJECT_TYPE, CREATED, last_ddl_time
      FROM user_objects
      ORDER by object_type, timestamp;
      

      Check Database

      After the data have been inserted into or loaded to the database, you may want to check the correctness of each datum. The following steps will help you to check.
      1. Enter SET SERVEROUTPUT ON in SQL*Plus.
      2. SQL>SELECT * FROM person_info_table;
      You should see the data be display on the screen.

      Create PL/SQL Package

      After loading the data into the table, user need to create a PL/SQL package to store procedures in ORACLE RDBM. The following example command file shows how to create a PL/SQL package and load the package into ORACLE database.

      
      /*  File: phone_wow_pkg.sql  */
      CREATE OR REPLACE PACKAGE phone_pkg AS
      
          TYPE person_rec_type IS RECORD              -- person-info record
             (person_id       NUMBER(6),
              last_name       VARCHAR2(15),
              first_name      VARCHAR2(15),
              title           VARCHAR2(8),
              user_name       VARCHAR2(8),
              address         VARCHAR2(12),
              url             VARCHAR2(20));
      
          PROCEDURE get_person_by_name(name IN VARCHAR2);
      
          PROCEDURE display_person_head(ctitle IN VARCHAR2);
      
          PROCEDURE display_person_rec(person_rec IN person_rec_type);
      
          PROCEDURE display_person_end;
      
      END phone_pkg;
      /
      
      CREATE OR REPLACE PACKAGE BODY phone_pkg AS
      
          PROCEDURE get_person_by_name(name IN VARCHAR2)
          IS
              CURSOR person_cur(name IN VARCHAR2)
              IS
                  SELECT *
                  FROM person_info_table
                  WHERE name IS NULL OR
                        last_name LIKE ('%' || LOWER(name) || '%') OR
                        first_name LIKE ('%' || LOWER(name) || '%');
      
              person_rec      person_rec_type;
      
          BEGIN
      
              display_person_head(name);
      
              IF NOT person_cur%ISOPEN THEN           -- open the cursor if
                  OPEN person_cur(name);              -- not open yet
              END IF;
      
              -- Fetch rows into PL/SQL table
      
              LOOP
                  FETCH person_cur INTO person_rec;
                  EXIT WHEN person_cur%NOTFOUND;
                  display_person_rec(person_rec);
              END LOOP;
      
              CLOSE person_cur;
      
              display_person_end;
      
          END get_person_by_name;
      
          PROCEDURE display_person_head(ctitle IN VARCHAR2)
          IS
          BEGIN
              htp.title('Query Results by Searching "' || ctitle || '"');
              htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
              htp.p('<BR>');
              htp.p('<TABLE BORDER ALIGN="CENTER">');
              htp.p('<TR>');
              htp.p('<TD>Last Name</TD>');
              htp.p('<TD>First Name</TD>');
              htp.p('<TD>Title</TD>');
              htp.p('<TD>User Name</TD>');
              htp.p('<TD>Address</TD>');
              htp.p('<TD>URL</TD>');
              htp.p('</TR>');
          END display_person_head;
      
          PROCEDURE display_person_rec(person_rec IN person_rec_type)
          IS
              url_string      VARCHAR2(120);
          BEGIN
              htp.p('<TR>');
              htp.p('<TD>' || INITCAP(person_rec.last_name) || '</TD>');
              htp.p('<TD>' || INITCAP(person_rec.first_name) || '</TD>');
              htp.p('<TD>' || INITCAP(person_rec.title) || '</TD>');
              htp.p('<TD>' || person_rec.user_name || '</TD>');
              htp.p('<TD>' || UPPER(person_rec.address) || '</TD>');
              url_string := '<a HREF=http://www.npac.syr.edu/users/' ||
                           person_rec.user_name ||
                           '/homepage/ TARGET=wow_display>';
              htp.p('<TD>' || url_string || htf.italic(person_rec.first_name) ||
                      '</a>' || '</TD>');
              htp.p('</TR>');
          END display_person_rec;
      
          PROCEDURE display_person_end
          IS
          BEGIN
              htp.p('</TABLE>');
          END display_person_end;
      
      END phone_pkg;
      /
      
      

      "/" is SQL*Plus command to execute the SQL command ot PL/SQL block currently stored in the SQL buffer.

      Load Packages into ORACLE RDBM

      • Load HTP/HTF package (ht.sql) into ORACLE RDBM.
      • Load WOW package (wow.sql) into ORACLE RDBM.
      • Load the user-defined package (phone_wow_pkg.sql) into ORACLE RDBM.
      User enter the following commands in SQL*Plus to load these three packages into ORACLE RDBM. (Assuming all these three files loacte at the current working directory, otherwise add the correct path to the files.)

      
      SQL> @ht.sql
      SQL> @wow.sql
      SQL> @phone_wow_pkg.sql
      

      Create ORACLE CGI

      Create a ORACLE CGI executable file using UNIX shell scripts. This CGI setups the system enviroment to run ORACLE web-based applications. It passes the argument list ($1) into "wowstub" and "wowstub" extracts the argument list and execute the corresponding statements in ORACLE RDBM.

      File: wow

      #!/bin/csh
      setenv ORACLE_HOME /oracle/app/oracle/product/7.3.2
      set path = ( ${ORACLE_HOME}/bin /bin /usr/bin /usr/ucb /usr/local/bin . )
      setenv WOW_UID cps616/cps616
      setenv ORACLE_SID osprey7
      setenv DEBUG NONE
      /oracle/wow/wowstub $1
      exit 0
      

      You need to setup the enviroment to use CGI scripts to interact Oracle database. Some important enviroment parameters are described as follow:
      • ORACLE_HOME: define the Oracle server root.
      • WOW_UID: define the userid and password.
      • ORACLE_SID: define Oracle server ID.
      After you create the CGI scripts, you need to change the mode of this file to readable and executable (e.g., chmod 755 CGI_file).
      File: wowstub.h and wowstub.c

      Create ORACLE Page

      This section shows how to create a link in a HTML file using CGI scripts to conduct the operations on ORACLE RDBM.

      
      <FORM ACTION="http://osprey7.npac.syr.edu:3768/reference-cgi/cwou/database/plsql/wow/phone_pkg.get_person_by_name"
      METHOD="GET">
      Enter the name:
      <INPUT NAME="name" VALUE="" SIZE=40>
      <BR>
      <INPUT TYPE="submit" value="Search">
      <INPUT TYPE="reset" value="Cancel">
      </FORM>
      

      ---

      Example:
      Enter the name:
      

      If you submit the query and want to return to this page, please use Back button at top of the browser.

      ---

      Oracle-Web Tips

      • When you develop SQL and SQL/PL codes in SQL*Plus, you can use SAVE command to save the code in the buffer to a file. Next time you need to use the same code, you just enter @filename to execute the code.
      • Use SET SERVEROUTPUT ON in SQL*Plus to see the result on the screen.
      • Put all reuseable codes in SQL/PL packages to save your time and gain better perfoemance.
      • If there are error messagesr when you load your package/procedures, you can use show err to list all error messages.
      • Develop Ora*Perl scripts and test it without using the browser (e.g. Netscape); all the output should be seen on the screen or you can redirect the output to a file.
      • Create a script file (e.g. C-Shell) to set up the Oracle enviroment on the server and execute Ora*Perl script in this script.

        #!/bin/csh
        setenv ORACLE_HOME /oracle/app/oracle/product/7.3.2
        setenv ORACLE_SID osprey7
        /servers/cgi-http-class/htdoc/reference-cgi/cwou/database/oraperl/example1.pl
        exit 0

      • Check objects (tables, packages, procedures, triggers, etc) have been created by Check Current Created Tables, Packages, or Procedures

      Personal Suggestions: User's Background

      This tutorial is designed for the users who already have experiences to develop work in FORMS in HTML2.0 (or higher), CGI scripts (Perl, Shell, C, etc) HTTPD, and SQL and PL/SQL on SQL*Plus. If you are not farmilar with these topics, please spend some time to learn them and complete the Web-Oracle applications later.