-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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