Home » SQL & PL/SQL » SQL & PL/SQL » need help in my code
icon7.gif  need help in my code [message #333612] Sun, 13 July 2008 11:30 Go to next message
EL-SAYED
Messages: 17
Registered: July 2008
Junior Member
dear all
how are you ?
first i want to say that I'm very Happy for joining this forum.
second :i have a problem in my pl\sql code that always raise the exception in both cases when i write true deptno or false deptno

the code is
declare 
cursor cur_emp is select last_name,salary from employees where department_id=&DEPT;
v_emp cur_emp%rowtype;
ERROR_DEPT EXCEPTION;

begin 
 open cur_emp;
  loop
    fetch cur_emp into v_emp;
  
    exit when cur_emp%notfound;
    dbms_output.put_line(v_emp.last_name||' get '||v_emp.salary) ;
 end loop;

  IF CUR_EMP%NOTFOUND 
  THEN 
     RAISE ERROR_DEPT;
  END IF ;

 close cur_emp;

EXCEPTION
  WHEN ERROR_DEPT
  THEN 
  DBMS_OUTPUT.PUT_LINE('WRONGE DEPTNO  TRY AGAIN.......');

end ;

----------------
thank you for all.

[Mod-edit: Frank added [code]-tags to improve readability]

[Updated on: Sun, 13 July 2008 11:56] by Moderator

Report message to a moderator

Re: need help in my code [message #333614 is a reply to message #333612] Sun, 13 July 2008 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

>i have a problem in my pl\sql code that always raise the exception

Exception? What exception? I don't see any exception posted.
Re: need help in my code [message #333619 is a reply to message #333612] Sun, 13 July 2008 11:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread your code. Pretend to be "the computer" executing it. How can the flow ever get to the end of the procedure without the error being raised?

Furthermore: I added a [code] tag before your code and a closing [/code] after it, so your code is more readable. Please do this in future posts. Any questions, see the Forum Guide.
Re: need help in my code [message #333912 is a reply to message #333619] Mon, 14 July 2008 19:38 Go to previous messageGo to next message
EL-SAYED
Messages: 17
Registered: July 2008
Junior Member
DEAR .FRANK

THANK YOU FOR YOUR INTEREST

I MEAN THAT TO SHOW MESSAGE TO THE END USER TELL HIM THAT "ENTER RIGHT DEPARTMENT ID "

IN ANOTHER MEANING I WANT TO HANDEL THE ERROR THAT MAY HAPPEND IN THE CURSOR IF HE ENTER WRONGE DEPTNO.


Re: need help in my code [message #333913 is a reply to message #333614] Mon, 14 July 2008 20:01 Go to previous messageGo to next message
EL-SAYED
Messages: 17
Registered: July 2008
Junior Member
DEAR

THANK YOU FOR YOUR NOTES
MY CODE IS

DECLARE
  CURSOR CUR_EMP IS 
    SELECT LAST_NAME,
           SALARY
    FROM   EMPLOYEES
    WHERE  DEPARTMENT_ID = &DEPT;
   V_EMP  CUR_EMP%ROWTYPE;
   ERROR_DEPT  EXCEPTION;
BEGIN
  OPEN CUR_EMP;
  
  LOOP
    FETCH CUR_EMP INTO V_EMP;
    
    EXIT WHEN CUR_EMP%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(V_EMP.LAST_NAME
                         ||' get '
                         ||V_EMP.SALARY);
  END LOOP;
  
  IF CUR_EMP%NOTFOUND THEN
    RAISE ERROR_DEPT;
  END IF;
  
  CLOSE CUR_EMP;
EXCEPTION
  WHEN ERROR_DEPT THEN
    DBMS_OUTPUT.PUT_LINE('WRONGE DEPTNO  TRY AGAIN.......');
END;

Re: need help in my code [message #333914 is a reply to message #333612] Mon, 14 July 2008 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It appears you are incapable or unwilling to read & follow the Posting Guidelines.

You're On Your Own (YOYO)!
Re: need help in my code [message #333937 is a reply to message #333612] Mon, 14 July 2008 23:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@EL-SAYED:
It seems that your Caps Lock is stuck, so, please, change your keyboard as posting in all caps is consired shouting.

When using cursor, did you try to study its behaviour (and go through examples) in Oracle documentation, found e.g. online on http://tahiti.oracle.com/? As you did not provide your Oracle version, you shall find corresponding part in PL/SQL User's Guide and Reference yourself.
Re: need help in my code [message #333998 is a reply to message #333913] Tue, 15 July 2008 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@El-Sayed,

YOu need to solve this one by yourself - being able to work through the execution of code in your mind is a vital skill for a developer.

<hint>
You say that the problem is that after leaving the Cursor loop, it always goes into the code triggered by the '%NOTFOUND check.

You need to look at your Cursor loop, and consider all the ways in which the program flow can leave it.
</hint>


Re: need help in my code [message #335072 is a reply to message #333612] Sun, 20 July 2008 15:14 Go to previous messageGo to next message
EL-SAYED
Messages: 17
Registered: July 2008
Junior Member
thank you for all

finally: i want an example to handle the error "no_data_found"
it is my be happend in the cursor .
Re: need help in my code [message #335073 is a reply to message #335072] Sun, 20 July 2008 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
EL-SAYED wrote on Sun, 20 July 2008 13:14
thank you for all

finally: i want an example to handle the error "no_data_found"
it is my be happend in the cursor .



http://asktom.oracle.com has many fine coding examples.
Re: need help in my code [message #335138 is a reply to message #335072] Mon, 21 July 2008 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The code you have posted will not raise a No Data Found. Cursors do not raise this exception - they will simply set the %NOTFOUND flag on the first fetch if they return no rows.
Re: need help in my code [message #335305 is a reply to message #333612] Mon, 21 July 2008 15:20 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Nevermind

[Updated on: Mon, 21 July 2008 15:21]

Report message to a moderator

Previous Topic: commit based on Group name
Next Topic: Percent_Rank Problem
Goto Forum:
  


Current Time: Sat Dec 03 03:41:04 CST 2016

Total time taken to generate the page: 0.09881 seconds