This content has been marked as final. Show 4 replies
If it works in a PL/SQL block, then why not do it that way in your file?
BEGIN UPDATE ITEM ...; UPDATE SERVICE ...; COMMIT; END; /
Thank you Adam,
I knew it was something stupid like that. I wonder why it can't handle two updates but when I'm enclosing them inside a begin ... end; it works. Most of the examples I saw weren't using a begin ... end syntax. But, they were very simple example with only one query. This is so weird. But it seems you need a PL/SQL block to run two or more updates. Is SQL*PLUS can only handle a single command per line ?
Yes, you can execute two or more commands in SQL*Plus via a file. Sometimes people get the line spacing wrong, or they get the command terminators wrong, forgetting the slash(/) or the semicolon (;).
I did not see an obvious problem with your script. You can try reformatting it a little to see what happens. Something like this:
UPDATE ITEM SET I_TRANSACTION = 'Y', I_TRANSACTION_DATE = SYSDATE WHERE I_ITEM_NUMBER IN (SELECT I_ITEM_NUMBER FROM ITEM WHERE I_TRANSACTION = 'N' AND I_SALE_DATE >= (SYSDATE - 30)) / UPDATE SERVICE SET S_TRANSACTION = 'Y', S_TRANSACTION_DATE = SYSDATE WHERE S_SERVICE_NUMBER IN (SELECT S_SERVICE_NUMBER FROM ITEM WHERE S_TRANSACTION = 'N' AND S_SALE_DATE >= (SYSDATE - 30)) / COMMIT / EXIT
I already tried to put a / after each instructions, and it didn't change anything. But since the BEGIN ... END; works correctly, I will use it. Maybe it depends on the version of SQL*Plus we are using.
Thank you again !