DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: trap sqlerr in sql*plus exit

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Does anyone know how to trap the sqlcode of an sql statement

    Code:
    sqlplus user/passwd@move << EOF
    select * from kk;
    exit sql.sqlcode;
    EOF
    echo $?
    This works partially, the code returned should be ORA-942 but it returns 174 because in UNIX the exit status is from 0 - 255, anyone know a way to overcome this :-?

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

    You might try this

    #!/bin/bash
    sqlplus user/passwd@move << EOF
    select * from kk;
    variable temp number
    whenever sqlerror exit sql.sqlcode;
    begin
    :temp := 25; -- this is really a function call, not 25
    if ( :temp <> 0 )
    then
    raise_application_error( (-20000-224) - :temp, 'test' );
    end if;
    end;
    /
    EOF
    echo $?

    i havent tested this as i am not very good at shell scripts

    the crux of the logic is raise_application_error( (-20000-224) - :temp, 'test' );

    We can raise errors in a given range but the probelm lies with unix shell as the shell will only keep an unsigned byte in the status return value (values 0..255). It takes our exit file and just looks at that last byte. By using -20000-224 and subtracting from the your return code -- we end up exiting with the value of your return code

    regards
    Hrishy

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that doesnt work =(

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    there is a function called sqlerrm. Have a search coz i don't remember the syntax.
    Vinit

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hello

    I think its working for me .well i am not sure why it is not working on your machine.

    regards
    Hrishy

  6. #6
    Join Date
    Mar 2001
    Posts
    314
    Here's a quick and dirty solution

    Put the following (sql commands) in a shell script

    #!/bin/sh
    # tt
    sqlplus -s uid/pwd@svc < select * from kk;
    EOF
    # end tt

    at the shell prompt try the following:

    $ chmod 755 tt
    $ tt | grep ORA | cut -c 5-9

    -amar

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    whenever sqlerror exit sql.sqlcode

    is definitely the key. I use this all the time when communicating back to a ksh.
    Jeff Hunter

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi marist, that works but the code it returns is incorrect, if you get ORA-0942 it will return $? = 174 because in UNIX only 0 - 255 is used

    Amar I understand it´s easier use a file and treat the file, I thought there is another way of doing it

    Vinit sqlerrm works in PL/SQL but not SQL

    hrishy what is the $? you get?

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Ah, yes. I didn't fully read the question. I usually only care if the return code is 0 or not.

    I don't think there is any way you will be able to return a status > 255. You can return the exit status in an environment variable and exit with a non-zero value. When you exit with the non-zero value you can then check the special environment variable.
    Jeff Hunter

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

    That was a typing mistake from me i am extremely sorry.here is teh right solution .


    #!/bin/bash
    sqlplus -s user/passwd@move << EOF |grep '^ORA'
    select * from kk;
    set echo on
    variable temp number
    whenever sqlerror exit sql.sqlcode;
    begin
    :temp := 25; -- this is really a function call, not 25
    if ( :temp <> 0 )
    then
    raise_application_error( (-20000-224) - :temp, 'test' );
    end if;
    end;
    /
    EOF
    set x=$?
    echo $?

    now save this file as test.sh

    and now do

    sh test.sh

    output would be
    ORA-00942: table or view does not exist
    ERROR at line 1:
    ORA-20279: test
    ORA-06512: at line 5


    regards
    Hrishy




    [Edited by hrishy on 11-22-2001 at 01:45 AM]

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