calling an Oracle procedure in a datastage routine
Moderators: chulett, rschirm, roy
calling an Oracle procedure in a datastage routine
I am new to datastage, we use 7.x. I want to create a routine that execute an Oracle(10g) procedure which inserts a row to a table then returns one value(seq.currval), in sql*plus, I run the code below to execute myProc:
DECLARE s number;
BEGIN
myProc ( 'test', s) ;
dbms_output.put_line(s);
END;
I am not sure how to call the procedure from datastage and get the return value. Can someone please help me ? Also, is there a good documentation on the basic language? Thanks in advance.
Sample code:
$INCLUDE UNIVERSE.INCLUDE ODBC.H
EQU ROUTINE_NAME TO "rtTest" ;
EQU SUCCESS To 0
EQU ERROR To 1
squote="'"
EQU Statement To "Call " :"myProc (":squote:Client_nm:squote:")"
cmd = Statement
Ans = SUCCESS
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
odbcStmtEnv = SQL.NULL.HSTMT
sqlState = ""
dbmscode = ""
error = ""
Connect:
status = SQLAllocEnv(henv)
if status <> 0 then
FN="SQLAllocEnv"
GOSUB ExceptionHandler
RETURN(Ans)
End
status = SQLAllocConnect(henv, hdbc)
if status <> 0 then
FN="SQLAllocConnect"
GOSUB ExceptionHandler
RETURN(Ans)
End
status = SQLConnect(hdbc, DSN, User, Pwd)
if status <> 1 then
FN="SQLConnect"
GOSUB ExceptionHandler
RETURN(Ans)
End
ExecuteSP:
status = SQLAllocStmt(hdbc, odbcStmtEnv)
if status <> 0 then
FN="SQLAllocStmt"
GOSUB ExceptionHandler
GOSUB Disconnect
RETURN(Ans)
End
status = SQLExecDirect(odbcStmtEnv, cmd)
if status = SQL.SUCCESS.WITH.INFO then
*
* The Maxid will be in the Info variable and needs to be extracted.
*
status = SQLError(henv,hdbc,odbcStmtEnv,sqlState,dbmscode,Info) ; * get the info
o = dcount(Info,"]") ; * find the last delimter from the odbc message
i = index(Info,"]",o-1) ; * Find the position of the delimter
Maxid = Info[i+1,len(Info) - i] ; * extract the key from the Info variable
*If Maxid = 0 then ;* the stored procedure, will return a 0 if the key was not generated.
* CALL DSLogWarn("Unable to generate key",ROUTINE_NAME)
* RETURN (ERROR)
*end
end else
FN="SQLExecDirect"
GOSUB ExceptionHandler
GOSUB Disconnect
RETURN(Ans)
End
Call DSLogInfo(cmd:" Status : " : status, "ROUTINE_NAME")
GOSUB Disconnect
Ans = status
RETURN(Ans)
Disconnect:
status = SQLFreeStmt(odbcStmtEnv, SQL.DROP)
status = SQLDisconnect(hdbc)
status = SQLFreeConnect(hdbc)
status = SQLFreeEnv(henv)
RETURN(Ans)
ExceptionHandler:
GetErrorStatus = SQLError(henv,hdbc,odbcStmtEnv,sqlState,dbmscode,error)
X= "SQLError status = ":GetErrorStatus
CALL DSLogWarn(FN:" STATUS = ":status:" SQLSTATE= ":sqlState: " dbmscode=":dbmscode: " Error=":error,ROUTINE_NAME)
Ans = ERROR
RETURN(Ans)
DECLARE s number;
BEGIN
myProc ( 'test', s) ;
dbms_output.put_line(s);
END;
I am not sure how to call the procedure from datastage and get the return value. Can someone please help me ? Also, is there a good documentation on the basic language? Thanks in advance.
Sample code:
$INCLUDE UNIVERSE.INCLUDE ODBC.H
EQU ROUTINE_NAME TO "rtTest" ;
EQU SUCCESS To 0
EQU ERROR To 1
squote="'"
EQU Statement To "Call " :"myProc (":squote:Client_nm:squote:")"
cmd = Statement
Ans = SUCCESS
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
odbcStmtEnv = SQL.NULL.HSTMT
sqlState = ""
dbmscode = ""
error = ""
Connect:
status = SQLAllocEnv(henv)
if status <> 0 then
FN="SQLAllocEnv"
GOSUB ExceptionHandler
RETURN(Ans)
End
status = SQLAllocConnect(henv, hdbc)
if status <> 0 then
FN="SQLAllocConnect"
GOSUB ExceptionHandler
RETURN(Ans)
End
status = SQLConnect(hdbc, DSN, User, Pwd)
if status <> 1 then
FN="SQLConnect"
GOSUB ExceptionHandler
RETURN(Ans)
End
ExecuteSP:
status = SQLAllocStmt(hdbc, odbcStmtEnv)
if status <> 0 then
FN="SQLAllocStmt"
GOSUB ExceptionHandler
GOSUB Disconnect
RETURN(Ans)
End
status = SQLExecDirect(odbcStmtEnv, cmd)
if status = SQL.SUCCESS.WITH.INFO then
*
* The Maxid will be in the Info variable and needs to be extracted.
*
status = SQLError(henv,hdbc,odbcStmtEnv,sqlState,dbmscode,Info) ; * get the info
o = dcount(Info,"]") ; * find the last delimter from the odbc message
i = index(Info,"]",o-1) ; * Find the position of the delimter
Maxid = Info[i+1,len(Info) - i] ; * extract the key from the Info variable
*If Maxid = 0 then ;* the stored procedure, will return a 0 if the key was not generated.
* CALL DSLogWarn("Unable to generate key",ROUTINE_NAME)
* RETURN (ERROR)
*end
end else
FN="SQLExecDirect"
GOSUB ExceptionHandler
GOSUB Disconnect
RETURN(Ans)
End
Call DSLogInfo(cmd:" Status : " : status, "ROUTINE_NAME")
GOSUB Disconnect
Ans = status
RETURN(Ans)
Disconnect:
status = SQLFreeStmt(odbcStmtEnv, SQL.DROP)
status = SQLDisconnect(hdbc)
status = SQLFreeConnect(hdbc)
status = SQLFreeEnv(henv)
RETURN(Ans)
ExceptionHandler:
GetErrorStatus = SQLError(henv,hdbc,odbcStmtEnv,sqlState,dbmscode,error)
X= "SQLError status = ":GetErrorStatus
CALL DSLogWarn(FN:" STATUS = ":status:" SQLSTATE= ":sqlState: " dbmscode=":dbmscode: " Error=":error,ROUTINE_NAME)
Ans = ERROR
RETURN(Ans)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not true at all - the code is using BCI functions. The statement being executed is a CALL statement.
I'd be a bit concerned at using a number as the target of put_line in the stored procedure, but you claim it works.
The BASIC language is documented in the DataStage BASIC manual, which you will find in C:\Program Files\Ascential\DataStage\Docs on your client machine, while the BCI functions are documented in the BASIC SQL Client Interface manual, which you would need to download from IBM's website.
Programming with the BCI is akin to programming with the ODBC API.
I'd be a bit concerned at using a number as the target of put_line in the stored procedure, but you claim it works.
The BASIC language is documented in the DataStage BASIC manual, which you will find in C:\Program Files\Ascential\DataStage\Docs on your client machine, while the BCI functions are documented in the BASIC SQL Client Interface manual, which you would need to download from IBM's website.
Programming with the BCI is akin to programming with the ODBC API.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
![Embarassed :oops:](./images/smilies/icon_redface.gif)
Seems like an awful lot of work.
Aren't they going to have the good old "works fine for 30 days and then needs a licensed driver" problem?
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: calling an Oracle procedure in a datastage routine
Thanks for replies. We use datastage 7.1. Does anyone know the right syntax of calling an oracle procedure from a datastage routine then return a value from the procedure?
squote="'"
EQU Statement To "Call " :"myProc (":squote:Client_nm:squote:")"
status = SQLExecDirect(odbcStmtEnv, cmd)
But it didn't work. Thanks.
squote="'"
EQU Statement To "Call " :"myProc (":squote:Client_nm:squote:")"
status = SQLExecDirect(odbcStmtEnv, cmd)
But it didn't work. Thanks.
I'm in a similar situation at the moment so I'm equally interested in what may be the answer to this question.
I want to call an Oracle procedure or function (whichever is more feasible in Data Stage) from a Data Stage routine. The return value I get is a varchar and the routine will be called from a Routine Activity in a job sequence.
The reason for this is to use the $ReturnValue of the Routine Activity as in parameter to a job later in the sequence.
From reading this posting and dealiang's other posts and answers in this Forum I right now mainly need more input on how to connect to the database from within the routine.
I want to call an Oracle procedure or function (whichever is more feasible in Data Stage) from a Data Stage routine. The return value I get is a varchar and the routine will be called from a Routine Activity in a job sequence.
The reason for this is to use the $ReturnValue of the Routine Activity as in parameter to a job later in the sequence.
From reading this posting and dealiang's other posts and answers in this Forum I right now mainly need more input on how to connect to the database from within the routine.
I have created a very simple program that will execute a simple SQL statement and return the result. This program gets the MAX of a column in a table. You can modify to meet your needs and make this a routine within DataStage if you like.
You could easily pass the necessary arguments to this routine such as -
SQL statment, DSN, User and Password information.
Please note that the GET(ARG.) syntax is for retrieving parameters passed to a BASIC program via the command line. You would take/comment this out when converting to a routine. The STOP's would also come out and appropriate logic would need to be added to report errors such as DSLogFatal, Warn or Info.
I hope this helps.
Code: Select all
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*** INITIALIZE VARIABLES
NUM.COLS = 0
RSTATUS = 0
DSN = ""
UID = ""
PWD = ""
STMT = "SELECT MAX(ROW_SEQ_NUM) FROM FACT_BUS_INVC_LINE_ITEM"
* Check and see if user passed command line args
GET(ARG.)DSN
GET(ARG.)UID
GET(ARG.)PWD
GOSUB ALLOCENV
GOSUB ALLOCCONNECTENV
GOSUB DBCONNECT
GOSUB ALLOCSTMTENV
GOSUB EXECUTESQL
GOSUB CHECKCOLUMNS
GOSUB BINDCOLUMNS
GOSUB FETCHDATA
GOSUB DESTROYOBJECTS
ALLOCENV:
*** ALLOCATE ENVIRONMENT
Fn = "SQLAllocEnv"
IF SQLAllocEnv(henv) # SQL.SUCCESS THEN STOP "Unable to allocate SQL Environment"
RETURN
ALLOCCONNECTENV:
*** ALLOCATE CONNECTION ENVIRONMENT
Fn = "SQLAllocConnect"
IF SQLAllocConnect(henv,hdbc) # SQL.SUCCESS THEN STOP "Unable to allocate connection environment"
RETURN
DBCONNECT:
*** CONNECT TO DATASOURCE (ODBC)
Fn = "SQLConnect"
IF TRIM(DSN) = "" THEN STOP "Missing DSN"
IF TRIM(UID) = "" THEN STOP "Missing User ID"
IF TRIM(PWD) = "" THEN STOP "Missing User Password"
RSTATUS = SQLConnect(hdbc,DSN,UID,PWD)
*** CHECK RETURN STATUS
IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN
ESTAT = SQLError(-1,hdbc,-1,SQLSTATE,DBMSCODE,ETEXT)
PRINT "ERROR TEXT ":ETEXT
PRINT "ERROR STATUS ":ESTAT
STOP
END
RETURN
ALLOCSTMTENV:
*** NOW ALLOCATE STATEMENT ENVIRONMENT
Fn = "SQLAllocStmt"
IF SQLAllocStmt(hdbc,hstmt) # SQL.SUCCESS THEN STOP "Unable to allocate statement environment"
RETURN
EXECUTESQL:
Fn = "SQLExecDirect"
RSTATUS = SQLExecDirect(hstmt,STMT)
IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN
PRINT "Error while executing SQL statement ":STMT
STOP
END
RETURN
CHECKCOLUMNS:
*
* Now find out if any columns were produced.
*
FUNC = "SQLNumResultCols"
RSTATUS = SQLNumResultCols(hstmt, NUM.COLS)
IF RSTATUS # SQL.SUCCESS THEN STOP "Error checking for columns"
RETURN
BINDCOLUMNS:
FUNC = "SQLBindCol"
FOR ICOL = 1 to NUM.COLS
RSTATUS = SQLBindCol(hstmt,ICOL,SQL.B.DEFAULT,MAX.RET)
IF RSTATUS # SQL.SUCCESS THEN STOP "Error binding columns"
NEXT ICOL
RETURN
FETCHDATA:
FUNC = "SQLFetch"
LOOP
STATUS = SQLFetch(hstmt)
WHILE STATUS <> SQL.NO.DATA.FOUND DO
PRINT MAX.RET
REPEAT
RETURN
DESTROYOBJECTS:
*** DESTROY STATEMENT ENVIRONMENT
RSTATUS = SQLFreeStmt(henv,SQL.UNBIND)
*** DESTROY CONNECTION ENVIRONMENT
Fn = "SQLDisconnect"
RSTATUS = SQLDisconnect(hdbc)
IF RSTATUS # SQL.SUCCESS THEN STOP "Unable to destroy SQL connection environment"
RETURN
END
SQL statment, DSN, User and Password information.
Please note that the GET(ARG.) syntax is for retrieving parameters passed to a BASIC program via the command line. You would take/comment this out when converting to a routine. The STOP's would also come out and appropriate logic would need to be added to report errors such as DSLogFatal, Warn or Info.
I hope this helps.
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The BASIC Call Interface emulates ODBC API and does not support other protocols (such as OCI). You'd be easier to create the statement and invoke it via sqlplus called through the DSExecute subroutine.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.