Original Post by Tom Kyte
create or replace type myScalarType as object
( rnum number, cname varchar2(30), val varchar2(4000) )
/
create or replace type myTableType as table of myScalarType
/
create or replace
function cols_as_rows( p_query in varchar2 ) return myTableType
-- this function is designed to be installed ONCE per database, and
-- it is nice to have ROLES active for the dynamic sql, hence the
-- AUTHID CURRENT_USER
authid current_user
-- this function is a pipelined function -- meaning, it'll send
-- rows back to the client before getting the last row itself
-- in 8i, we cannot do this
PIPELINED
as
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_descTbl dbms_sql.desc_tab;
l_rnum number := 1;
begin
-- parse, describe and define the query. Note, unlike print_table
-- i am not altering the session in this routine. the
-- caller would use TO_CHAR() on dates to format and if they
-- want, they would set cursor_sharing. This routine would
-- be called rather infrequently, I did not see the need
-- to set cursor sharing therefore.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
end loop;
-- Now, execute the query and fetch the rows. Iterate over
-- the columns and "pipe" each column out as a separate row
-- in the loop. increment the row counter after each
-- dbms_sql row
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt
loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
pipe row
(myScalarType( l_rnum, l_descTbl(i).col_name, l_columnValue ));
end loop;
l_rnum := l_rnum+1;
end loop;
-- clean up and return...
dbms_sql.close_cursor(l_theCursor);
return;
end cols_as_rows;
/
set pagesize 1000
col val for a15
select *
from TABLE( cols_as_rows('select *
from user_tables
where rownum=1 ') );
select *
from TABLE( cols_as_rows('select *
from user_tables
where table_name = ''&TABLE_NAME'' ') );
SQL> select *
2 from TABLE( cols_as_rows('select *
3 from user_tables
4 where table_name = ''&TABLE_NAME'' ') );
Enter value for table_name: TOTO
old 4: where table_name = ''&TABLE_NAME'' ') )
new 4: where table_name = ''TOTO'' ') )
RNUM CNAME VAL
---------- ------------------------------ ---------------
1 TABLE_NAME TOTO
1 TABLESPACE_NAME SYSTEM
1 CLUSTER_NAME
1 IOT_NAME
1 STATUS VALID
1 PCT_FREE 10
1 PCT_USED 40
1 INI_TRANS 1
1 MAX_TRANS 255
1 INITIAL_EXTENT 65536
1 NEXT_EXTENT
1 MIN_EXTENTS 1
1 MAX_EXTENTS 2147483645
1 PCT_INCREASE
1 FREELISTS 1
1 FREELIST_GROUPS 1
1 LOGGING YES
1 BACKED_UP N
1 NUM_ROWS 1
1 BLOCKS 1
1 EMPTY_BLOCKS 0
1 AVG_SPACE 0
1 CHAIN_CNT 0
1 AVG_ROW_LEN 2
1 AVG_SPACE_FREELIST_BLOCKS 0
1 NUM_FREELIST_BLOCKS 0
1 DEGREE 1
1 INSTANCES 1
1 CACHE N
1 TABLE_LOCK ENABLED
1 SAMPLE_SIZE 1
1 LAST_ANALYZED 31-JUL-08
1 PARTITIONED NO
1 IOT_TYPE
1 TEMPORARY N
1 SECONDARY N
1 NESTED NO
1 BUFFER_POOL DEFAULT
1 ROW_MOVEMENT DISABLED
1 GLOBAL_STATS YES
1 USER_STATS NO
1 DURATION
1 SKIP_CORRUPT DISABLED
1 MONITORING YES
1 CLUSTER_OWNER
1 DEPENDENCIES DISABLED
1 COMPRESSION DISABLED
1 DROPPED NO
48 rows selected.
SQL>