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;