Skip to Main Content
  • Questions
  • External table in a PL/SQL procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Dexter.

Asked: May 12, 2022 - 1:37 pm UTC

Last updated: May 23, 2022 - 10:42 am UTC

Version: 19.3

Viewed 1000+ times

You Asked

Hi Tom –

My task: move several dozen text file imports from SQLLDR (on AIX) into callable PL/SQL procedures. The text files are static in structure with daily refreshes of the contents. The contents are loaded into individual tables in our 19c EE database.

The solution appeared to be external tables, so I created a proof-of-concept example that worked as expected as stand-alone code.

So far, so good:
SELECT * FROM all_directories WHERE directory_name = 'CONNECT2';        -- returns /connect2.

CREATE TABLE MY_EXT_TBL (
    CUSIP           VARCHAR2(25 BYTE), 
    DESCRIPTION     VARCHAR2(200 BYTE), 
    QTY             NUMBER(18,5), 
    ACCOUNT         VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY CONNECT2
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        BADFILE CONNECT2:'MY_EXT_TBL%a_%p.bad'
        LOGFILE CONNECT2: 'MY_EXT_TBL%a_%p.log'
        DISCARDFILE CONNECT2: 'MY_EXT_TBL%a_%p.discard'
        FIELDS TERMINATED BY '|'
        MISSING FIELD VALUES ARE NULL
        (
            CUSIP,
            DESCRIPTION,
            QTY,
            ACCOUNT
        )
    ) 
    LOCATION ('exttabletestfile.txt')
)
REJECT LIMIT UNLIMITED;
-- Table MY_EXT_TBL created.
SELECT COUNT(*) FROM MY_EXT_TBL;    -- Returns 65159.  Matches file row count.


It was when I attempted to move the working code into a procedure that things went sour.

This example shows a very basic (no Log, Bad, or Discard files) example and hints at the hazards of going that route. I accepted that challenge, but after trying every combination of single and double quotes around file names without success, I am stumped. This feels harder than it should be.

If External Tables in a sproc are a valid, if tricky, solution, could you please demonstrate a working example?
Or should I be using UTL_File instead? Or something else?

Best regards,
Dexter



and Chris said...

I'm unclear exactly what you're doing here - you certainly can access external tables in PL/SQL. See below for a complete example.

Are you trying to paste the CREATE TABLE statement itself directly into PL/SQL? e.g.

begin
  create table ...
end;
/


If so that's the root of your problem. To run DDL inside PL/SQL you must use dynamic SQL (e.g. EXECUTE IMMEDIATE).

But generally you should avoid this and create the external tables in plain SQL outside of PL/SQL.

create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ( 'TMP', 'test.txt', 'w' );
  utl_file.put_line ( f, 'row1' );
  utl_file.put_line ( f, 'row2' );
  utl_file.fclose ( f );
end;
/

create table ext (
  c1 varchar2(10)
) organization external (
  default directory tmp
  location ( 'test.txt' )
);

begin
  for rws in ( select * from ext ) loop
    dbms_output.put_line ( rws.c1 );
  end loop;
end;
/

row1
row2

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clarification

Dexter, May 13, 2022 - 12:37 am UTC

Thanks for the reply - I'm sorry I wasn't more clear - let me rephrase the task.
I need a callable procedure that loads a given text file into an existing 19c (staging) table. The procedure will be called on a daily basis to populate (refresh) the table with the file data.

Perhaps it's because I'm a PL/SQL novice, but I don't see how I can use an anonymous block (yours or mine) to accomplish this task. Can you help me see what I'm missing?

Chris Saxon
May 13, 2022 - 8:44 am UTC

I'm still unsure what you're trying to do - perhaps some pseudo code would help.

If you're trying to load a file with a different name (but the same structure) each day, you could use the EXTERNAL MODIFY clause. This enables you to change which file the external table reads.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ( 'TMP', 'first_file.txt', 'w' );
  utl_file.put_line ( f, 'first' );
  utl_file.fclose ( f );
  f := utl_file.fopen ( 'TMP', 'second_file.txt', 'w' );
  utl_file.put_line ( f, 'second' );
  utl_file.fclose ( f );
end;
/

create table ext (
  c1 varchar2(10)
) organization external (
  default directory tmp
  location ( 'dummy.txt' )
);

create table stage (
  c1 varchar2(10)
);

create or replace procedure p ( filename varchar2 ) as
begin
  execute immediate '
  insert into stage
    select * from ext
      external modify ( location ( :filename ) )'
  using filename;
end p;
/

exec p ( 'first_file.txt' );

select * from stage;

C1        
----------
first

exec p ( 'second_file.txt' );

select * from stage;

C1        
----------
first
second


This uses EXECUTE IMMEDIATE because there's currently a bug which stops this working as static SQL in PL/SQL.

Getting closer(?)

Dexter, May 13, 2022 - 9:02 pm UTC

Thanks, Chris.

Looks like I didn't include quite enough detail.

The source file (there's only one) name and location are constant, only the data changes.
Likewise the destination table is constant and its structure matches the source file to the extent no logic, etc. is required for the table to accept the file's data.

I take your point dynamic SQL is best avoided, but is it possible to accomplish this without it? How else could you run your sample code in a named procedure?

Or is there another method I'm unaware of (quite very possible) to execute an anonymous block from another procedure or function?

Source file: /u01/vendordata/somedatafile.txt (contains pipe-delimited data.)
Dest table: VendorXYZ.TargetTable (matching number of columns of compatible data types as text file.)




Chris Saxon
May 16, 2022 - 11:29 am UTC

I'm utterly lost as to what the actual problem is.

If the file name and structure are constant, all you need is a basic INSERT ... SELECT (with appropriate logic to handle retries, errors, etc.). See below for an example.

How else could you run your sample code in a named procedure?

Just call it! The code is in the procedure P access it in another procedure, anonymous block, OS script, scheduler job, Java app, ...; it really depends on how this process works.

Like I asked, please provide pseudo code showing what you're trying to achieve and exactly what you're struggling with.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ( 'TMP', 'file.txt', 'w' );
  utl_file.put_line ( f, 'first' );
  utl_file.fclose ( f );
end;
/

create table ext (
  c1 varchar2(10)
) organization external (
  default directory tmp
  location ( 'file.txt' )
);

create table stage (
  c1 varchar2(10)
);

create or replace procedure p as
begin
  insert into stage
    select * from ext;
end p;
/

exec p ();

select * from stage;

C1        
----------
first

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ( 'TMP', 'file.txt', 'w' );
  utl_file.put_line ( f, 'second' );
  utl_file.fclose ( f );
end;
/

begin 
  p ();
end;
/

select * from stage;

C1        
----------
first
second

Using Inline external table with dynamic file names...

Rajeshwaran Jeyabal, May 17, 2022 - 2:49 pm UTC

Team,

was trying to use an Inline External table (new feature of 18c) here to get the filenames handled as parameter to stored procedure - but ended up with errors.

It is not possible to dynamically pass the Filename as parameters in the Inline External Tables?

demo@XEPDB1> select c1,c2
  2     from external(
  3             ( c1 number,
  4               c2 char(10) )
  5             type oracle_loader
  6             default directory TEMP_DIR
  7             access parameters
  8                     ( records delimited by newline
  9                       nologfile
 10                       nobadfile
 11                       fields terminated by ','
 12                       (c1 recnum, c2 char(10))
 13                     )
 14             location(TEMP_DIR:'file1.txt')
 15             reject limit unlimited )
 16  /

        C1 C2
---------- ----------
         1 KING
         2 BLAKE
         3 CLARK
         4 JONES
         5 SCOTT
         6 FORD
         7 SMITH
         8 ALLEN
         9 WARD
        10 MARTIN
        11 TURNER
        12 ADAMS
        13 JAMES
        14 MILLER

14 rows selected.

demo@XEPDB1> create table target_table( c1 number, c2 varchar2(60) );

Table created.

demo@XEPDB1> create or replace procedure load_data( p_filenm in varchar2 )
  2  as
  3  begin
  4     insert into target_table( c1,c2 )
  5     select c1,c2
  6     from external(
  7             ( c1 number,
  8               c2 char(10) )
  9             type oracle_loader
 10             default directory TEMP_DIR
 11             access parameters
 12                     ( records delimited by newline
 13                       nologfile
 14                       nobadfile
 15                       fields terminated by ','
 16                       (c1 recnum, c2 char(10))
 17                     )
 18             location(TEMP_DIR:p_filenm)
 19             reject limit unlimited ) ;
 20  end;
 21  /

Warning: Procedure created with compilation errors.

demo@XEPDB1> show err
Errors for PROCEDURE LOAD_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2      PL/SQL: SQL Statement ignored
18/20    PLS-00049: bad bind variable 'P_FILENM'
18/21    PL/SQL: ORA-00922: missing or invalid option
demo@XEPDB1>

Connor McDonald
May 18, 2022 - 3:07 am UTC

From the docs for SELECT we have

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

"You must enclose a filename in the LOCATION clause within quotes"

which I'm assuming is meant to infer that a bind is not going to work.

My own perspective is that I consider this to be a form of "dynamic DDL" and thus the same rules for DDL would apply. So a little dynamic SQL plus dbms_assert to clean the input is probably the way to go here.

why this duplicate column error ?

Rajeshwaran Jeyabal, May 17, 2022 - 2:55 pm UTC

Team,

This was from Oracle 21c XE database and why this duplicate column error ? can you please help us to understand ?

demo@XEPDB1> insert into target_table
  2  select *
  3  from external(
  4     ( c1 number,
  5       c2 char(10) )
  6     type oracle_loader
  7     default directory TEMP_DIR
  8     access parameters
  9             ( records delimited by newline
 10               nologfile
 11               nobadfile
 12               fields terminated by ','
 13               (c1 recnum, c2 char(10))
 14             )
 15     location(TEMP_DIR:'file1.txt')
 16     reject limit unlimited ) ;

14 rows created.

demo@XEPDB1> rollback;

Rollback complete.

demo@XEPDB1> insert into target_table
  2  select c1,c2
  3  from external(
  4     ( c1 number,
  5       c2 char(10) )
  6     type oracle_loader
  7     default directory TEMP_DIR
  8     access parameters
  9             ( records delimited by newline
 10               nologfile
 11               nobadfile
 12               fields terminated by ','
 13               (c1 recnum, c2 char(10))
 14             )
 15     location(TEMP_DIR:'file1.txt')
 16     reject limit unlimited ) ;

14 rows created.

demo@XEPDB1> rollback;

Rollback complete.

demo@XEPDB1> insert into target_table(c1,c2)
  2  select c1,c2
  3  from external(
  4     ( c1 number,
  5       c2 char(10) )
  6     type oracle_loader
  7     default directory TEMP_DIR
  8     access parameters
  9             ( records delimited by newline
 10               nologfile
 11               nobadfile
 12               fields terminated by ','
 13               (c1 recnum, c2 char(10))
 14             )
 15     location(TEMP_DIR:'file1.txt')
 16     reject limit unlimited ) ;
insert into target_table(c1,c2)
                            *
ERROR at line 1:
ORA-00957: duplicate column name


demo@XEPDB1>

Chris Saxon
May 23, 2022 - 10:42 am UTC

It looks like you've hit a bug - please raise this with support

why this duplicate column error ?

Rajeshwaran, Jeyabal, May 24, 2022 - 4:11 am UTC

Thanks Chris, Raised an SR# with Oracle support for this - SR 3-29617368291

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.