David,
You mentioned a week or two ago that you issue calls to stored procedures via a routine that will execute SQL*Plus statements. Did I get this correctly?
I know how to call DOS and Universe commands via a routine, but Im not familiar on how to do it for SQL*Plus to an Oracle machine. Any hints?
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176
Calling stored procecures
Brad,
SQL*Plus will accept a script file as a command line parameter. Keep in mind that the name of SQL*Plus sometimes changes from version to version. We are running Oracle 8.0.4 where it is called PLUS80.EXE. Refer to your Oracle documentation for exact syntax. In my case, I first create the script file using writeseq. The script declares an inline anonymous pl/sql procedure which calls the procedure I want to run. This way, my scripted procedure can have an "exception" clause to trap errors and write something to an error table etc. After the scripted procedure, my script runs a query against the error table to determine the maximum error level resulting from running the target procedure. The name and parameters of the target procedure are all soft coded in my DataStage routine (passed in as a list of param=value etc). The DataStage routine scans the results from the DOS command session to find the results of this query and therefore success or failure of the PL/SQL procedure. It is all rather messy, but it works.
As I said in my previous message, if I did it again, Id use ODBC instead.
Does this help? Im not exactly sure what you are fishing for.
Cheers
David
-----Original Message-----
From: Vincent, Brad [SMTP:BVincent@dmc.org]
Sent: Monday, 31 July 2000 12:18
To: informix-datastage@oliver.com
Subject: Calling stored procecures
David,
You mentioned a week or two ago that you issue calls to stored procedures
via a routine that will execute SQL*Plus statements. Did I get this
correctly?
I know how to call DOS and Universe commands via a routine, but Im not
familiar on how to do it for SQL*Plus to an Oracle machine. Any hints?
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
SQL*Plus will accept a script file as a command line parameter. Keep in mind that the name of SQL*Plus sometimes changes from version to version. We are running Oracle 8.0.4 where it is called PLUS80.EXE. Refer to your Oracle documentation for exact syntax. In my case, I first create the script file using writeseq. The script declares an inline anonymous pl/sql procedure which calls the procedure I want to run. This way, my scripted procedure can have an "exception" clause to trap errors and write something to an error table etc. After the scripted procedure, my script runs a query against the error table to determine the maximum error level resulting from running the target procedure. The name and parameters of the target procedure are all soft coded in my DataStage routine (passed in as a list of param=value etc). The DataStage routine scans the results from the DOS command session to find the results of this query and therefore success or failure of the PL/SQL procedure. It is all rather messy, but it works.
As I said in my previous message, if I did it again, Id use ODBC instead.
Does this help? Im not exactly sure what you are fishing for.
Cheers
David
-----Original Message-----
From: Vincent, Brad [SMTP:BVincent@dmc.org]
Sent: Monday, 31 July 2000 12:18
To: informix-datastage@oliver.com
Subject: Calling stored procecures
David,
You mentioned a week or two ago that you issue calls to stored procedures
via a routine that will execute SQL*Plus statements. Did I get this
correctly?
I know how to call DOS and Universe commands via a routine, but Im not
familiar on how to do it for SQL*Plus to an Oracle machine. Any hints?
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************