Oracle Programming Manual

Within this manual it is assumed that Excel tables have already been created and are saved in Excel 3.0 format. For this manual the Excel 3.0 file will be referred to as 'fortable1.xcl'.

Loading tables into the database.

1. To begin a table must be defined and created within the Oracle database.

a) Create a '.cta' file using a text editor. For example:

create table fortable1
(ILLUSNO NUMBER(20) NOT NULL,
GENUSNAME VARCHAR2(100) NOT NULL,
SPECIESNM VARCHAR2(100) NOT NULL,
SAMPLNO VARCHAR2(20) NOT NULL,
ILLUSFILE VARCHAR2(35),
BIGILLUS VARCHAR2(35),
IBILLUS VARCHAR2(35),
ILLVIEW VARCHAR2(35));

b) The above is the SQL code creating a table with the name 'fortable1'. The column names follow and what type of values are within each column. Column names within the 'cta' file should be the same as those in 'fortable1.xcl'.

c) All numbers should be defined as 'NUMBER' variables.

d) All other values should be defined as 'VARCHAR2' variables.

e) The number that follows the value type indicates that maximum number of characters allowed for that variable.

f) To summarize c,d, and e.

ILLUSNO NUMBER(20) NOT NULL

ILLUSNO is the name of a column within the Excel file that holds the data that is to be put into the NM01 database.
NUMBER (20) indicates that the column will hold values of type NUMBER that are 20 characters or less.
NOT NULL indicates that all rows within this column must have a value.

g) Save this file as a "_____.cta". For example the above '.cta' file should be saved as 'fortable1.cta'.

h) Open the program SQL Plus. User Name________ Password___________ Host String________

-Host String for NMITA users is 'NM01'
-Host String for Calvin users is 'CALV'

i) Copy the text of the 'cta' file from the text editor and paste it into the SQL Plus program. If SQL Plus does nothing push 'Enter' on the keyboard.

j) SQL Plus should return the line 'Table created', otherwise it will list the errors within the 'cta' text.

2. The Excel 3.0 file (fortable1.xcl) will be transformed into a text file by the program 'Paradox for Windows'.

a) Open the 'Paradox' program.

b) Click on 'Tools - Utilities - Import' and select the Excel 3.0 file that is to be transformed. In this case it would be 'fortable1.xcl'. Make sure that files of type Excel 3.0/4.0 is selected and that the correct drive and folder is selected.

c) The program will create a file with the extension '.db'. Through the Project Viewer view the Paradox form of the Excel file to ensure the correctness of it.  Again, the correct drive and folder must be selected to view the newly created '.db' file.

d) Click on 'Tools - Utilities - Export'. Output file = text file output. Select Options and the following must be selected - [options = commas, quotes, text field only].

e) A text file has now been created that contains all values within the Excel file.

3. A '.ctl' file must now be created.

a) The following is an example of a '.ctl' file that corresponds to 'fortable1'.

load data
INFILE *
into table fortable1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
ILLUSNO,
GENUSNAME,
SPECIESNM,
SAMPLNO,
BIGILLUS,
IBILLUS,
ILLVIEW)
BEGINDATA

b) Column names must be the same as those in the '.cta' file.

c) Copy the corresponding text file and paste it into the '.ctl' document. For example:

load data
INFILE *
into table fortable1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
ILLUSNO,
GENUSNAME,
SPECIESNM,
SAMPLNO,
ILLUSFILE,
BIGILLUS,
IBILLUS,
ILLVIEW)
BEGINDATA
1.0000,"Ammonia","beccarii","PPP20","a_becarii.gif","a_becarii_big.gif","a_becarii_tb.gif","umbilical",,,,,
2.0000,"Ammonia","beccarii","PPP20","a_becarii2.gif","a_becarii2_big.gif","a_becarii2_tb.gif","spiral",,,,,
3.0000,"Ammonia","beccarii","PPP20","a_becarii3.gif","a_becarii3_big.gif","a_becarii3_tb.gif","edge",,,,,
4.0000,"Asterigerina","pettersi","PPP1090","a_pettersi.gif","a_pettersi_big.gif","a_pettersi_tb.gif","umbilical",,,,,
5.0000,"Asterigerina","pettersi","PPP1090","a_pettersi2.gif","a_pettersi2_big.gif","a_pettersi2_tb.gif","spiral",,,,,
6.0000,"Asterigerina","pettersi","PPP1090","a_pettersi3.gif","a_pettersi3_big.gif","a_pettersi3_tb.gif","edge",,,,,

d) Save the file as'________.ctl'. For example, the above would be saved as 'fortable1.ctl'.

4. The following outlines the process of loading the '.ctl' into Oracle.

a) Open the Oracle Administrative Toolbar and click on the Data Manager.

b) Database Username_______________ Database Password______________
Database Connect String____________

Connect String for NMITA = NM01
Connect String for Calvin = CALV

c) Select Load.

d) On the line 'Control File' select 'Browse' and select the corresponding '.ctl' file.

e) Select Load.

f) If there are no errors in the '.ctl' file the table is fully loaded into the database on Oracle.

5. Restarting the Oracle Server.

a) Through Netscape go to 'http://nmita:8211'.

b) Select Webserver Manager - Oracle7 Server - 'database name(NM01 or CALV)' - shutdown = normal

c) Select startup = normal

6. The table is now accessible through the Oracle Schema Manager and Oracle Navigator.

7. Through the Oracle Schema Manager and Oracle Navigator there are Table Wizards that can also create tables.

All Oracle SQL programs are located within the Oracle Schema Manager or Oracle Navigator under Procedures and Stored Program Unites respectively.

Writing SQL programs to perform database searches by web based interfaces.
 

• 1. Open the Oracle Schema manager and select the database.
• 2. Select Procedures. From here a new procedure can be created using the ‘Create new…’ function from the toolbar or pull-down menu. Name the new procedure with a name that relates to what the search will do. For example, the procedure to list all the species of bivalves would be called ‘BIV_SPECIES’.
• 3. Write the procedure(SQL program) in a text editor.
• 4. Select the procedure previously created. Copy the procedure from the text editor and paste it into the space provided for the SQL code based procedure. All procedures start with the line ‘PROCEDURE xxxxxxxxx’, however, this may not be needed in the program if the Schema Manager creates this line automatically. The line will be directly above the space for the SQL program if created automatically when the procedure was created.
• 5. Select Apply. Compilation errors will appear if present, otherwise, the result of the procedure can be found at:
NM01 users – nmita:8001/ows-bin/owa/procedure name
Ex. – nmita:8001/ows-bin/owa/BIV_SPECIES
CALV users – porites:8301/calvin/owa/procedure name
 

• 1. Before beginning writing the SQL program have a plan as to what tables will be needed and what linkages there are between the tables.
• 2. Initially, within the program the queries are defined. For example….


PROCEDURE OTOLITH_SPECIES AS
CURSOR C1 IS
SELECT DISTINCT PAGETITL
FROM OSPECIES
ORDER BY PAGETITL;
X1 VARCHAR2 (100);

The above is from the procedure OTOLITH_SPECIES. It is the beginning of a program that will create a pull-down list of otolith species to be selected from to create species HTML pages.

A Cursor is what searches the database for information from tables. The cursor above is called ‘C1’. It is defined as selecting each row of the column ‘PAGETITL’ from the table ‘OSPECIES’. ‘SELECT DISTINCT’ eliminates repeat values within the column, in other words, if a value appears multiple times it will only be considered once. ‘ORDER BY PAGETITL’ puts the values in alphabetical order. As the cursor selects these values it must have a variable to place them into. ‘X1 VARCHAR2 (100)’ defines this variable. VARCHAR2 is the variable type (VARCHAR2 are used for everything except numbers which are defined as NUMBER). ‘(100)’ indicates that the variable can hold values up to 100 characters long.
 

• 3. To begin the program…..


BEGIN
HTP.HTMLOPEN;
HTP.HEADER(1,'<font color="red">Otolith Species Search Tool</font>');
HTP.P('<BODY BGCOLOR="WHITE">');
HTP.LINE;
HTP.FORMOPEN('OTOLITH_FORM1');
HTP.PREOPEN;

All ‘HTP.____’ are explained in any of the Oracle books or on the web at nmita:8888 and select Administration Manual.

The only tags that will change from form to form are the header value and the value in HTP.FORMOPEN. HTP.FORMOPEN defines the procedure that will process the value selected from this form. In this case, whichever otolith species is selected by the web user will be sent to the procedure OTOLITH_FORM1 where the HTML species page will be created and made availabe to the web user.
 

• 4. The following is the cursor applied to create the pull-down list.


OPEN C1;
FETCH C1 INTO X1;
HTP.FORMSELECTOPEN('PAGE', 'Select Genus and Species: ');
WHILE C1%FOUND LOOP
HTP.FORMSELECTOPTION(X1);
FETCH C1 INTO X1;
END LOOP;
CLOSE C1;

First the cursor is opened or started with the command ‘OPEN C1’. ‘FETCH C1 INTO X1’ begins the cursor as it was defined above in #2. The first value the cursor finds is placed in the allocated space for the variable X1. ‘HTP.FORMSELECTOPEN’ is the SQL tag creating the HTML tags for a pull-down menu. The variables within this tag specify first the variable to which the object selected from the pull-down list will be placed and second the text that will appear next to the pull-down list. In this example ‘PAGE’ is the variable where selected object will be placed, and ‘Select Genus and Species’ will appear next to the pull-down list. ‘WHILE C1%FOUND LOOP’ starts a loop that will run the cursor continually until the cursor does not find a value that fits C1 as defined in #2. A loop is only necessary when multiple values are needed from one column within a table. The next line puts the value within the variable X1 into the options of the pull-down list. ‘FETCH C1 INTO X1’ is written again to get the next value from the table that is being queried. Once the cursor can find no more values that fit the definition of the cursor the loop will end ‘END LOOP’ and the cursor will quit ‘CLOSE C1’.
 

• 5. The end of the program follows…


HTP.FORMSELECTCLOSE;
HTP.PRECLOSE;
HTP.FORMRESET;
HTP.FORMSUBMIT;
HTP.FORMCLOSE;
HTP.LINE;
HTP.HTMLCLOSE;
END;

The above inserts the HTML tags that close the pull-down list, the form, and the HTML page.
 

• 6. The following is the complete SQL procedure.


PROCEDURE OTOLITH_SPECIES AS
CURSOR C1 IS
SELECT DISTINCT PAGETITL
FROM OSPECIES
ORDER BY PAGETITL;
X1 VARCHAR2 (100);
BEGIN
HTP.HTMLOPEN;
HTP.HEADER(1,'<font color="red">Otolith Species Search Tool</font>');
HTP.P('<BODY BGCOLOR="WHITE">');
HTP.LINE;
HTP.FORMOPEN('OTOLITH_FORM1');
HTP.PREOPEN;
OPEN C1;
FETCH C1 INTO X1;
HTP.FORMSELECTOPEN('PAGE', 'Select Genus and Species: ');
WHILE C1%FOUND LOOP
HTP.FORMSELECTOPTION(X1);
FETCH C1 INTO X1;
END LOOP;
CLOSE C1;
HTP.FORMSELECTCLOSE;
HTP.PRECLOSE;
HTP.FORMRESET;
HTP.FORMSUBMIT;
HTP.FORMCLOSE;
HTP.LINE;
HTP.HTMLCLOSE;
END;
 

• 7. The form that creates the HTML page for the selected value from the pull-down list is as follows.


PROCEDURE OTOLITH_FORM1 (
PAGE VARCHAR2 := NULL) AS
CURSOR C1 IS
SELECT GENUSNAME, SPECIESNM, SAUTHOR, SYEAR
FROM OSPECIES
WHERE (PAGETITL = PAGE);
CURSOR C2 IS
SELECT OIMAGE.ILLUSFILE, OIMAGE.ILLUSTYPE, OIMAGE.ILLVIEW, OIMAGE.SCVALUE, OIMAGE.SCUNITS, OIMAGE.TYPSTATUS, OIMAGE.SAMPLNO, OIMAGE.BIGILLUS, OLOCAT.STRATUNIT, OLOCAT.LOCALITY, OLOCAT.CNTRY
FROM OIMAGE, OLOCAT, OSPECIES
WHERE (OSPECIES.PAGETITL = PAGE)
AND (OSPECIES.TAXON = OIMAGE.TAXON)
AND (OIMAGE.SAMPLNO = OLOCAT.SAMPLNO);
CURSOR C3 IS
SELECT OMORPHOL.CHNAME, OMORPHOL.CHSTATE
FROM OSPECIES, OMORPHOL
WHERE (OSPECIES.PAGETITL = PAGE)
AND (OSPECIES.TAXON = OMORPHOL.TAXON);
CURSOR C4 IS
SELECT DISTINCT OLOCAT.SEPOCHBOT, OLOCAT.EPOCHBOT, OLOCAT.SEPOCHTOP, OLOCAT.EPOCHTOP, OLOCAT.STRATUNIT, OLOCAT.LOCALITY, OLOCAT.CNTRY
FROM OLOCAT, OIMAGE, OSPECIES
WHERE (OSPECIES.PAGETITL = PAGE)
AND (OSPECIES.TAXON = OIMAGE.TAXON)
AND (OIMAGE.SAMPLNO = OLOCAT.SAMPLNO);
X1 VARCHAR2 (100);
X2 VARCHAR2 (100);
X3 VARCHAR2 (100);
X4 VARCHAR2 (100);
X5 VARCHAR2 (100);
X6 VARCHAR2 (100);
X7 VARCHAR2 (100);
X8 VARCHAR2 (100);
X9 VARCHAR2 (100);
X10 VARCHAR2 (100);
X11 VARCHAR2 (100);
X12 VARCHAR2 (100);
X13 VARCHAR2 (100);
X14 VARCHAR2 (100);
X15 VARCHAR2 (100);
X16 VARCHAR2 (100);
X17 VARCHAR2 (255);
X18 VARCHAR2 (100);
X19 VARCHAR2 (100);
X20 VARCHAR2 (100);
X21 VARCHAR2 (100);
X22 VARCHAR2 (100);
X23 VARCHAR2 (100);
X24 VARCHAR2 (100);
BEGIN
HTP.HTMLOPEN;
HTP.TITLE('NMITA: Otolith '||Page||' Species Page');
HTP.P('<BODY TEXT="#000000" BGCOLOR="#FFFFFF" LINK="#009191" VLINK="#551A8B" ALINK="#00CACA">');
HTP.HEADER(1,'<img SRC="nmitalog.gif" HSPACE=6
VSPACE=3 BORDER=2 height=32 width=32 align=LEFT><b><font size=+1><font color="#005EBB">
NMITA:</font><font color="#CA0065">N</font><font color="#005EBB">eogene</font>
<font color="#CA0065">M</font><font color="#005EBB">arine
B</font><font color="#CA0065">i</font><font color="#005EBB">ota of </font>
<font color="#CA0065">T</font><font color="#005EBB">ropical</font><font color="#CA0065">
A</font><font color="#005EBB">merica</font></font></b>
');
HTP.NL;
OPEN C1;
FETCH C1 INTO X1,X2,X3,X4;
HTP.P('<TR><TD>');
HTP.NL;
HTP.LINE;
IF (X3 != X4) THEN
HTP.HEADER(1, '<CENTER><I>'||X1||' '||X2||'</I> ('||X3||', '||X4||')</CENTER>');
ELSE
HTP.HEADER(1, '<CENTER><I>'||X1||' '||X2||'</I></CENTER>');
END IF;
HTP.P('<HR ALIGN=CENTER SIZE = 2 WIDTH = 40%><BR>');
HTP.NL;
HTP.P('</TD></TR>');
FETCH C1 INTO X1,X2,X3,X4;
CLOSE C1;
HTP.P('<TABLE BORDER>');
OPEN C2;
FETCH C2 INTO X5,X6,X7,X8,X9,X10,X11,X12,X13,X14,X15;
WHILE C2%FOUND LOOP
HTP.P('<TR><TD align=center><img src=http://nmita.geology.uiowa.edu/database/teleost/systemat/'||X5||' HSPACE=2 VSPACE=2 WIDTH=300></TD>');
HTP.P('<TD>');
HTP.BOLD('<I><font size=+1>Specimen details:</font></I><BR>');
HTP.P('<U>Sample Number:</U> '||X11||'');
HTP.NL;
HTP.P('<U>Type Status:</U> '||X10||'');
HTP.NL;
HTP.P('<U>Scale Bar is</U> '||X8||' '||X9||'');
HTP.NL;
HTP.P('<U>Type:</U> '||X6||'');
HTP.NL;
HTP.P('<U>Orientation:</U> '||X7||'');
HTP.NL;
HTP.P('<U>Locality:</U> '||X14||', '||X14||', '||X15||'');
HTP.P('<BR><BR><a href="database/teleost/systemat/'||X12||'"><B>- Enlarge Image -</B></a>');
HTP.P('</TD></TR>');
FETCH C2 INTO X5,X6,X7,X8,X9,X10,X11,X12,X13,X14,X15;
END LOOP;
CLOSE C2;
HTP.P('</TABLE>');
HTP.PARAGRAPH;
HTP.PARAGRAPH;
HTP.BOLD('<FONT SIZE=+1>Morphologic Description:</FONT>');
HTP.OLISTOPEN;
OPEN C3;
FETCH C3 INTO X16,X17;
WHILE C3%FOUND LOOP
HTP.P('<DD>');
HTP.BOLD(''||X16||' ');
HTP.P(''||X17||';');
HTP.P('</DD>');
FETCH C3 INTO X16,X17;
END LOOP;
CLOSE C3;
HTP.OLISTCLOSE;
HTP.BOLD('<FONT SIZE=+1>Distribution:</FONT>');
HTP.OLISTOPEN;
OPEN C4;
FETCH C4 INTO X18,X19,X20,X21,X22,X23,X24;
WHILE C4%FOUND LOOP
IF (X18=X19) THEN
HTP.P('<DD>');
HTP.BOLD(''||X18||':');
HTP.P(''||X22||', '||X23||', '||X24||';');
HTP.P('</DD>');
ELSIF (X18=X20 AND X19=X21) THEN
HTP.P('<DD>');
HTP.BOLD(''||X18||' '||X19||':');
HTP.P(''||X22||', '||X23||', '||X24||';');
HTP.P('</DD>');
ELSE
HTP.P('<DD>');
HTP.BOLD(''||X18||' '||X19||' to '||X20||' '||X21||':');
HTP.P(''||X22||', '||X23||', '||X24||';');
HTP.P('</DD>');
END IF;
FETCH C4 INTO X18,X19,X20,X21,X22,X23,X24;
END LOOP;
CLOSE C4;
HTP.OLISTCLOSE;
HTP.BR;
HTP.P('<hr ALIGN=LEFT WIDTH=100%><b>Return to:<BR>');
HTP.ULISTOPEN;
HTP.LISTITEM('<a href="">NMITA homepage</a>');
HTP.LISTITEM('<a href="database/teleost/teleomnu.htm">Fish Otoliths </a>');
HTP.LISTITEM('<a href="database/teleost/otoliths.htm">List of Fish Otolith Taxa</a></b>');
HTP.ULISTCLOSE;
HTP.HTMLCLOSE;
END;

The additional information needed to write this procedure follows.
 

• 8. The value selected from OTOLITH_SPECIES is passed into OTOLITH_FORM1 by the following.


PROCEDURE OTOLITH_FORM1 (
PAGE VARCHAR2 := NULL)

-Remember in OTOLITH_SPECIES when the variable assigned to the form was PAGE (HTP.FORMSELECTOPEN(‘PAGE’,’Select Genus and Species:’)).
 

• 9. The PAGE variable is very important. It must be used in the definition of the cursor so only data concerning the value selected from the pull-down list is retrieved from the database. The following shows how this is done.


CURSOR C1 IS
SELECT GENUSNAME, SPECIESNM, SAUTHOR, SYEAR
FROM OSPECIES
WHERE (PAGETITL = PAGE);

The above can be interpreted as follows. Values in the columns GENUSNAME, SAUTHOR, SYEAR of the table OSPECIES are selected when the value of the variable placed in PAGE, by the form OTOLITH_SPECIES, is equal to a value in the column PAGETITL of the table OSPECIES.
 

• 10. The cursors within OTOLITH_FORM1 are much more difficult. The cursors within this form call on multiple columns from multiple tables.


The following is an example of a cursor that does what was previously described.

CURSOR C2 IS
SELECT OIMAGE.ILLUSFILE, OIMAGE.ILLUSTYPE, OIMAGE.ILLVIEW, OIMAGE.SCVALUE, OIMAGE.SCUNITS, OIMAGE.TYPSTATUS, OIMAGE.SAMPLNO, OIMAGE.BIGILLUS, OLOCAT.STRATUNIT, OLOCAT.LOCALITY, OLOCAT.CNTRY
FROM OIMAGE, OLOCAT, OSPECIES
WHERE (OSPECIES.PAGETITL = PAGE)
AND (OSPECIES.TAXON = OIMAGE.TAXON)
AND (OIMAGE.SAMPLNO = OLOCAT.SAMPLNO);

When selecting multiple columns from multiple tables in the cursor each column selected must be identified first by the table to which it belongs. For example, in the above cursor the first column selected is ILLUSFILE from the table OIMAGE or as it appears in the cursor OIMAGE.ILLUSFILE. The WHERE function of cursor now becomes much more difficult as many table must now have common linkages.

WHERE (OSPECIES.PAGETITL = PAGE)
AND (OSPECIES.TAXON = OIMAGE.TAXON)
AND (OIMAGE.SAMPLNO = OLOCAT.SAMPLNO)

The first line is explained above in #9. The only difference is that now PAGETITL must be identified as a part of the table OSPECIES since there are multiple tables within the cursor. Now the row of the information needed has been identified within the table OSPECIES. The linkages to the other tables can now be made. Since the tables OSPECIES and OIMAGE both have the column TAXON that is there linkage. Likewise, the tables OIMAGE and OLOCAT use the column SAMPLNO as there linkage.
 

• 11. The program only needs as many variable defined as the cursor with the greatest number of columns being selected. However, the majority of the programs written at this point for NMITA have assigned a variable to every column selected within all cursors.
• 12. Again all HTP commands can be explained in the Oracle books in room 259 or on the web at ‘nmita:8888’ by selecting User Manual.
• 13. This is an example of HTML embedded in SQL.

HTP.P('<U>Type:</U> '||X6||'');
<U>Type:</U> is the HTML code language to output the text ‘Type:’ that is underlined. The code ‘||X6||’ is used to retrieve the contents of a variable the single quote and two vertical bars tell the program to ouput the contents and not the text X6.