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!
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>