create or replace package was_dsql as
  procedure dsql1;
  procedure dsql2(tcl in varchar2);
end was_dsql;
/
show errors
create or replace package body was_dsql as
procedure dsql1 as
-- This procedure displays a pull-down list of courses
  cursor c1 is
    select cno,term,lineno
    from   courses;
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Select Course');
   htp.header(1,'Display Enrolled Students');
   htp.headClose;
   htp.bodyOpen;
   htp.FormOpen(owa_util.get_owa_service_path ||
                'was_dsql.dsql2');
   htp.nl;
   htp.formSelectOpen('tcl','Select Course: ');
   -- Encode term, cno, and lineno as a string
   for c1_rec in c1 loop
     htp.formSelectOption('(' ||c1_rec.term||','||
                          c1_rec.cno||','||
                          c1_rec.lineno || ')');
   end loop;
   htp.formSelectClose;
   htp.formSubmit(null,'Display Enrolled Students');
   htp.formClose;
   htp.bodyClose;
   htp.htmlClose;
end dsql1;

procedure dsql2(tcl in varchar2) 
-- This procedure decoded the tcl value and extracts
-- term, cno and lineno values and displays a list
-- of students enrolled in this course.
as
  query varchar2(1024);
  cursor_id integer;
  p1 integer;
  p2 integer;
  trm  courses.term%type;
  cnum courses.cno%type;
  lnum courses.lineno%type;
begin
   -- Decode term, cno and lineno values
   p1 := instr(tcl,',',1,2)+1;
   p2 := instr(tcl,')',1,1)-1;
   trm := substr(tcl,2,instr(tcl,',',1)-2);
   lnum := to_number(substr(tcl,p1,p2-p1+1));
   cnum := substr(tcl,instr(tcl,',',1,1)+1,
                      p1-instr(tcl,',',1,1)-2);
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Dynamic SQL Utilities');
   htp.headClose;
   htp.bodyOpen;
   htp.line;
   htp.header(1,'Students enrolled in');
   htp.print(cnum); htp.br;
   htp.print(trm); htp.br;
   htp.print(lnum); htp.br;
   htp.line;
   -- Construct query string with place holders for
   -- cno, term and lineno values
   query := 
     'select students.sid,fname,lname ' ||
     'from enrolls,courses,students ' ||
     'where enrolls.lineno = courses.lineno and ' ||
     '      enrolls.term = courses.term and ' ||
     '      students.sid = enrolls.sid and ' ||
     '      cno = :cc and ' ||
     '      courses.term = :tt and ' ||
     '      courses.lineno = :ll';
   -- invoke utility to prepare cursor value for query
   cursor_id := 
     owa_util.bind_variables(query,
        'cc',cnum,'tt',trm,'ll',lnum); 
   -- Print open table tag
   htp.tableOpen(cborder => 'BORDER=1');
   -- Invoke utility to generate table row tags
   owa_util.cellsprint(cursor_id);
   -- Print close table tag
   htp.tableClose;
   htp.bodyClose;
   htp.htmlClose;
end dsql2;
end was_dsql;
/
show errors
