Re: Stored Proc in table

Dirk wrote:
>
> 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;
> ...
>

1. Create a schema called sp_test.
2. then create your proc as
CREATE OR REPLACE PROCEDURE sp_test.my_proc AS . . . .

Now your procedure is stored in sp_text.dba_source. to execute your procedure:
BEGIN
  sp_text.my_proc(arg1, arg2, . . .);
END;
/

Simple.

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net