Execute Oracle Stored Procedure in Routine

[tang]

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?

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

Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
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

[ray.wurlod]

You need to "escape" the quote characters. Otherwise they are interpreted by the operating system shell as quote characters rather than being passed.

Code: Select all

InputArg = "dev_pds_my,dev_pds_my,devpdsmy,EXECUTE DATASTAGE_TEST_SP(\'abcd\');" 

I have found the -S (silent) option to sqlplus to be beneficial at times.

[tang]

hmm.... after i've changed accordingly the same error shown...
I'm using DS version 6.0.1.11.

Any idea why?

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 -S dev_pds_my/*****@devpdsmy
> *** Output from command was: ***
> SH: syntax error at line 1: `(' unexpected
>

Result = SH: syntax error at line 1: `(' unexpected

[/quote]

[tang]

After i changed the command to actually excuting a script file, different error has shown.

Code: Select all

command = "echo @/datastage/Ascential/DataStage/Projects/PRS/test.sql|sqlplus -S dev_pds_my/dev_pds_my@devpdsmy"

Result = SH: sqlplus: not found

Is there anything do with this command cannot executed by the user?
Which user will actually excute the command?

[chulett]

Have you tried running this command from outside of DataStage? Suggest you get it to work first from the command line, then transfer it into the routine. You should also ditch the semi-colon before the pipe.

[tang]

the below is the succeed code in oracle syntax

Code: Select all

sqlplus -S dev_pds_my/dev_pds_my@devpdsmy @/datastage/Ascential/DataStage/Projects/PRS/test.sql

So, i changed the command as below

Code: Select all

command = "echo sqlplus -S dev_pds_my/dev_pds_my@devpdsmy @/datastage/Ascential/DataStage/Projects/PRS/test.sql"

It has the output result as in the quote

TEST #1
*******

Arg1 = 1

Test completed.

DSLogInfo called from : SQLPLUS
Message to be logged is...
> Executed command: echo EXECUTE DATASTAGE_TEST_SP(\'abcd\');| sqlplus -S dev_pds_my/*****@devpdsmy
> *** Output from command was: ***
> sqlplus -S dev_pds_my/dev_pds_my@devpdsmy @/datastage/Ascential/DataStage/Projects/PRS/test.sql
>

Result = sqlplus -S dev_pds_my/dev_pds_my@devpdsmy @/datastage/Ascential/DataStage/Projects/PRS/test.sql

I expect the .sql has completed and the result will spooled it to a file which i've tested using the same command in unix.

Anybody know how to detect whether the SP has run successfully?
Need SOS

[ririr]

Use the Exec_SH(Routine). Call a shell script that executes the database procedure. I can send information, if interested.

[tang]

thx guys
I've use Routine DSExecute to solve this prob.

[ray.wurlod]

And the other thing is, this being a sharing place, is that we share our solutions. Can you please report how you solved it?

[johm73]

[quote="ray.wurlod"]And the other thing is, this being a sharing place, is that we share our solutions. Can you please report how you solved it?[/quote]

I was able to do this by using ExecSH Before/After-job subroutine that looked like this:

echo "execute pdw_exec_status.exec_status_mgmt('TESTER', NULL, 'B');" | sqlplus -S username/pawword@sid

I use variables for the proc parameters and DB connection info.

Works like a champ![/i]

[RichardM]

I realise that this may be a bit late in the day, but anyway... I ran into the same problem using DSExecute and getting:

Result = SH: syntax error at line 1: `(' unexpected

The quick solution (without changing to ExecSH) is to escape the brackets, not the quotes, so the original example would become:

Code: Select all

InputArg = "dev_pds_my,dev_pds_my,devpdsmy,EXECUTE DATASTAGE_TEST_SP\('abcd'\);"

instead of

Code: Select all

InputArg = "dev_pds_my,dev_pds_my,devpdsmy,EXECUTE DATASTAGE_TEST_SP(\'abcd\');"

Seemed to work OK...