Dynamic SQL in PL/SQL, DBMS problem

[cuqsy0]

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_cursor; -- 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.execute(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_tab;
l_dummy NUMBER;
l_value NUMBER;
BEGIN
cur_hdl := dbms_sql.open_cursor; -- 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.describe_columns(cur_hdl, l_dummy, l_columns);
FOR i IN 1..l_columns.count LOOP
dbms_sql.define_column(cur_hdl, i, l_columns(i).col_name, l_columns(i).col_type);
END LOOP;
--

--rows_processed := dbms_sql.execute(cur_hdl);
l_dummy := dbms_sql.execute_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.count 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

[cuqsy0]

It is a very simple job to do this in Microsoft T-SQL, but it seems to be a very hard or impossible work in Oracle PL/SQL.