CODEWITHSUNDEEP
oracle-databaseplsqlexternal-tables
y2j
y2j

Reputation: 207

how to use "like" command in below query

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.

Upvotes: 3

Views: 170

Answers (1)

Doug Porter
Doug Porter

Reputation: 7897

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;

Upvotes: 2

Related Questions