Now, in the following code, which I use in sqlplus infrequently - i said "didn't do cursor_sharing..."
You might want to or to add bind variable support in some other fashion!!!
ops$tkyte%ORA9IR2> create or replace type myScalarType as object
2 ( rnum number, cname varchar2(30), val varchar2(4000) )
3 /
Type created.
ops$tkyte%ORA9IR2> create or replace type myTableType as table of myScalarType
2 /
Type created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace
2 function cols_as_rows( p_query in varchar2 ) return myTableType
3 -- this function is designed to be installed ONCE per database, and
4 -- it is nice to have ROLES active for the dynamic sql, hence the
5 -- AUTHID CURRENT_USER
6 authid current_user
7 -- this function is a pipelined function -- meaning, it'll send
8 -- rows back to the client before getting the last row itself
9 -- in 8i, we cannot do this
10 PIPELINED
11 as
12 l_theCursor integer default dbms_sql.open_cursor;
13 l_columnValue varchar2(4000);
14 l_status integer;
15 l_colCnt number default 0;
16 l_descTbl dbms_sql.desc_tab;
17 l_rnum number := 1;
18 begin
19 -- parse, describe and define the query. Note, unlike print_table
20 -- i am not altering the session in this routine. the
21 -- caller would use TO_CHAR() on dates to format and if they
22 -- want, they would set cursor_sharing. This routine would
23 -- be called rather infrequently, I did not see the need
24 -- to set cursor sharing therefore.
25 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
26 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
27 for i in 1 .. l_colCnt loop
28 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
29 end loop;
30
31 -- Now, execute the query and fetch the rows. Iterate over
32 -- the columns and "pipe" each column out as a separate row
33 -- in the loop. increment the row counter after each
34 -- dbms_sql row
35 l_status := dbms_sql.execute(l_theCursor);
36 while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
37 loop
38 for i in 1 .. l_colCnt
39 loop
40 dbms_sql.column_value( l_theCursor, i, l_columnValue );
41 pipe row
42 (myScalarType( l_rnum, l_descTbl(i).col_name, l_columnValue ));
43 end loop;
44 l_rnum := l_rnum+1;
45 end loop;
46
47 -- clean up and return...
48 dbms_sql.close_cursor(l_theCursor);
49 return;
50 end cols_as_rows;
51 /
Function created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select *
2 from TABLE( cols_as_rows('select *
3 from emp
4 where rownum = 1') );
RNUM CNAME VAL
---------- ------------------------------ --------------------
1 EMPNO 7369
1 ENAME SMITH
1 JOB CLERK
1 MGR 7902
1 HIREDATE 17-DEC-80
1 SAL 800
1 COMM
1 DEPTNO 20
8 rows selected.