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

Last updated on NOVEMBER 28, 2016

Applies to:

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

Goal

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
           (
              col1, 
              col2,
              col3,
              col4,
              col5
           )
        )
        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:

AAAAAAAAAA|BBBBBBBBBB|CCCCCCCCCC|DDDDDDDDDD|EEEEEEEEEE
AAAAAAAAAA|BBBBBBBBBB|CCCCCCCCCC|DDDDDDDDDD|EEEEEEEEEE
AAAAAAAAAA|BBBBBBBBBB|CCCCCCCCCC|DDDDDDDDDD|EEEEEEEEEE
.....

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.

Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms