Hi All,
I have created a function which returns a sys_refcursor but when calling this function, I get the result like below:
create or replace function test009 return sys_refcursor is
v_curs sys_refcursor;
begin
open v_curs for select * from all_objects where owner = 'SYS' and object_type = 'CLUSTER';
return v_curs;
end;
/
select test009 from dual;
Result (I get result something like below):
TEST009
{<OWNER=SYS,OBJECT_NAME=C_TS#,SUBOBJECT_NAME=null,OBJECT_ID=6,DATA_OBJECT_ID=6,OBJECT_TYPE=CLUSTER,CREATED=26-SEP-08,LAST_DDL_TIME=26-SEP-08,TIMESTAMP=2008-09-26:13:59:45,STATUS=VALID,TEMPORARY=N,GENERATED=N,SECONDARY=N,NAMESPACE=5,EDITION_NAME=null>,<OWNER=SYS,OBJECT_NAME=C_OBJ#,SUBOBJECT_NAME=null,OBJECT_ID=2,DATA_OBJECT_ID=2,OBJECT_TYPE=CLUSTER,CREATED=26-SEP-08,LAST_DDL_TIME=26-SEP-08,TIMESTAMP=2008-09-26:13:59:45,STATUS=VALID,TEMPORARY=N,GENERATED=N,SECONDARY=N,NAMESPACE=5,EDITION_NAME=null>,}
How can I get a result set which we usually get when we do a select query with proper column names and their values.
I know, there is one technique where we can return an object type from a function which could be used to return multiple rows with appropriate columns. But, how can we do it with functions returning sys_refcursor.
Basically, I would like to call this function and have the result set shown with column names (which we normally get when we do a select * from table).
Thanks.