The below code snippet UTL_FILE.PUT_LINE(v_file,V_TEMPA);
in the below BLOCK does not spool the data of the table but the columns instead as defined in variable V_TEMPA

I want to spool dynamically for the table. Can you let me know what change is needed? Execute immediate?

Thanks a ton


--
set serveroutput on
DECLARE

V_TABLE_NAME varchar2(60):='&Table';
V_TEMPA varchar2(2000);
V_TEMPB varchar2(2500);
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR',
filename => V_TABLE_NAME||'.txt',
open_mode => 'w',
max_linesize => 32767);
FOR TAB_REC IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER (V_TABLE_NAME))
LOOP
V_TEMPA :='';

FOR COL_REC IN (SELECT *
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = TAB_REC.TABLE_NAME
ORDER BY COLUMN_ID)
LOOP
V_TEMPA := V_TEMPA || 'cur_rec2.'||COL_REC.COLUMN_NAME||'||''~''||';


END LOOP;
V_TEMPA := SUBSTR(V_TEMPA,1, LENGTH(V_TEMPA)-7);
--V_TEMPB := 'SELECT '||V_TEMPA||' FROM '||TAB_REC.TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(V_TEMPA);
FOR cur_rec2 IN (SELECT * FROM emp)
loop
UTL_FILE.PUT_LINE(v_file,V_TEMPA);
end LOOP;
UTL_FILE.FCLOSE(v_file);
END loop;
END;
/