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.

It would be easier with code but in PL/SQL I fight for the slightest modification. Not sure what you mean. PL/SQL is a programming language, you are writing code in it. How is this not code? — William Robertson
– William Robertson, Commented Feb 9, 2022 at 22:41
What length do you expect from number, date, timestamp, interval columns? — William Robertson
– William Robertson, Commented Feb 9, 2022 at 22:50
The code indents between open and close as if there was a loop, but there isn't one. (Logically it should not be indented there.) If you want to loop through more than one column then you should use a loop. — William Robertson
– William Robertson, Commented Feb 9, 2022 at 23:00
I mean language I'm uses to like Java or JS, syntax IS really not the same. For number, date i don't really need to know it cause you can't choose length, my audit goal is to know if an incremented id will reach the max length we set (example: We declared Varchar2(10) and our id reached 10000000000 or is near to). — Thomas
– Thomas, Commented Feb 10, 2022 at 6:01
I know about loop but as i tried many ways, i've like 7 files with different ways to reach my goal including this one with a loop in (copy ans paste it removing loop in this case, that's why it's indented there) — Thomas
– Thomas, Commented Feb 10, 2022 at 6:09

MT0 · Accepted Answer · 2022-02-09 22:23:59Z

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

edited Feb 9, 2022 at 22:23

answered Feb 9, 2022 at 22:16

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

Thanks it works really well ! So do PIPELINE works like a temporary table ? You declare a fonction which return an object (you created it previously) you can use whenever you want like a table. What if i want to have another column like v_data_length ? (for example : MIN of column v_min_data_length). Should i had it to the function's return like this ?

CREATE FUNCTION column_details(   i_owner IN VARCHAR2 ) RETURN output_allcolumns PIPELINED IS   v_data_length NUMBER,   v_min_data_length NUMBER;

MT0 Over a year ago

@Thomas The return declaration is RETURN output_allcolumns PIPELINED so if you want to return an extra column then you need to edit the t_output_allcolumns data type to add it there. However, yes, you may need to declare additional local variables between the IS and BEGIN keywords if you want to store temporary values. Something like this db<>fiddle

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

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

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related