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 IN
. 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