Posted: Wed Sep 04, 2002 12:15 pm
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
/* 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