How to create an External Table in Oracle from a Function

Recently I had to create an (oracle) external table inside a function and using the parameter as part of the new external table name.
Creating the external table directory under Oracle was the first step.

Running this command within Oracle will set the directory.

CREATE OR REPLACE directory ext_dir as '/mydir/myjob/load'

My function to create the external table starting with a prefix “ex_” followed by the name of the file.
The “.DAT” raw data file has the following structure:

0001234567890123
0012345678901234
0123456789012345
1234567890123456
0012345678901234

Just one column representing the account numbers. No comma delimited fields and no headers or trailers.

FUNCTION fn_create_ext_table(filename IN VARCHAR2)
 RETURN VARCHAR2
 AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  prefix varchar2(20) := 'ex_';
  retTableName varchar2(100);
 BEGIN

  retTableName := prefix || filename;

  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ' || retTableName;
  EXCEPTION WHEN others THEN NULL;
  END;

  EXECUTE IMMEDIATE 'create table ' || retTableName ||
      '(
          AccountNumber VARCHAR2(16)
      )
      organization external (
	   type    oracle_loader
	   default directory ext_dir
	   access  parameters (
	   records delimited  by newline
	   fields terminated by WHITESPACE
	   missing field values are null
	)
	location (''' || filename || '.DAT' || ''')
        )
        reject limit 100';
    return retTableName;
END fn_create_ext_table;

After running the function, the external table is created consisting of one column: “AccountNumber” of VARCHAR2 (16) type and size. And ready to be used loaded with the raw data.

To call the function and pass the parameter in java I used CallableStatement. (Note: Connection already setup in base class)

private String CreateExternalTable(String filename) throws Exception {
		filename = filename.substring(0, filename.lastIndexOf('.')); //strip extension
		String ext_tablename = "";
		CallableStatement cs = null;
		try {
			cs = con.prepareCall("{?= call ABC.fn_create_ext_table(?) }");
			cs.registerOutParameter(1, OracleTypes.VARCHAR);
			cs.setString(2, filename);
			cs.execute();
			ext_tablename = cs.getString(1);
		}
		catch (Exception e) {
			...
			throw e;
		}
		finally {
			// close CallableStatement(cs);
		}
		return ext_tablename;
	}

Hope this helps.