Re: Passing queries from procedure call in a script ... possible?
Date: Sun, 17 Aug 2008 00:09:42 GMT
Message-ID: <a%Jpk.63$UX.40@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
Received on Sat Aug 16 2008 - 19:09:42 CDT