Home » SQL & PL/SQL » SQL & PL/SQL » Comments for external tables (9.0.2.0.8)
Comments for external tables [message #303280] Thu, 28 February 2008 11:55 Go to next message
ThomasG
Messages: 3189
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

Re: Comments for external tables [message #303318 is a reply to message #303280] Thu, 28 February 2008 21:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think you've found all there is to be found.

You can create a VIEW over the EOT and comment that.

Ross Leishman
Re: Comments for external tables [message #303377 is a reply to message #303318] Fri, 29 February 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good idea. ./fa/2115/0/

Regards
Michel
Re: Comments for external tables [message #303412 is a reply to message #303377] Fri, 29 February 2008 05:02 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, that is inded a good Idea.

Thanks ./fa/451/0/
Previous Topic: maximum number of recursive SQL levels (50) exceeded
Next Topic: handling leading zeros in a csv file
Goto Forum:
  


Current Time: Sat Dec 03 17:54:58 CST 2016

Total time taken to generate the page: 0.07720 seconds