DBMS_SQL package

I've described:

col_value varchar2(4000);

now i'm trying in a procedure the following

DBMS_SQL.COLUMN_VALUE(c3,col_num,col_value);

normally this should return data buth it gives nothing...

Somebody has an idea?

Here under I'll give you the full code:

CREATE OR REPLACE PROCEDURE xxresponsabilities_sp3 (PIN_PAGE IN NUMBER) IS
/* Cursor declaration */
CURSOR C1 is
SELECT id
,table_name
FROM xxtables
WHERE page_ID = PIN_PAGE;

CURSOR C2(cin_table_id in number) is
SELECT column_name, alias_name, typevar, leng
FROM xxcolumns
WHERE table_id = cin_table_id
order by id;

/* Type declaration */
TYPE resrec IS RECORD(V_COL VARCHAR2(60),
V_DATA VARCHAR2(240));
TYPE t_restab is table of resrec index by binary_integer;
TYPE colrec IS RECORD(V_COL VARCHAR2(240));
TYPE t_coltab is table of colrec index by binary_integer;

/* Variables */
v_restab t_restab;
v_coltab t_coltab;
i number;
j number;
z number;
lengte number;
tabstr varchar2(4000);
str varchar2(4000);
v_value varchar2(4000) := null;
tablename varchar2(30);
r1 c1%rowtype;
c3 INTEGER;
ignore INTEGER;
v_exec INTEGER;
col_cnt INTEGER;
col_num number;
colposition INTEGER;
rec_tab dbms_sql.desc_tab;
col_value Varchar2(4000);
num_value number;
the_id number;

BEGIN
OPEN c1;
FETCH c1 into r1.id, r1.table_name;
IF c1%found THEN
tablename := r1.table_name;
i := 0;
tabstr := 'SELECT ';
FOR r2 in c2(r1.id) loop
v_coltab(i).v_col := r2.column_name;
tabstr := concat(tabstr,r2.column_name);
tabstr := concat(tabstr,',');
i:=i+1;
if r2.column_name != 'ID' then
****print
end if;
END LOOP;
lengte := length(tabstr);
tabstr := substr(tabstr,1,lengte-1);
tabstr := concat(tabstr,' from ');
tabstr := concat(tabstr,tablename);
END IF;

j := 0;
i := i-1;
z := 1;
CLOSE C1;

c3 := dbms_sql.open_cursor;
DBMS_SQL.PARSE(c3,tabstr,DBMS_SQL.native);

v_exec := DBMS_SQL.EXECUTE_AND_FETCH(c3);

dbms_sql.describe_columns(c3,col_cnt,rec_tab);

htp.tableRowOpen;
LOOP
col_num := rec_tab.first;
IF (col_num is not null) then
LOOP
DBMS_SQL.define_column(c3,col_num,rec_tab(col_num) .col_name,rec_tab(col_num).col_max_len);
if rec_tab(col_num).col_name = 'ID' then
DBMS_SQL.column_value(c3,col_num,col_value);
the_id := col_value;
else
str := 'SELECT '||rec_tab(col_num).col_name||' from '||tablename||' where id = '||the_id;
dbms_output.put_line(str);
EXECUTE IMMEDIATE str INTO v_value;
v_restab(j).v_data := v_value;
j := j+1;
EXIT WHEN j > i;
end if;
col_num := rec_tab.next(col_num);
EXIT WHEN (col_num is null);
END LOOP;
END IF;
IF DBMS_SQL.FETCH_ROWS(c3)=0 THEN
EXIT;
END IF;
END LOOP;
htp.tableRowClose;
DBMS_SQL.CLOSE_CURSOR(c3);

FOR k IN v_restab.FIRST .. v_restab.LAST LOOP
****Print
END LOOP;
END;have a look at: http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg09dyn.htm#26586

Some two years ago I completely switch from using the dbms-sql package to "native dynamic sql", which seems to be easer for my approaches.

coming back to your coding.
(1) I have never used a dbms_sql.execute_and_fetch Procedure ... only dbms_sql.fetch_rows
(2) code the dbms_sql.define_columns BEFORE the dbms_sql.fetch_rows ...