DSXchange

Posted: **Thu Jul 11, 2013 9:53 am**

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

Posted: **Thu Jul 11, 2013 5:18 pm**

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.

Posted: **Fri Jul 12, 2013 2:41 am**

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"

Posted: **Fri Jul 12, 2013 3:04 am**

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

Posted: **Fri Jul 12, 2013 7:14 am**

I concur with Arnd.

DSXchange

Delete statement in execute command activity

Delete statement in execute command activity

Re: Delete statement in execute command activity