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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…