dynamic pl-sql and use of & [message #35817] |
Wed, 17 October 2001 06:48 |
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.
----------------------------------------------------------------------
|
|
|
|