Re: dynamic query on Oracle Webserver

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/06/08
Message-ID: <31b9b1e2.4915357_at_dcsun4>

On 8 Jun 1996 00:01:12 GMT, a00jcc00_at_nchc.gov.tw (Joy Chiping Chan) wrote:

>Hi,
>
>I am working on a dynamic ad hoc query using pl/sql on WEB,
>(using Oracle webserver 1.0) so far I was told ad hoc can not be 
>done with pl/sql, is it true?  I need to allow user enter their 
>request on web form and pass to database for processing and return 
>the result on html(www).
>
>Anyone have any idea to implement such flow?
>
>
>Joy Chan a00jcc00_at_nchc.gov.tw
>
>National Center for High performance Computing
>
>

Joy,

The following is a package I developed to do what you are looking for, it automates:

generation of html tables from pl/sql using dynamic sql

support for bind variables in the dynamic sql (to let shared sql be useful)

generation of drop down lists, single select lists and multiple select lists from queries

and lots of other stuff.

Hope it helps...

create or replace package owa_sql
as

pragma restrict_references( owa_sql, wnds, rnds, wnps, rnps );

Simple 'pure' function you can use to select anchors with sql
for example:
select owa_sql.anchor( '/x/y/show_emp?p_empno=' || empno, empno ), ename
from scott.emp
--
function anchor( p_url in varchar2, p_text in varchar2 ) return varchar2; pragma restrict_references( anchor, wnds, rnds, wnps, rnps );
Exception raised when a query fails to be parsed of when a non SELECT
statement is passed down
--
INVALID_QUERY exception;
Utility routine used to figure out who called you. I use it in my
standard footer routine. For example:
procedure footer
is
o varchar2(255);
n varchar2(255);
l number;
t varchar2(255);
begin
owa_sql.who_called_me( o, n, l, t );
owa_util.signature( o || '.' || n );
htp.bodyClose;
htp.htmlClose;
end footer;
creates a footer that contains the link to SHOWSOURCE without each caller
of footer having to pass down that information.

     procedure who_called_me( owner      out varchar2,
                              name       out varchar2,
                              lineno     out number,
                              caller_t   out varchar2 );

Ite = If then Else.
If you ever find yourself coding:
if ( x = 5 ) then
y := 'Yes';
else
y := 'No';
end if;
you should replace it with:
y := owa_sql.ite( x = 5, 'Yes', 'No' );

     function ite( tf in boolean, yes in varchar2, no in varchar2 ) 
     return varchar2;

shorthand for owa_util.get_cgi_env( 'SCRIPT_NAME' );

function path_to_me return varchar2;

In addition to &, <, >, and " being escaped, this turns % into %25
useful when putting a link together that includes a % sign (search
character in a LIKE clause)

function escape_url( p_url in varchar2 ) return varchar2;

See functions below for usage. This prepares a sql query and binds
variables to it. For example:
owa_sql.init( 'select * from emp where ename like :x', ':x', 'K%' );

     function init
     ( theQuery in varchar2,
       bv1Name  in varchar2 default NULL, bv1Value in varchar2 default NULL,
       bv2Name  in varchar2 default NULL, bv2Value in varchar2 default NULL,
       bv3Name  in varchar2 default NULL, bv3Value in varchar2 default NULL,
       bv4Name  in varchar2 default NULL, bv4Value in varchar2 default NULL,
       bv5Name  in varchar2 default NULL, bv5Value in varchar2 default NULL,
       bv6Name  in varchar2 default NULL, bv6Value in varchar2 default NULL,
       bv7Name  in varchar2 default NULL, bv7Value in varchar2 default NULL,
       bv8Name  in varchar2 default NULL, bv8Value in varchar2 default NULL,
       bv9Name  in varchar2 default NULL, bv9Value in varchar2 default NULL,
       bv10Name  in varchar2 default NULL, bv10Value in varchar2 default NULL,
       bv11Name  in varchar2 default NULL, bv11Value in varchar2 default NULL,
       bv12Name  in varchar2 default NULL, bv12Value in varchar2 default NULL,
       bv13Name  in varchar2 default NULL, bv13Value in varchar2 default NULL,
       bv14Name  in varchar2 default NULL, bv14Value in varchar2 default NULL,
       bv15Name  in varchar2 default NULL, bv15Value in varchar2 default NULL,
       bv16Name  in varchar2 default NULL, bv16Value in varchar2 default NULL,
       bv17Name  in varchar2 default NULL, bv17Value in varchar2 default NULL )
     return integer;

Many forms of cells_from_query. First parm is always a query or
an open cursor (from owa_sql.init above).
in its simplest form:
--
owa_sql.cells_from_query( 'select * from emp' );
--
will return an HTML table (you must OPEN and CLOSE the table yourself)
in the form <tr><td>data</td><td>data</td></tr>......
--
A more 'complex' one might be:
owa_sql.cells_from_query(
owa_sql.init( 'select * from emp, dept where emp.deptno=dept.deptno
and dept.deptno = :d', ':d', 10 ), 100, 'Yes' );
--
would display the first 100 rows of emps in dept 100 and format numbers
with commas and right justify them.
--
use the max_rows to limit the number of rows displayed (defaults to 100)
set p_format_numbers to any NON-NULL value to have any field that
is an oracle number right justified with commas and rounded off to 2
decimal places (if it has decimals)

     procedure cells_from_query( p_theQuery            in varchar2,
                                 p_max_rows            in number default 100,
                                 p_format_numbers    in varchar2 default NULL );
     
     procedure cells_from_query( p_theCursor         in integer, 
                                 p_max_rows          in number  default 100,
                                 p_format_numbers    in varchar2 default NULL );

More involved cells_from_query allows you to slice and dice a result
set. I use it to page up and down thru queries. In addition to the
above you can tell it what row to start printing at (eg: skip the first
25 records and then print the next 25 records) and it will tell you
if there are more rows to print. You would save the offset within the
query in a hidden field to paginate. procedure cells_from_query( p_theQuery in varchar2, p_more_data out boolean, p_max_rows in number default 100, p_skip_rec in number default 0, p_format_numbers in varchar2 default NULL );

     procedure cells_from_query( p_theCursor         in integer, 
                                 p_more_data            out boolean,
                                 p_max_rows          in number  default 100,
                                 p_skip_rec             in number default 0,
                                 p_format_numbers    in varchar2 default NULL );

Create a multi select list, a drop down list or a single select list.
You send it a query that selects out in ORDER:
COLUMN 1 - What your procedure will get back
COLUMN 2 - What your user will see in the list box
COLUMN 3 - a null or non-null field. If the field is non-null,
the current row will be flagged as SELECTED in the list box
--
For example: create drop down list that shows usernames, returns the
user_id to the procedure and by default selects SYSTEM for you:
--
owa_sql.list_from_query( 'select username, userid,
decode(username,''SYSTEM'',1,NULL)
from all_users
order by username',
'p_my_list',
1 );
--
Or, using a more dynamic approach:
--
owa_sql.list_from_Query(
owa_sql.init( 'select object_name, object_id, null
from all_objects
where owner = :owner
order by object_name', ':owner', theUserName ),
'p_my_other_list',
5,
TRUE );
--
Which dynamically binds the current value of the pl/sql variable
theUserName with :owner

     procedure list_from_query( p_theCursor in integer,
                                p_cname     in varchar2,
                                p_nsize     in number,
                                p_multiple     in boolean default FALSE );
 
     procedure list_from_query( p_theQuery  in varchar2,
                                p_cname     in varchar2,
                                p_nsize     in number,
                                p_multiple     in boolean default FALSE );
 

-- procedure for displaying a Date field in html and allowing the user
-- to pick an arbritrary date.  This procedure uses 3 html input fields
-- to get the DAY, MONTH, and YEAR fields.  The procedure you write that
-- recieves the input should have an input variable of type
-- owa_sql.datetype.
-- 
-- Example usage:
--
-- procedure show_form
-- is
-- begin
--    htp.formOpen( 'procedure_2' );
--    .....
--    owa_sql.select_date( 'my_date_field', sysdate );
--    .....
--    htp.formClose;
-- end;
--
-- procedure procedure_2( my_date_field in owa_sql.datetype )
-- is
-- begin
--    htp.p( 'You chose the date ' || owa_sql.todate( my_date_field ) );

-- end;

--
    type dateType is table of varchar2(10) index by binary_integer;
    procedure select_date( p_name in varchar2, p_date in date default sysdate);
    function todate( p_dateArray in dateType ) return date;

    empty_date    owa_sql.datetype;

end owa_sql;
/
show errors
--
--
create or replace package body owa_sql
as
--
--
function ite( tf in boolean, yes in varchar2, no in varchar2 ) return varchar2
is
begin
    if ( tf ) then
        return yes;
    else
        return no;
    end if;
end ite;
--
--
procedure bv( c in integer, n in varchar2, v in varchar2 )
is
begin
    if ( n is NOT NULL ) then
        dbms_sql.bind_variable( c, n, v );
    end if;
end bv;
--
--
function tochar( d in number, f in varchar2 ) return varchar2
is
begin
    return nvl(ltrim(to_char(d,f)), '(null)');
end tochar;
--
--
--
procedure format_cell( columnValue in varchar2, format_numbers in varchar2 )
is
    dummy    number;
begin
    if ( format_numbers is NULL ) then
        htp.tableData( columnValue );
    else
        dummy := to_number( columnValue );
        if ( trunc(dummy) = dummy ) then
            htp.tableData( tochar(dummy,'999,999,999,999'), 'right' );
        else
            htp.tableData( tochar(dummy,'999,999,990.99'), 'right' );
        end if;
    end if;
exception
    when others then
        htp.tableData( nvl(columnValue,'(null)') );
end format_cell;
--
--
function bind_outputs( p_theCursor in integer ) return number
is
    columnValue        varchar2(1);
    colCnt            number default 0;
    status            integer;
begin
    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
            colCnt := colCnt + 1;
        exception
            when others then
                if ( sqlcode = -1007 ) then
                    exit;
                else
                    raise;
                end if;
        end;
    end loop;
    status := dbms_sql.execute(p_theCursor);
    return colCnt;
end bind_outputs;
--
--
function escape_url( p_url in varchar2 ) return varchar2
is
begin
    return replace( htf.escape_sc(p_url), '%', '%25' );
end escape_url;

function path_to_me return varchar2
is
    o    varchar2(50);
    n    varchar2(50);
    l    number;
    t    varchar2(50);
begin
    who_called_me( o, n, l, t );

    return owa_util.get_cgi_env( 'SCRIPT_NAME' ) || '/' || n;
end path_to_me;


function anchor( p_url in varchar2, p_text in varchar2 ) return varchar2
is
begin
    return '<a href=' || p_url || '>' || p_text || '</a>';
end anchor;


procedure who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2 )
as
    call_stack  varchar2(4096) default dbms_utility.format_call_stack;
    n           number;
    found_stack BOOLEAN default FALSE;
    line        varchar2(255);
    cnt         number := 0;
begin
--
    loop
        n := instr( call_stack, chr(10) );
        exit when ( cnt = 3 or n is NULL or n = 0 );
--
        line := substr( call_stack, 1, n-1 );
        call_stack := substr( call_stack, n+1 );
--
        if ( NOT found_stack ) then
            if ( line like '%handle%number%name%' ) then
                found_stack := TRUE;
            end if;
        else
            cnt := cnt + 1;
            -- cnt = 1 is ME
            -- cnt = 2 is MY Caller
            -- cnt = 3 is Their Caller
            if ( cnt = 3 ) then
                lineno := to_number(substr( line, 13, 6 ));
                line   := substr( line, 21 );
                if ( line like 'pr%' ) then
                    n := length( 'procedure ' );
                elsif ( line like 'fun%' ) then
                    n := length( 'function ' );
                elsif ( line like 'package body%' ) then
                    n := length( 'package body ' );
                elsif ( line like 'pack%' ) then
                    n := length( 'package ' );
                else
                    n := length( 'anonymous block ' );
                end if;
                caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
                line := substr( line, n );
                n := instr( line, '.' );
                owner := ltrim(rtrim(substr( line, 1, n-1 )));
                name  := ltrim(rtrim(substr( line, n+1 )));
            end if;
        end if;
    end loop;
end;
--
--
function init
(     theQuery in varchar2,
    bv1Name  in varchar2 default NULL, bv1Value in varchar2 default NULL,
    bv2Name  in varchar2 default NULL, bv2Value in varchar2 default NULL,
    bv3Name  in varchar2 default NULL, bv3Value in varchar2 default NULL,
    bv4Name  in varchar2 default NULL, bv4Value in varchar2 default NULL,
    bv5Name  in varchar2 default NULL, bv5Value in varchar2 default NULL,
    bv6Name  in varchar2 default NULL, bv6Value in varchar2 default NULL,
    bv7Name  in varchar2 default NULL, bv7Value in varchar2 default NULL,
    bv8Name  in varchar2 default NULL, bv8Value in varchar2 default NULL,
    bv9Name  in varchar2 default NULL, bv9Value in varchar2 default NULL, 
    bv10Name  in varchar2 default NULL, bv10Value in varchar2 default NULL,
    bv11Name  in varchar2 default NULL, bv11Value in varchar2 default NULL,
    bv12Name  in varchar2 default NULL, bv12Value in varchar2 default NULL,
    bv13Name  in varchar2 default NULL, bv13Value in varchar2 default NULL,
    bv14Name  in varchar2 default NULL, bv14Value in varchar2 default NULL,
    bv15Name  in varchar2 default NULL, bv15Value in varchar2 default NULL,
    bv16Name  in varchar2 default NULL, bv16Value in varchar2 default NULL,
    bv17Name  in varchar2 default NULL, bv17Value in varchar2 default NULL )
return integer
is
    theCursor    integer;
begin
    if ( upper( substr( ltrim( theQuery ), 1, 6 ) ) <> 'SELECT' ) then
        raise INVALID_QUERY;
    end if;
--
    theCursor := dbms_sql.open_cursor;
    dbms_sql.parse( theCursor, theQuery, dbms_sql.native );
--
    bv( theCursor, bv1Name, bv1Value );
    bv( theCursor, bv2Name, bv2Value );
    bv( theCursor, bv3Name, bv3Value );
    bv( theCursor, bv4Name, bv4Value );
    bv( theCursor, bv5Name, bv5Value );
    bv( theCursor, bv6Name, bv6Value );
    bv( theCursor, bv7Name, bv7Value );
    bv( theCursor, bv8Name, bv8Value );
    bv( theCursor, bv9Name, bv9Value );
    bv( theCursor, bv10name, bv10Value );
    bv( theCursor, bv11name, bv11Value );
    bv( theCursor, bv12name, bv12Value );
    bv( theCursor, bv13name, bv13Value );
    bv( theCursor, bv14name, bv14Value );
    bv( theCursor, bv15name, bv15Value );
    bv( theCursor, bv16name, bv16Value );
    bv( theCursor, bv17name, bv17Value );
--
    return theCursor;
end init;
--
--
procedure cells_from_query( p_theQuery            in varchar2,
                            p_more_data            out boolean,
                            p_max_rows            in number default 100,
                            p_skip_rec             in number default 0,
                            p_format_numbers    in varchar2 default NULL )
is
    l_theCursor    integer default init(p_theQuery);
begin
    cells_from_query( l_theCursor, p_more_data, p_max_rows, p_skip_rec,
p_format_numbers );
end cells_from_query;
--
procedure cells_from_query( p_theQuery            in varchar2,
                            p_max_rows            in number default 100,
                            p_format_numbers    in varchar2 default NULL )
is
    l_more_data    boolean;
begin
    cells_from_query( p_theQuery,l_more_data,p_max_rows, 0, p_format_numbers );
end;
    
procedure cells_from_query( p_theCursor         in integer, 
                            p_max_rows          in number  default 100,
                            p_format_numbers    in varchar2 default NULL )
is
    l_more_data    boolean;
begin
    cells_from_query( p_theCursor,l_more_data,p_max_rows, 0, p_format_numbers );
end;
--
--
procedure cells_from_query( p_theCursor         in integer, 
                            p_more_data            out boolean,
                            p_max_rows          in number   default 100,
                            p_skip_rec             in number   default 0,
                            p_format_numbers    in varchar2 default NULL )
is
    columnValue varchar2(2000);
    colCnt      number default 0;
    tmpcursor    number default p_theCursor;
    reccnt        number default 0;
begin
--
    colCnt := bind_outputs( p_theCursor );
    p_more_data := FALSE;
--
    loop
        exit when ( reccnt-p_skip_rec = p_max_rows OR 
                    dbms_sql.fetch_rows(p_theCursor) <= 0 );
        reccnt := reccnt + 1;
        if ( reccnt >= p_skip_rec ) then
            htp.tableRowOpen;
            for i in 1 .. colCnt loop
                dbms_sql.column_value( p_theCursor, i, columnValue );
                format_cell( columnValue, p_format_numbers );
            end loop;
            htp.tableRowClose;
            if ( reccnt-p_skip_rec = p_max_rows ) then
                p_more_data := TRUE;
            end if;
        end if;
    end loop;
    dbms_sql.close_cursor(tmpCursor);
exception
    when others then
        if dbms_sql.is_open(p_theCursor) then
            dbms_sql.close_cursor(tmpCursor);
        end if;
        raise;
end cells_from_query;
--
--
--
procedure list_from_query( p_theCursor in integer,
                           p_cname     in varchar2,
                           p_nsize     in number,
                           p_multiple     in boolean default FALSE )
is
    colCnt        number;
    value        varchar2(2000);
    visible        varchar2(2000);
    selected    varchar2(2000);
begin
    colCnt := bind_outputs( p_theCursor );
--
    htp.formSelectOpen( cname => p_cname, 
                        nsize => p_nsize, 
                        cattributes => ite( p_multiple,'multiple',NULL) );
    loop
        exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
        dbms_sql.column_value( p_theCursor, 1, value );
        dbms_sql.column_value( p_theCursor, 2, visible );
        dbms_sql.column_value( p_theCursor, 3, selected );
        htp.formSelectOption( cvalue => visible, 
                              cselected => ite( selected IS NULL, NULL,
'SELECTED' ),
                              cattributes => 'value="' || value || '"' );
    end loop;
    htp.formSelectClose;
end list_from_query;
--
procedure list_from_query( p_theQuery in varchar2,
                           p_cname     in varchar2,
                           p_nsize     in number,
                           p_multiple     in boolean default FALSE )
is
    theCursor    integer default init( p_theQuery );
begin
    list_from_query( theCursor, p_cname, p_nsize, p_multiple );
end list_from_query;
--
--

procedure select_date( p_name in varchar2, p_date in date default sysdate)
is
    l_day        number default to_number(to_char(p_date,'DD'));
    l_mon        number default to_number(to_char(p_date,'MM'));
    l_year        number default to_number(to_char(p_date,'YYYY'));
begin
    htp.formSelectOpen( cname => p_name, nsize => 1 );
    for i in 1 .. 31 loop
        htp.formSelectOption( cvalue => i, 
                              cselected => ite( i=l_day, 'SELECTED', NULL ),
                              cattributes => 'value="' || 
                                        ltrim(to_char(i,'00')) || '"' );
    end loop;
    htp.formSelectClose;
    htp.p( '-' );
    htp.formSelectOpen( cname => p_name, nsize => 1 );
    for i in 1 .. 12 loop
        htp.formSelectOption( cvalue => to_char( to_date( i, 'MM' ), 'MON' ), 
                              cselected => ite( i=l_mon, 'SELECTED', NULL ),
                              cattributes => 'value="' || 
                                        ltrim(to_char(i,'00')) || '"' );
    end loop;
    htp.formSelectClose;
    htp.p( '-' );
    htp.formSelectOpen( cname => p_name, nsize => 1 );
    for i in l_year-5 .. l_year+5 loop
        htp.formSelectOption( cvalue => i,
                              cselected => ite( i=l_year, 'SELECTED', NULL ),
                              cattributes => 'value="' || 
                                        ltrim(to_char(i,'0000')) || '"' );
    end loop;
    htp.formSelectClose;
end;

function todate( p_dateArray in dateType ) return date
is
begin
    return to_date( p_dateArray(1) || '-' || p_dateArray(2) || '-' ||
                    p_dateArray(3), 'DD-MM-YYYY' );
exception
    when no_data_found then
        return NULL;
    when others then
        return last_day( to_date( p_dateArray(2) || '-' || p_dateArray(3), 
                                'MM-YYYY' ) );
end todate;

end owa_sql;
/
show errors





Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
                         
http://govt.us.oracle.com   -- Check out our web site!  Brand new, uses Oracle Web Server and Database


-------------------

statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation

Received on Sat Jun 08 1996 - 00:00:00 CEST

This message: [ Message body ]
Next message: Thomas J. Kyte: "Re: Date-format"
Previous message: Joy Chiping Chan: "dynamic query on Oracle Webserver"
In reply to: Joy Chiping Chan: "dynamic query on Oracle Webserver"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message