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 AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 10.1 to 11.1
Information in this document applies to any platform.


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.