Oracle Data Dictionary via MS Access Passthrough Query 3

[IngDep]

Is it possible to query an Oracle database to obtain a data dictionary via a MS Access passthrough query?

If so, does anyone have the sql?

Thanks in advance.

 

[jimirvine]

Please give more detail as to your needs

 

[IngDep]

Several months ago, I obtained a data dictionary for the
Sql Server database by "running" the following code in the Sql Query Analyzer Query Pane.

Just thought that maybe there was something similar that I can run as a passthrough query in MS Access to extract a data dictionary for the Oracle database.

Any additional insight appreciated.


SELECT sysobjects.name AS

, syscolumns.name AS [Column], sysproperties.[value] AS Description
FROM (sysobjects INNER JOIN syscolumns
ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
ON sysproperties.smallid = syscolumns.colid AND
sysproperties.id = syscolumns.id
where sysobjects.name <> 'CLAIMAMT'

 

[IngDep]

I assume that this is not possible???

 

[Turkbear]

Hi,
Give up that easy..Why?

Look at all_tab_cols in Oracle

SQL> desc all_tab_cols
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HIDDEN_COLUMN                                      VARCHAR2(3)
 VIRTUAL_COLUMN                                     VARCHAR2(3)
 SEGMENT_COLUMN_ID                                  NUMBER
 INTERNAL_COLUMN_ID                        NOT NULL NUMBER

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IngDep]

Need to generate a listing of tables and fields within the tables similar to the following;


Table----------------Field
Sample---------------Name
Sample---------------Address
Sample---------------City
Sample---------------State
Sample1
.
.
.

 

[Thargy]

Try the following:-

SELECT TABLE_NAME, COLUMN_NAME
  FROM USER_TAB_COLUMNS
 ORDER BY TABLE_NAME,COLUMN_ID

Regards

T

 

[IngDep]

At this point, not able to generate a Oracle data dictionary using a MS Access passthrough.

Maybe, need to access the sql plus worksheet?

Thoughts?

 

[jimirvine]

At this point, not able to generate a Oracle data dictionary using a MS Access passthrough.

Not really any useful information there. What did you try? what happened when you tried it?

Maybe, need to access the sql plus worksheet? Thoughts?

Sounds like a plan. Go for it

 

[Turkbear]

Hi,
What about the ideas you have been given..The all_tab_cols table( or user_tab_cols, or dba_tab_cols ) has all the info you seem to need...

Select table_name,column_name from all_tab_cols order by table_name,column_name;

( This should be able to be used in an MSACCESS pass-through assuming sufficient rights)







To Paraphrase:"The Help you get is proportional to the Help you give.."