Re: Help: how to update a table with a value from Stored procedure
Date: Fri, 07 Nov 2008 16:17:56 +1100
> Dear Group,
> We have a third party supplied package which we do not want to change.
> I would like to update a table with a value from the procedure. I can
> use PL/SQL to do that. But our tool does not support PL/SQL, i have
> to use SQL.
> Something like
> SQL> var res varchar2(30)
> SQL> exec test_procedure(res);
> SQL> update my_table set col1=:res where ...
> But our application does not even support that. I have to do
> something like
> SQL>update my_table set col = (:test_procedure)
> or something like
> SQL>update my_table set col =(select :test_procedure from dual);
> Anyway we can make this into one SQL?
From the info you provided, its nearly impossible to tell. some things to consider -
- If its a plsql procedure, it doesn't return a value, so your idea of calling itsomehow inside a select won't work.
- In pl/sql, functions return values, not procedures. If it is a function, then you might be able to do something. However, you need to read up on the restrictions on using user defined functions in sql - see the sql reference manual.
- The only way I can think of to get a value out of a procedure is if one of its arguements is defined as an OUT or IN OUT parameter.
-- tcross (at) rapttech dot com dot auReceived on Thu Nov 06 2008 - 23:17:56 CST