My Oracle Support Banner

How to Identify the Symbol Substitutions %p and %a Used With External Tables Filenames (Doc ID 556078.1)

Last updated on OCTOBER 07, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.1 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

NOTE: The document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


You created an external table that is using the ORACLE_LOADER driver and contains symbol substitution parameters %p and %a in filename:

SQL> connect / as sysdba
SQL> grant select on v_$session to test;
SQL> grant select on v_$session to test;

SQL> connect test/test
SQL> drop table ext_test;
SQL> purge recyclebin;
SQL> create table ext_test
        col1 varchar2(255),
        col2 varchar2(255),
        col3 varchar2(255),
        col4 varchar2(255),
        col5 varchar2(255)
     organization external
        type oracle_loader
        default directory ext
        access parameters
           records delimited by newline
           logfile 'ext_test_%p_%a.log'
           badfile 'ext_test_%p_%a.bad'
           fields terminated by '|'
           missing field values are null
        location ('ext_test1.dat', 'ext_test2.dat', 'ext_test3.dat') 
     reject limit unlimited
     parallel 3;

The data files ext_test1.dat...ext_test3.dat contain records like:


After select from external table EXT_TEST, you would like to check, if there some error messages in logfiles and/or some written badfiles related to your session.

The example below shows how the files can be identified at OS level.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.