Execute Oracle Stored Procedure in Routine
Posted: Tue Oct 12, 2004 8:13 pm
Hi all,
I'm trying to write a routines to execute a stored procedure from oracle.
My Stored Procedure expecting an varchar input and will return a varchar output as well.
I get an example from the forum but i have the below error, when i try to execute the routines.
Can anyone help me in solving this?
Equate RoutineName To "SQLPLUS"
* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).
If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to execute.", "")
GoTo ErrorExit
End
InputArg = "dev_pds_my,dev_pds_my,devpdsmy,EXECUTE DATASTAGE_TEST_SP('abcd');"
UserName = Trim(Field(InputArg, ",", 1))
Password = Trim(Field(InputArg, ",", 2))
DSN = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4,9999)
*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End
command = "echo " : SQL : "|sqlplus -L ":UserName : "/" : Password : "@" : DSN
command2 = "echo " : SQL : "| sqlplus -L ":UserName : "/*****@" : DSN
Call DSExecute(SysType, command, Output, SystemReturnCode)
If Trim(Output) "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "*** Output from command was: ***", ""):Output
End
Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No output from command ***", "")
End
* Check contents of output for an error
if Index(Output, "ERROR", 1) > 0 OR Index(Output, "Invalid option",1) > 0 OR Index(Output, "Usage",1) > 0 then
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
If SystemReturnCode = 0 Then
Message = DSRMessage("DSTAGE_TRX_I_0003", "Executed command: ", ""):command2:Message
Call DSLogInfo(Message, RoutineName)
End Else
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
* Exit with no error.
ErrorCode = 0
GoTo NormalExit
* Exit with error, logging warning message first.
ErrorExit:
Call DSLogWarn(Message, RoutineName)
ErrorCode = 1
NormalExit:
Ans=Output
I'm trying to write a routines to execute a stored procedure from oracle.
My Stored Procedure expecting an varchar input and will return a varchar output as well.
I get an example from the forum but i have the below error, when i try to execute the routines.
Can anyone help me in solving this?
![Confused :?](./images/smilies/icon_confused.gif)
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"TEST #1
*******
Arg1 = 1
Test completed.
DSLogWarn called from : SQLPLUS
Message to be logged is...
> Error when executing command: echo EXECUTE DATASTAGE_TEST_SP('abcd');| sqlplus -L dev_pds_my/*****@devpdsmy
> *** Output from command was: ***
> SH: syntax error at line 1: `(' unexpected
>
Result = SH: syntax error at line 1: `(' unexpected
Equate RoutineName To "SQLPLUS"
* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).
If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to execute.", "")
GoTo ErrorExit
End
InputArg = "dev_pds_my,dev_pds_my,devpdsmy,EXECUTE DATASTAGE_TEST_SP('abcd');"
UserName = Trim(Field(InputArg, ",", 1))
Password = Trim(Field(InputArg, ",", 2))
DSN = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4,9999)
*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End
command = "echo " : SQL : "|sqlplus -L ":UserName : "/" : Password : "@" : DSN
command2 = "echo " : SQL : "| sqlplus -L ":UserName : "/*****@" : DSN
Call DSExecute(SysType, command, Output, SystemReturnCode)
If Trim(Output) "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "*** Output from command was: ***", ""):Output
End
Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No output from command ***", "")
End
* Check contents of output for an error
if Index(Output, "ERROR", 1) > 0 OR Index(Output, "Invalid option",1) > 0 OR Index(Output, "Usage",1) > 0 then
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
If SystemReturnCode = 0 Then
Message = DSRMessage("DSTAGE_TRX_I_0003", "Executed command: ", ""):command2:Message
Call DSLogInfo(Message, RoutineName)
End Else
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
* Exit with no error.
ErrorCode = 0
GoTo NormalExit
* Exit with error, logging warning message first.
ErrorExit:
Call DSLogWarn(Message, RoutineName)
ErrorCode = 1
NormalExit:
Ans=Output