13 Replies Latest reply: May 23, 2013 11:46 AM by jgarry RSS

    How to show  success/ failure information

    936666
      Hi,
      How to show the Success/ Failure information to outer world.
      Will it be able for java or .net to read the information

      Say this is my SP

      Please note it is a sample SP for my understanding
      Create Table Test(Col1 number(5),Col2 Varchar2(30));
      
      Create or replace procedure P_Test (P_check char,p_Col1 number ,p_Col2 Varchar2)
      as
      begin
      If P_check <=5 then
      dbms_output.put_line (1 ||','|| 'Please pass more than 5');
      return;
      end if;
      Insert into Test (Col1, Col2)
      Values (p_Col1,p_Col2);
      Commit;
      dbms_output.put_line (0 ||','||p_Col1 ||','|| p_Col2);
      End;
      exec P_Test(1,1,'test');

      what i have given now will work with java and .net or i need to add the output parameter in the stored procedure like
      Create or replace procedure P_Test_V1 (P_check char,
      p_Col1 number ,
      p_Col2 Varchar2,
      P_Msg_Details Out varchar )
      as
      begin
      If P_check <=5 then
      P_Msg_Details :=(1 ||','|| 'Please pass more than 5');
      return;
      end if;
      Insert into Test (Col1, Col2)
      Values (p_Col1,p_Col2);
      Commit;
      P_Msg_Details :=(0 ||','||p_Col1 ||','|| p_Col2);
      End;
      var l_Msg_Details VARCHAR2(200);
      exec P_Test_V1(1,1,'test', :l_Msg_Details);
      print l_Msg_Details;
        • 1. Re: How to show  success/ failure information
          936666
          Any suggestions..!
          • 2. Re: How to show  success/ failure information
            John Stegeman
            Yes, some suggestions.

            Stop using the 1970's paradigm of return codes to communicate success/failure.

            Have a read about exceptions - they are the proper way.
            • 3. Re: How to show  success/ failure information
              936666
              John Stegeman wrote:
              Yes, some suggestions.

              Stop using the 1970's paradigm of return codes to communicate success/failure.

              Have a read about exceptions - they are the proper way.
              I need to give away my own msg when there is an failure for that cause from my error table.
              • 4. Re: How to show  success/ failure information
                EdStevens
                933663 wrote:
                Any suggestions..!
                Patience, Grasshopper

                You posted this follow-up a mere 2+ hours after your previous post.

                This forum is not a chat line, and it is not paid support.

                Everyone here has a job for which they are paid, and this forum is not it.

                No one is responsible for monitoring it and giving a quick response.

                Furthermore, it is a global forum. The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours.

                Your original post went up in the middle of the night for half the world.

                No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.
                • 5. Re: How to show  success/ failure information
                  John Stegeman
                  Patience, Grasshopper
                  I chuckle every time I see this, as my son's nickname means "grasshopper" in the local language.
                  • 6. Re: How to show  success/ failure information
                    Billy~Verreynne
                    933663 wrote:
                    John Stegeman wrote:
                    Yes, some suggestions.

                    Stop using the 1970's paradigm of return codes to communicate success/failure.

                    Have a read about exceptions - they are the proper way.
                    I need to give away my own msg when there is an failure for that cause from my error table.
                    Have a read about exceptions - they are the proper way.

                     
                    PS. What don't you understand about the statement made??
                    • 7. Re: How to show  success/ failure information
                      936666
                      what i have given now will work with java and .net or i need to add the output parameter in the stored procedure?
                      • 8. Re: How to show  success/ failure information
                        John Stegeman
                        What you have given now, while it may "work" is not best practices. It is all too easy for the calling Java or .NET or whatever_type_of_code_you're_using code to ignore the return code.

                        Exceptions are the best way of doing things because you actually have to make an effort to ignore them.
                        • 9. Re: How to show  success/ failure information
                          936666
                          In the above how to handel the exception
                          • 10. Re: How to show  success/ failure information
                            John Stegeman
                            Well, technically, my suggestion was for you to read about and understand exceptions, not for me to read the documentation and explain it to you.

                            You can go to http://tahiti.oracle.com to find the documentation, or simply use Google to search for PL/SQL exceptions and find all kinds of lovely documentation and examples.

                            If, after doing that, you had a specific question about something you didn't understand, that would be an appropriate time for you to ask in the forums.
                            • 11. Re: How to show  success/ failure information
                              936666
                              I have a chance to see the below website

                              http://www.techonthenet.com/oracle/exceptions/named_system.php
                              CREATE OR REPLACE PROCEDURE add_new_supplier
                                 (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
                              IS
                              
                              BEGIN
                                 INSERT INTO suppliers (supplier_id, supplier_name )
                                 VALUES ( supplier_id_in, supplier_name_in );
                              
                              EXCEPTION
                                 WHEN DUP_VAL_ON_INDEX THEN
                                    raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
                              
                                 WHEN OTHERS THEN
                                    raise_application_error (-20002,'An error has occurred inserting a supplier.');
                              
                              END;
                              it has made as raise_application_error where as i need to display the information from my table not hardcoded at each at every stored procedure
                              EXCEPTION
                                 WHEN DUP_VAL_ON_INDEX THEN
                              Select errmsg from myerrortable where errorid=10;
                                 WHEN OTHERS THEN
                              Select errmsg from myerrortable where errorid=11;
                              some thing like above
                              • 12. Re: How to show  success/ failure information
                                EdStevens
                                933663 wrote:
                                I have a chance to see the below website

                                http://www.techonthenet.com/oracle/exceptions/named_system.php
                                CREATE OR REPLACE PROCEDURE add_new_supplier
                                (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2)
                                IS
                                
                                BEGIN
                                INSERT INTO suppliers (supplier_id, supplier_name )
                                VALUES ( supplier_id_in, supplier_name_in );
                                
                                EXCEPTION
                                WHEN DUP_VAL_ON_INDEX THEN
                                raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.');
                                
                                WHEN OTHERS THEN
                                raise_application_error (-20002,'An error has occurred inserting a supplier.');
                                
                                END;
                                it has made as raise_application_error where as i need to display the information from my table not hardcoded at each at every stored procedure
                                EXCEPTION
                                WHEN DUP_VAL_ON_INDEX THEN
                                Select errmsg from myerrortable where errorid=10;
                                WHEN OTHERS THEN
                                Select errmsg from myerrortable where errorid=11;
                                some thing like above
                                So code it to do that. Just because the example showed a hard-coded value for the error message doesn't mean you can't retrieve the message text from a table and insert it.
                                • 13. Re: How to show  success/ failure information
                                  jgarry
                                  Be sure you understand the rending of garments here: http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html