Simplifying a complex SQL query into a simple query

Question

The below query generates a select statement. Instead of generating the select statement I want the query to execute the select statement that is getting generating and also display the TABLE_NAME, COLUMN_NAME, DATA_TYPE and the MAX(COLUMN_SIZE), and group the results by TABLE_NAME and sort the results in descending order based on the max size of the LOB column.

select table_name, 
       column_name,
       data_type,
       'select (max(length(' || COLUMN_NAME || '))/(1024)) as "Size in KB" from '
       || owner || '.' || TABLE_NAME ||';' "querytogetlobsize" 
from dba_tab_cols 
where owner='&SCHEMA' 
  and data_type in ('CLOB','BLOB','NCLOB');

Could anyone help me with generating the query. Thank you so much for your help in advance!

the dynamic query cannot be executed in a single step. You can't do that in SQL. Would be an option for you PLSQL ? — Roberto Hernandez
– Roberto Hernandez, Commented Sep 17, 2021 at 9:02
This is not possible with SQL. You cannot select meta data (table and column names) and use this information in the same query to select data from the tables and columns. What you can do, tough, is generate a single query instead of multiple separate queries. But what do you mean by "group the results by table_name" and "display the COLUMN_NAME"? If you group by table name, there is one row per table, so which of its columns would you select? — Thorsten Kettner
– Thorsten Kettner, Commented Sep 17, 2021 at 9:11

Littlefoot · Accepted Answer · 2021-09-17 09:07:39Z

3

Dynamic SQL it is.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_size  NUMBER;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT table_name,
  6                   column_name,
  7                   data_type,
  8                      'select (max(length('
  9                   || COLUMN_NAME
 10                   || '))/(1024)) as "Size in KB" from '
 11                   || owner
 12                   || '.'
 13                   || TABLE_NAME querytogetlobsize
 14              FROM all_tab_cols
 15             WHERE     owner = 'SCOTT'
 16                   AND data_type IN ('CLOB', 'BLOB', 'NCLOB'))
 17     LOOP
 18        EXECUTE IMMEDIATE cur_r.querytogetlobsize
 19           INTO l_size;
 20
 21        DBMS_OUTPUT.put_line (
 22              RPAD (cur_r.table_name, 20, ' ')
 23           || ' - '
 24           || RPAD (cur_r.column_name, 20, ' ')
 25           || ': '
 26           || TO_CHAR (l_size, '999G990D00'));
 27     END LOOP;
 28  END;
 29  /

which results in

DUGOTRAJNO       - KRAJ_BLOB           :    1.708,98
DUGOTRAJNO       - POCETAK_BLOB        :    2.596,62
OSOBA            - PHOTO               :      390,32
OSOBA            - FAKSIMIL            :       23,18
ZAHTJEV_PUTNI_NA - NALOG_BLOB          :   16.286,69
ZAHTJEV_PUTNI_NA - POZIV_BLOB          :   25.609,50

PL/SQL procedure successfully completed.

SQL>

answered Sep 17, 2021 at 9:07

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

3 Comments

xpetta Over a year ago

Thank you so much @Littlefoot works like a charm. I have one small question could you please explain me this formatting TO_CHAR (l_size, '999G990D00')

Littlefoot Over a year ago

You're welcome. TO_CHAR just nicely formats output (as you can see on the screen); you don't have to use it, and then you'll get bunch of numbers right of the decimal point character, and thousands won't be separated by a group character (dot in my database).

xpetta Over a year ago

Thanks again! You saved my day. Have a lovely weekend :)

Collectives™ on Stack Overflow

Simplifying a complex SQL query into a simple query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related