DSExecute to connect to oracle and update table...

[gdean]

Hi,

I need to update a oracle table from datastage routine. After going through earlier messages in this forum, I tried running the following routine:

Code: Select all

strLogin = DBUser : "/" : DBPwd : "@" : DBSid
strUpdate = "update myschema.mytable set myflag= 'Y' where mycolumn ='" :DSJob:"';"
Call DSExecute('UNIX','echo "': strUpdate : '" | sqlplus ' : strLogin, Output, SystemReturnCode) 
Ans = Output:' =':SystemReturnCode

When I run

Code: Select all

'echo "': strUpdate : '" | sqlplus ' : strLogin

on the unix box, the table is updated as given in the query. But when I test the same routine in datastage, I get SystemReturnCode = 0 and the Output = " ". So, I assume the command ran successfully. But when I checked the table, the update did not happen. I also tried adding a commit after the update query in the echo. Even that doesn't work.

I don't know what am I missing here. Datastage and Oracle are on different servers. Any hints would be really helpful and I appreciate it.

Thank You,
Gregg

[kcbland]

I'd be 99% sure quotes are being messed with by DS when it tries to execute the command. Put the entire command into a shell script and execute the shell script. Pass parameters like the credentials to the script and have the script manage executing sqlplus appropriately. I do this method and it always works. In fact, I wrote a generic script that takes as parameters the login credentials and script name and it manages executing sqlplus and handles all of the error conditions.

[chulett]

I would agree. DataStage tends to screw with your quotes when you are trying to do something like you are doing. Best solution is what Ken posted, build a shell script to do all of the dirty work and then use DSExecute to call the script, passing in parameters.

The other thing to realize is that you probably got a zero return code simply because it was able to execute sqlplus ok. Doing it in this fashion, you'd have no clue if the DML itself succeeded or failed. In the script you can take the extra step needed of capturing the output of the sqlplus session, grepping through it for Oracle errors and setting the return code of the script appropriately.