PL/SQL ORACLE ERROR : PLS-00225 in my Code

Question

I know that there are already other posts on this subject, but I can't find a way to make it work. Can someone take a shot at my code, please? I'm blocked on this issue.

So basically, I can't create correctly my PROCEDURE because of these 3 errors:

PL/SQL: SQL Statement ignored PL/SQL: ORA-00904: "BCM_CURSOR"."ORDER_REF" : invalid indentificator PLS-00225: subprogram ref or cursor 'BCM_CURSOR' out of range

This is my schema for my SUBORDERS TABLE:

CREATE TABLE SUBORDERS (
    IDSUBORDER VARCHAR(128) PRIMARY KEY,
    StatusSubOrders VARCHAR(128) DEFAULT 'Open',
    DescriptionSubOrders VARCHAR(128)
);

And my schema for my XXBCM_ORDER_MGT table is :

CREATE OR REPLACE PROCEDURE pr_MigrateSubOrders AS

CURSOR BCM_cursor IS select * FROM XXBCM_ORDER_MGT where ORDER_REF LIKE '%-%'; -- DECLARING MY CURSOR

v_current_max NUMBER;
r_current_row XXBCM_ORDER_MGT%ROWTYPE; -- ROWTYPE USED BY THE CURSOR
BEGIN

OPEN BCM_cursor;
FETCH BCM_cursor INTO r_current_row;
WHILE BCM_cursor%NOTFOUND != FALSE
LOOP

    SELECT MAX(TO_NUMBER(regexp_replace(IDSUBORDER, '.*-(.*)','1'))) INTO v_current_max FROM SUBORDERS WHERE IDSUBORDER LIKE regexp_replace(BCM_cursor.ORDER_REF, '(.*)-.*','1') || '%'; -- working
    IF v_current_max = NULL THEN
        v_current_max := 1;
    ELSE
        v_current_max := v_current_max + 1;
    END IF;
    DBMS_OUTPUT.PUT_LINE(v_current_max);
    
    FETCH BCM_cursor INTO r_current_row;
END LOOP;   

CLOSE BCM_cursor;
END;

I have tested the main part of my algorithm and it seems to work correctly! ( the SELECT MAX...)

question from:https://stackoverflow.com/questions/65875757/pl-sql-oracle-error-pls-00225-in-my-code

Answer 1

CREATE OR REPLACE PROCEDURE pr_MigrateSubOrders AS

   CURSOR BCM_cursor IS select * FROM XXBCM_ORDER_MGT where ORDER_REF LIKE '%-%'; -- DECLARING MY CURSOR
   
   v_current_max NUMBER;
   r_current_row XXBCM_ORDER_MGT%ROWTYPE; -- ROWTYPE USED BY THE CURSOR
BEGIN
   
   OPEN BCM_cursor;
   LOOP
   FETCH BCM_cursor INTO r_current_row;
   EXIT WHEN BCM_cursor%NOTFOUND;
   
       SELECT MAX(TO_NUMBER(regexp_replace(IDSUBORDER, '.*-(.*)','1'))) INTO v_current_max
         FROM SUBORDERS
        WHERE IDSUBORDER LIKE regexp_replace(r_current_row.ORDER_REF, '(.*)-.*','1') || '%'; -- working
       
       v_current_max := NVL( v_current_max, 0) + 1;
       
       DBMS_OUTPUT.PUT_LINE(v_current_max);
       
   END LOOP;   
   
   CLOSE BCM_cursor;
END;