scott@ORA92> CREATE OR REPLACE DIRECTORY fresho_export_gl AS 'c:\oracle'
  2  /

Directory created.

scott@ORA92> CREATE TABLE temp_table (col1 NUMBER)
  2  /

Table created.

scott@ORA92> CREATE OR REPLACE PROCEDURE create_temp_table
  2  AS
  3    v_ref SYS_REFCURSOR;
  4    v_val NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'DROP TABLE temp_table';
  7    EXECUTE IMMEDIATE
  8  	 'CREATE TABLE temp_table
  9  	    (COL1 NUMBER)
 10  	  ORGANIZATION external
 11  	    (TYPE oracle_loader
 12  	     DEFAULT DIRECTORY fresho_export_gl
 13  	     ACCESS PARAMETERS
 14  	       (FIELDS TERMINATED BY ","
 15  		  (COL1))
 16  	    location (''kkk.dat''))';
 17    OPEN v_ref FOR SELECT * FROM temp_table;
 18    LOOP
 19  	 FETCH v_ref INTO v_val;
 20  	 EXIT WHEN v_ref%NOTFOUND;
 21  	 DBMS_OUTPUT.PUT_LINE ('value:	' || v_val);
 22    END LOOP;
 23  END create_temp_table;
 24  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXECUTE create_temp_table
value:  1
value:  2

PL/SQL procedure successfully completed.

scott@ORA92> HOST DEL c:\oracle\kkk.dat

scott@ORA92> EXECUTE create_temp_table
BEGIN create_temp_table; END;

*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file kkk.dat in FRESHO_EXPORT_GL not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
ORA-06512: at "SCOTT.CREATE_TEMP_TABLE", line 19
ORA-06512: at line 1

scott@ORA92> CREATE OR REPLACE PROCEDURE create_temp_table
  2  AS
  3    v_ref SYS_REFCURSOR;
  4    v_val NUMBER;
  5  BEGIN
  6    EXECUTE IMMEDIATE 'DROP TABLE temp_table';
  7    EXECUTE IMMEDIATE
  8  	 'CREATE TABLE temp_table
  9  	    (COL1 NUMBER)
 10  	  ORGANIZATION external
 11  	    (TYPE oracle_loader
 12  	     DEFAULT DIRECTORY fresho_export_gl
 13  	     ACCESS PARAMETERS
 14  	       (FIELDS TERMINATED BY ","
 15  		  (COL1))
 16  	    location (''kkk.dat''))';
 17    OPEN v_ref FOR SELECT * FROM temp_table;
 18    LOOP
 19  	 FETCH v_ref INTO v_val;
 20  	 EXIT WHEN v_ref%NOTFOUND;
 21  	 DBMS_OUTPUT.PUT_LINE ('value:	' || v_val);
 22    END LOOP;
 23  EXCEPTION
 24    WHEN OTHERS THEN
 25  	 IF DBMS_UTILITY.FORMAT_ERROR_STACK LIKE '%KUP-04040%' THEN
 26  	   RAISE_APPLICATION_ERROR (-20001, 'The data file does not exist!');
 27  	 ELSE
 28  	   RAISE;
 29  	 END IF;
 30  END create_temp_table;
 31  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXECUTE create_temp_table
BEGIN create_temp_table; END;

*
ERROR at line 1:
ORA-20001: The data file does not exist!
ORA-06512: at "SCOTT.CREATE_TEMP_TABLE", line 26
ORA-06512: at line 1


scott@ORA92>