Oracle store procedure

[kww]

Here is a quick and dirty solution. 2 objects, table and function. You can place the Sql statement (insert) into the before/after table on the SQL tab in your OCI stage. Keep in mind that the stored procedure you call cannot have COMMIT and ROLLBACK statements. *** This is not used to process a result *** This is just used to call a stored procedure within your stage. Not sure this will help with your initial request.

/* Table to hold call spec, you can use this to audit procedures being called. The table I use has other info like mod date, create date and so on. This is the basics. /*

CREATE TABLE DS_RUN_PROC (
SP_SPEC VARCHAR2 (200))

/* Function used to call the stored procedure */

FUNCTION fnc_runproc (sp_spec IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
DBMS_OUTPUT.put_line ( 'Procedure Call '
|| sp_spec
|| ' Processed');
EXECUTE IMMEDIATE 'begin '
|| sp_spec
|| '; end;';
RETURN sp_spec;
END fnc_runproc;

/* Sql to call the function.

insert into ds_run_proc(sp_spec) values (fnc_runproc('MY_STORED_PROC'))




Edited by - kww on 09/04/2002 13:17:27

Edited by - kww on 09/05/2002 06:57:50