When you connect to an Oracle database with Access and ADO, is there a way
to get information on the Oracle table relationships?

Jeff

"Daniel Morgan" <damorgan@x.washington.edu> wrote

HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle
installed let alone get Oracle training, or ANY database training for that
matter. All government desktop PCs come with Microsoft Access installed and
I had to teach myself to use it. I have a couple of stand-alone
applications I have to use that connect to an Oracle database, but the
applications leave ALOT to be desired because I still have to build my own
lists of data and compile statistics by hand over and over. If I can
connect to the Oracle database with Access and ADO, I can build my own
queries and reports once and be done with it at the click of a button
whenever it's needed. Whether or not Access and ADO are the worst possible
tools, it's still a hell of alot faster than doing it by hand the way I do
it now. The Oracle database I tried to connect to turned out to have about
2000 tables with a jillion fields. I need to figure out what the
relationships are before I can build my own queries.

Jeff

You can start out with something like the following - select * from
dict where table_name like '%COLUMN%';
Note that most views are available in three flavors (e.g. user_tables,
all_tables, dba_tables).
I'd recommend looking at User_tab_columns and user_constraints (where
constraint_type='R').
And if you are really lucky, you could try user_tab_comments and
user_col_comments.

GQ

"Jeff" <idont (AT) thinkso (DOT) com> wrote

"Jeff" <idont (AT) thinkso (DOT) com> wrote

Hmmm, when I was a gummint mil contractor a few years ago, it seemed
there was a lot of online training available. Perhaps, really bad
training, but there you go. Find out where the online training
available to you is, and you might find a scrollable list that
actually includes both unix and oracle. One thing you might check out
is perl, which has oracle extensions as well as numerous places online
with examples. You might also discover the gummint has pretty
extensive site licensing, although you may need to discover how to
find a demigod called a "DBA" to find out how to use that stuff. But
for free, register on technet.oracle.com and you can download an
amazing amount of stuff, as well as tutorials and other information.
Not to mention your help desk might be able to give you licensed
copies of stuff. If you can access an Oracle database, you must be
authorized, right? There are a number of tools that help you figure
out the relationships, too, no need to torture yourself (especially in
the future, when you have to unlearn all the bad habits) with access.
TOAD, or even Oracle's own SQL Worksheet are better just for starters.
You surely must be able to get official Oracle client software for
accessing Oracle servers?

Assuming you aren't Libyan, or whatever.

jg
--
@home.com is bogus.
http://en.wikipedia.org/wiki/Data_haven

Oracle client software? Why didn't I think of that? Probably because
Access users don't think in those terms. That sounds like something that
might help me. Does that allow you to run queries and view table
relationships after logging into an X.WORLD connection?

"Joel Garry" <joel-garry (AT) home (DOT) com> wrote

Thanks, I'll play with that.

"GQ" <dbaguy_ott (AT) yahoo (DOT) com> wrote

"Jeff" <idont (AT) thinkso (DOT) com> wrote

There's so much, I can't begin to tell you. Enterprise manager can
show dependencies, table layouts and contents. The downside is, there
is so much I can't begin to tell you.

A lot of people like http://www.quest.com/toad/ (I have no opinion one
way or another, no relation to quest, I know many mil sites use their
stuff). Look at cdo.marketplace in general for downloadable tools.

comp.databases.oracle is defunct, don't crosspost between cdos and
there. Here is a primer for cdos:
http://members.cox.net/oracleunix/readme-cdos.htm

jg
--
@home.com is bogus.
....persists for 4 or more hours, seek medical help immediately.
http://www.sfgate.com/cgi-bin/articl...0/MN243061.DTL

"Jeff" <idont (AT) thinkso (DOT) com> wrote

The following query lists all relationships:

select a.owner||'.'||a.table_name "Referenced Table",
b.owner||'.'||b.table_name "Referenced by", b.constraint_name "Foreign
Key"
from all_constraints a, all_constraints b
where b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name;

....And the following one lists all columns that compose each foreign
key:
SELECT CONSTRAINT_NAME, TABLE_NAME, POSITION, COLUMN_NAME
FROM ALL_CONS_COLUMNS
ORDER BY CONSTRAINT_NAME, TABLE_NAME, POSITION
/

Good luck.