Re: Using Data Dictionary to tables with Referential Integrity in proper order

Well, no offense intended, but your original question gave me no clue at all that what you wanted was a way to glom a parcel of relationally-consistent data OUT of the database for such use as testing by programmers. I must be far more dense than others here (this is a surprise?).

In any case, if you are pulling data OUT , and you navigate downward from the higher levels, it's far easier to write a PL/SQL program to either populate your test tables directly or that writes the SQL to do it.

There are a number of DBA_ tables you need to examine, or your program does, rather. I think it was a bit of a sneak attack to spring "oh I want programmers to pull relationally consistent sets of record out" on us like this, and then to criticize the answers given as not meeting your needs.

Without manuals handy and without much inclination to dig them out, you would need to look at of course the DBA_CONS_COLUMNS (?) table, and a number of other critical data dictionary tables. The problem with ALL_ anything is it is constrained by who the executing user is, so you may not identify items outside the scope of the privileges of the user doing that query. There are a number of DD tables you must examine.

If you don't go top-down, you will have an ickkier mess. Follow the tree downward after identifying the PK's at each step, copy those rows into corresponding tables in lalaland, until you have traversed the tree fully. If you want to do this non-ugly, a stored procedure / package that takes a table as input for a list of the records you want full recordsets for, would be nice; the input table being of course a list of the PK's of the master records for which you wish the whole nine yards.

Slightly less non-ugly, write something that takes the PK of the root record as input and then spits out into your sample data tables all the corresponding stuff; of course that would have to be invoked record by record in some sort of SQL PLUS script.

There is no easy simple quick answer for this, everyone I know who's had to do this has had to face writing their own code; there is no DBMS_UTILITY.GET_NICE_SAMPLE_DATA resource I am aware of.

You encapsulated it in a nutshell when you stated (later, after we all thought you wanted something entirely different):

"...Say a developer wants all data related to an employee_id,
> he would enter it and then a stored procedure would populate (in proper
> order) all tables that depend on it, right down to refer tables. The
> problem gets interesting when you deal with associative entities.

Interesting is one word for it, yes. There is nothing off the shelf I am aware of that will accomplish this for you; it will be a lot of painstaking work in PL/SQL and referring to multiple Oracle DD tables to resolve the dependency tree; however, if you are pulling from existing tables with existing constraints in place (naughty monkey, didn't tell us that, did you?), by that fact, you can rely on pulling relationally consistent data out, and you need most worry about simply populating tables first that have tables below them with dependent rows, and so on. It's just a bit of programming, but nothing insurmountably difficult that I can see, having done similar things in the past.

RSH. "Brent Eamer" <breamer_at_nb.sympatico.ca> wrote in message news:63Vg8.9845$Ek1.1169762_at_news-nb00s0.nbnet.nb.ca...
> All_Dependencies does not handle dependencies between tables and foreign
> keys. It is used mainly for dependencies between stored procedures and
> between views and tables.
>
> What I really want is the ability for developers to copy a sub-set of
> production data. Say a developer wants all data related to an
employee_id,
> he would enter it and then a stored procedure would populate (in proper
> order) all tables that depend on it, right down to refer tables. The
> problem gets interesting when you deal with associative entities.
>
> "RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message
> news:csUg8.15272$gK2.1065389_at_bgtnsc04-news.ops.worldnet.att.net...
> > Dan's answer is great, but I've got a question: load how using what?
> >
> > Assuming empty tables all around? Or with stuff in some or all of them?
> >
> > If they're empty, and your data source is consistent (hah, have heard
THAT
> > one too many times!) but seriously, if so, you could disable the
> > constraints, load em up, and re-enable the constraints. If however your
> > source is not reliable, that would be a mess.
> >
> > How much data are we talking about in Gab and rows, and how many tables?
> >
> > But yeah, if it is a complicated thing, you'd probably be best off
having
> a
> > nice stored procedure to do the dependency tree analysis.
> >
> > An E/R diagram out of Designer would help a lot, too!
> >
> > RSH.
> > "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> > news:3C840C96.3C08DF97_at_ci.seattle.wa.us...
> > > Look at all_dependencies and all_constraints.
> > >
> > > Daniel Morgan
> > >
> > >
> > >
> > > Brent Eamer wrote:
> > >
> > > > I would like to be able to enter a table name and have a procedure
> list
> > the
> > > > order in which to load tables.
> > > >
> > > > Anybody done this?
> > > >
> > > > I have tried but have not found a solution.
> > >
> >
> >
>
>
Received on Tue Mar 05 2002 - 01:25:01 CST