+ Reply to Thread
Results 1 to 7 of 7

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

  1. Passing queries from procedure call in a script ... possible?

    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.



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


    "Dereck L. Dietz" wrote in message
    news:WgIpk.23108$Ri.20173@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.
    >
    >

    Not sure I understnad the question. Can you give an example?
    Do you mean (something like this, ignore syntaxs errors):

    pkg_mypackage.update_procedure(i_dateid in number, i_date in date) as
    begin
    update dates_table set thedate=i_date where dateid=i_dateid;
    end;
    /

    You could call that procedure from any "source".
    eg
    in a procedure
    ...
    for l_rec in (select dateid ,thedate from one_table) loop
    pkg_mypackage.update_procedure(l_rec.dateid,l_rec.thedate);
    end loop;
    ....

    and in a different procedure:
    ...
    for l_rec in (select a_dateid ,anothedate from two_table) loop
    pkg_mypackage.update_procedure(l_rec.adateid,l_rec.anothedate);
    end loop;
    ....

    Your question is not clear.
    Jim



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


    "gym dot scuba dot kennedy at gmail" wrote in
    message news:a%Jpk.63$UX.40@trnddc03...
    >
    > "Dereck L. Dietz" wrote in message
    > news:WgIpk.23108$Ri.20173@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.
    >>
    >>

    > Not sure I understnad the question. Can you give an example?
    > Do you mean (something like this, ignore syntaxs errors):
    >
    > pkg_mypackage.update_procedure(i_dateid in number, i_date in date) as
    > begin
    > update dates_table set thedate=i_date where dateid=i_dateid;
    > end;
    > /
    >
    > You could call that procedure from any "source".
    > eg
    > in a procedure
    > ...
    > for l_rec in (select dateid ,thedate from one_table) loop
    > pkg_mypackage.update_procedure(l_rec.dateid,l_rec.thedate);
    > end loop;
    > ...
    >
    > and in a different procedure:
    > ...
    > for l_rec in (select a_dateid ,anothedate from two_table) loop
    > pkg_mypackage.update_procedure(l_rec.adateid,l_rec.anothedate);
    > end loop;
    > ...
    >
    > Your question is not clear.
    > Jim
    >


    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.




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

    Dereck L. Dietz wrote:
    > 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.


    Post the full and complete version and the DDL that creates the procedure.

    Not one of us can see your monitor or read your mind.
    --
    Daniel A. Morgan
    Oracle Ace Director & Instructor
    University of Washington
    damorgan@x.washington.edu (replace x with u to respond)
    Puget Sound Oracle Users Group
    www.psoug.org

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


    "Dereck L. Dietz" wrote in message
    news:WgIpk.23108$Ri.20173@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.
    >


    I hope your not trying to make a dynamic procedure to handle all possible
    update statements in your application.
    That would be a bad idea basically.

    Matthias



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

    On Aug 16, 3:14*pm, "Dereck L. Dietz" wrote:
    > 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.


    Maybe http://asktom.oracle.com/pls/asktom/...D:210612357425
    and search for "silly question".

    jg
    --
    @home.com is bogus.
    Well, I'll probably be doing the big upgrade that weekend anyways:
    http://www.signonsandiego.com/uniont...16alcohol.html

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

    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@trnddc03...
    >
    >
    >
    > > "Dereck L. Dietz" wrote in message
    > >news:WgIpk.23108$Ri.20173@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 liketo
    > be able to call the package procedure which processes the data to be ableto
    > 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.

+ Reply to Thread