CREATE OR REPLACE PACKAGE BODY TABLE_EXTRACT AS
   
   PROCEDURE CALL_EXTRACT_PROC IS
   BEGIN
       TABLE_EXTRACT.EXTRACT_PROC('ACTIVITY_TYPE_RT');
   END CALL_EXTRACT_PROC;
   
   PROCEDURE EXTRACT_PROC(I_TABLE_NAME IN VARCHAR2) IS
   select_string  VARCHAR2(10000)  := '  Select';
   source_cursor  INTEGER;
   ignore         INTEGER;
   export_rec     VARCHAR2(20000);
   i       		  NUMBER    := 0;
   filetype    	  utl_file.File_type;
   fileopen    	  BOOLEAN   := FALSE;
   filename       VARCHAR2(30) ;
   CURSOR COL IS
    SELECT column_name, Data_type
      FROM ALL_TAB_COLUMNS
     WHERE table_name = UPPER(I_TABLE_NAME)
     ORDER BY column_id;
   BEGIN
     filename := I_TABLE_NAME||'.txt';
     source_cursor := DBMS_SQL.OPEN_CURSOR;
   FOR col_rec IN COL LOOP
    IF UPPER(col_rec.data_type) = 'VARCHAR2' THEN
      select_string := select_string || ' '||col_rec.column_name ;
	ELSIF UPPER(col_rec.data_type) = 'NUMBER' THEN
      select_string := select_string || ' '||col_rec.column_name ;
    ELSIF UPPER(col_rec.data_type) = 'DATE' THEN
      select_string := select_string || ' TO_CHAR('||col_rec.column_name ||',   ''YYYYMMDD HH24:MI:SS'')';
    ELSE
      select_string := select_string || ' '||col_rec.column_name ;
    END IF;
    select_string := select_string || '||''~''||';
  END LOOP;
  select_string := SUBSTR(select_string,1,LENGTH(select_string)-7)|| ' FROM '||I_TABLE_NAME;
  DBMS_SQL.PARSE(SOURCE_CURSOR, SELECT_STRING,DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN(source_cursor,1,export_rec,20000);
  IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
  filetype := utl_file.Fopen('C:\DEV',filename, 'w');
  fileopen := utl_file.Is_Open(filetype);
  IF NOT fileopen THEN
     filetype := utl_file.Fopen('C:\DEV',filename, 'w');
  END IF;
  LOOP
     IF DBMS_SQL.FETCH_ROWS(source_cursor) = 0 THEN
        EXIT;
     ELSE
        I := I+1;
        DBMS_SQL.COLUMN_VALUE(source_cursor,1,export_rec);
        utl_file.Put_line(filetype, export_rec);
     END IF;
  END LOOP;
  utl_file.fclose(filetype);
  DBMS_SQL.CLOSE_CURSOR(SOURCE_CURSOR) ;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END EXTRACT_PROC;
END TABLE_EXTRACT;