MacLochlainns Weblog

It’s not recommended to mess with the catalog but, occasionally, it’s the right thing to do. This code creates a read only catalog view that links table, virtual directory, and external file names. It seems a better course of action to create the view as SYS, rather than grant permissions to SYSTEM on three catalog tables. Clearly, there are differences of opinion on pursing this type of solution.

Oracle Database 11g creates two catalog tables for external files – EXTERNAL_TAB$ and EXTERNAL_LOCATION$. They’re created by the dexttab.bsq file when you build an instance. You’re also provided with a DBA_EXTERNAL_TABLES, ALL_EXTERNAL_TABLES, and USER_EXTERNAL_TABLES. This example only creates a user view only.

1. Creates the view.

CREATE OR REPLACE VIEW user_external_table_files AS
SELECT   o.name AS TABLE_NAME
,        'SYS' AS table_owner
,        xt.type$ AS type_name
,        xl.name AS file_name
,        'SYS' AS default_directory_owner
,        xt.default_dir AS default_directory_name
,        DECODE(xt.reject_limit,2147483647,'UNLIMITED',xt.reject_limit) AS reject_limit
,        DECODE(xt.par_type,1,'BLOB',2,'CLOB','UNKNOWN') AS access_type
,        CASE WHEN xt.par_type = 2 THEN xt.param_clob ELSE NULL END AS access_parameters
,        DECODE(xt.property,2,'REFERENCED',1,'ALL','UNKNOWN') AS property
FROM     sys.external_tab$ xt
,        sys.external_location$ xl
,        sys.obj$ o
WHERE    o.owner# = userenv('SCHEMAID')
AND      o.obj# = xt.obj#
AND      o.obj# = xl.obj#;

2. Grant SELECT privileges to specific users or PUBLIC (probably a bad idea):

SQL> GRANT SELECT ON user_external_table_files TO user_name;

3. Create a synonym for the view:

SQL> CREATE SYNONYM user_external_table_files FOR sys.user_external_table_files;

You now have access to the table, virtual directory, and file names as TABLE_NAME, DEFAULT_DIRECTORY_NAME, and FILE_NAME columns respectively.