Fish: Dynamic searches of families and species

http://nmita.geology.uiowa.edu:8001/ows-bin/owa/OTOLITH_SPECIES

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;

 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;


http://nmita.geology.uiowa.edu:8001/ows-bin/owa/OTOLITH_FAMILY

 PROCEDURE      OTOLITH_FAMILY AS
CURSOR C1 IS
SELECT DISTINCT FAMILYNM
FROM OGENUS
ORDER BY FAMILYNM;
X1 VARCHAR2 (100);
 BEGIN
 HTP.HTMLOPEN;
 HTP.HEADER(1,'<font color="red">Otolith Family Search Tool</font>');
 HTP.P('<BODY BGCOLOR="WHITE">');
 HTP.LINE;
  HTP.FORMOPEN('OTOLITH_FORM');
 HTP.PREOPEN;
 OPEN C1;
 FETCH C1 INTO X1;
 HTP.FORMSELECTOPEN('FAMILY_TYPE', 'Select Family: ');
 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;

 PROCEDURE     OTOLITH_FORM (FAMILY_TYPE VARCHAR2 := NULL) AS
CURSOR C1 IS
SELECT DISTINCT OGENUS.GENUSNAME,OGENUS.GAUTHOR, OGENUS.GYEAR, OGENUS.GWEBPAGE,OSPECIES.SPECIESNM,OSPECIES.SWEBPAGE, OIMAGE.TBILLUS
FROM OGENUS, OSPECIES, OIMAGE
WHERE (OGENUS.FAMILYNM = FAMILY_TYPE)
AND (OSPECIES.GENUSNAME = OGENUS.GENUSNAME)
AND (OIMAGE.TAXON = OSPECIES.TAXON);

X1 VARCHAR2 (100);
X2 VARCHAR2 (100);
X3 VARCHAR2 (100);
X4 VARCHAR2 (100);
X5 VARCHAR2 (100);
X6 VARCHAR2 (100);
X7 VARCHAR2 (100);

BEGIN
HTP.HTMLOPEN;
HTP.TITLE('NMITA: Otolith '||FAMILY_TYPE||' Family 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;
HTP.LINE;
HTP.HEADER(1, '<CENTER>'||FAMILY_TYPE||'</CENTER>');
HTP.P('<HR ALIGN=CENTER SIZE = 2 WIDTH = 40%><BR>');
HTP.BOLD('Species Available:');
HTP.PARAGRAPH;
HTP.NL;
OPEN C1;
FETCH C1 INTO X1,X2,X3,X4,X5,X6,X7;
WHILE C1%FOUND LOOP
HTP.P('<A HREF=http://nmita.geology.uiowa.edu/database/teleost/systemat/'||X6||'> <img src=http://nmita.geology.uiowa.edu/database/teleost/systemat/'||X7||' HSPACE=2 VSPACE=2 HEIGHT=100 WIDTH=100 ALIGN=ABSCENTER></A>');
HTP.P('<I>');
HTP.ANCHOR('http://nmita.geology.uiowa.edu/database/teleost/systemat/'||X6||'',''||X1||', '||X5||'');
HTP.P('</I>');
HTP.PARAGRAPH;
FETCH C1 INTO X1,X2,X3,X4,X5,X6,X7;
END LOOP;
CLOSE C1;
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;


Last updated on April 29, 2000-afb.