DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: trap sqlerr in sql*plus exit

  1. #11
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello Pando

    I ahve tested this on sun-solaris and redhat linux this MUST work for you

    regards
    Hrishy

  2. #12
    Join Date
    Jan 2001
    Posts
    2,828
    Hello Pando

    You can as well try this

    #!bin/ksh
    sqlplus -s system/manager < |grep '^ORA'
    select * from kk;
    set echo on
    show spool on
    end;
    /


    EOF

    save test.sh

    sh test.sh

    results

    ORA-00942: table or view does not exist
    ORA-00942: table or view does not exist

    regards
    Hrishy




  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well no I want to trap the error code only and once not twice...

  4. #14
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I think it can be done if AWK will be included in the script.
    I haven't try it yet, but I believe this can be achieve.

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I want to avoid create files that is where the problem arises

    I dont want to spool, I want to pass directly to $?

    I have a method which works for PL/SQL but not for pure SQL :(

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    #!bin/ksh
    sqlplus -s system/manager < |grep '^ORA' |uniq
    select * from kk;
    set echo on
    show spool on
    end;
    /


    EOF

    save test.sh

    sh test.sh

    results

    ORA-00942: table or view does not exist

  7. #17
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you know how to store that in $? (exit status)

  8. #18
    Join Date
    Jan 2001
    Posts
    2,828
    #!bin/ksh
    sqlplus -s system/manager < select * from kk;
    set echo on
    show spool on
    end;
    /


    EOF


    ~
    ~
    ~
    ~
    ~
    ~
    ~
    ~
    ~
    ~
    ~
    "test1.sh" 12 lines, 134 characters
    oracle:/db/oracle $sh test1.sh
    ORA-00942
    oracle:/db/oracle $

  9. #19
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi Pando

    Hmm does this work are you happy now :-)

    regards
    Hrishy

  10. #20
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    heh no... I dont want that!

    I want to do like this

    exit sql.sqlcode


    so I can reference a shell variable

    status=$?

    then use $status for other things...

    something like this but for SQL

    Code:
    #!/bin/ksh
    
    puto=`sqlplus -s zup/zap@move << EOF
    variable scode number
    set feed off
    set serverout on
    declare
            las emp.empno%type;
    begin
            select empno
            into las
            from emp;
    exception
            when others then
            :scode:=to_number(substr(sqlerrm, 5, 5));
            dbms_output.put_line(:scode);
    end;
    /
    exit :scode
    EOF`
    
    echo $puto
    
    ...................................
    
    
    ksh kk.ksh
    
    1422

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