I have a function that receives a query as parameter (as clob type) and 'selects' this query's rows for returning. I need to use dbms_sql, because the query's size is larger than 32kb (~150kb).
I'm stuck at point of fetching into result:
-- execute immediate style (does not work with clob):
EXECUTE IMMEDIATE large_query BULK COLLECT INTO V_TAB ;
-- dbms_sql style:
v_upperbound := CEIL(DBMS_LOB.GETLENGTH(large_query)/256);
FOR i IN 1..v_upperbound
LOOP
v_sql(i) := DBMS_LOB.SUBSTR(large_query,256,((i-1)*256)+1);
END LOOP;
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
v_ret := DBMS_SQL.EXECUTE(v_cur);
-- NOW WHAT??
I'm in Oracle 9i/10g, so I can't use dbms_slq.to_refcursor.
Any suggestions?
2 Answers
Here's an example from the Oracle docs. Basically you need dbms_sql.fetch_rows and dbms_sql.column_value:
CREATE TABLE multi_tab (num NUMBER,
dat1 DATE,
var VARCHAR2(24),
dat2 DATE)
declare
c NUMBER;
d NUMBER;
n_tab DBMS_SQL.NUMBER_TABLE;
d_tab1 DBMS_SQL.DATE_TABLE;
v_tab DBMS_SQL.VARCHAR2_TABLE;
d_tab2 DBMS_SQL.DATE_TABLE;
indx NUMBER := 10;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, 'select * from multi_tab order by 1', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 2, d_tab1, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 3, v_tab, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 4, d_tab2, 5, indx);
d := DBMS_SQL.EXECUTE(c);
loop
d := DBMS_SQL.FETCH_ROWS(c);
DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
DBMS_SQL.COLUMN_VALUE(c, 2, d_tab1);
DBMS_SQL.COLUMN_VALUE(c, 3, v_tab);
DBMS_SQL.COLUMN_VALUE(c, 4, d_tab2);
EXIT WHEN d != 5;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
Thats what I did based on the answer:
-- open cursor and execute the query
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
v_ret := DBMS_SQL.EXECUTE(v_cur);
-- define a column to receive the result (in my case, it's a single varchar2 column)
dbms_sql.define_column(v_cur, 1, V_ROW, 4000);
-- initialize de result table ( TABELA_REGISTROS_TYPE is a table of varchar2(4000) )
v_tab := TABELA_REGISTROS_TYPE();
-- initializa a counter
v_d := 1;
-- loop through result rows
LOOP
-- fetch a row into a varchar2(4000) variable
EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cur) = 0;
DBMS_SQL.COLUMN_VALUE(v_cur, 1, V_ROW);
-- append that row into result set
V_TAB.EXTEND();
V_TAB(v_d) := v_row;
v_d := v_d + 1;
END LOOP;
-- finally close the cursor
DBMS_SQL.close_cursor(v_cur);
-- then return the result set
RETURN V_TAB;
