This discussion is archived
4 Replies Latest reply: Feb 7, 2012 11:30 AM by leinadjan RSS

Problem with 2 updates called from a batch file

leinadjan Explorer
Currently Being Moderated
Hello everybody,

I'm using Oracle 10G and SQLPLUS to generate an export file on a Windows platform from a batch file.

Everything seems to be fine, except the part where I'm doing two consecutive updates. Each time I'm doing it with SQL plus, only one of the two updates is commited, meaning I only have half of the data I must export. When I'm doing it in a PL/SQL anonymous block, it works fine. I don't understant where is my error :
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
/
Why is only one of those two update is commited ? When I'm doing my spool, I see either the items or the services, but never both. I run the same in a PL/SQL block and it works correctly.

Do I have to split the updates in separate files ?

I've made more complex stuff with SQL Loader, I wonder why this thing is annoying me.

I'm sure it's only something trivial, but I can't see it right now.


Thank you for your help.

Leinad
  • 1. Re: Problem with 2 updates called from a batch file
    AdamMartin Pro
    Currently Being Moderated
    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;
    /
  • 2. Re: Problem with 2 updates called from a batch file
    leinadjan Explorer
    Currently Being Moderated
    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 ?
  • 3. Re: Problem with 2 updates called from a batch file
    AdamMartin Pro
    Currently Being Moderated
    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
  • 4. Re: Problem with 2 updates called from a batch file
    leinadjan Explorer
    Currently Being Moderated
    Hi Adam,

    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 !

    Leinad

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points