/*
CREATE OR REPLACE TYPE newType AS ROW(cnt integer)
 */

CREATE OR REPLACE PROCEDURE invalid_char()
    RETURNS INTEGER
    LANGUAGE NZPLSQL
AS BEGIN_PROC

DECLARE
    vSQL    VARCHAR(200);
    vSQL2   VARCHAR(200);
    vSQL3   VARCHAR(200);
    vSchemaName VARCHAR(50);
    rec1    SYSCAT.TABLES%ROWTYPE;
    rec2    SYSCAT.COLUMNS%ROWTYPE;
    rec3    RECORD AS IOLAP_TRAINING.NEWTYPE;
    vCnt    INTEGER;
    vInvalidChar    VARCHAR(20);

BEGIN
    vSchemaName := 'IOLAP_TRAINING';
    vInvalidChar := '?';
    vSQL := 'SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = ''' || vSchemaName || '''';
    FOR rec1 IN EXECUTE vSQL
    LOOP
        vSQL2 := 'SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME = ''' || rec1.TABNAME || ''' AND TABSCHEMA = ''' || vSchemaName || '''ORDER BY COLNO';
        FOR rec2 IN EXECUTE vSQL2
        LOOP
            vSQL3 := 'SELECT COUNT(*) FROM ' || vSchemaName || '.' || rec1.TABNAME || ' WHERE ' || rec2.COLNAME || ' LIKE ''%' || vInvalidChar || '%''';
            FOR rec3 IN EXECUTE vSQL3
            LOOP
                vCnt := rec3.cnt;
            END LOOP;
            IF vCnt > 0 THEN
                RAISE NOTICE 'Invalid column: % % %', vSchemaName, rec1.TABNAME, rec2.COLNAME;
            END IF;
        END LOOP;
    END LOOP;

    RETURN 0;
END;
END_PROC