Some pl/sql web procedures for dynamic page creation

create or replace procedure simple as
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Comp 353 simple example');
   htp.headClose;
   htp.bodyOpen;
   htp.line;
   htp.header(1,'Here is my demo');
   htp.line;
   htp.paragraph;
   htp.print('Today is ' || to_char(sysdate, 'DD/MM/YYYY'));
   htp.paragraph;
   htp.print('Ordinary <b>tags</b> can be used in strings');
   -- comment
   htp.print('Your oracle userid is ' || USER);
   htp.line;
   htp.bodyClose;
   htp.htmlClose;
end;
/
show errors

===================================================================

-- Table1 builds an html table from a database table

create or replace procedure table1 as
    cursor c1 is select studentid,last,first,majorid from student;
    rec c1%rowtype;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('table of students and majors');
    htp.headClose;
    htp.bodyOpen;
    htp.line;
    htp.header(1,'Table of students');
    htp.line;
    htp.paragraph;

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    htp.tableHeader('StudentID');
    htp.tableHeader('Last name');
    htp.tableHeader('First name');
    htp.tableHeader('department');

    for rec in c1 loop
    	htp.tableRowOpen;
    	htp.tableData(rec.studentid);
    	htp.tableData(rec.last);
    	htp.tableData(rec.first);
    	htp.tableData(rec.majorid);
    end loop;
    htp.tableClose;


    htp.bodyClose;
    htp.htmlClose;
end;

=====================================================================

-- table2 builds an html table from a database table
-- uses a join

create or replace procedure table2 as

    cursor c1 is select s.studentid,s.last,s.first,m.majordesc 
    	from student s, major m
    	where s.majorid = m.majorid;
    rec c1%rowtype;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('table of students and majors');
    htp.headClose;
    htp.bodyOpen;
    htp.line;
    htp.header(1,'Table of students with majors');
    htp.line;
    htp.paragraph;

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    htp.tableHeader('StudentID');
    htp.tableHeader('Last name');
    htp.tableHeader('First name');
    htp.tableHeader('department');

    for rec in c1 loop
    	htp.tableRowOpen;
    	htp.tableData(rec.studentid);
    	htp.tableData(rec.last);
    	htp.tableData(rec.first);
    	htp.tableData(rec.majordesc);
    	-- forgot htp.tableRowClose;
    end loop;
    htp.tableClose;


    htp.bodyClose;
    htp.htmlClose;
end;

====================================================================

-- param takes a string and a number and creates a 1-column table
-- of that string repeated that number of times.

create or replace procedure param(str in varchar2, num in number)
as
i number;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Parameters');
    htp.headClose;
    htp.bodyOpen;
    htp.line;
    htp.header(1,'parameter passing');
    htp.line;
    htp.paragraph;
    htp.print('str = "' || str || '", num = ' || num);
    htp.paragraph;

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    htp.tableHeader('theString');

    for i in 1..num loop
    	htp.tableRowOpen;
    	htp.tableData(str);
    	htp.tableRowClose;
    end loop;
    htp.tableClose;


    htp.bodyClose;
    htp.htmlClose;
end;

====================================================================

-- param_multi shows how to create a procedure that takes a flexible set of params
-- The four param names must be spelled exactly this way

create or replace procedure param_multi
(num_entries in number,
 name_array  in owa.vc_arr,
 value_array in owa.vc_arr,
 reserved    in owa.vc_arr)
as
i number;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Parameters');
    htp.headClose;
    htp.bodyOpen;
    htp.line;
    htp.header(1,'passing arbitrary list of parameters');
    htp.paragraph;

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    htp.tableHeader('theString');

    for i in 1..num_entries loop
    	htp.tableRowOpen;
    	htp.tableData(name_array(i));
    	htp.tableData(value_array(i));
    	htp.tableRowClose;
    end loop;
    htp.tableClose;


    htp.bodyClose;
    htp.htmlClose;
end;

=====================================================================

-- form1 is a trivial form; this version generates one parameter, theText

create or replace procedure form1
as
i number;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Form');
    htp.headClose;
    htp.bodyOpen;
    
    htp.formOpen('/indo/formcatcher', 'GET');	-- note leading /
    
    htp.print('Here is my first <b>form</b>');
    htp.paragraph;
    htp.print('Here is the service path: "' ||
    	owa_util.get_owa_service_path || '"');
    htp.paragraph;
    
    htp.formText('theText', 30, 50, 'initial value');
    htp.line;
    
    htp.formSubmit(NULL, 'proceed');

    htp.formClose;
    
    htp.bodyClose;
    htp.htmlClose;
end;

=========================================================================

-- formcatcher1 is invoked upon clicking SUBMIT on form1
-- this is the one-parameter version

create or replace procedure formcatcher(theText in varchar2)
as
i number;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Form Catcher');
    htp.headClose;
    htp.bodyOpen;
    
    htp.header(2, 'come here on click of submit button');
    
    htp.print('Here is the parameter: "' || theText || '"');
    
    htp.print('Now what?');

    
    htp.bodyClose;
    htp.htmlClose;
end;

=========================================================================

-- form2 goes to formcatcher2, which is slightly less trivial
-- note that this generates TWO parameters: theText and theButton.

create or replace procedure form2
as
i number;
begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Form');
    htp.headClose;
    htp.bodyOpen;
    
    htp.formOpen('/indo/formcatcher2', 'GET');	-- note leading /
    
    htp.print('Here is my first <b>form</b>');
    htp.paragraph;
    htp.print('Here is the service path: "' ||
    	owa_util.get_owa_service_path || '"');
    htp.paragraph;
    
    htp.formText('theText', 30, 50, 'initial value');
    htp.paragraph;
    
    htp.formSubmit('theButton', 'proceed');
    htp.br;
    htp.formSubmit('theButton', 'give up');
    htp.br;
    htp.formSubmit('theButton', 'new student');

    htp.formClose;
    
    htp.bodyClose;
    htp.htmlClose;
end;

=========================================================================

-- formcatcher2 accepts the two parameters from form2,
-- and dispatches new_student if appropriate.

create or replace procedure formcatcher2
(theText in varchar2, theButton in varchar2)
--(theButton in varchar2, theText in varchar2)
as
i number;
begin
    if lower(theButton) = 'new student'
    then 
    	new_student2;
    	return;
    end if;
    
    htp.htmlOpen;
    htp.headOpen;
    htp.title('Form Catcher, two-param version');
    htp.headClose;
    htp.bodyOpen;

    htp.header(2, 'come here on click of submit button');

    htp.print('Here is the button parameter: "' || theButton || '"');
    htp.paragraph;
    htp.print('Here is the text parameter: "' || theText || '"');

    htp.print('Now what?');


    htp.bodyClose;
    htp.htmlClose;
end;

=========================================================================

-- new_student provides text boxes for the student fields.
-- it also displays a table of all the existing records.
-- on SUBMIT, it invokes make_new_student to create the record.

create or replace procedure new_student
as
i number;
cursor c1 is select s.studentid,s.last,s.first, s.startterm, s.facultyid,
	m.majordesc || ' (' || m.majorid || ')' AS MAJOR
	from student s, major m
	where s.majorid = m.majorid
	order by s.studentid;
rec c1%rowtype;

begin
    htp.htmlOpen;
    htp.headOpen;
    htp.title('New Student');
    htp.headClose;
    htp.bodyOpen;


    htp.formOpen('/indo/make_new_student', 'GET');	-- note leading /
    htp.header(2, 'Create a new student record');
    
    htp.tableOpen;
    
    htp.tableRowOpen;
    htp.tableData('Student ID');
    htp.tableData(htf.formText('studid', 30, 50));
    htp.tableRowClose;
    htp.br;
    
    htp.tableRowOpen;
    htp.tableData('Last Name');
    htp.tableData(htf.formText('lname', 30, 50));
    htp.tableRowClose;
    htp.br;
    
    htp.tableRowOpen;
    htp.tableData('First Name');
    htp.tableData(htf.formText('fname', 30, 50));
    htp.tableRowClose;
    htp.br;
    
    htp.tableRowOpen;
    htp.tableData('start term');
    htp.tableData(htf.formText('startterm', 30, 50, 'SP03'));
    htp.tableRowClose;
    htp.br;
    
    htp.tableRowOpen;
    htp.tableData('Advisor ID');
    htp.tableData(htf.formText('advisor', 30, 50, 555));
    htp.tableRowClose;
    htp.br;
    
    htp.tableRowOpen;
    htp.tableData('Major ID');
    htp.tableData(htf.formText('major', 30, 50));
    htp.tableRowClose;
    htp.br;
    
    htp.tableClose;
    
    htp.formSubmit('theButton', 'do it');
    htp.formSubmit('theButton', 'cancel');

    htp.formClose;
        

    htp.print('Here are the existing students');

    htp.tableOpen(cattributes => 'border=2 width=60%' );
    htp.tableRowOpen;
    htp.tableHeader('StudentID');
    htp.tableHeader('Last name');
    htp.tableHeader('First name');
    htp.tableHeader('start term');
    htp.tableHeader('advisor');
    htp.tableHeader('major');		-- notice how I gave this a name above

    for rec in c1 loop
    	htp.tableRowOpen;
    	htp.tableData(rec.studentid);
    	htp.tableData(rec.last);
    	htp.tableData(rec.first);
    	htp.tableData(rec.startterm);
    	htp.tableData(rec.facultyid);
    	htp.tableData(rec.major);
    	htp.tableRowClose;
    end loop;
    htp.tableClose;

    htp.bodyClose;
    htp.htmlClose;
end;

=========================================================================

-- make_new_student is invoked by new_student to create the record.
-- Note option for CANCEL
-- also note the exception handler, and try commenting it out and trying
-- to insert a bad record.

create or replace procedure make_new_student
(
	 studid in varchar2,
	 lname  in varchar2,
	 fname  in varchar2,
	 startterm in varchar2,
	 advisor in varchar2,
	 major   in varchar2,
	 theButton in varchar2
) 
as
i number;

begin

    if lower(theButton) = 'cancel'
    then
    	form2;
        return;
    end if;
    
    insert into student values
    (studid, lname, fname, null, null, null, null, startterm, null, advisor, major, null);
    
    --commit;
    
    htp.htmlOpen;
    htp.headOpen;
    htp.title('New Student');
    htp.headClose;
    htp.bodyOpen;

    htp.header(2, 'Student record created (maybe)');
    
    htp.formOpen('/indo/new_student', 'GET');
    
    htp.formSubmit(NULL, 'ok');

    htp.formClose;
        


    htp.bodyClose;
    htp.htmlClose;

exception
    when DUP_VAL_ON_INDEX then
    	htp.htmlOpen;
    	htp.bodyOpen;
    	htp.header(1, 'insert failed!');
    	htp.print('Student ID = ' || studid);
    	htp.bodyClose;
    	htp.htmlClose;
    when others then
    	htp.htmlOpen;
    	htp.bodyOpen;
    	htp.header(1, 'insert failed!');
    	htp.print('Student ID = ' || studid);
    	htp.bodyClose;
    	htp.htmlClose;
    
   
end;

=========================================================================