Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: how to put error message

    Hi,


    can you help me please,is it possible in the stored procedure to put error message or to print error message,example i want to find a certain employee id No. and it does not exist to the table employee then this will print that "Employee ID Does Not exist" else if the id is exist this will update the employee lastname.can you help me please on this, i don't have idea on this how to print error message and to search the employee i.d using the stored procedure.Thank you in advance.
    Last edited by jemz; 08-10-11 at 20:13.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    A stored procedure runs on the database server; where do you expect it to print anything?

    Use SIGNAL, RESIGNAL, or RAISE_ERROR() to report the condition to the client application, which can then print a message to the user. You can find examples in the manual.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in developerswork there was an article (I am looking for) that documented how to execute a command from a stored proc for debugging purposes..
    when I find the article, I will indicate the link..
    http://www.ibm.com/developerworks/da.../0211yip4.html
    Last edited by przytula_guy; 08-11-11 at 03:05.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this article in developerWorks?

    Making Operating System Calls from SQL

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    With 9.7, you can also do the following ...

    Pl note, this is untested code.

    Code:
    CREATE OR REPLACE PROCEDURE proc1()
    begin
    DECLARE EMPNOTF CONDITION FOR SQLSTATE '40999' ;
    DECLARE outfile  utl_file.file_type ;
    DECLARE EXIT HANDLER FOR EMPNOTF
    BEGIN
    -- The three methods can be used with one another too
    -- eg. use file output to record the output and the classic method to handle the error programatically
    --
    -- Classic method
    --SIGNAL SQLSTATE '40999' SET MESSAGE_TEXT='Employee Not found'  ;
    -- to the Std out
    --CALL DBMS_OUTPUT.PUT_LINE('Employee Not found') ;
    -- To a file
    SET  outfile=utl_file.fopen('/tmp','spout.txt','w') ;
    call utl_file.put_line(outfile,'Employee Not found') ;
    
    END ;
    SIGNAL SQLSTATE '40999' ;
    END
    @
    
    set serveroutput on @
    
    call proc1()  @
    
    set serveroutput off @
    Last edited by sathyaram_s; 08-11-11 at 06:00. Reason: formatting
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by przytula_guy View Post
    in developerswork there was an article (I am looking for) that documented how to execute a command from a stored proc for debugging purposes..
    when I find the article, I will indicate the link..
    Yet Another Article on Advanced Scripting
    Hi sir,Here is my stored procedure i get stuck on this,I don't know how. can you help me please how to do the for loop?is there a for loop in stored procedure i have not yet tried the loop controls in sp.please help me sir how to search the employee id so that i can update the table also to display message if does not exist.Thank you in advance.

    Create procedure search(in emp_id varchar(5))

    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

    DECLARE MYCURSOR CURSOR WITH RETURN TO CLIENT FOR
    DECLARE ID VARCHAR(5);

    BEGIN

    FETCH MY_CURSOR INTO ID;

    IF ID = EMP_ID THEN

    UPDATE LASTNAME FROM EMPLOYEE

    WHERE EMP_ID = ID;

    END IF;
    END

  7. #7
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    How about this article in developerWorks?

    Making Operating System Calls from SQL


    sir can you help me in searching the employee id with the use of fetch and cursor with return for,i have no idea in combining this...Thank you in advance

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there are many samples in the doc
    Looping statements in SQL procedures
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by przytula_guy View Post
    there are many samples in the doc
    Looping statements in SQL procedures


    Hello sir, Thank you for this link,i will try this and i will write again if i have doubt.more power to you....

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    jemz, sir, how do you know all responders are male?

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by db2girl View Post
    jemz, sir, how do you know all responders are male?
    Easy on him/her

    BTW, what language is this " Белла "
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  13. #13
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by db2girl View Post
    jemz, sir, how do you know all responders are male?


    huh?

    because they use male name...

  14. #14
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by przytula_guy View Post
    there are many samples in the doc
    Looping statements in SQL procedures

    HI, Sir...

    Can i ask something can i download the manuals just like the link that you give me,so that if i am not connecting to the internet i have a reference the manuals...can i download them all,please help me how to dowload the manuals?Thank you in advance...

  15. #15
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    How about this article in developerWorks?

    Making Operating System Calls from SQL

    sir, Here is my code i get problem on this in searching,please help me sir.Thank you in advance and I am hoping for your positive response.

    CREATE PROCEDURE SEARCH_ID (IN EMP_ID CHAR(6),)
    IN E_LASTNAME VARCHAR(30),
    OUT MESSAGE VARCHAR(100))




    BEGIN


    FOR ROW_VAR AS CUR_SEARCH CURSOR FOR
    IF EMPNO = EMP_ID
    UPDATE LASTNAME FROM EMPLOYEE
    WHERE EMPNO=EMP_ID;
    FETCH FIRST 1 ROWS ONLY

    SIGNAL SQLSTATE '80000' SET MESSAGE ='EMPLOYEE I.D DOES NOT EXIST';


    END FOR;

    END

Posting Permissions

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