Search code examples
sqlplsqloracle11gdatabase-metadata

Retrieve Oracle Metadata with Internal Data

I am trying to write a PL/SL Procedure which uses both meta data and internal data of a table.It is like:

table1 (ABC varchar2(50),wsx varchar2(50));
table2 (ABC number(50),dv varchar2(50));
table3 (ABC varchar2(10),wsds varchar2(50));
table4 (ABC varchar2(20),wfsdg varchar2(50));
table5 (ABC number(50),wsxsfd varchar2(50));

All five tables have one column with same name 'ABC'.

Suppose table1 has 3 rows like ('JOHN.TEDA','avdv'),('MARK.LEE','fesf'),('JOHN.DEA','fwfd') and other table also have any data like this.

Now using column name as an input('ABC') i should get output as attached.

we can get column info from user_tab_columns.

Max length means max length of existing data in column :: 

select max(length(ABC)) from table1

I am getting problem in joining bothenter image description here Tables are not referential.

Solution

  • You cannot achieve this directly with SQL. You need PL/SQL + Execute Immediate to do the job.

    • create a table with your expected columns
    • then use for each loop to count the max length on each column.