Below are three different cursors: POTENTIAL_USERS, NO_WORKFLOWS, and NO_MAPPINGS. I am trying to find a way to delete from the POTENTIAL USERS CURSOR where it is found in the NO_MAPPINGS and also the NO_WORKFLOWS cursors. I am using cursors because the queries they are referencing are rather long, and this makes it easier for me to follow. Also, the NO_WORKFLOWS and NO_MAPPINGS is referencing two different WITH statements, which I substituted instead of using views because I am in a read-only database, and the POTENTIAL_USERS references a query that pulls out inactive user accounts. I gathered the error messages from creating a similar scenario in another database. I would appreciate any advice or recommendations.
CURSOR USERS_WITHOUT_CHECKEDOUT_WORKFLOWS
IS
WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_WORKFLOWS
AS (SELECT C.SUBJ_NAME,
A.TASK_NAME,
B.USER_NAME,
USER_ID
FROM GAI_PM.OPB_TASK A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_WORKFLOWS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_WORKFLOWS.USER_ID
WHERE CHECKED_OUT_WORKFLOWS.USER_NAME IS NULL;
CURSOR POTENTIAL_USERS_TO_DELETE
IS
SELECT USER_NAME AS "USERS TO DELETE"
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%CSTEINKAMP%';
BEGIN
DECLARE
CURSOR USERS_WITHOUT_CHECKEDOUT_MAPPINGS
IS
WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_MAPPINGS
AS (SELECT C.SUBJ_NAME,
A.MAPPING_NAME,
B.USER_NAME,
B.USER_ID
FROM GAI_PM.OPB_MAPPING A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME, MAPPING_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_MAPPINGS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_MAPPINGS.USER_ID
WHERE CHECKED_OUT_MAPPINGS.USER_NAME IS NULL;
POTENTIAL_USERS POTENTIAL_USERS_TO_DELETE%ROWTYPE;
NO_WORKFLOWS USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ROWTYPE;
NO_MAPPINGS USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ROWTYPE;
BEGIN
LOOP
IF NOT (POTENTIAL_USERS_TO_DELETE%ISOPEN)
THEN
OPEN POTENTIAL_USERS_TO_DELETE;
END IF;
IF NOT (USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ISOPEN)
THEN
OPEN USERS_WITHOUT_CHECKEDOUT_WORKFLOWS;
END IF;
IF NOT (USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ISOPEN)
THEN
OPEN USERS_WITHOUT_CHECKEDOUT_MAPPINGS;
END IF;
FETCH POTENTIAL_USERS_TO_DELETE INTO POTENTIAL_USERS;
FETCH USERS_WITHOUT_CHECKEDOUT_WORKFLOWS INTO NO_WORKFLOWS;
FETCH USERS_WITHOUT_CHECKEDOUT_MAPPINGS INTO NO_MAPPINGS;
DELETE FROM POTENTIAL_USERS
WHERE POTENTIAL_USERS.USER_NAME = NO_WORKFLOWS.USER_NAME
OR POTENTIAL_USERS.USER_NAME = NO_MAPPINGS.USER_NAME;
IF (POTENTIAL_USERS_TO_DELETE%ISOPEN)
THEN
CLOSE POTENTIAL_USERS_TO_DELETE;
END IF;
IF (USERS_WITHOUT_CHECKEDOUT_WORKFLOWS%ISOPEN)
THEN
CLOSE USERS_WITHOUT_CHECKEDOUT_WORKFLOWS;
END IF;
IF (USERS_WITHOUT_CHECKEDOUT_MAPPINGS%ISOPEN)
THEN
CLOSE USERS_WITHOUT_CHECKEDOUT_MAPPINGS;
END IF;
END LOOP;
END;
END;
These are the error messages I keep getting:
ORA-06550: line 22, column 29: PLS-00103: Encountered the symbol "NO_WORKFLOWS" when expecting one of the following:
( The symbol "(" was substituted for "NO_WORKFLOWS" to continue. ORA-06550: line 23, column 3: PLS-00103: Encountered the symbol "THEN" when expecting one of the following:
) , and or as The symbol ")" was substituted for "THEN" to continue. ORA-06550: line 26, column 40: PLS-00103: Encountered the symbol "NO_WORKFLOWS" when expecting one of the following:
( The symbol "(" was substituted for "NO_WORKFLOWS" to continue. ORA-06550: line 27, column 44: PLS-00103: Encountered the symbol "NO_MAPPINGS" when expecting one of the following:
(
I realized I was making this way too difficult. All I had to do was split the queries into two separate delete scripts, and then just run each of them separately or as a script.
--Finds and deletes users with no access for past year
--and sees if they have any checked out workflows.
DELETE USER_NAME
FROM (SELECT USER_NAME
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%CSTEINKAMP%')
WHERE USER_NAME IN
(WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (
SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE),
-12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_WORKFLOWS
AS (SELECT C.SUBJ_NAME,
A.TASK_NAME,
B.USER_NAME,
USER_ID
FROM GAI_PM.OPB_TASK A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_WORKFLOWS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_WORKFLOWS.USER_ID
WHERE CHECKED_OUT_WORKFLOWS.USER_NAME IS NULL);
--Finds and deletes users with no access for past year
--and sees if they have any checked out mappings.
DELETE USER_NAME
FROM (SELECT USER_NAME
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE), -12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%CSTEINKAMP%')
WHERE USER_NAME IN
(WITH POTENTIAL_USERS_TO_DELETE
AS (SELECT USER_NAME, USER_ID
FROM GAI_PM.REP_USERS
WHERE USER_NAME NOT IN
(SELECT USER_NAME
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY REP_USERS.USER_NAME
ORDER BY
TO_DATE (
SUBSTR (
LAST_SAVED,
1,
10),
'MM/DD/YYYY') DESC)
RN,
REP_USERS.USER_NAME,
REP_VERSION_PROPS.LAST_SAVED
FROM GAI_PM.REP_USERS
JOIN
GAI_PM.REP_VERSION_PROPS
ON REP_USERS.USER_ID =
REP_VERSION_PROPS.USER_ID)
WHERE RN = 1
AND TO_DATE (
SUBSTR (LAST_SAVED, 1, 10),
'MM/DD/YYYY') >
ADD_MONTHS (TRUNC (SYSDATE),
-12))
AND UPPER (REP_USERS.USER_NAME) NOT LIKE '%ADMIN%'
AND UPPER (REP_USERS.USER_NAME) NOT LIKE
'%CSTEINKAMP%'),
CHECKED_OUT_MAPPINGS
AS (SELECT C.SUBJ_NAME,
A.MAPPING_NAME,
B.USER_NAME,
B.USER_ID
FROM GAI_PM.OPB_MAPPING A
JOIN GAI_PM.OPB_USERS B
ON A.CHECKOUT_USER_ID = B.USER_ID
JOIN GAI_PM.OPB_SUBJECT C
ON A.SUBJECT_ID = C.SUBJ_ID
WHERE A.CHECKOUT_USER_ID <> 0)
SELECT DISTINCT POTENTIAL_USERS_TO_DELETE.USER_NAME
FROM POTENTIAL_USERS_TO_DELETE
LEFT JOIN
CHECKED_OUT_MAPPINGS
ON POTENTIAL_USERS_TO_DELETE.USER_ID =
CHECKED_OUT_MAPPINGS.USER_ID
WHERE CHECKED_OUT_MAPPINGS.USER_NAME IS NULL);