REGEXP_SUBSTR function in stored procedure

Question

Please help me resolve PLS-00103 error for this stored procedure:

'CREATE OR REPLACE PROCEDURE REPORTS_MV."Custom_Report" (l_cursor OUT sys_refcursor) AS v_sql CLOB; 
BEGIN 
v_sql := 'SELECT 
REGEXP_SUBSTR(Z2T.DISTRICT_NUMBER, '[^-]+') AS "District Number", REGEXP_SUBSTR(Z2T.DISTRICT_NUMBER, '[^-]+', 1, 2) AS "District Name", REGEXP_SUBSTR(Z2T.TERRITORY_NUMBER, '[^-]+') AS "Territory Number", REGEXP_SUBSTR(Z2T.TERRITORY_NUMBER, '[^-]+', 1, 2) AS "Territory Name"';

 v_sql := v_sql || ' FROM REPORTS_MV."STAGE-EMDEON_REDEMPTION" A ' || ' LEFT JOIN REPORTS_MV."STAGE-CARD_GROUP" CG ON A.GROUP_NUMBER = CG.GROUP_NUMBER'; 

v_sql := v_sql || ' LEFT JOIN REPORTS_MV."STAGE-ZIP2TERRITORY" Z2T' 
|| ' ON CG.ORGANIZATION_KEY = Z2T.ORGANIZATION_KEY'; 

v_sql := v_sql || ' ORDER BY A.GROUP_NUMBER, A.RECEIVED_DATE '; 
--dbms_output.put_line (v_sql); 
OPEN l_cursor FOR v_sql; END; /'

Error

PLS-00103 (40: 53): PLS-00103: Encountered the symbol "[" when expecting one of the following:

& = - + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like like2 like4

Thanks XYZ. It appears there are nested single-quotes within the v_sql string literal. Effectively the string ends and is followed by a [ symbol, which won't compile. You can fix by escaping them or by using alternative quoted literals. docs.oracle.com/database/121/SQLRF/… — alexgibbs
@alexgibbs Would you be abe to edit my store procedure to get this right for me? — XYZ

alexgibbs alexgibbs · Accepted Answer · 2019-07-17T18:17:09

I can't of course say whether the dynamic sql is executable as written, but using alternative-quoting or escaping should allow this to compile.

The example below uses q-quoting to allow for single-quotes within the string literal. The Oracle documentation on Literals has much more information on the topic.

CREATE OR REPLACE PROCEDURE REPORTS_MV.CUSTOM_REPORT(L_CURSOR OUT SYS_REFCURSOR)
AS
    V_SQL CLOB;
BEGIN
    V_SQL := Q'!SELECT
        REGEXP_SUBSTR(Z2T.DISTRICT_NUMBER, '[^-]+') AS "District Number",
        REGEXP_SUBSTR(Z2T.DISTRICT_NUMBER, '[^-]+', 1, 2) AS "District Name",
        REGEXP_SUBSTR(Z2T.TERRITORY_NUMBER, '[^-]+') AS "Territory Number",
        REGEXP_SUBSTR(Z2T.TERRITORY_NUMBER, '[^-]+', 1, 2) AS "Territory Name"
        FROM REPORTS_MV."STAGE-EMDEON_REDEMPTION" A
        LEFT JOIN REPORTS_MV."STAGE-CARD_GROUP" CG ON A.GROUP_NUMBER = CG.GROUP_NUMBER
        LEFT JOIN REPORTS_MV."STAGE-ZIP2TERRITORY" Z2T
        ON CG.ORGANIZATION_KEY = Z2T.ORGANIZATION_KEY
        ORDER BY A.GROUP_NUMBER, A.RECEIVED_DATE!';
    --DBMS_OUTPUT.PUT_LINE(V_SQL);
    OPEN L_CURSOR FOR V_SQL;
END;
/

REGEXP_SUBSTR function in stored procedure

1 Answers