sql - Error using Oracle Ref Cursor -

i'm trying simple, trying automate removal , of tables personal table space. have 100 tables , want rid of of them (except table i'm using store table names), want keep data tables in case need them sometime in future. below code trying use accomplish this. getting error on ref cursor, i'll include below code. half expect tell me i'm idiot , explain easier way this. if not, please tell me i'm doing wrong way doing it, thanks.

declare     v_folder_name        varchar2(100) := 'my_folder';     type qry_cursor      ref cursor;     v_qry_cursor         qry_cursor;     v_file_name          varchar2(320);     v_file               sys.utl_file.file_type;     v_max_buffer_length  constant binary_integer := 32767;     v_qry_str            varchar2(4000); --i've tried 32767, made no difference     v_drop_string        varchar2(4000);     v_dynamic_record     varchar2(4000); --tried 32767  cursor get_table_names     select * temp_backup_table table_name <> 'temp_backup_table';  function startfile(file_name varchar2)     --working function, used many procedures, left out brevity end startfile;  function closefile(file_name varchar2)     --working function, used many procedures, left out brevity end closefile;  begin     insert temp_backup_table select distinct table_name all_tab_cols owner = 'me';     commit; rec in get_table_names loop     v_file_name := rec.table_name;     v_file := startfile(v_file_name);     v_qry_str := 'select * ' || v_file_name;     v_drop_string := 'drop table ' || v_file_name;     open v_qry_cursor v_qry_str;  -- line returns error     loop         fetch v_qry_cursor v_dynamic_record;         exit when v_qry_cursor%notfound;         sys.utl_file.put_line(v_file, v_dynamic_record);     end loop;      close v_qry_cursor;     execute immediate v_drop_string;     commit;      v_file := closefile(v_file_name); end loop; delete temp_backup_table; end; 

the error i'm getting follows:

error report: ora-00932: inconsistent datatypes: expected - got - ora-06512: @ line 73 00932. 00000 - "inconsistent datatypes: expected %s got %s" *cause: *action: 

thanks help.

at minimum, utl_file.put_line not take arbitrary record , can't fetch arbitrary list of columns varchar2.

you iterate on each column , construct sql statement concatenates values each column single string. include doing things putting to_char explicit format mask on date or timestamp columns, adding delimiter, escaping delimiters exist in data, etc. rather tedious , error-prone process. , you'll need write sql*loader control file load data in future.

it sounds you'd better off exporting table using oracle export utility. that's command-line utility (exp or expdp depending on whether want use classic version or datapump version) lets export table definition , data file can load later using oracle import utility.