Oracle Execute Immediate into variable error: inconsistent datatypes

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';

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;

fiddle

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.