PL SQL Oracle Store Dynamic SQL result (Dynamic in a loop)

Question

I'm new to PL/SQL on Oracle (have to do some work on this but it's clearly not my domain).

So I have to do an 'audit' of our DB which consists in giving for each column of each table of our schema its max length (how we declared it (VARCHAR2(15) for example) and the max value currently of this column (ID 15000 for example) (might evolve and want more data in my results but at the moment i just need this).

I will explain my problem with an example to be clear :

I have a table EMPLOYEE with 3 columns : NAME in VARCHAR2(50), the longest i have (in length) is 48 CITY in VARCHAR2(100), the longest i have (in length) is 95 AGE in NUMBER, the longest i have (in length) is 2

So for this table of my schema I would like to have as output of my script (to work on it in excel), it must be taken into account that here the employee table is only one among many others which is returned by the first request:

TABLE_NAME	COLUMN_NAME	MAX_LENGTH_DATA	MAX_LENGTH_COLUMN
EMPLOYEE	NAME	48	50
EMPLOYEE	CITY	95	100
EMPLOYEE	AGE	2	() (don't need)

So we will have 1 line per column and table, if my table have 5 columns i will have 5 lines. I've tried many solutions with LOOP, CURSOR and now TYPE OBJECT but i'm doing something wrong i know but can't figure out what it is.

CREATE OR REPLACE TYPE t_output_allColumns FORCE AS OBJECT
           (maxLengthColumn  NUMBER,
            COLUMN_NAME     VARCHAR2(80),
            TABLE_NAME    VARCHAR2(80));
/
CREATE OR REPLACE TYPE output_allColumns FORCE AS TABLE OF t_output_allColumns;


DECLARE

    maxlengthTab  output_allColumns;
    v_requete_maxLength varchar2(4000);
    TYPE MyCurTyp  IS REF CURSOR;
    c1 MyCurTyp;
    v_column_name VARCHAR2(400);
    v_table_name VARCHAR2(400);
 
begin
 maxlengthTab:= output_allColumns();
 OPEN c1 FOR 'select TABLE_NAME, COLUMN_NAME from ALL_TAB_COLUMNS';
    
    FETCH c1 INTO v_column_name , v_table_name;
    v_requete_maxLength := 'SELECT MAX( LENGTH(' || v_column_name ||'), ' || v_column_name ||',' || v_table_name ||'  FROM ' ||v_table_name;
    EXECUTE IMMEDIATE v_requete_maxLength BULK COLLECT INTO maxlengthTab;
    dbms_output.put_line(output_allColumns);
  CLOSE c1;
END;

Here is a script i tried, first thing i do is to select all columns from my schema (no problem with this, i already printed them to test and it's good) But the main probleme is when i try to use dynamic SQL on my result

I try SELECT MAX( LENGTH(' || Colum_name i get from my 1st request||'), ' || Colum_name i get from my 1st request||',' || Table_name i get from my 1st request||' FROM ' ||Table_name i get from my 1st request; and this is where I'm stuck, I can't store each result and display it.

Answer 1

You can use a pipelined function.

---

Given the types:

CREATE TYPE t_output_allColumns AS OBJECT(
  OWNER           VARCHAR2(80),
  TABLE_NAME      VARCHAR2(80),
  COLUMN_NAME     VARCHAR2(80),
  maxLengthData   NUMBER,
  maxLengthColumn NUMBER
);

CREATE TYPE output_allColumns AS TABLE OF t_output_allColumns;

Then the function:

CREATE FUNCTION column_details(
  i_owner IN VARCHAR2
)
RETURN output_allcolumns PIPELINED
IS
  v_data_length NUMBER;
BEGIN
  FOR r IN (
    SELECT owner,
           table_name,
           column_name,
           data_length
    FROM   all_tab_columns
    WHERE  owner = i_owner
  )
  LOOP
    EXECUTE IMMEDIATE
      'SELECT MAX(LENGTH("'||r.column_name||'")) FROM "'||r.owner||'"."'||r.table_name||'"'
      INTO v_data_length;
    PIPE ROW (
      t_output_allcolumns(
        r.owner,
        r.table_name,
        r.column_name,
        v_data_length,
        r.data_length
      )
    );
  END LOOP;
END;
/

Then you can use:

SELECT * FROM TABLE(column_details('SCHEMA_NAME'));

Which outputs:

OWNER	TABLE_NAME	COLUMN_NAME	MAXLENGTHDATA	MAXLENGTHCOLUMN
SCHEMA_NAME	EMPLOYEES	NAME	48	50
SCHEMA_NAME	EMPLOYEES	AGE	2	22
SCHEMA_NAME	EMPLOYEES	CITY	95	100

db<>fiddle here