How to use the "like" command in the following query
I am importing a text file via SQL * Loader into an Oracle table, but I don't want to specify a specific filename, I only want to import the .txt file extension. look below code:
create or replace PROCEDURE EXT_TABLE AS A1 NUMBER ; L_QUERY VARCHAR2(1000) := NULL; L_DROP VARCHAR2(10000) := NULL; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE IMPORT_TEST ( EMP_ID NUMBER (10) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY IMPORT ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY '','' MISSING FIELD VALUES ARE NULL ) LOCATION ('file with .txt extension') )reject limit unlimited'; L_QUERY:= 'INSERT INTO MPRN SELECT * FROM IMPORT_TEST'; EXECUTE IMMEDIATE L_QUERY; L_DROP := 'drop table IMPORT_TEST '; execute immediate L_DROP; --execute immediate 'DROP IMPORT_TEST'; commit; END EXT_TABLE;
In the location,
LOCATION ('file with .txt extension')
I don't want to give the filename as in a directory, just one txt file. I don't want to use a parameter
. I only want to search from a directory. The user will follow this procedure and automatically import the txt file without manually selecting.
source to share
For the most part, you cannot do this in pure PL / SQL mode. Below is a workaround: Listing files in a specified directory using PL / SQL , but considering the SYS requirement, which might not be exactly what you are looking for. After that, your best bet is to execute the Java Stored Procedure.
If you can determine the filename, you can override the location of your external table on the fly with an immediate execution call. You can put it in a procedure like this and use it before querying the outer table:
procedure alterExtTableFileName(a_tableName varchar2, a_filename varchar2) is pragma autonomous_transaction; begin dbms_output.put_line('alterExtTableFileName(TableName=' || a_tableName || ' FileName=' || a_filename || ')'); execute immediate 'alter table ' || a_tableName || ' LOCATION (''' || a_filename || ''')'; commit; exception when others then rollback; raise; end alterExtTableFileName;
source to share