Re: Dynamic Cursors

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 4 Feb 2002 23:08:25 +0300
Message-ID: <a3mph9$ktv$1@babylon.agtel.net>

Looks like a case for weak ref cursor. If I understood correctly, you are trying to pass the *name* of the column by which the result set should be ordered into the procedure. This can be achieved by using weak ref cursor:

procedure .......
is
-- define a record type into which we will be fetching type Customer_Listing_Type is record (customerid t_customers.customerid%type,

                                                    contactlastname
t_customers.contactlastname%type,
                                                    contactfirsttname
t_customers.contactfirstname%type,
                                                    phonenumber t_customers.phonenumber%type,
                                                    cell t_customers.cell%type,
                                                    emailaddress t_customers.emailaddress%type,
                                                    rate t_customers.rate%type);

-- define a weak ref cursor...
type c_Customer_Listing_Type is ref cursor; -- ...and an instance of both cursor and row record c_Customer_Listing c_Customer_Listing_Type; c_Customer_Listing_Row Customer_Listing_Type;

begin

• open the cursor for query with appended column name to order by (use first column
• if i_order is NULL to keep the query valid). open c_Customer_Listing for 'SELECT CUSTOMERID, CONTACTLASTNAME, CONTACTFIRSTNAME, PHONENUMBER, CELL, EMAILADDRESS, RATE FROM T_CUSTOMERS ORDER BY '||nvl(i_order,'1'); loop
  • fetch rows in loop fetch c_Customer_Listing into c_Customer_Listing_Row;
  • leave the loop when no more rows to process exit when c_Customer_Listing%NOTFOUND;
  • process your row here end loop;
• close cursor and free associated memory. If you are about to call the
• procedure more than once in one session, you may also want to
• ALTER SESSION SET SESSION_SHARED_CURSORS=100
• or more to cache session cursors - increases memory usage but *greatly*
• improves performance if cursors are reused within session. close c_Customer_Listing; exception when others then
  • don't leave anything to chance - and chances are that somebody may call
  • this procedure with invalid column name in I_ORDER parameter. Cursor
  • will not open in this case and an exception will be thrown indicating that
  • query was not parsed. dbms_output.put_line('Oops. Exception: '||sqlerrm); if c_Customer_Listing%ISOPEN then
    • unlikely, but just in case... otherwise you may run out of available cursors. close c_Customer_Listing; end if; end; /

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3C59AD08.8EB99765_at_exesolutions.com...
> Your assumption that your other code is irrelevant is incorrect. Please post it
> all if you want help.
>
> But you a quick note ... you are only selecting from a single table there is no
> reason to type 'T_CUSTOMERS.RATE' when you could just type 'RATE'.
>
> And are you really sure you need to do an ORDER BY in your cursor? It is very
> expensive if the processing does not need to be ordered.
>
> Daniel Morgan
>
>
>
> Tim Savage wrote:
>
> > I am having trouble understanding why my code is not working.
> >
> > PROCEDURE P_CUSTOMER_LISTING(
> > i_order IN VARCHAR2,
> > o_Customer_ID OUT tblCustomerID,
> > o_LastName OUT tblLastName,
> > o_FirstName OUT tblFirstName,
> > o_Phone OUT tblPhoneNumber,
> > o_Cell OUT tblCell,
> > o_Email OUT tblEmail,
> > o_Rate OUT tblRate)
> >
> > IS
> >
> >   CURSOR c_Customer_Listing IS
> >     SELECT T_CUSTOMERS.CUSTOMERID, T_CUSTOMERS.CONTACTLASTNAME,
> > T_CUSTOMERS.CONTACTFIRSTNAME, T_CUSTOMERS.PHONENUMBER, T_CUSTOMERS.CELL,
> > T_CUSTOMERS.EMAILADDRESS, T_CUSTOMERS.RATE
> >     FROM T_CUSTOMERS
> >     ORDER BY i_order;
> >
> > BEGIN
> >
> > NULL; -- there is more code but I think that it is irelavent
> >
> > END P_CUSTOMER_LISTING;
> >
> > I am trying to pass a variable to the cursor (i_order). However when I run
> > the code it acts as if i_order is NULL.  is this possible to pass the INPUT
> > i_order to the cursor?
> >
> > Thank you,
> > Tim Savage
>

Received on Mon Feb 04 2002 - 14:08:25 CST