This discussion is archived
4 Replies Latest reply: May 2, 2013 6:25 AM by CraigB RSS

Show the result of execution of a database procedure

Erdem_ Newbie
Currently Being Moderated
Hi everyone,

I want to execute an oracle database procedure in oracle forms environment and show the result of execution to user(as "procedure executed successfully".. etc) How can I achieve this ?

Thanks..
  • 1. Re: Show the result of execution of a database procedure
    Christian Erlinger Guru
    Currently Being Moderated
    That would depend on the implementation of your procedure. I would say if there is no exception thrown then the procedure completed successfully. Of course there might be some additional conditions when you consider your procedure run successful.

    So in the end you have to design your procedure so the caller can recognize if the procedure run successful or not. How you want to do this is up to you; you can use user defined exceptions (raise_application_error) if an error in your logic occurs which is not a default oracle exceptions, functions which return booleans or output stati.
    I for one prefer exceptions, as they force the caller to handle them (I add the user-defined exceptions thrown by each procedure in the specification right to the documentation so the one using my procedure sees it right away) and ensure my procedure is atomic.
    But as said this is entirely up to you.

    cheers
  • 2. Re: Show the result of execution of a database procedure
    Erdem_ Newbie
    Currently Being Moderated
    Hi,
    When I run the procedure from Toad(right-click procedure-execute) , it shows me alert as "Procedure Completed". Can I get this result to forms environment ?
  • 3. Re: Show the result of execution of a database procedure
    Christian Erlinger Guru
    Currently Being Moderated
    The success message in SQL*Plus/Toad/SQL Developper/whatever is shown when no exceptions are thrown by your procedure. Basically this would be something like
    begin
      your_procedure;
      message('Procedure Completed');
    exception
      when others then
        message('Procedure run with errors: '||sqlerrm);
    end;
    However as said depending on the implementation of your procedure this is correct or not.

    cheers
  • 4. Re: Show the result of execution of a database procedure
    CraigB Guru
    Currently Being Moderated
    If you really want to know the outcome of your database procedure, you could do one of three things.

    First, use defined exceptions (as Christian suggested) or you could convert the procedure to a function that returns a success or failure value or returns the exception. The other option would be to add and OUT parameter to your procedure that basically does the same thing; returns a success or failure value or the exception thrown.

    Craig...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points