how to use "like" command in below query

Question

I am importing text file through SQL*Loader into an Oracle table but i don't want to give the specific name of the file, I want to import only the .txt file extensions file. look the 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;

At the location, LOCATION ('file with .txt extension'), I don't want to give the name of the file as in the directory only one txt file is there. I don't want to use the IN parameter. I want to search from the directory only. The user will run the procedure and it will import the txt file automatically without selecting manually.

You`ll have just one text file in that directory at all times? — Mihai, Oct 03 '14 at 09:31
If I remember it well, it is relatively hard to list the files from a directory with PL/SQL. If this is a "one time only" thing, maybe you could simply wrap that in a shell script? — Sylvain Leroux, Oct 03 '14 at 09:55
What happens if another user puts a file in the directory and calls the procedure at the same time as the first user is running his call to the procedure? Continually creating/dropping table is not nice. Maybe consider calling ALTER TABLE instead specifying a new LOCATION? Still not very good multiuser, but slightly better. Or perhaps use PREPROCESSOR parameter to do something like `cat *.txt` which lets the external table read the standard output of the cat command. That way you do not have to recreate the table or alter it all the time. — Kim Berg Hansen, Oct 03 '14 at 10:58
Also just noticed: Why do you do EXECUTE IMMEDIATE of the INSERT statement? That is just plain DML and is better off as static SQL rather than dynamic SQL. ... Sorry, of course that is because you are creating the table using EXECUTE IMMEDIATE rather than creating the table once and for all. Yet another argument for not doing CREATE/DROP all the time ;-) — Kim Berg Hansen, Oct 03 '14 at 11:41
If your goal is to determine the right filename for the external table at runtime one possibilty is a stored java procedure. It can scan the directory folder for the file available and return the name through a plsql wrapper function or procedure. Provided you have "java read privilege" for the folder. — Jens Krogsboell, Oct 03 '14 at 14:56
Or you could have the java procedure rename the available file to whatever name is expected by the external table just before loading. — Jens Krogsboell, Oct 03 '14 at 19:15

score 2 · Accepted Answer · edited May 23 '17 at 10:24

For the most part you aren't going to be able to do this in a pure PL/SQL fashion. There is a workaround listed here: Listing files in a specified directory using PL/SQL but considering the requirement for SYS that may not be exactly what you are looking for. After that a Java Stored Procedure would be your best bet.

If you are able to determine the filename, you can redefine the location for your external table on the fly with an execute immediate call. You could put it in a procedure like this and make use of it before querying your external 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;

how to use "like" command in below query

1 Answers1