Re: Passing queries from procedure call in a script ... possible?

On Aug 17, 1:34 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote in
> messagenews:a%Jpk.63$UX.40_at_trnddc03...
>
>
>
> > "Dereck L. Dietz" <diet..._at_ameritech.net> wrote in message
> >news:WgIpk.23108$Ri.20173_at_flpi146.ffdc.sbc.com...
> >> Oracle 10.2.0.3.0
>
> >> Windows 2003 Server
>
> >> UPDATE_PROCEDURE will update the DATES_TABLE but could be updated from
> >> different sources.
>
> >> How do I make the UPDATE_PROCEDURE able to accept different data sets
> >> retrieved from different cursors when the UPDATE_PROCEDURE procedure is
> >> called from different SQL scripts.
>
> >> I'm not experienced with reference cursors so I'm not sure if what I'm
> >> trying to do can be done or not. Would it be reference cursors, cursor
> >> variables or what?
>
> >> This UPDATE_PROCEDURE will be a packaged procedure which could be called
> >> by different processes to update the same table. However, each process
> >> will have different tables which create the data set to use to update the
> >> DATES_TABLE.
>
> >> Currently this is all done in scripts and, for each process, there is a
> >> duplication of code for the update of the DATES_TABLE.
>
> >> Can a cursor be passed from a procedure call in a script and if so, is
> >> there any examples anywhere around?
>
> >> Thanks.
>
> What I need to pass would be a cursor or query.  I know I can do it from
> within a package but I need to have it so the procedure can be called from
> an SQL script (using EXECUTE package.update_procedure(...) ).  I'd like to
> be able to call the package procedure which processes the data to be able to
> be called from multiple processes instead of having to write a procedure for
> each process.

I thought I'd have a go at this using a strongly typed ref cursor.

Test table and data:

CREATE TABLE dates_table
(datecol DATE NOT NULL, someval VARCHAR2(20));

INSERT INTO dates_table
SELECT DATE '1999-12-31' + ROWNUM, 'Banana' FROM dual CONNECT BY ROWNUM < 10000;

Package:

Note the ref cursor type that returns a specific record type rather than just a generic SYS_REFCURSOR, because the package code will need to fetch the cursor into a record collection, and it will be more robust if we tell the calling code what record type to pass. It would work with SYS_REFCURSOR but we couldn't trust it to have the right number of columns.

CREATE OR REPLACE PACKAGE wr_test AS

    TYPE date_rec IS RECORD (dt DATE);
    TYPE date_set IS REF CURSOR RETURN date_rec;

    PROCEDURE update_dates

        ( p_dates date_set
        , p_value dates_table.someval%TYPE );

The update_dates procedure is passed a set of dates in a ref cursor, and updates every corresponding record in dates_table with the value in p_value. This is probably not the exact requirement but it illustrates the approach:

CREATE OR REPLACE PACKAGE BODY wr_test
AS

    TYPE date_tt IS TABLE OF DATE;

    PROCEDURE update_dates

        ( p_dates date_set
        , p_value dates_table.someval%TYPE )
    IS
        v_dates date_tt;
    BEGIN
        LOOP
             FETCH p_dates BULK COLLECT INTO v_dates LIMIT 100;

             FORALL i IN v_dates.FIRST.. v_dates.LAST
             UPDATE dates_table d
             SET    d.someval = p_value
             WHERE  d.datecol = v_dates(i);

             -- For debugging only, not for production code...
             DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');

             EXIT WHEN v_dates.count < 100;
        END LOOP;

Test:

DECLARE
    cursor_param wr_test.date_set;
BEGIN
    OPEN cursor_param FOR

         SELECT DATE '2000-01-02' dt FROM dual
         UNION ALL
         SELECT DATE '2003-04-05' FROM dual;

    wr_test.update_dates(cursor_param, 'Aardvark'); END; Note that we have to define a cursor explicitly in PL/SQL - we can't use an SQL "CURSOR(SELECT...)" expression inline in the procedure call, as we can for functions (I didn't realise that until I tried.) For example, we can't do this:

EXECUTE wr_test.update_dates(CURSOR(SELECT SYSDATE dt FROM dual));

The alternative would be to pass a string containing a SELECT statement, as I think the AskTom thread demonstrates. Received on Wed Aug 20 2008 - 05:15:54 CDT