Query to identify columns that may be boolean

[frost242]

This may help others :

SET SERVEROUTPUT ON SIZE UNLIMITED;
SET FEEDBACK OFF;

DECLARE
    v_distinct_values VARCHAR2(4000);
    v_count           NUMBER;
    v_sql             VARCHAR2(1000);
    v_ora2pg_conf     CLOB := 'REPLACE_AS_BOOLEAN ';
    v_owner           VARCHAR2(30) := 'OWNER'; -- À ADAPTER
    v_first           BOOLEAN := TRUE;
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('TABLE_NAME', 30) || ' | ' || RPAD('COLUMN_NAME', 30) || ' | ' || 'DISTINCT VALUES');
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 30, '-') || ' | ' || RPAD('-', 30, '-') || ' | ' || RPAD('-', 30, '-'));

    FOR rec IN (
        SELECT owner, table_name, column_name
        FROM dba_tab_cols
        WHERE data_type IN ('VARCHAR2', 'CHAR', 'NUMBER')
          AND data_length = 1
          AND owner = v_owner
          AND table_name NOT LIKE 'BIN$%' 
        ORDER BY table_name, column_name
    ) LOOP
        BEGIN
            -- 1. On compte d'abord le nombre de valeurs distinctes
            v_sql := 'SELECT COUNT(DISTINCT TO_CHAR(' || rec.column_name || ')) ' ||
                     'FROM "' || rec.owner || '"."' || rec.table_name || '" ' ||
                     'WHERE ' || rec.column_name || ' IS NOT NULL';
            
            EXECUTE IMMEDIATE v_sql INTO v_count;

            -- 2. On récupère les valeurs pour l'affichage
            v_sql := 'SELECT LISTAGG(distinct_val, '', '') WITHIN GROUP (ORDER BY distinct_val) ' ||
                     'FROM (SELECT DISTINCT TO_CHAR(' || rec.column_name || ') as distinct_val ' ||
                     '      FROM "' || rec.owner || '"."' || rec.table_name || '" ' ||
                     '      WHERE ' || rec.column_name || ' IS NOT NULL)';

            EXECUTE IMMEDIATE v_sql INTO v_distinct_values;

            -- Affichage de l'analyse en cours
            DBMS_OUTPUT.PUT_LINE(
                RPAD(rec.table_name, 30) || ' | ' || 
                RPAD(rec.column_name, 30) || ' | ' || 
                NVL(v_distinct_values, '[EMPTY]')
            );

            -- 3. Si on a 1 ou 2 valeurs max, on ajoute à la conf ora2pg
            IF v_count > 0 AND v_count <= 2 THEN
                IF NOT v_first THEN
                    v_ora2pg_conf := v_ora2pg_conf || ' ';
                END IF;
                v_ora2pg_conf := v_ora2pg_conf || rec.table_name || ':' || rec.column_name;
                v_first := FALSE;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(RPAD(rec.table_name, 30) || ' | ' || RPAD(rec.column_name, 30) || ' | Error: ' || SQLERRM);
        END;
    END LOOP;

    -- Affichage de la ligne de configuration finale
    DBMS_OUTPUT.PUT_LINE(CHR(10));
    DBMS_OUTPUT.PUT_LINE('--- COPIER LA LIGNE CI-DESSOUS DANS ORA2PG.CONF ---');
    DBMS_OUTPUT.PUT_LINE(v_ora2pg_conf);
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
END;
/

[bersace]

@frost242 que penses-tu d'utiliser ALL_TAB_COL_STATISTICS.NUM_DISTINCT ?

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2092.htm#i1590494