OWA_UTIL: bind_variables

The bind_variables function in the OWA_UTIL package prepares a SQL query (binding variables to it, if any), and stores the output in an opened cursor. The integer returned by the function is a unique identifier for that cursor. The syntax is as follows:

  n := owa_util.bind_variables(
    theQuery, 
    bv1Name, bv1Value, 
    ...
    bv25Name, bv25Value, bv25Name);

where theQuery is a string variable containing a SQL query, possibly with bind variables, and the remaining parameters are the bind variable names and their values (there is a maximum limit of 25 such pairs).

The results of the query can be printed using the cellsprint procedure.

  owa_util.cellsprint(
    p_theCursor in integer
    p_max_rows in number DEFAULT 100
    p_format_numbers in varchar2 DEFAULT NULL);

This procedure generates an HTML table from the output of a SQL query. SQL atomic data items are mapped to HTML cells and SQL rows to HTML rows. The code to begin and end the HTML table must be written separately. The p_theCursor is the output of a call made to the bind variables procedure.

There are 4 versions of this procedure; please see online documentation.

• Web page with a pull down list of courses.
• The user may select one of the courses
• Upon submission of the form, the second procedure prints the students enrolled in the particular course.
• The query to obtain the enrolled students is assigned to a string variable and has three bind variables.

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
  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: ');
   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) as
  query varchar2(1024);
  cursor_id integer;
  p1 integer;
  p2 integer;
  trm  courses.term%type;
  cnum courses.cno%type;
  lnum courses.lineno%type;
begin
   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;

   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';
   cursor_id := 
     owa_util.bind_variables(query,
        'cc',cnum,'tt',trm,'ll',lnum); 
   htp.tableOpen(cborder => 'BORDER=1');
   owa_util.cellsprint(cursor_id);
   htp.tableClose;
   htp.bodyClose;
   htp.htmlClose;
end dsql2;
end was_dsql;
/
show errors

Notice that explicit statements are written to open and close the HTML table, as the cellsprint procedure generates only the code for the rows of the HTML table.