CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
xpetta
xpetta

Reputation: 718

Simplifying a complex SQL query into a simple query

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!

Upvotes: 0

Views: 361

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

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>

Upvotes: 3

Related Questions