Using Foreign Keys to join related tables to the main one?

Question

Oracle

Hello, I ran into a problem - I have to implement interaction with an existing system that uses the Oracle database. There are a bunch of basic tables with 80-100 columns, many of which contain keys for related tables, directories.
It is required to write a script that selects values ​​from the main table, as well as values ​​from related tables - that is, opened the reference books.
The task is trivial in theory and would be easily done by hand, if it were not for the deadlines and such a huge amount of tables and fields.
Maybe there is a functionality in oracle that can join related tables, or a 3d party tool that will help generate the necessary script.
I think a couple of illustrations will not hurt - I threw in an example:
Main table:

Its limitations:

External table:

The result is similar to the one below:

<font color="black"><font color="#0000ff">Select</font> m.id,<br/>
&nbsp;&nbsp;&nbsp; first_field,<br/>
&nbsp;&nbsp;&nbsp; second_field,<br/>
&nbsp;&nbsp;&nbsp; --прикреплённая табличка<br/>
&nbsp;&nbsp;&nbsp; s.short_name <font color="#0000ff">as</font> second_field_short_name,<br/>
&nbsp;&nbsp;&nbsp; s.long_name&nbsp;second_field_long_name,<br/>
&nbsp;&nbsp;&nbsp; s.amount&nbsp;&nbsp; <font color="#0000ff">as</font> second_field_amount third_field,<br/>
&nbsp;&nbsp;&nbsp; fourth_field,<br/>
&nbsp;&nbsp;&nbsp; fifth_field,<br/>
<br/>
&nbsp;<font color="#0000ff">from</font> a_maintable m<br/>
&nbsp;<font color="#0000ff">left</font> <font color="#0000ff">join</font> a_ref_second s<br/>
&nbsp;&nbsp;<font color="#0000ff">on</font> m.second_field = s.ID</font><br/>
<br/>
<font color="gray">* This source code was highlighted with <a href="http://virtser.net/blog/post/source-code-highlighter.aspx"><font color="gray">Source Code Highlighter</font></a>.</font>

Pliz halp!

Answer 1

Install some EMS, generate a common table and then for each template generate SELECT INTO or just insert with a subquery ... Or I misunderstood you.

Answer 2

I don’t know about separate software, but I would collect the queries I need in the cursor based on the SYS.DBA_TABLES, SYS.DBA_TAB_COLUMNS, SYS.DBA_CONSTRAINTS and SYS.DBA_CONS_COLUMNS views. In theory, they should be enough.