I have below procedure giving me error as

Error(17,3): PLS-00103: Encountered the symbol "FOR" when expecting one of the following: ( - case mod new not null select with continue avg count current exists max min prior sql stddev sum variance execute fora

Here is the procedure.

PROCEDURE DeactiveUsers (
      P_DeactiveUsers_OUT      OUT SYS_REFCURSOR ) AS
  BEGIN
  
       
        
   OPEN P_DeactiveUsers_OUT FOR
  for P_DeactiveUsers_Lst in(
    select * from (    
        select username, MAX(TRANSACTION_DATE) As last_login_date 
        from r4g_application_activity_log
        Group By username
    ) where last_login_date <= sysdate-90
    order by 2 desc
  )
  
  update r4g_application_activity_log
  set ISACTIVE = 1
  where USERNAME = P_DeactiveUsers_OUT.username;
         
    EXCEPTION 
    WHEN no_data_found THEN 
    INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
    WHEN others THEN 
    INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
  END DeactiveUsers;

CodePudding user response：

Hm, yes - procedure is here, but - what is its purpose? The way you put it, it seems that it shouldn't return anything so you don't really want to open a ref cursor, but use a cursor FOR loop which then updates the log table.

If that's so,

• remove OUT parameter
• remove OPEN clause (btw. you've got two FORs)
• use FOR loop
• remove WHEN NO_DATA_FOUND as there's nothing that could raise it

PROCEDURE DeactiveUsers             -- (P_DeactiveUsers_OUT OUT SYS_REFCURSOR)
AS
BEGIN
   --OPEN P_DeactiveUsers_OUT FOR
   FOR P_DeactiveUsers_Lst
      IN (  SELECT *
              FROM (  SELECT username, MAX (TRANSACTION_DATE) AS last_login_date
                        FROM r4g_application_activity_log
                    GROUP BY username)
             WHERE last_login_date <= SYSDATE - 90
          ORDER BY 2 DESC)
   LOOP
      UPDATE r4g_application_activity_log
         SET ISACTIVE = 1
       WHERE USERNAME = P_DeactiveUsers_OUT.username;
   END LOOP;
EXCEPTION
   --    WHEN no_data_found THEN
   --    INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
   WHEN OTHERS
   THEN
      INS_UMS_ERRORLOG (SQLCODE || ' : ' || SUBSTR (SQLERRM, 1, 200),
                        NULL,
                        'DeactiveUsers',
                        NULL,
                        NULL,
                        NULL,
                        'DB : DeactiveUsers',
                        'Scheduler - UMS_DeactiveUser');
END DeactiveUsers;

[EDIT]

As you want to return list of deactivated users, then you could do it as follows: instead of a ref cursor, just loop through result set, do the update and return a collection of deactivated users:

PROCEDURE deactiveusers (p_deactivataed OUT SYS.odcivarchar2list)
IS
   l_deactivated  SYS.odcivarchar2list := sys.odcivarchar2list ();
BEGIN
   FOR cur_r
      IN (  SELECT *
              FROM (  SELECT username, MAX (TRANSACTION_DATE) AS last_login_date
                        FROM r4g_application_activity_log
                    GROUP BY username)
             WHERE last_login_date <= SYSDATE - 90
          ORDER BY 2 DESC)
   LOOP
      UPDATE r4g_application_activity_log
         SET isactive = 1
       WHERE username = cur_r.username;

      l_deactivated.EXTEND;
      l_deactivated (l_deactivated.LAST) := cur_r.username;
   END LOOP;

   p_deactivated := l_deactivated;
END;

You'd call it as e.g.

DECLARE
   l_deactivated  SYS.odcivarchar2list;
BEGIN
   deactiveusers (l_deactivated);

   FOR i IN l_deactivated.FIRST .. l_deactivated.LAST
   LOOP
      DBMS_OUTPUT.put_line (l_deactivated (i));
   END LOOP;
END;
/