Re: Help: how to update a table with a value from Stored procedure

On Nov 6, 5:45 pm, emdproduct..._at_hotmail.com wrote:
> 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?
>
> Thanks very much

You can't update a table with a value from the procedure because procedure
doesn't return value if you have a function for example f1 in that case you
can update table update my_table set col = (select f1(par) from dual);

If you have a procedure which has an IN OUT parameter, you can use a trigger before insert or update and in that trigger you can calculate your variable
for example
CREATE OR REPLACE PROCEDURE hold.p1 (res IN OUT VARCHAR2) IS
BEGIN
   IF res = 'asdf'
   THEN
      res := '123456';
   ELSE
      res := '987654';
   END IF;
END p1;
/

CREATE TABLE T1
(
  ACC_NAME VARCHAR2(200 BYTE)
)

CREATE OR REPLACE TRIGGER T1_trg
before insert or update ON T1 referencing new as new for each row begin
p1(:new.acc_name);
end;
/

as result
update t1 set acc_name='asdf' ;
commit;
select acc_name from t1;

ACC_NAME

update t1 set acc_name='qwert' ;
commit;
select acc_name from t1;
ACC_NAME

I think this is not so bad decision.

Alex Received on Thu Nov 06 2008 - 19:35:58 CST