sqlplus update issue

Shell Programming and Scripting
1

Hi all,

I'm facing a problem with a query in a shell script (AIX).

sqlplus -s NAME/PASS@SCHEMA<<EOF
set echo off
UPDATE HISTORY SET HISTORY.ID = (SELECT OFFER.ID FROM OFFER WHERE HISTORY.ID=TO_NUMBER(OFFER.CODE)) WHERE EXISTS (SELECT OFFER.ID FROM OFFER WHERE HISTORY.ID=TO_NUMBER(OFFER.CODE));
commit;
exit;
EOF

I have no answer after that. I try to type exit; but it does nothing like if sqlplus was "thinking".
When I try this query directly in Oracle, it works fine.
Through a sql script I have the same problem.

Could you please help me ?
Thanks in advance.

Deeper

2

Did you load the same profile ? Can you reach the database using this ?

sqlplus NAME/PASS@SCHEMA
3

Yes, no problem.
I tried with a select and it works, but this query don't...

4

use below code to trace error.
Seems like sqlplus is unable to connect to database may be some issue with userid/password or SID.

sqlplus -s name/pass@schema<<EOF 1>error.txt
your query..
EOF

then check the error.txt for the error occured

Also try to write the sql query in a seperate file sqlquery.txt and execute it as below

sqlplus user/pass@SID @sqlquery.txt

and see what happens...

5

Same problem with sqlquery.txt.
error.txt is empty.

> commit;
> exit;
> EOF
... nothing then

6

Either sqlplus is waiting for your input or busy updating. Try

truss -o log sqlplus ...

then check the log to see what sqlplus is waiting for.