4 Replies Latest reply: May 2, 2013 8:25 AM by CraigB RSS

    Show the result of execution of a database procedure

    Erdem_
      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
          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_
            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
              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
                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...