22
Hi Experts,
I need to develop a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a sample query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
With "known number of column" method (in this case we have known that we are querying two columns: "FIRST_NAME " and "LAST_NAME" ), I write a code based on the template in table 8-2 of http://docs.oracle.com/cd/B10500_01/...9dyn.htm#26586. The query works fine.
--------------------------------------------------------------------------
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_c ursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS ';
dbms_sql.parse( cur_hdl, stmt_str, dbms_sql.native );
-- describe defines
dbms_sql.define _column(cur_hdl , 1, FIRST_NAME, 200);
dbms_sql.define _column(cur_hdl , 2, LAST_NAME, 200);
rows_processed := dbms_sql.execut e(cur_hdl);
--execute
LOOP
-- fetch a row
IF dbms_sql.fetch_ rows(cur_hdl) > 0 then
-- fetch columns from the row
dbms_sql.column _value(cur_hdl, 1, FIRST_NAME);
dbms_sql.column _value(cur_hdl, 2, LAST_NAME);
dbms_output.put _line('First Name' ||' = '||FIRST_NAME || '; Last Name' ||' = '||LAST_NAME);
-- <process data>
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_ cursor(cur_hdl) ; -- close cursor
END;
/
--------------------------------------------------------------------------
However, for "unknown number of columns" method, following http://stackoverflow.com/questions/1...l-from-sqlplus, my code for the same query has error.
--------------------------------------------------------------------------
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_t ab;
l_dummy NUMBER;
l_value NUMBER;
BEGIN
cur_hdl := dbms_sql.open_c ursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS ';
dbms_sql.parse( cur_hdl, stmt_str, dbms_sql.native );
-- describe defines
--dbms_sql.define _column(cur_hdl , 1, FIRST_NAME, 200);
--dbms_sql.define _column(cur_hdl , 2, LAST_NAME, 200);
--
dbms_sql.descri be_columns(cur_ hdl, l_dummy, l_columns);
FOR i IN 1..l_columns.co unt LOOP
dbms_sql.define _column(cur_hdl , i, l_columns(i).co l_name, l_columns(i).co l_type);
END LOOP;
--
--rows_processed := dbms_sql.execut e(cur_hdl);
l_dummy := dbms_sql.execut e_and_fetch(cur _hdl, TRUE);
--execute
LOOP
-- fetch a row
IF dbms_sql.fetch_ rows(cur_hdl) > 0 then
-- fetch columns from the row
-- dbms_sql.column _value(cur_hdl, 1, FIRST_NAME);
-- dbms_sql.column _value(cur_hdl, 2, LAST_NAME);
FOR i IN 1..l_columns.co unt LOOP
dbms_sql.column _value(cur_hdl, i, l_value);
-- dbms_output.put _line('First Name' ||' = '||FIRST_NAME || '; Last Name' ||' = '||LAST_NAME);
dbms_output.put _line(l_columns (i).col_name ||' = '||l_value);
END LOOP;
-- <process data>
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_ cursor(cur_hdl) ; -- close cursor
END;
/
--------------------------------------------------------------------------
Thank for help in advance.
-Cuq
