CODEWITHSUNDEEP
oracle-databasestored-procedurescursor
HEEN
HEEN

Reputation: 4737

Updation of cursor is giving error in Oracle

I have created a stored procedure which has cursor in it. Also it updates 2 columns based on some calculations.

Below is the procedure.

CREATE OR REPLACE PROCEDURE FIBER_SIGNOFF_UGAR_UPD AS 
BEGIN

for cur_r in (
                   select inv.CIRCLE, 
                          
      
   regexp_substr(MP,'[^/]+',1,1)MPNAME,regexp_substr(MP,'[^/]+',1,2)MPCODE, 
                          inv.ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH, 
                          inv.SPAN_TYPE,  ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, 
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                         ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH,
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4)/ ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4)*100 as ug_percentage,      
                    mv.rj_intracity_link_id  FROM NE.MV_SPAN@DB_LINK_NE_VIEWER mv
                   join TBL_FIBER_INV_CMP_REPORT_MV inv
              on   ((inv.SPAN_TYPE = 'INTERCITY' AND inv.SPAN_LINK_ID = mv.rj_span_id)
                 or (inv.SPAN_TYPE = 'INTRACITY' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id)
                 or (inv.SPAN_TYPE = 'ENTERPRISE' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id))
              GROUP BY mv.rj_intracity_link_id, inv.ROUTE_APPROVED_BY_CMM, inv.SPAN_TYPE,inv.CIRCLE, inv.MP
              
              )
              
loop


          begin
          
            update TBL_FIBER_INV_SIGN_OFF_SHEET set
              FSA_UG = cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100,
              FSA_AERIAL = cur_r.CMM_APPROVED_LENGTH - (cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100)
            WHERE  SPAN_LINK_ID = 
              CASE cur_r.SPAN_TYPE
                         WHEN 'INTERCITY' THEN cur_r.rj_span_id
                         WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
                         WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
                         END;          
          
          end


end loop;


END FIBER_SIGNOFF_UGAR_UPD;

The error is

Error(33,29): PL/SQL: ORA-00920: invalid relational operator Error(39,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: ; The symbol ";" was substituted for "END" to continue.

Upvotes: 1

Views: 46

Answers (2)

user12766509
user12766509

Reputation:

Try this ,Replace where clause with case statement

  CREATE OR REPLACE PROCEDURE FIBER_SIGNOFF_UGAR_UPD AS 
BEGIN

for cur_r in (
                   select inv.CIRCLE, 
                          inv.MP, 
                          inv.ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH, 
                          inv.SPAN_TYPE,  ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, 
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                         ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH,
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4)/ ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4)*100 as ug_percentage,      
                    mv.rj_intracity_link_id  FROM NE.MV_SPAN@DB_LINK_NE_VIEWER mv
                   join TBL_FIBER_INV_CMP_REPORT_MV inv
              on   ((inv.SPAN_TYPE = 'INTERCITY' AND inv.SPAN_LINK_ID = mv.rj_span_id)
                 or (inv.SPAN_TYPE = 'INTRACITY' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id)
                 or (inv.SPAN_TYPE = 'ENTERPRISE' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id))
              GROUP BY mv.rj_intracity_link_id, inv.ROUTE_APPROVED_BY_CMM, inv.SPAN_TYPE,inv.CIRCLE, inv.MP
              
              )
              
loop


          begin
          
            update TBL_FIBER_INV_SIGN_OFF_SHEET set
              FSA_UG = cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100,
              FSA_AERIAL = cur_r.CMM_APPROVED_LENGTH - (cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100)
           WHERE  SPAN_LINK_ID = 
                  CASE  WHEN cur_r.SPAN_TYPE ='INTERCITY' THEN cur_r.rj_span_id
                        WHEN cur_r.SPAN_TYPE IN('INTRACITY','ENTERPRISE') THEN cur_r.rj_intracity_link_id
                  END;     
          
          end;


end loop;


END FIBER_SIGNOFF_UGAR_UPD;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143163

This END is missing a semi-colon:

      END;                    --> here
   END LOOP;
END FIBER_SIGNOFF_UGAR_UPD;

As of invalid relation operator: this CASE is missing the relation operator:

WHERE CASE cur_r.span_type
           WHEN 'INTERCITY' THEN cur_r.rj_span_id
           WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
           WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
      END

Is it equal to something? Different from something? What? Should be e.g.

WHERE CASE cur_r.span_type
           WHEN 'INTERCITY' THEN cur_r.rj_span_id
           WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
           WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
      END = 1
          ---
          This

Upvotes: 1

Related Questions