calling an Oracle procedure in a datastage routine
Posted: Wed Mar 02, 2005 7:23 pm
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)