Skip to content

Instantly share code, notes, and snippets.

@guestart
Last active December 10, 2021 02:36
Show Gist options
  • Save guestart/1e0f49089530564a9f7c01b015416ad7 to your computer and use it in GitHub Desktop.
Save guestart/1e0f49089530564a9f7c01b015416ad7 to your computer and use it in GitHub Desktop.
Two anonymous plsql blocks checking those tables (starting with "DBA_HIST_" in SYS schema of oracle database) returned line is 0
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(200);
tab_num NUMBER;
CURSOR cur_dba_hist IS
SELECT table_name
FROM dict
WHERE table_name LIKE '%DBA_HIST_%'
ORDER BY 1;
BEGIN
FOR v_dba_hist IN cur_dba_hist
LOOP
v_sql := 'SELECT COUNT(*) INTO tab_num FROM ' || v_dba_hist.table_name;
EXECUTE IMMEDIATE v_sql;
IF tab_num = 0 THEN
DBMS_OUTPUT.PUT_LINE('The lines of ' || v_dba_hist.table_name || ' has been returned 0.');
END IF;
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 15
-- The updated version after replied to me on https://www.freelists.org/post/oracle-l/Why-these-two-methods-using-cursor-to-acquire-table-name-and-then-using-this-table-name-to-select-its-returned-lines-is-always-encoutered-a-weird-error,1.
-- on Oracle-L with FreeLists.org.
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(200);
tab_num NUMBER;
CURSOR cur_dba_hist IS
SELECT table_name
FROM dict
WHERE table_name LIKE '%DBA_HIST_%'
ORDER BY 1;
BEGIN
FOR v_dba_hist IN cur_dba_hist
LOOP
v_sql := 'SELECT COUNT(*) FROM ' || v_dba_hist.table_name;
EXECUTE IMMEDIATE v_sql INTO tab_num;
IF tab_num = 0 THEN
DBMS_OUTPUT.PUT_LINE('The lines of ' || v_dba_hist.table_name || ' has been returned 0.');
END IF;
END LOOP;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment