how to put error message

jemz · #1 (**permalink**) 08-10-11, 19:06

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.

n_i · #2 (**permalink**) 08-10-11, 22:02

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.

przytula_guy · #3 (**permalink**) 08-11-11, 02:02

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

tonkuma · #4 (**permalink**) 08-11-11, 03:23

How about this article in developerWorks?

Making Operating System Calls from SQL

sathyaram_s · #5 (**permalink**) 08-11-11, 04:59

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 @

jemz · #6 (**permalink**) 08-11-11, 10:29

Quote:

Originally Posted by przytula_guy

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

jemz · #7 (**permalink**) 08-12-11, 00:56

Quote:

Originally Posted by tonkuma

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

przytula_guy · #8 (**permalink**) 08-12-11, 01:54

there are many samples in the doc
Looping statements in SQL procedures

jemz · #9 (**permalink**) 08-12-11, 05:14

Quote:

Originally Posted by przytula_guy

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

db2girl · #10 (**permalink**) 08-12-11, 08:10

jemz, sir, how do you know all responders are male?

sathyaram_s · #11 (**permalink**) 08-12-11, 08:45

Quote:

Originally Posted by db2girl

jemz, sir, how do you know all responders are male?

Easy on him/her

BTW, what language is this " Белла "

db2girl · #12 (**permalink**) 08-12-11, 09:06

Russian Alphabet - Cyrillic alphabet

jemz · #13 (**permalink**) 08-12-11, 11:48

Quote:

Originally Posted by db2girl

jemz, sir, how do you know all responders are male?

huh?

because they use male name...

jemz · #14 (**permalink**) 08-12-11, 12:06

Quote:

Originally Posted by przytula_guy

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

jemz · #15 (**permalink**) 08-13-11, 03:27

Quote:

Originally Posted by tonkuma

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

Internet Services

Web Development

Digital Marketing

Consumer Tech