executing a package in sql plus

[mkey]

hi all,
my package and package body look like this:

CREATE OR REPLACE PACKAGE Employee_RefCur_pkg
AS
TYPE empcur IS REF CURSOR;
PROCEDURE EmployeeSearch(o_EmpCursor OUT empcur);
END Employee_RefCur_pkg;


CREATE OR REPLACE PACKAGE BODY Employee_RefCur_pkg
AS
PROCEDURE EmployeeSearch(o_EmpCursor OUT empcur)
IS
BEGIN
OPEN o_EmpCursor FOR
SELECT emp.empno,
emp.ename,
emp.job,
emp.sal,
dept.dname,
dept.loc
FROM emp, dept
ORDER BY UPPER(emp.ename);
END EmployeeSearch;
END Employee_RefCur_pkg;

In sql plus I executed the package like this:

variable v1 refcursor
select employee_refcur_pkg.employeesearchv1) from dual;

i'm getting an invalid column errorra-00904

any thoughts will help!

 

[ThomVF]

Do you have access to emp and demp ? Try cutting that SELECT statement out and running it from SQL*Plus "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[mkey]

I have done that. I just selected the SELECT STATEMENT. It works by it self.

 

[ThomVF]

Did you create the package using the same user as you ran the SELECT test ?

Perhaps you have an invoker versus definer problem? "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[mkey]

I am in scott user. I have all the tables and packages within scott user.

This is driving me crazy!

Thank you for your help!

 

[ThomVF]

Did you try EXEC'ing the proc ?


variable v1 refcursor
exec employee_refcur_pkg.employeesearchv1);
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[mkey]

hey exec worked. but i thought packages cannot use exec??? anyways thanks for all your help!

 

[jee]

Just a note - your original attempt to execute the packaged procedure (by Selecting from dual) would probably have worked if packaged module was a function rather than a procedure. With procedues, even in a package, you have to use the EXEC command in SQL*PLUS.

 

[sem]

Jee: even with functions this could not work, due to at least 2 reasons:

1. SQL doesn't support this (refcursor) type.
2. Functions with OUT arguments can not be called from SQL.

Regards, Dima