Problem when dropping tables with empty PL / SQL records

0

Very good, I have done a procedure in PL that filters tables whose columns match or contain the word you receive from a parameter and generate a .CSV file, the fact is that I have thought about, in addition, discard those tables that do not contain no record How could I make the discard when walking the cursor? (Below the code)

CREATE OR REPLACE PROCEDURE EXPORTCOLUMNSDATA
    ( V_REGSEARCH IN VARCHAR2, V_DIRECTORY OUT VARCHAR2 ) IS

    V_TABLE ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
    V_COLUMN VARCHAR2(5000);
    V_FILE  UTL_FILE.FILE_TYPE;

    CURSOR REGFILTER IS 
        SELECT co.TABLE_NAME, co.COLUMN_NAME
        FROM ALL_TAB_COLUMNS co
        WHERE REGEXP_LIKE (co.COLUMN_NAME, V_REGSEARCH)
        ORDER BY co.TABLE_NAME asc;

BEGIN
    SELECT DIRECTORY_PATH INTO V_DIRECTORY FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'EXTRACTION';

/*Creates the file and sets it's name*/
V_FILE := UTL_FILE.FOPEN('EXTRACTION','ColumnData_'|| To_char(Sysdate, 'DD-MM_YY')||'.CSV','W'); 
/*Header*/
UTL_FILE.PUT_LINE(V_FILE,'TABLE:'||';'||'COLUMNS:');
V_TABLE := NULL;
V_COLUMN := NULL;

FOR reg IN REGFILTER
    LOOP
        IF(V_TABLE = reg.TABLE_NAME) THEN
            V_COLUMN := V_COLUMN || '  ' || reg.COLUMN_NAME;
        ELSE
            V_TABLE := reg.TABLE_NAME;
            V_COLUMN := reg.COLUMN_NAME;
            UTL_FILE.PUT_LINE(V_FILE, V_TABLE||';'|| V_COLUMN); --Writes the data.
        END IF;
    END LOOP;

UTL_FILE.FCLOSE(V_FILE);

/*Error-Handling*/

END EXPORTCOLUMNSDATA;

UPDATE:

Fragment of tested code:

-- create/replace procedure

--v_table
--v_column
V_SELECT VARCHAR2(500);
V_CHECK NUMBER(1);
--v_file
 --declare cursor

BEGIN
--Set directory + file & initialise table, column null

FOR reg IN REGFILTER
    LOOP
        IF(V_TABLE = reg.TABLE_NAME) THEN
            V_SELECT := 'SELECT COUNT(*) FROM '|| V_TABLE ||' WHERE ROWNUM = 1';
            begin 
            EXECUTE IMMEDIATE V_SELECT INTO V_CHECK;
            exception
                when others then
                v_check := 0;
            end;
            IF (V_CHECK = 1) THEN
                V_COLUMN := V_COLUMN || '  ' || reg.COLUMN_NAME;
            END IF;
        ELSE
            V_TABLE := reg.TABLE_NAME;
            V_COLUMN := reg.COLUMN_NAME;
            UTL_FILE.PUT_LINE(V_FILE, V_TABLE||';'|| V_COLUMN); --Writes the data.
        END IF;
    END LOOP;

-- close file + exceptions

END EXPORTCOLUMNSDATA;
    
asked by ProK 05.09.2018 в 13:46
source

0 answers