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, Feb 09 '22 at 22:41
What length do you expect from number, date, timestamp, interval columns? — William Robertson, Feb 09 '22 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](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/cursor-FOR-LOOP-statement.html). — William Robertson, Feb 09 '22 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, Feb 10 '22 at 06: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, Feb 10 '22 at 06:09
Yes, of course different programming languages have different syntax, but that doesn't mean that any language with a different syntax to Java or JavaScript is not code. I code in PL/SQL for a living and I can assure you that it is a programming language. — William Robertson, Feb 10 '22 at 08:38
I see your point, i just meant it's a procedural language in contrary to Java and i'm not used to this type of coding, i can't learn this in 2 days, i understand the basics but my case is too tricky or advanced for my knowledge — Thomas, Feb 10 '22 at 09:02

MT0 · Answer 1 · 2022-02-09T22:23:59.137

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

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;` — Thomas, Feb 10 '22 at 08:56
@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](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=930b6dc818564e12ab35b99299c8d406) — MT0, Feb 10 '22 at 09:06

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

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

1 Answers1