Help With Executing Stored Procedure 1

[gtb12314]

Hello Everyone,

Below is my package & procedure and the code to execute the procedure from SQL*PLUS.

(I)

create or replace package mypackage2
as
type myrecord is record
(myempno emp.empno%type,
myename emp.ename%type,
myjob emp.job%type,
mydeptno dept.deptno%type,
mydname dept.dname%type,
mydptno emp.deptno%type
);
type mycursortype2 is ref cursor return myrecord;
end mypackage2;
/

(II)

create or replace procedure myprocedurewrecord(mycursorvar2 in out mypackage2.mycursortype2)
is
begin
open mycursorvar2 for
select e.empno, e.ename, e.job, e.deptno as empdept,
d.deptno as deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
end;
/

(III)

declare
mycursorvar2 mypackage2.mycursortype2;
myresultset mycursorvar2%rowtype;
begin
myprocedurewrecord(mycursorvar2);
fetch mycursorvar2 into myresultset;
while mycursorvar2%found
loop
DBMS_OUTPUT.PUT_LINE(myresultset.myempno);
fetch mycursorvar2 into myresultset;
end loop;
end;
/

Whenever I am trying to execute the procedure(Code (III) above) from SQL*PLUS I am getting the following error:

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 6

Any Suggestions will be greatly appreciated.

I am trying to create a crystal report off of the oracle stored procedure. I am using crystal report XI & Oracle 9.2.0

Thank You

 

[LKBrwnDBA]

This:

select e.empno, e.ename, e.job, e.deptno as empdept,  
 d.deptno as deptno, d.dname

Does not match this:

type myrecord is record
(myempno emp.empno%type,
 myename emp.ename%type,
 myjob emp.job%type,
 mydeptno dept.deptno%type,
 mydname dept.dname%type,
 mydptno emp.deptno%type
);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb