Home » SQL & PL/SQL » SQL & PL/SQL » Y is this not working..Urgent ..Plz
Y is this not working..Urgent ..Plz [message #191145] Tue, 05 September 2006 00:51 Go to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

CREATE or replace PACKAGE emp_data AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER);
END emp_data;


create OR REPLACE package body emp_data as
procedure open_emp_cv(emp_cv IN OUT EMPCURTYP,CHOICE IN NUMBER)
is
BEGIN
IF CHOICE=1 THEN
OPEN EMP_CV FOR SELECT  * FROM EMP WHERE SAL>1000;
ELSIF CHOICE=2 THEN
OPEN EMP_CV FOR SELECT * FROM EMP WHERE COMM is not null;
END IF;
END OPEN_EMP_CV;
END EMP_DATA;


Here I am trying to get the records into emp_rec..but it shows an error.Y is it?

 DECLARE
 EMP_REC EMP%ROWTYPE;
  begin
  EMP_DATA.OPEN_EMP_CV(EMp_rec,1);
 dbms_output.put_line(emp_rec.ename);
 end;
 


The error is
Quote:

ERROR at line 1:
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'OPEN_EMP_CV'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored


Please let me know what to do..This is very urgent.
Regds
Siri..
Re: Y is this not working..Urgent ..Plz [message #191155 is a reply to message #191145] Tue, 05 September 2006 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Y is not a word.
Plz is not a word.
Urgent to you is of NO interest to me. Start earlier and it would not have been so urgent.

I think you should check the docs some more on how to use ref cursors. Looks like you used the examples from the docs; I must admit it isn't very clear there.
You only retrieve a pointer to the cursor, not the individual rows. It is your responsibility to fetch those rows after calling open_emp_cv.
Look at the signature of open_emp_cv and then at the datatypes of the parameter in your procedure call. They don't match.
Re: WHY is this not working.. [message #191159 is a reply to message #191155] Tue, 05 September 2006 01:41 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

 DECLARE
 EMP_REC  emp_data.EmpCurTyp;
 begin
 EMP_DATA.OPEN_EMP_CV(EMp_REC,1);
  dbms_output.put_line(emp_rec.ename);
  end;

Now also it is not working.

Error IS
Quote:

ERROR at line 1:
ORA-06550: line 5, column 31:
PLS-00487: Invalid reference to variable 'EMP_REC'
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored


Answer Please.
Re: WHY is this not working.. [message #191167 is a reply to message #191159] Tue, 05 September 2006 01:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Fetch!
you only have a cursor now, so create a loop and fetch the records from the cursor.
Re: WHY is this not working.. [message #191180 is a reply to message #191167] Tue, 05 September 2006 02:13 Go to previous messageGo to next message
sireeshraju
Messages: 32
Registered: October 2005
Member

Hi frank...
I am very sorry to say that I tried and I failed.Please help me with the answer.
Thanks in Advance
Siri..
Re: WHY is this not working.. [message #191186 is a reply to message #191159] Tue, 05 September 2006 02:26 Go to previous messageGo to next message
venkatsp
Messages: 22
Registered: March 2005
Junior Member
Try to fetch to recods first

using bulk collect .. or other fetch operations . it will work.

with out fetch you'are trying to display the information
Re: WHY is this not working.. [message #191191 is a reply to message #191180] Tue, 05 September 2006 02:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Like I said, you have to fetch

untested:
declare
  emp_cur emp_data.empcurtyp;
  emp_rec emp%rowtype;
begin
  emp_data.open_emp_cv(emp_cur, 1);
  loop
    fetch emp_cur into emp_rec;
    exit when emp_cur%notfound;
    dbms_output.put_line(emp_rec.ename);
  end loop;
end;
Re: WHY is this not working.. [message #191207 is a reply to message #191191] Tue, 05 September 2006 03:26 Go to previous message
sireeshraju
Messages: 32
Registered: October 2005
Member

Thanks a lot Frank... Razz
Previous Topic: Load Log file into Oracle
Next Topic: number to word ( first, second , third, forth ...etc)
Goto Forum:
  


Current Time: Tue Dec 06 08:07:50 CST 2016

Total time taken to generate the page: 0.25572 seconds