SQL & PL/SQL

Hi to all,

How to export some data from the tables of an owner with integrity?

I want to bring some data from all tables in a single owner of the production database for development environment.

My initial requirements are: seeking information on company code (emp), contract status (status) and / or effective date of contract settlement (dt_liq_efetiva) - a small amount of data to developers.

These three fields are present in the main system table (the table of contracts). Then I thought about ...

- create a temporary table from the query results table to contract;
- and then use this temporary table as a reference to fetch the data in other tables of the owner while maintaining integrity. But how? I have not found the answer, because: what to do when not there is the possibility of a join between the contract and any other table?

I am considering the possibility of consulting the names of tables, foreign keys and columns above, and create dynamic SQL. Conceptually, something like:

select r.constraint_name "FK name",
r.table_name "FK table",
r.column_name "FK column",
up.constraint_name "Referencing name",
up.table_name "Referencing table",
up.column_name "Referencing column"
from all_cons_columns up
join all_cons_columns r
using (owner, position), (select r.owner,
r.constraint_name fk,
r.table_name table_fk,
r.r_constraint_name r,
up.table_name table_r
from all_constraints up, all_constraints r
where r.r_owner = up.owner
and r.r_constraint_name = up.constraint_name
and up.constraint_type in ('P', 'U')
and r.constraint_type = 'R'
and r.owner = 'OWNERNAME') aux
where r.constraint_name = aux.fk
and r.table_name = aux.table_fk
and up.constraint_name = aux.r
and up.table_name = aux.table_r;

-- + Dynamic SQL

If anyone has any suggestions and / or reuse code to me thank you very much!

After resolving this standoff intend to mount the inserts in utl_file by a table and create another program to read and play in the development environment.

Thinking...
Let's Share!

My thanks in advance,
Philips