Ref Cursor and type

[nitai_co]

Hi all

I finally got the Ref Cursor stored procedure working but am stuck on the
following. I have this package body:

CREATE OR REPLACE PACKAGE BODY PACK_MYCURSOR AS
PROCEDURE REFCUR (
theuserid IN OUT users.user_id%type,
userfirstname OUT users_TableRows) IS
BEGIN
OPEN userfirstname FOR
select * from users
where user_id = theuserid
;
END REFCUR ;
END PACK_MYCURSOR ;
/

Now this is working fine, but I would need to have explisit fields in the
select statement, like "select user_id from users", but when I do this then I
get error messages of "expression is of wrong type". Anybody know how to solve
this?

Thank you.

[paross1]

Declare your refererence cursor as WEAK type instead of STRONG. In other words,
do not specify RETURN rowtype%ROWTYPE in your declaration in the spec.

Declare as:
TYPE users_TableRows IS REF CURSOR;

Phil

[nitai_co]

You don't know how many hours I spend on this...Thank you. I am in the middle of reading a PL/SQL Book but some things are just not so easy to crasp :-)

[nitai_co]

Just another quick question in this regard.

We are using quite a lot of stored procedures with "Execute immediate" which
allows us to create dynamic SQL. Now is this possible with the package specs
too?

I have this:

CREATE OR REPLACE PACKAGE similar AS
TYPE users_TableRows IS REF CURSOR;
PROCEDURE img_colour (
theimgid IN OUT users.user_id%type,
thecur OUT users_TableRows);
END similar ;
/

We would need to have the "users" table to be dynamic, since some use the
tables with "test_users" and others with "demo_users". Is this even possible?

[paross1]

If you want a GREAT PL/SQL reference, read Oracle PL/SQL Programming by Steven
Feuerstein from O'Reilly. In my opinion, Feuerstein is the guru of gurus for
PL/SQL, and he is easy to read and follow.

Phil

[nitai_co]

Hi Phil

That's the one I am reading :-)