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
Oracle store procedure
Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.
Moderators: chulett, rschirm, roy
Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”
Jump to
- Moderators' Choice
- ↳ Editor's BLOG Corner
- ↳ Ask the Experts! - Dads and Grads
- ↳ DSXchange Testimonials
- ↳ Cognos (IBM BI)
- FAQs
- ↳ FAQs
- ↳ FAQ Discussion
- DataStage
- ↳ General
- ↳ IBM<sup>®</sup> Infosphere DataStage Server Edition
- ↳ IBM<sup>®</sup> DataStage Enterprise Edition (Formerly Parallel Extender/PX)
- ↳ Archive of DataStage Users@Oliver.com
- IBM<sup>®</sup>Infosphere Products<sup></sup>
- ↳ Business Glossary
- Suggestions
- ↳ Site/Forum
- ↳ Enhancement Wish List
- Consulting
- ↳ Talent
- ↳ Looking for Talent
- Support
- ↳ Parameter Manager
- ↳ Compile All Plus
- Usergroup Forums
- ↳ Usergroup Central Forum
- ↳ Heartland Usergroup Forum
- The Written Word
- ↳ Articles, White Papers and Tips and Tricks
- ↳ Product Documentation
- Third Party Applications
- ↳ Third Party Applications
- Product Derivatives
- ↳ Functions
- ↳ Routines
- ↳ Jobs
- ↳ Logs
- Tools
- ↳ Tools Forum
- Category
- ↳ Infosphere Master Data Management
- ↳ Data Quality Best Practices
- ↳ IBM QualityStage
- ↳ Information Analyzer (formerly ProfileStage)
- ↳ IBM<sup>®</sup> SOA Editions (Formerly RTI Services)
- ↳ IBM<sup>®</sup> DataStage TX
- ↳ BI
- ↳ Data Integration