CODEWITHSUNDEEP
sqlplsqloracle11gdatabase-metadata
Beginner
Beginner

Reputation: 109

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.

Upvotes: 1

Views: 313

Answers (3)

Beginner
Beginner

Reputation: 109

CREATE OR REPLACE PROCEDURE test2 ( p_column_name IN  varchar  ) 
    IS

    CURSOR GET_DATA (COL  VARCHAR )IS 

    SELECT TABLE_NAME ,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE 
    FROM 
    user_tab_columns where COLUMN_NAME = COL; 

    a_table varchar2(50);
    B_COL varchar2(50);
    a_max  varchar2(50);

    BEGIN  

    FOR C IN GET_DATA(p_column_name) LOOP 

    a_table := c.table_name;  
    B_COL  :=  C.COLUMN_NAME;         

    EXECUTE IMMEDIATE 'SELECT MAX(LENGTH('||B_COL||')) FROM '||a_table into a_max ;            

    insert into received_Data values  (c.table_name,C.COLUMN_NAME,C.DATA_TYPE,C.DATA_LENGTH,C.NULLABLE,a_max);

    END LOOP;


    EXCEPTION 
            WHEN OTHERS THEN 
                 RAISE_APPLICATION_ERROR (-20001, 
                                          p_column_name || ':$:' || SQLERRM, TRUE) ; 
    END test2;

    /

    CREATE TABLE RECEIVED_DATA
    ( TABLE_NAME   VARCHAR2(50 BYTE), 
      COLUMN_NAME  VARCHAR2(50 BYTE),    
      DATA_TYPE    VARCHAR2(50 BYTE),
      DATA_LENGTH  VARCHAR2(50 BYTE),
      IS_NULL      VARCHAR2(50 BYTE),
      MAX_LENGTH   VARCHAR2(50 BYTE));

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

I have tried to replicate the scenario as mentioned by you with the use of PIPELINED function in Oracle. Hope this helps.

CREATE OR REPLACE TYPE fun_obj
IS
  OBJECT
  (
    tab_name VARCHAR2(100),
    colname  VARCHAR2(100),
    datatyp  VARCHAR2(100),
    datlen   NUMBER,
    nullable VARCHAR2(1),
    LEN      NUMBER );
  /

CREATE OR REPLACE
  FUNCTION test_max_count(
      colname IN VARCHAR2)
    RETURN fun_tab PIPELINED
  AS
    tab fun_obj:=fun_obj(NULL,NULL,NULL,NULL,NULL,NULL);
    lvlen NUMBER;
  BEGIN
    FOR I IN
    (SELECT DISTINCT table_name,
      OWNER,
      COLUMN_NAME,
      DATA_TYPE,
      DATA_LENGTH,
      NULLABLE,
      NULL AS MAX_LEN
    FROM all_tab_columns
    WHERE column_name = colname
    )
    LOOP
      tab.tab_name:=i.table_name;
      tab.colname :=i.COLUMN_NAME;
      tab.datatyp :=i.DATA_TYPE;
      tab.datlen  :=i.DATA_LENGTH;
      tab.nullable:=i.NULLABLE;
      EXECUTE IMMEDIATE 'SELECT MAX(LENGTH('||i.column_name||')) FROM '||I.OWNER||'.'||I.TABLE_NAME INTO lvlen;
      tab.len:=lvlen;
      PIPE ROW(tab);
    END LOOP;
  END;
  /

------------------------------------To Execute----------------------------------

SELECT * FROM TABLE(test_max_count('abc'));

Upvotes: 1

J. Chomel
J. Chomel

Reputation: 8395

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.

Upvotes: 0

Related Questions