auto commit if run sql script from unix prompt?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: auto commit if run sql script from unix prompt?

  1. #1

    auto commit if run sql script from unix prompt?

    Hi,

    If I have a sql script that has something like below and without exit at the end:

    =================================
    spool filename
    select . . .
    from . . .
    where . . .
    /

    update . . .
    set . . .
    where . . .
    /

    select . . .
    from . . .
    where . . .
    /

    spool off
    ==============================

    I run the script as "sqlplus -s username/password @filename.sql" from UNIX command line. Then I press ctrl+C to exit the process as it fails to go back to UNIX prompt due to no "exit" at the end of sql script. Does it do auto commit even though we don't specify commit in the script after update statement? For some reason it does. I thought it should rollback because I do force exit the process.

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Talking Whenever...

    It does autocommit unless you specify the WHENEVER ...ROLLBACK clause or SET AUTOCOMMIT OFF
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Hi,

    Thanks for replying.

    Are you saying that in any sql scripts, I don't have to put any commit line (after update/insert/delete) as it will be committed automatically on exit (exit line at the end of sql script) if I run sql from command line? Just need clarification. Thanks.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool AUTOCOMMIT is ON

    SET AUTOCOMMIT ON is the default, it will commit on exit.

    It will NOT commit after each individual insert/update/delete.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    You are asking the wrong question. You should be asking how to write a bash script that runs sql trhough the database.

    If that is really what you want to do you need to first write a script that sets up your environment variables. Including: ORACLE_HOME, TNS_ADMIN, ORACLE_SID, and maybe ORACLE_BASE. Then all you need to do is add this to a script, making sure to keep the leading period and space. That tells the shell to apply the environment variables to the current shell and not to a shell that gets created and dropped after the script finishes.

    Code:
    . /oracle/bin/set_oracle_enrivonment.sh sidname
    Now all you need to do is call sqlplus and give it the commands. As in this example:

    Code:
    export USER=$2
    
    # Log into the DB through SQLPLUS.  You must be the oracle user.
    $ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF
    set term off
    set echo off
    set feedback off
    alter session set cursor_sharing=exact;
    begin
    dbms_stats.gather_schema_stats (ownname=>'$2',granularity=>'ALL',cascade=>true);
    end;
    /
    exit
    EOF
    The above code will analyze a schema and then exit. "<< EOF" is the key it tells Oracle run every command between the start and EOF as if it was typed into a sqlplus session. It doesn't have to be EOF it could be anything, it just has to be the same anything. If you strategy depends on you pressing control-c to end it how will you ever schedule jobs using cron? And when using cron you need to make sure that your script has everything that it needs to run. That is every environment variable set.
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width