You can use execute immediate. I would have to experiment, but I think your
string would be something like
execstring:=' begin sp_test.generate(:param1,:param2,:param3); end;'
execute immediate execstring using a,b,c;
Then do the comit if that is what you want. You might want to not put it in
the stored proc, since if you have an error you might not want a commit.
Jim
"Dirk" <dirk02_at_my-deja.com> wrote in message
news:d52106cc.0208282358.53487246_at_posting.google.com...
> Hi,
>
> i want to have a table with different stored procedures like this:
>
> id call
> 1 sp_test.generatesomething
> 2 sp_test.insertthis
>
>
> How can i call this procedures from a stored procedure?
> And how can i pass parametersm to the selected stored proc. ?
> How can i handle inout-parameters?
>
> Can i do this by dynamic sql (execute immediate) like this?
> ...
> EXECUTE IMMEDIATE '
> DECLARE
> IPara1 VARCHAR2(3);
> OANRID NUMBER;
> OACTION NUMBER;
>
> BEGIN
> IPara1 := NULL;
> OANR := NULL;
> OACTION := NULL;
>
> APSDBA.SP_Test.generate ( IPara1, OANR, OACTION );
> COMMIT;
> END; '
> into out1, out2;
> ...
>
> tia,
>
> Dirk
Received on Thu Aug 29 2002 - 08:09:17 CDT
