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

"gym dot scuba dot kennedy at gmail" <kennedyii_at_verizon.net> wrote in message news:a%Jpk.63$UX.40_at_trnddc03...
>
> "Dereck L. Dietz" <dietzdl_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.
>>
>>
> 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. Received on Sat Aug 16 2008 - 19:34:47 CDT