Delete statement in execute command activity

[RAJARP]

Hi ,

I am having a job sequence

uservariableactivity--->execute command activity

In the execute command acitvity, I am using a SQL statement to delete records from a table
In command:

Code: Select all

echo "SET HEADING OFF;\n delete from CUST.CUST_BAL where seq_id ='#uservariableactivity.seq_id#';\n exit\n" | sqlplus -s Userid/PWD@SERVER

where
uservariableactivity.seq_id is a variable which i derive from uservariable activity.

But If i run this job sequence, it simply hangs and run forever.


Instead of job parameter, if i hard code the value like

Code: Select all

echo "SET HEADING OFF;\n delete from CUST.CUST_BAL where seq_id ='123ABC';\n exit\n" | sqlplus -s Userid/PWD@SERVER

this command runs and deletes the records as expected

I tried this command even without quotes on two sides of #uservariableactivity.seq_id

Code: Select all

echo "SET HEADING OFF;\n delete from CUST.CUST_BAL where seq_id =#uservariableactivity.seq_id#;\n exit\n" | sqlplus -s Userid/PWD@SERVER

But it gives me error

ERROR at line 1:
ORA-00933: SQL command not properly ended


How do i overcome this?

Thanks in advance,
Raja R P

[SURA]

Fine, you can try like this!

Code: Select all

echo "SET HEADING OFF;\n delete from CUST.CUST_BAL where seq_id =" '#uservariableactivity.seq_id#' ";\n exit\n" | sqlplus -s Userid/PWD@SERVER

Please give a single space like this " ;\n exit...."

I am not able to access my system to try this. This is what I have in my mind. Give it a try and let us know.

If some one else given any other suggestions, please do check that too.

[ArndW]

I think you need to distinguish between strings and variables when putting together your arguments

The commnd is "echo", the arguments should be:

"SET HEADING OFF;\n delete from CUST.CUST_BAL where seq_id ='":#uservariableactivity.seq_id#:"';\n exit\n" | sqlplus -s Userid/PWD@SERVER"

[eph]

Hi,

Maybe you need to make the echo command interpret the \n character as well?

Code: Select all

$ echo "\n"
\n
$ echo -e "\n"
<blank line>

Eric

[chulett]

I concur with Arnd.