I have a script, that generates a dynamic statement to capture null and not null information about each column in a given table.
I was able to get the script to run initially, but only received the message PL/SQL procedure successfully completed.
After some additional research, it looks like I need to put my dynamic query result into a separate variable and use DBMS_OUTPUT.PUTLINE to display the actual results from the execute immediate statement.
This is inside the loop, so it should only have one string that is a query as a result to run, so I didn't think BULK COLLECT INTO was the correct option. That said, if it is, could someone provide a decent example, as what I did try to mimic failed even worse than what I have here.
Any advice is greatly appreciated!
set serveroutput on;
DECLARE
v_Schema ALL_TAB_COLUMNS.OWNER%TYPE;
v_Table ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
v_columnName ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
v_columnPosition ALL_TAB_COLUMNS.COLUMN_ID%TYPE;
v_dataType ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
v_sql varchar2(4000);
v_result varchar2(4000);
CURSOR c1 IS
SELECT OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_ID, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE OWNER = '<my_schema>' AND table_name='<my_table>';
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_Schema, v_Table, v_columnName, v_columnPosition, v_dataType;
EXIT WHEN c1%NOTFOUND;
v_sql := 'SELECT '''
|| v_Table || ''' AS TableName '
||',''' || v_columnName || ''' AS ColumnName '
||',''' || TO_CHAR(v_columnPosition) || ''' AS ColumnPosition '
||',COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN 1 ELSE NULL END) AS CountNulls'
||',COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN NULL ELSE 1 END) AS CountnonNulls '
||',COUNT(*) AS TotalRows '
||',COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN 1 ELSE 0 END) / CASE WHEN COUNT(*) <> 0 THEN COUNT(*) ELSE .001 * 100 END AS PercentNull '
||',COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN NULL ELSE 1 END) / CASE WHEN COUNT(*) <> 0 THEN COUNT(*) ELSE 0.1 END * 100 AS PercentNotNull '
|| 'FROM ' || v_Table;
EXECUTE IMMEDIATE v_sql;
END LOOP;
CLOSE c1;
END;
After some additional research, it looks like I need to put my dynamic query result into a separate variable and use DBMS_OUTPUT.PUTLINE to display the actual results from the execute immediate statement.
Yes, you do. Or, possibly, a record type with a field for each value. From the documentation:
If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables (defines) in the INTO clause and in-bind variables in the USING clause.
At the moment your dynamic SQL isn't even being executed; if you don't execute it into something then it is parsed, but not executed; also from the documentation:
Note: If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.
You already have some of the data you're selecting and can calculate the percentages later, so you only really need the three counts (total/null/not-null):
DECLARE
v_Schema ALL_TAB_COLUMNS.OWNER%TYPE;
v_Table ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
v_columnName ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
v_columnPosition ALL_TAB_COLUMNS.COLUMN_ID%TYPE;
v_dataType ALL_TAB_COLUMNS.DATA_TYPE%TYPE;
v_sql varchar2(4000);
v_result varchar2(4000);
v_countNulls NUMBER;
v_countNonNulls NUMBER;
v_totalRows NUMBER;
CURSOR c1 IS
SELECT OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_ID, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE OWNER = user AND table_name='T42';
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_Schema, v_Table, v_columnName, v_columnPosition, v_dataType;
EXIT WHEN c1%NOTFOUND;
v_sql := 'SELECT '
||'COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN 1 ELSE NULL END) AS CountNulls'
||',COUNT(CASE WHEN ' || v_columnName || ' IS NULL THEN NULL ELSE 1 END) AS CountnonNulls '
||',COUNT(*) AS TotalRows '
|| 'FROM ' || v_Table;
EXECUTE IMMEDIATE v_sql
INTO v_countNulls, v_countNonNulls, v_totalRows;
dbms_output.put_line(v_table || ' ' || v_columnName || ' ' || v_columnPosition
|| ' ' || v_countNulls || ' ' || v_countNonNulls || ' ' || v_totalRows
|| ' ' || v_countNulls/v_totalRows || ' ' || v_countNonNulls/v_totalRows);
END LOOP;
CLOSE c1;
END;
/
With a dummy sample table that then outputs:
T42 ID 1 0 5 5 0 1
T42 VALUE 2 2 3 5 .4 .6
... which isn't very helpful. You could potentially use a collection to build up the results and then pass that back to your caller, but that's a bit of a pain, particularly if you're just trying to run this manually to see the values.
Another way to tackle this is with a kind of XML trick. You can use what is essentially your current cursor to generate an XML representation of the results, as another kind of dynamic SQL:
select dbms_xmlgen.getxmltype(
'SELECT '''
|| table_name || ''' AS TableName '
||',''' || column_name || ''' AS ColumnName '
||',' || column_id || ' AS ColumnPosition '
||',COUNT(CASE WHEN "' || column_name || '" IS NULL THEN 1 ELSE NULL END) AS CountNulls'
||',COUNT(CASE WHEN "' || column_name || '" IS NULL THEN NULL ELSE 1 END) AS CountNonNulls '
||',COUNT(*) AS TotalRows '
|| 'FROM "' || owner || '"."' || table_name || '"') as xml
from all_tab_columns
where owner = user -- use your fixed value here instead of user
and table_name = 'T42';
| XML |
|---|
| <ROWSET> <ROW> <TABLENAME>T42</TABLENAME> <COLUMNNAME>ID</COLUMNNAME> <COLUMNPOSITION>1</COLUMNPOSITION> <COUNTNULLS>0</COUNTNULLS> <COUNTNONNULLS>5</COUNTNONNULLS> <TOTALROWS>5</TOTALROWS> </ROW> </ROWSET> |
| <ROWSET> <ROW> <TABLENAME>T42</TABLENAME> <COLUMNNAME>VALUE</COLUMNNAME> <COLUMNPOSITION>2</COLUMNPOSITION> <COUNTNULLS>2</COUNTNULLS> <COUNTNONNULLS>3</COUNTNONNULLS> <TOTALROWS>5</TOTALROWS> </ROW> </ROWSET> |
Which still doesn't look all that helpful; but you can then extract the data from that XML relationally, all in one query:
select x.TableName, x.columnName, x.ColumnPosition,
x.CountNulls, x.CountNonNulls, x.TotalRows,
x.CountNulls/x.TotalRows as PercentNull, x.CountNonNulls/x.TotalRows as PercentNonNull
from (
select dbms_xmlgen.getxmltype(
'SELECT '''
|| table_name || ''' AS "TableName" '
||',''' || column_name || ''' AS "ColumnName" '
||',' || column_id || ' AS "ColumnPosition" '
||',COUNT(CASE WHEN "' || column_name || '" IS NULL THEN 1 ELSE NULL END) AS "CountNulls"'
||',COUNT(CASE WHEN "' || column_name || '" IS NULL THEN NULL ELSE 1 END) AS "CountNonNulls" '
||',COUNT(*) AS "TotalRows" '
|| 'FROM "' || owner || '"."' || table_name || '"') as xml
from all_tab_columns
where owner = user -- use your fixed value here instead of user
and table_name = 'T42'
) t
cross apply xmltable(
'/ROWSET/ROW'
passing t.xml
columns TableName varchar2(30) path 'TableName',
ColumnName varchar2(30) path 'ColumnName',
ColumnPosition number path 'ColumnPosition',
CountNulls number path 'CountNulls',
CountNonNulls number path 'CountNonNulls',
TotalRows number path 'TotalRows'
) x
order by x.TableName, x.ColumnPosition;
| TABLENAME | COLUMNNAME | COLUMNPOSITION | COUNTNULLS | COUNTNONNULLS | TOTALROWS | PERCENTNULL | PERCENTNONNULL |
|---|---|---|---|---|---|---|---|
| T42 | ID | 1 | 0 | 5 | 5 | 0 | 1 |
| T42 | VALUE | 2 | 2 | 3 | 5 | .4 | .6 |
As @p3consulting mentioned, all_tab_columns will include columns for views and other things, but as you are looking for a specific table that might not be an issue. (If you're always looking at your own schema you can use user_tab_columns to simplify the query slightly.)
it will also include invisible and system-generated columns, which you may or may not want. To ignore invisible columns just add a filter that column_id is not null.