Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: comparing updated rows before commit

    Hi all

    I am trying to do the following in a vbscript.

    Code:
    
    Dim WshShell
    Set WshShell = CreateObject("WScript.Shell")
    
    cmdString = "C:\oracle\instantclient_11_1\sqlplus user/pass@server"
    
    Set oExec = WshShell.Exec(cmdString)
    WshShell****n "spool C:\temp\result.log"
    WshShell****n "insert into table1 ..."
    WshShell****n "update table2 set ..."
    WshShell****n "update table3 set ..."
    WshShell****n "spool off"
    
    Set objTextFile = objFSO.OpenTextFile _
        ("C:\temp\result.log", ForReading)
    
    Dim arrResult(4)
    Dim i
    i = 0
    
    Do Until objTextFile.AtEndOfStream	
    	strNextLine = objTextFile.Readline
    	If Instr(strNextLine,"UPDATED") > 0 Or Instr(strNextLine,"INSERTED") then 
    		arrServiceList = Split(strNextLine , " ")
    		arrResult(i) = arrServiceList(0)	
    		i = i + 1
    	End if
        
    Loop
    
    If arrResult(0) <> arrResult(1) Or arrResult(1) <> arrResult(2) Or arrResult(0) <> arrResult(2) Then
    	WshShell****n "rollback"
    Else
    	WshShell****n "commit"
    End if
    Of course my "shell"-Part is totally wrong. But I just want to show my idea. Basicly I would like to execute several update and an insert script in sqlplus from a vbs-File, determine the updated and inserted rows, and if it's the same number of rows updated/inserted in all the statements, I commit the transaction, otherwise I rollback.

    Is this possible? I know that sqlplus commits automatically. How can I prevent that? How can I run the sqlplus statements with the vbscript? I haven't worked with vbscript yet so I have no idea.

    Thanks for any advice in advance.

    ximul

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    In SQL*Plus, you'd write some PL/SQL like

    Code:
    DECLARE
      cnt1 NUMBER;
      cnt2 NUMBER;
      cnt3 NUMBER;
    BEGIN
      insert into table1 ...;
      cnt1 := SQL&#37;ROWCOUNT;
      update table2 set ...;
      cnt2 := SQL%ROWCOUNT;
      update table3 set ...;
      cnt3 := SQL%ROWCOUNT;
    
      If cnt1 = cnt2 and cnt2 = cnt3 then
        commit;
      Else
        rollback;
      End If;
    
    END;
    Spool that out to a file, and then invoke SQL*Plus passing in that file name as a parm.
    --=cf
    Last edited by chuck_forbes; 03-10-10 at 16:30.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    And sqlplus does NOT auto-commit, that is a settable parameter in your ODBC setup and is not the default behavior of sqlplus. It only commits when a commit is issued or you execute an exit command.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2010
    Posts
    5
    Hi guys

    Thanks a lot for your answers:

    @beilstwh: Sorry, you're right, I thought it autocommited, because in my tests it did, but it exitted before I could do anything, so it must have been that. I'm a total beginner, so I have no idea.... .

    @chuck_forbes:
    I didn't think at PL/SQL because I never worked with it... But your idea sounds great!

    So now I pasted your commands in a file called "script.sql" and added

    Code:
    spool C:\temp\output.log
    on top and
    Code:
    spool off
    at the bottom of the file.

    My vbs-Script now looks like this

    Code:
    '* Create objects
    Dim WshShell
    Set WshShell = CreateObject("WScript.Shell")
    cmdString = "C:\oracle\instantclient_11_1\sqlplus user/pass@server @c:\temp\script.sql"
    
    Set oExec = WshShell.Exec(cmdString)
    
    Set oExec = Nothing
    Set WshShell = Nothing
    The thing is, it doesn't do anything.
    When I call the script.sql directly from the sqlplus console it creates the output file, but stops and types out a line number on the console (like 20 or 23) and then won't do anything further.

    What am I doing wrong?

    ximul

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Make sure that the end (last line in procedure) is followed by a / in the first column of the script file and that you follow that with an exit; command so your script exits sqlplus. The forward slash tells sqlplus to execute the preceding procedure.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2010
    Posts
    5
    Hi beilstw

    Thanks so much, it works!

    ximul

  7. #7
    Join Date
    Mar 2010
    Posts
    5

    return value to batch-file

    Hi guys

    One more thing (I've tried to figure it out myself, but just don't get it to work ):

    I changed the vbs to a dos-Batch-Script:

    Code:
    echo off
    
    
    C:\oracle\instantclient_11_1\sqlplus user/pass@db @c:\temp\script.sql
    if errorlevel 1 goto error
    
    echo we have success
    goto end
    
    :error
    echo we have an error
    
    :end
    pause
    in my sql-script I then try to set an error-code and exit with this:

    Code:
    spool c:\temp\output.log
    
    SET serveroutput ON
    
    DECLARE
    	cnt1 NUMBER;
    	cnt2 NUMBER;
    	cnt3 NUMBER; 
    	error_cd NUMBER;
      
    BEGIN
    	
    	Update ...;
    	cnt1 := SQL%ROWCOUNT;
     
    	Update ...;
    	cnt2 := SQL%ROWCOUNT;  
    
    	Update ...;
    	cnt3 := SQL%ROWCOUNT;
      
    
    	If cnt1 = cnt2 and cnt2 = cnt3 and cnt3 = cnt1 then
    		commit;
    		dbms_output.put_line(cnt1 || ' rows updated');  	
    		dbms_output.put_line(cnt2 || ' rows updated');
    		dbms_output.put_line(cnt3 || ' rows updated');
    		error_cd :=0;
    	Else
    		rollback;
    		error_cd :=1;
    	End If;  
    END;
    /
    exit error_cd
    spool off
    Now, even if the statements committed successfully, the batch-file always goes to the error part, meaning errorlevel is always 1. How can I return a result to the batch-File from sqlplus? It seems that after the / the variable error_cd is empty again, but how is the right order to get it to work? Sorry, I really have never worked with plsql.

    Thanks for your help again!

    ximul

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can't reference a variable that is within a pl/sql procedure outside of the block.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2010
    Posts
    5
    Is there any other way to return a value from pl/sql to a batch file? Can I somehow exit pl/sql within the block where the variable is still available?

    Thanks for any suggestions in advance.

    ximul

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    I think if you put this as the first line in your *.sql file, it will accurately return a success/failure to your batch file

    Code:
    whenever sqlerror exit failure
    DECLARE
    	cnt1 NUMBER;
    	cnt2 NUMBER;
            ...
    You won't be returning a particular value back out, just whether or not your PL/SQL succeeded.

    --=Chuck

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    and then

    Code:
    If cnt1 = cnt2 and cnt2 = cnt3 and cnt3 = cnt1 then
            commit;
            dbms_output.put_line(cnt1 || ' rows updated');      
            dbms_output.put_line(cnt2 || ' rows updated');
            dbms_output.put_line(cnt3 || ' rows updated');
            error_cd :=0;
        Else
            rollback;
            -- Generate the error condition
            select 1/0
            into cnt1
            from dual;
        End If;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •