OraFAQ Forum: SQL & PL/SQL » Comments for external tables

Home » SQL & PL/SQL » SQL & PL/SQL » Comments for external tables (9.0.2.0.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Comments for external tables [message #303280]

Thu, 28 February 2008 11:55

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Hi,

I was wondering where the best / standard place is to put comments on external tables and their columns.

For my normal tables I just create the normal comments with

COMMENT ON TABLE  tab     IS 'foo';
COMMENT ON COLUMN tab.col IS 'bar';

And they show up in the user_tab_comments and user_col_comments, and thus in the nicely formatted data dictionary we have and in most development tools.

That doesn't seem to be possible with external tables, it throws

COMMENT ON TABLE EXT_IMPORT IS 'foo';
ORA-30657: operation not supported on external organized table

The structure of the table itself is available in user_tables and user_tab_columns, but I haven't found a good place to store comments on the table and the columns in the database itself.

The only mentioning of comments regarding external tables I have found is here, where it is stated that the comment can be placed before any access parameters in the access_parameters clause like this :

CREATE  TABLE ext_test (
  col      VARCHAR2(10)
)
ORGANIZATION EXTERNAL
   ( type oracle_loader
     default directory edi_dir
     access parameters
       ( -- Comment
         -- Comment
         -- Comment
         records delimited BY newline
         fields terminated by ';'
         missing field VALUES are null
           ( col
           )
       )
     location ('test.csv')
   )
;

But that comments don't show up in the access_parameters column in the user_external_tables view.

If there is no standard place I will possibly create my own comment tables and extend our dictionary formatter to use them, but I thought I ask here first before I go off and re-invent the wheel. Wink