I need to store the result of a select query with IDs into a variable and use it latter. I have tried with this:
DECLARE
CURSOR commit_ids IS
SELECT COMMIT_ID FROM JV_COMMIT WHERE COMMIT_DATE < (CURRENT_TIMESTAMP - INTERVAL '5' day);
But I got the following error:
SQL Error [6550] [65000]: ORA-06550: line 3, column 80:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
;
Well, you have various options; here are some of them.
Sample data (based on Scott's EMP table):
SQL> SELECT * FROM jv_commit;
COMMIT_ID ENAME COMMIT_DAT
---------- ---------- ----------
7369 SMITH 17.12.1980
7499 ALLEN 20.02.1981
7521 WARD 22.02.1981
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR commit_ids IS
3 SELECT commit_id
4 FROM jv_commit
5 WHERE commit_date < (CURRENT_TIMESTAMP - INTERVAL '5' DAY);
6
7 commit_ids_r commit_ids%ROWTYPE;
8 --
9 t_commit_ids SYS.odcinumberlist;
10 --
11 rc SYS_REFCURSOR;
12 l_commit_id jv_commit.commit_id%TYPE;
13 BEGIN
14 DBMS_OUTPUT.put_line ('CURSOR ------');
15
16 FOR commit_ids_r IN commit_ids
17 LOOP
18 DBMS_OUTPUT.put_line (commit_ids_r.commit_id);
19 END LOOP;
20
21 --
22
23 DBMS_OUTPUT.put_line ('COLLECTION -----');
24
25 SELECT commit_id
26 BULK COLLECT INTO t_commit_ids
27 FROM jv_commit
28 WHERE commit_date < (CURRENT_TIMESTAMP - INTERVAL '5' DAY);
29
30 FOR i IN t_commit_ids.FIRST .. t_commit_ids.LAST
31 LOOP
32 DBMS_OUTPUT.put_line (t_commit_ids (i));
33 END LOOP;
34
35 --
36
37 DBMS_OUTPUT.put_line ('REF CURSOR -----');
38
39 OPEN rc FOR SELECT commit_id
40 FROM jv_commit
41 WHERE commit_date < (CURRENT_TIMESTAMP - INTERVAL '5' DAY);
42
43 LOOP
44 FETCH rc INTO l_commit_id;
45
46 EXIT WHEN rc%NOTFOUND;
47 DBMS_OUTPUT.put_Line (l_commit_id);
48 END LOOP;
49 END;
50 /
CURSOR ------
7369
7499
7521
COLLECTION -----
7369
7499
7521
REF CURSOR -----
7369
7499
7521
PL/SQL procedure successfully completed.
SQL>
[EDIT]
If you want to delete rows from another table whose IDs match the ones in collection, just modify code within the loop and ... well, delete rows.
Before:
SQL> SELECT *
2 FROM jv_commit_property
3 ORDER BY commit_id;
COMMIT_ID ENAME COMMIT_D
---------- ---------- --------
7369 SMITH 17.12.80
7499 ALLEN 20.02.81
7521 WARD 22.02.81
7566 JONES 02.04.81
7654 MARTIN 28.09.81
7698 BLAKE 01.05.81
7782 CLARK 09.06.81
7788 SCOTT 09.12.82
7839 KING 17.11.81
7844 TURNER 08.09.81
7876 ADAMS 12.01.83
7900 JAMES 03.12.81
7902 FORD 03.12.81
7934 MILLER 23.01.82
14 rows selected.
Code:
SQL> DECLARE
2 t_commit_ids SYS.odcinumberlist;
3 BEGIN
4 DBMS_OUTPUT.put_line ('COLLECTION -----');
5
6 SELECT commit_id
7 BULK COLLECT INTO t_commit_ids
8 FROM jv_commit
9 WHERE commit_date < (CURRENT_TIMESTAMP - INTERVAL '5' DAY);
10
11 FOR i IN t_commit_ids.FIRST .. t_commit_ids.LAST
12 LOOP
13 DELETE FROM jv_commit_property
14 WHERE commit_id = t_commit_ids (i);
15
16 DBMS_OUTPUT.put_line (
17 'COMMIT_ID = ' || t_commit_ids (i) || ': deleted ' || SQL%ROWCOUNT || ' row(s)');
18 END LOOP;
19 END;
20 /
COLLECTION -----
COMMIT_ID = 7369: deleted 1 row(s)
COMMIT_ID = 7499: deleted 1 row(s)
COMMIT_ID = 7521: deleted 1 row(s)
PL/SQL procedure successfully completed.
After:
SQL> SELECT *
2 FROM jv_commit_property
3 ORDER BY commit_id;
COMMIT_ID ENAME COMMIT_D
---------- ---------- --------
7566 JONES 02.04.81
7654 MARTIN 28.09.81
7698 BLAKE 01.05.81
7782 CLARK 09.06.81
7788 SCOTT 09.12.82
7839 KING 17.11.81
7844 TURNER 08.09.81
7876 ADAMS 12.01.83
7900 JAMES 03.12.81
7902 FORD 03.12.81
7934 MILLER 23.01.82
11 rows selected.
SQL>