This discussion is archived
13 Replies Latest reply: May 23, 2013 9:46 AM by jgarry RSS

How to show  success/ failure information

936666 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    Any suggestions..!
  • 2. Re: How to show  success/ failure information
    John Stegeman Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    In the above how to handel the exception
  • 10. Re: How to show  success/ failure information
    John Stegeman Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Be sure you understand the rending of garments here: http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

Legend

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