Re: Passing queries from procedure call in a script ... possible?
Date: Wed, 20 Aug 2008 03:15:54 -0700 (PDT)
Message-ID: <0457f997-e901-4680-95c3-680bc7512481@c65g2000hsa.googlegroups.com>
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 );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:
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;
END update_dates;
END wr_test;
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