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.
1 | 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:
1 2 3 4 5 | 0001234567890123 0012345678901234 0123456789012345 1234567890123456 0012345678901234 |
Just one column representing the account numbers. No comma delimited fields and no headers or trailers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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.
Julio Struthers
Very good blog article.Much thanks again. Really Great.
Bart Rakyta
Simply wanna remark on few general things, The website style and design is perfect, the subject material is real excellent : D.