Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

cursor for loop with exception handling

Ricky007Jan 16 2015 — edited Jan 28 2015

Dear all,    

     I am using oracle11g  I need to write a stored procedure for around 10 select statements with exception handling for each select statement. I'am unable to handle exception handling.please help guys

Here is the sample code i have written

CREATE OR REPLACE PROCEDURE TEST_PROC

( P_ID IN VARCHAR2,

P_MOB IN VARCHAR2,

P_OUT_MSG OUT VARCHAR2 )

AS

CURSOR CUR_RES AS SELECT INFO,NAME,AGE,VER_DETAILS  FROM TABLE_NAME WHERE ID=P_ID AND MOBILE_NO=P_MOB;

BEGIN

FOR REC IN CUR_RES LOOP

P_OUT_MSG := 'BKY000'

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

P_OUT_MSG := 'BKY001'

END;

DESIRED OUTPUT REQUIRED WHILE RUNNING IN JAVA APPLICATION IS

IF DATA FOUND BKY000 | RESULT OF SELECT STATEMENT

IF DATA NOT FOUND  BKY001

PLEASE HELP WITH SAMPLE ...PLZZZZZZZZZZZZZ FRIENDS

This post has been answered by Etbin on Jan 18 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 25 2015
Added on Jan 16 2015
34 comments
18,525 views