Home » SQL & PL/SQL » SQL & PL/SQL » dynamic pl-sql and use of &
dynamic pl-sql and use of & [message #35817] Wed, 17 October 2001 06:48 Go to next message
ash
Messages: 43
Registered: February 2001
Member
I have two problems.

1. I want to return a cursor from stored procedure which I declare as

TYPE RepCursor IS REF CURSOR RETURN RefRec;

where RefRec is record type. Now I do the following

test(v_refcursor IN OUT RepCursor,
p_name In VARCHAR2)

begin;
v_refcursor := DBMS_SQL.OPEN_CURSOR;
end;

It doesn't allow me to do so.

2. I have a stored procedure

test(v_refcursor IN OUT RepCursor, p_name IN VARCHAR2, p_city IN VARCHAR2)

v_defval VARCHAR2(30);
v_groupfield VARCHAR2(40);
begin
v_defval := '"A "';

IF length(rtrim(p_city) > 0 then
v_groupfield = p_city;
else
v_groupfield = v_defval;
end if;

OPEN v_refcursor FOR
select emp.id, emp.name, community.cityname, v_groupfield
from emp, community
where emp.commcode = community.code
and emp.name like p_name;
end;

Now when I run this procedure test(test_cursor, 'JOE', 'Community.cityname')
I want actual city names and not 'community.cityname'.

I don't know if I can use & or anything else which refers the address.

Please reply me at earliest.

Thanks in advance.

----------------------------------------------------------------------
Re: dynamic pl-sql and use of & [message #35841 is a reply to message #35817] Thu, 18 October 2001 11:43 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
1.) I'm not aware that dbms_sql can open a ref cursor.
2.) Try defining a WEAK ref cursor i.e.
TYPE RepCursor IS REF CURSOR;
this allows the output format to be determined at runtime. Now you can dynamically build the "open v_refcursor for" based on the column(s) you want back.

the following is a are useful links:
http://osi.oracle.com/~tkyte/ResultSets/index.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:196683::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1640161160708,

----------------------------------------------------------------------
Previous Topic: How to (auto-)recompile dependencies?
Next Topic: how to find the top 5 biggest value from a query
Goto Forum:
  


Current Time: Thu Jun 17 21:03:20 CDT 2021