Re: Q - OWS21: owa_util.cellsprint with parameters in query?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/16
Message-ID: <33d1096e.16040855@newshost>#1/1

Actually, I think you meant tableprint, not cellsprint when you said "whole chunk, little control".

Cellsprint is pretty flexible. Here is a small sample that shows how to use bind variables with cellsprint to page up/dn through a dynamic result set

( the only doc is on the samples page, you can goto http://govt.us.oracle.com/ and follow the link to downloadable utilities. the owa extensions on that page are the cellsprint, list_from_query routines found in ows2.1. Full doc and online demos are there. the names of the routines changed, on govt.us.oracle.com they are in a package called owa_sql and the names are what I called them. in ows2.1 they are in owa_util with different names but same functions)..

create or replace
procedure page_up_dn( p_search_str in varchar2 default NULL,

                      p_start      in number   default 0 )
is
    l_more_data        boolean default FALSE;

begin  

    htp.tableOpen( 'border' );

• owa_util.bind_variables allows you to put upto 17 named
• bind variables in a query. I use :x1, :x2, :x3 and so on
• as bind variable placeholders. bind_variables takes a
• variable number of inputs, you send it a query and then
• a list of bind variable name, bind variable value...
• max rows controls the 'size' of the page
• format_number: should anything that looks
• like a number have commas in it? Yes or NULL
• skip_rec: how many records to skip over (what page are we on?)
• more_data: a flag that will let you know if
• we are showing the last row or not

    owa_util.cellsprint(

        owa_util.bind_variables( 'select rownum, a.*
                                    from all_objects a
                                   where object_name like :x1
                                      or :x1 is NULL',
                                   'x1', upper('%'||p_search_str||'%') ),

         p_max_rows => 15,
         p_format_numbers => 'Yes',
         p_skip_rec => p_start,
         p_more_data => l_more_data );
 

    htp.tableClose;

• now, create a pair of small forms that save the 'state' (the
• inputs to the query.

    if ( p_start > 0 ) then

        htp.formOpen( 'page_up_dn' );
        htp.formHidden( 'p_start', p_start-15 );
        htp.formHidden( 'p_search_str', htf.escape_sc(p_search_str) );
        htp.formSubmit( cvalue => 'Previous 15' );
        htp.formClose;

    end if;
    if ( l_more_data ) then

        htp.formOpen( 'page_up_dn' );
        htp.formHidden( 'p_start', p_start+15 );
        htp.formHidden( 'p_search_str', htf.escape_sc(p_search_str) );
        htp.formSubmit( cvalue => 'Next 15' );
        htp.formClose;

    end if;
end;
/

On Wed, 16 Jul 1997 11:39:21 +0800, Terrence Wong <mingjun_at_singnet.com.sg> wrote:

>Gerhard Moeller wrote:
>>
>> Hi,
>>
>> I would like to have a user enter some values, then build a query with
>> those values and finally print out a Table with the results of this
>> query. Seems to be easy up to here. But as the table can be quite big,
>> I would like to use owa_util.cellsprint for printing only 20 records
>> at once, then waiting for the next 20 or showing the previous 20.
>>
>> Unfortunatelly something like
>>
>> owa_util.cellsprint( 'select * from test.foo where test.foo.bar like
>> searchstring', 20, 'Yes', p_start, l_more_data );
>>
>> with searchstring beeing a PL/SQL variable does not work!
>>
>> It seems to me that cellsprint only accepts static queries, and I did
>> not find cellsprint in the documentation of Ows21.
>>
>> Do I have to write a PL/SQL procedure with cursors?
>>
>> Thanks for any hint, Gerhard.
>> --
>> Dipl. Inform. Gerhard Möller -- Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.DE
>>
>> OFFIS | | | | | | Tel.: 0441/9722-122
>> Escherweg 2 | | | | | | Sekr.: 0441/9722-113 oder -101
>> D-26121 Oldenburg |O|F|F|I|S| Fax: 0441/9722-102
>I think you are better of with PL/SQL.
>I have a similar issue where i used a cursor to extract all required
>fields and throw them onto a HTML table. Wrote a routine to limit the
>number of records displayed onto a page. Has previous and next
>capability.
>
>cellsprint prints the whole chunk, little control.
>
>Hope this enlightens.
>Terrence Wong
>Systems Developer

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 16 1997 - 00:00:00 CDT