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;

You need to sign in to view this answers