calling an Oracle procedure in a datastage routine

[dealiang]

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)

[chulett]

Which 7.x version do you have? It's only with the release of the 7.5 version with its Store Procedure Stage where you can do something along the lines of what you are wanting to do.

[ray.wurlod]

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.

[chulett]

Didn't even look deeply at the code itself, especially since it isn't formatted at all. Title of the thread mentioned Oracle procedures and I figured it was something that would require the stored procedure stage, mostly because it returns values.

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?

[ray.wurlod]

No, because they're on Windows and are probably using the Microsoft ODBC driver.

Attention to detail, lad!

[chulett]

Great... smacked twice by Uncle Ray in one thread.

[dealiang]

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.

[StefL]

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.

[mhester]

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.

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

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.

[MrBlack]

I know this is an old thread but can anyone help me to modify this routine to use OCI instead of ODBC? Or how to check if ODBC is not configured, to then try and see if an OCI connection could be made?

[ray.wurlod]

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.