Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13

    Unanswered: Execution status of a procedure

    Hi,

    I have a unix script that calls a procedure in Oracle database. I need to do some other things in the script based on the successfull completion of the stored procedure. How do I know that the procedure has successfully completed before proceeding to my next task in the script?
    Thanks in advance for the help.

  2. #2
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    Since nobody responded to my question so far I assume that it isn't that easy to achieve this. What I am thinking is I could send the exceptions/errors to a file and then grep the file for any ORA errors. The thing is I have some exit conditions from the procedure other than the ORA errors. So is it possible to assign my own ORA error numbers for the user defined exceptions? Thanks

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't write Unix scripts much, but presumably the script doesn't actually call the procedure, rather it invokes a sqlplus session that calls the procedure? You can use the "EXIT <n>" and "WHENEVER SQLERROR EXIT <n>" sqlplus commands to return status info.

  4. #4
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    To be more specific I am calling the procedure from Unix script by invoking sqlplus.

    I don't think WHENEVER SQLERROR works for me because I need to exit the procedure if some conditions are not met prior to processing any SQL statement. I can list you the portion of the script that calls the procedure so that you will get a better understanding.

    sqlplus -s test_721_d/tecsys@fnok_dms >$LOGFILE <<-EOF
    set feed off veri off head off
    set serveroutput on
    exec gl_export_hyperion('$EXPFILE','$EXPDIR')
    set serveroutput off
    set feed on veri on head on
    EOF

    I need to continue with the script only if the sqlplus session is succes, no SQL errors, no exceptions, met all user defined conditions. Thanks.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    My understanding is that if the procedure raises an exception, then SQLERROR will be set to the ORA number of the exception raised - e.g. -20001 if you use RAISE_APPLICATION_ERROR(-20001,...).

  6. #6
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    That's true for exceptions. But what about user defined exit conditions where I don't raise an exception. eg. I don't want to continue the procedure if a particular table is empty. I just use RETURN to terminate the procedure, instead should I raise an exception that eventually the WHENEVER SQLERROR will catch?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes - if you just RETURN there is no way to tell that it was not successful, is there?

  8. #8
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    Absolutely. I tried it and it's working. Thanks for the help.

  9. #9
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    I thought it was working, but I am wrong. My unix script is not able to get the exit status from sqlplus, even though I could exit from sqlplus using WHENEVER SQLERROR EXIT. How do I pass the exit value(ie sql.sqlcode) to the unix script?

    Any way I found a work aroud by scanning my log file for ERROR lines and stop the process if any found.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

  11. #11
    Join Date
    Jan 2004
    Location
    USA
    Posts
    13
    Somehow it's still not working for me. As per Tom's script, I am supposed to get the exit code from the 'status' variable but I don't. I had send him a mail but he hasn't responded so far.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    He has publicly responded to your query on that page - go back and see! Scroll down to the bottom.

Posting Permissions

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