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

From: William Robertson <>
Date: Wed, 20 Aug 2008 03:15:54 -0700 (PDT)
Message-ID: <>

On Aug 17, 1:34 am, "Dereck L. Dietz" <> wrote:
> "gym dot scuba dot kennedy at gmail" <> wrote in
> messagenews:a%Jpk.63$UX.40_at_trnddc03...
> > "Dereck L. Dietz" <> wrote in message
> >news:WgIpk.23108$
> >> Oracle
> >> 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
> >> 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;


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.


    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 );
END wr_test;

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:


    TYPE date_tt IS TABLE OF DATE;

    PROCEDURE update_dates

        ( p_dates date_set
        , p_value dates_table.someval%TYPE )
        v_dates date_tt;
             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;

    END update_dates;
END wr_test;


    cursor_param wr_test.date_set;
    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

Original text of this message