Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Give error code from sqlplus to shell script

    Hello,
    First sorry for my englis I'm french.

    I try to use 'whenever sqlerror exit sql.sqlcode' to pass the SQL error code to the bash but it doesn't work:
    This is my script It is in a file file_name.ksh :


    sqlplus -s /nolog << END | tee -a $LogFile
    connect $CidUser/$CidPwd
    whenever sqlerror exit sql.sqlcode
    alter session enable parallel dml;
    select /*+ parallel (HIST_REQUEST 4) */ count(0) "HIST_REQUEST" from HIST_REQUEST ;
    select /*+ parallel (HIST_REQUEST_PARAM 4) */ count(0) "HIST_REQUEST_PARAM" from HIST_REQUEST_PARAM ;

    set timing on;
    DECLARE
    CURSOR req_cursor(nbdays NUMBER) IS
    SELECT /*+ parallel (REQUEST 4) */
    DISTINCT REQUEST_ID
    FROM REQUEST
    WHERE PARENT_REQUEST_ID IS NULL
    AND REQUEST_STATUS_CODE in ( SELECT /*+ PARALLEL (REQUEST_STATUS 2 ) */ REQUEST_STATUS_CODE FROM REQUEST_STATUS WHERE REQUEST_PROCESSED = 1)
    AND TRUNC ( SYSDATE ) - TRUNC (REQUEST_STATUS_CHANGE_DATE ) >= nbdays ;
    nbloop integer := 0;
    nbloop2 integer := 0;
    maxloop integer := 4000;
    BEGIN
    FOR req_record IN req_cursor(40) LOOP
    /*
    ** Delete the child REQUEST and the associated REQUEST_PARAM
    ** This process need to be done recursively.
    */
    PURGE_REQUEST_PRCA (req_record.REQUEST_ID);
    if (nbloop = 100) then
    COMMIT;
    nbloop2 := nbloop2 + nbloop;
    nbloop := 0;
    end if;
    nbloop := nbloop +1;
    -- EXIT WHEN nbloop2 >= maxloop;

    END LOOP;

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    END;
    /
    select /*+ parallel (HIST_REQUEST 4) */ count(0) "HIST_REQUEST" from HIST_REQUEST ;
    select /*+ parallel (HIST_REQUEST_PARAM 4) */ count(0)"HIST_REQUEST_PARAM" from HIST_REQUEST_PARAM ;
    END
    echo $?



    The echo $? give 0 and Idon't know how??? I gonna be crazy!!
    Thanks a lot for your help!!

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    if the SQL is run in a child process then it will be this child process that is exited and not the parent script you are running. (i think)

    Code:
    After calling the child process try something like
    if [ $? = "0" ]; then
     echo "child process Ran ok"
    else
     echo "child process failed"
     exit 3
    fi

  3. #3
    Join Date
    Dec 2002
    Posts
    5

    GIve SQL error to the script

    Originally posted by WingMan
    if the SQL is run in a child process then it will be this child process that is exited and not the parent script you are running. (i think)

    Code:
    After calling the child process try something like
    if [ $? = "0" ]; then
     echo "child process Ran ok"
    else
     echo "child process failed"
     exit 3
    fi

    Thanks a lot for your help.
    So i don't know if it's a child process and i don't think so.
    The 'echo $?' instruction I have written after the SQL script is done to see what is the error code. But it's always 0 and i don't understand. I have written a call to a SQL stocked procedure which doesn't exist to exit with an error but it 's always 0.
    That's the real problem.

  4. #4
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    'echo $?' will return the error status of the last command executed.

    Have you tried just one SQL statement like (below) and tried to test $?
    Code:
    select /*+ parallel (HIST_REQUEST_PARAM 4) */ 
    count(0)"HIST_REQUEST_PARAM" 
    from HIST_REQUEST_PARAM ;

  5. #5
    Join Date
    Mar 2008
    Posts
    1
    Reraise exception in your exception handler routine.

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
    END;

Posting Permissions

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