When you connect to an Oracle database with Access and ADO, is there a way
to get information on the Oracle table relationships?
Jeff
to get information on the Oracle table relationships?
Jeff
-
-
Re: table relationships
"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1093194494 .325919@yasure. ..[color=blue]
>
> Query the data dictionary views. Run this query for a list of them.
>
> SELECT view_name
> FROM all_views
> WHERE view_name LIKE 'ALL%';
>
> But why connect to Oracle with MS Access? An overwhelming desire to use
> the worst possible tool to do the job?[/color]
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
-
Re: table relationships
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_column s and user_constraint s (where
constraint_type ='R').
And if you are really lucky, you could try user_tab_commen ts and
user_col_commen ts.
GQ
"Jeff" <idont@thinkso. com> wrote in message news:<Pw7Wc.92$ 4o.40@fed1read0 1>...[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1093194494 .325919@yasure. ..[color=green]
> >
> > Query the data dictionary views. Run this query for a list of them.
> >
> > SELECT view_name
> > FROM all_views
> > WHERE view_name LIKE 'ALL%';
> >
> > But why connect to Oracle with MS Access? An overwhelming desire to use
> > the worst possible tool to do the job?[/color]
>
> 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[/color]
Comment
-
Re: table relationships
"Jeff" <idont@thinkso. com> wrote in message news:<Pw7Wc.92$ 4o.40@fed1read0 1>...[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1093194494 .325919@yasure. ..[color=green]
> >
> > Query the data dictionary views. Run this query for a list of them.
> >
> > SELECT view_name
> > FROM all_views
> > WHERE view_name LIKE 'ALL%';
> >
> > But why connect to Oracle with MS Access? An overwhelming desire to use
> > the worst possible tool to do the job?[/color]
>
> 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.[/color]
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.
Comment
-
Re: table relationships
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@home.com> wrote in message
news:91884734.0 408231524.43207 122@posting.goo gle.com...[color=blue]
>
> 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[/color]
Comment
-
Re: table relationships
Thanks, I'll play with that.
"GQ" <dbaguy_ott@yah oo.com> wrote in message
news:aad8b5cb.0 408230705.2164b 54a@posting.goo gle.com...[color=blue]
> 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_column s and user_constraint s (where
> constraint_type ='R').
> And if you are really lucky, you could try user_tab_commen ts and
> user_col_commen ts.
>
> GQ
>
> "Jeff" <idont@thinkso. com> wrote in message[/color]
news:<Pw7Wc.92$ 4o.40@fed1read0 1>...[color=blue][color=green]
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1093194494 .325919@yasure. ..[color=darkred]
> > >
> > > Query the data dictionary views. Run this query for a list of them.
> > >
> > > SELECT view_name
> > > FROM all_views
> > > WHERE view_name LIKE 'ALL%';
> > >
> > > But why connect to Oracle with MS Access? An overwhelming desire to[/color][/color][/color]
use[color=blue][color=green][color=darkred]
> > > the worst possible tool to do the job?[/color]
> >
> > 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[/color][/color]
that[color=blue][color=green]
> > matter. All government desktop PCs come with Microsoft Access installed[/color][/color]
and[color=blue][color=green]
> > 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[/color][/color]
own[color=blue][color=green]
> > 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[/color][/color]
possible[color=blue][color=green]
> > tools, it's still a hell of alot faster than doing it by hand the way I[/color][/color]
do[color=blue][color=green]
> > it now. The Oracle database I tried to connect to turned out to have[/color][/color]
about[color=blue][color=green]
> > 2000 tables with a jillion fields. I need to figure out what the
> > relationships are before I can build my own queries.
> >
> > Jeff[/color][/color]
Comment
-
Re: table relationships
"Jeff" <idont@thinkso. com> wrote in message news:<pfxWc.158 5$4o.24@fed1rea d01>...[color=blue]
> 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?[/color]
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:
jg
--
@home.com is bogus.
....persists for 4 or more hours, seek medical help immediately.
Comment
-
Re: table relationships
"Jeff" <idont@thinkso. com> wrote in message news:<lH3Wc.14$ 4o.0@fed1read01 >...[color=blue]
> When you connect to an Oracle database with Access and ADO, is there a way
> to get information on the Oracle table relationships?
>
> Jeff[/color]
The following query lists all relationships:
select a.owner||'.'||a .table_name "Referenced Table",
b.owner||'.'||b .table_name "Referenced by", b.constraint_na me "Foreign
Key"
from all_constraints a, all_constraints b
where b.constraint_ty pe = 'R'
and a.constraint_na me = 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_COLUMN S
ORDER BY CONSTRAINT_NAME , TABLE_NAME, POSITION
/
Good luck.
Comment
Comment