0

I am writing below stored proc and getting missing right parenthesis error. It worked when I tried just the query but it throws the error when I run a complete stored procedure script in oracle 12c. There error is for line where I have used REGEXP_SUBSTR expression. If I comment it out then proc runs without any error. Please suggest a solution.

PROMPT ** Creating stored procedure TAHolidayCal_FindBrkCls *********************       

CREATE OR REPLACE PACKAGE TAHolidayCal_FindBrkClsPkg AS
TYPE RT1 IS RECORD (
    InstrumentID        Instrument.InstrumentID%TYPE,
    Status              Instrument.Status%TYPE,
    Product             Instrument.Product%TYPE,
    SysDescription      Instrument.SysDescription%TYPE,
    TradeAdminStatus    EXTTradeStatus.TradeAdminStatus%TYPE,
    PayDate             InstCfl.PayDate%TYPE
    );
TYPE RCT1 IS REF CURSOR RETURN RT1;
END;
/

CREATE OR REPLACE PROCEDURE TAHolidayCal_FindBrkCls
(
    calDate     DATE,  
    holCalID    CHAR,
    InstID01 CHAR             ,InstID02 CHAR DEFAULT NULL,InstID03 CHAR DEFAULT NULL,InstID04 CHAR DEFAULT NULL,InstID05 CHAR DEFAULT NULL,InstID06 CHAR DEFAULT NULL,InstID07 CHAR DEFAULT NULL,
    InstID08 CHAR DEFAULT NULL,InstID09 CHAR DEFAULT NULL,InstID10 CHAR DEFAULT NULL,InstID11 CHAR DEFAULT NULL,InstID12 CHAR DEFAULT NULL,InstID13 CHAR DEFAULT NULL,InstID14 CHAR DEFAULT NULL,
    InstID15 CHAR DEFAULT NULL,InstID16 CHAR DEFAULT NULL,InstID17 CHAR DEFAULT NULL,InstID18 CHAR DEFAULT NULL,InstID19 CHAR DEFAULT NULL,InstID20 CHAR DEFAULT NULL,InstID21 CHAR DEFAULT NULL,
    InstID22 CHAR DEFAULT NULL,InstID23 CHAR DEFAULT NULL,InstID24 CHAR DEFAULT NULL,InstID25 CHAR DEFAULT NULL,InstID26 CHAR DEFAULT NULL,InstID27 CHAR DEFAULT NULL,InstID28 CHAR DEFAULT NULL,
    InstID29 CHAR DEFAULT NULL,InstID30 CHAR DEFAULT NULL,InstID31 CHAR DEFAULT NULL,InstID32 CHAR DEFAULT NULL,InstID33 CHAR DEFAULT NULL,InstID34 CHAR DEFAULT NULL,InstID35 CHAR DEFAULT NULL,
    InstID36 CHAR DEFAULT NULL,InstID37 CHAR DEFAULT NULL,InstID38 CHAR DEFAULT NULL,InstID39 CHAR DEFAULT NULL,InstID40 CHAR DEFAULT NULL,InstID41 CHAR DEFAULT NULL,InstID42 CHAR DEFAULT NULL,
    InstID43 CHAR DEFAULT NULL,InstID44 CHAR DEFAULT NULL,InstID45 CHAR DEFAULT NULL,InstID46 CHAR DEFAULT NULL,InstID47 CHAR DEFAULT NULL,InstID48 CHAR DEFAULT NULL,InstID49 CHAR DEFAULT NULL,
    InstID50 CHAR DEFAULT NULL,InstID51 CHAR DEFAULT NULL,InstID52 CHAR DEFAULT NULL,InstID53 CHAR DEFAULT NULL,InstID54 CHAR DEFAULT NULL,InstID55 CHAR DEFAULT NULL,InstID56 CHAR DEFAULT NULL,
    InstID57 CHAR DEFAULT NULL,InstID58 CHAR DEFAULT NULL,InstID59 CHAR DEFAULT NULL,InstID60 CHAR DEFAULT NULL,InstID61 CHAR DEFAULT NULL,InstID62 CHAR DEFAULT NULL,InstID63 CHAR DEFAULT NULL,
    InstID64 CHAR DEFAULT NULL,InstID65 CHAR DEFAULT NULL,InstID66 CHAR DEFAULT NULL,InstID67 CHAR DEFAULT NULL,InstID68 CHAR DEFAULT NULL,InstID69 CHAR DEFAULT NULL,InstID70 CHAR DEFAULT NULL,
    InstID71 CHAR DEFAULT NULL,InstID72 CHAR DEFAULT NULL,InstID73 CHAR DEFAULT NULL,InstID74 CHAR DEFAULT NULL,InstID75 CHAR DEFAULT NULL,InstID76 CHAR DEFAULT NULL,InstID77 CHAR DEFAULT NULL,
    InstID78 CHAR DEFAULT NULL,InstID79 CHAR DEFAULT NULL,InstID80 CHAR DEFAULT NULL,InstID81 CHAR DEFAULT NULL,InstID82 CHAR DEFAULT NULL,InstID83 CHAR DEFAULT NULL,InstID84 CHAR DEFAULT NULL,
    InstID85 CHAR DEFAULT NULL,InstID86 CHAR DEFAULT NULL,InstID87 CHAR DEFAULT NULL,InstID88 CHAR DEFAULT NULL,InstID89 CHAR DEFAULT NULL,InstID90 CHAR DEFAULT NULL,InstID91 CHAR DEFAULT NULL,
    InstID92 CHAR DEFAULT NULL,InstID93 CHAR DEFAULT NULL,InstID94 CHAR DEFAULT NULL,InstID95 CHAR DEFAULT NULL,InstID96 CHAR DEFAULT NULL,InstID97 CHAR DEFAULT NULL,InstID98 CHAR DEFAULT NULL,
    InstID99 CHAR DEFAULT NULL,InstID100 CHAR DEFAULT NULL, 
    RC1         IN OUT TAHolidayCal_FindBrkClsPkg.RCT1
)
AS
BEGIN

OPEN RC1 FOR
    SELECT DISTINCT i.InstrumentID, i.Status, i.Product, i.SysDescription, e.TradeAdminStatus, TRUNC(TAHolidayCal_FindBrkCls.calDate) AS PayDate
    FROM Instrument i LEFT OUTER JOIN EXTTradeStatus e 
    ON ( i.InstrumentID = e.InstrumentID ) AND ( i.VersionNumber = e.VersionNumber ) 
    INNER JOIN EXTBreakClause b
    ON ( i.InstrumentID = b.InstrumentID ) AND ( i.VersionNumber = b.VersionNumber )
    WHERE i.VersionNumber = 0 
    AND i.InstrumentID in 
    (
        TAHolidayCal_FindBrkCls.InstID01,TAHolidayCal_FindBrkCls.InstID02,TAHolidayCal_FindBrkCls.InstID03,TAHolidayCal_FindBrkCls.InstID04,
        TAHolidayCal_FindBrkCls.InstID05,TAHolidayCal_FindBrkCls.InstID06,TAHolidayCal_FindBrkCls.InstID07,TAHolidayCal_FindBrkCls.InstID08,
        TAHolidayCal_FindBrkCls.InstID09,TAHolidayCal_FindBrkCls.InstID10,TAHolidayCal_FindBrkCls.InstID11,TAHolidayCal_FindBrkCls.InstID12,
        TAHolidayCal_FindBrkCls.InstID13,TAHolidayCal_FindBrkCls.InstID14,TAHolidayCal_FindBrkCls.InstID15,TAHolidayCal_FindBrkCls.InstID16,
        TAHolidayCal_FindBrkCls.InstID17,TAHolidayCal_FindBrkCls.InstID18,TAHolidayCal_FindBrkCls.InstID19,TAHolidayCal_FindBrkCls.InstID20,
        TAHolidayCal_FindBrkCls.InstID21,TAHolidayCal_FindBrkCls.InstID22,TAHolidayCal_FindBrkCls.InstID23,TAHolidayCal_FindBrkCls.InstID24,
        TAHolidayCal_FindBrkCls.InstID25,TAHolidayCal_FindBrkCls.InstID26,TAHolidayCal_FindBrkCls.InstID27,TAHolidayCal_FindBrkCls.InstID28,
        TAHolidayCal_FindBrkCls.InstID29,TAHolidayCal_FindBrkCls.InstID30,TAHolidayCal_FindBrkCls.InstID31,TAHolidayCal_FindBrkCls.InstID32,
        TAHolidayCal_FindBrkCls.InstID33,TAHolidayCal_FindBrkCls.InstID34,TAHolidayCal_FindBrkCls.InstID35,TAHolidayCal_FindBrkCls.InstID36,
        TAHolidayCal_FindBrkCls.InstID37,TAHolidayCal_FindBrkCls.InstID38,TAHolidayCal_FindBrkCls.InstID39,TAHolidayCal_FindBrkCls.InstID40,
        TAHolidayCal_FindBrkCls.InstID41,TAHolidayCal_FindBrkCls.InstID42,TAHolidayCal_FindBrkCls.InstID43,TAHolidayCal_FindBrkCls.InstID44,
        TAHolidayCal_FindBrkCls.InstID45,TAHolidayCal_FindBrkCls.InstID46,TAHolidayCal_FindBrkCls.InstID47,TAHolidayCal_FindBrkCls.InstID48,
        TAHolidayCal_FindBrkCls.InstID49,TAHolidayCal_FindBrkCls.InstID50,TAHolidayCal_FindBrkCls.InstID51,TAHolidayCal_FindBrkCls.InstID52,
        TAHolidayCal_FindBrkCls.InstID53,TAHolidayCal_FindBrkCls.InstID54,TAHolidayCal_FindBrkCls.InstID55,TAHolidayCal_FindBrkCls.InstID56,
        TAHolidayCal_FindBrkCls.InstID57,TAHolidayCal_FindBrkCls.InstID58,TAHolidayCal_FindBrkCls.InstID59,TAHolidayCal_FindBrkCls.InstID60,
        TAHolidayCal_FindBrkCls.InstID61,TAHolidayCal_FindBrkCls.InstID62,TAHolidayCal_FindBrkCls.InstID63,TAHolidayCal_FindBrkCls.InstID64,
        TAHolidayCal_FindBrkCls.InstID65,TAHolidayCal_FindBrkCls.InstID66,TAHolidayCal_FindBrkCls.InstID67,TAHolidayCal_FindBrkCls.InstID68,
        TAHolidayCal_FindBrkCls.InstID69,TAHolidayCal_FindBrkCls.InstID70,TAHolidayCal_FindBrkCls.InstID71,TAHolidayCal_FindBrkCls.InstID72,
        TAHolidayCal_FindBrkCls.InstID73,TAHolidayCal_FindBrkCls.InstID74,TAHolidayCal_FindBrkCls.InstID75,TAHolidayCal_FindBrkCls.InstID76,
        TAHolidayCal_FindBrkCls.InstID77,TAHolidayCal_FindBrkCls.InstID78,TAHolidayCal_FindBrkCls.InstID79,TAHolidayCal_FindBrkCls.InstID80,
        TAHolidayCal_FindBrkCls.InstID81,TAHolidayCal_FindBrkCls.InstID82,TAHolidayCal_FindBrkCls.InstID83,TAHolidayCal_FindBrkCls.InstID84,
        TAHolidayCal_FindBrkCls.InstID85,TAHolidayCal_FindBrkCls.InstID86,TAHolidayCal_FindBrkCls.InstID87,TAHolidayCal_FindBrkCls.InstID88,
        TAHolidayCal_FindBrkCls.InstID89,TAHolidayCal_FindBrkCls.InstID90,TAHolidayCal_FindBrkCls.InstID91,TAHolidayCal_FindBrkCls.InstID92,
        TAHolidayCal_FindBrkCls.InstID93,TAHolidayCal_FindBrkCls.InstID94,TAHolidayCal_FindBrkCls.InstID95,TAHolidayCal_FindBrkCls.InstID96,
        TAHolidayCal_FindBrkCls.InstID97,TAHolidayCal_FindBrkCls.InstID98,TAHolidayCal_FindBrkCls.InstID99,TAHolidayCal_FindBrkCls.InstID100
    )
    AND TAHolidayCal_FindBrkCls.holCalID in 
  (
    SELECT REGEXP_SUBSTR((extract(value(NHC), 'NotificationHolidayCalendar/text()')).getStringVal(),'[^,]+',1,LEVEL)
    FROM table(xmlsequence(extract(XMLType(b.BreakClauseDefn, 1), '/Definitions/BreakDefn/NotificationHolidayCalendar'))) NHC
    CONNECT BY REGEXP_SUBSTR((extract(value(NHC), 'NotificationHolidayCalendar/text()')).getStringVal(),'[^,]+',1,LEVEL) IS NOT NULL order by 1
    );

END;

/


SHOW ERRORS;

EXECUTE DROP_SYNONYM ('TAHolidayCal_FindBrkCls') 
EXECUTE CREATE_SYNONYM ('TAHolidayCal_FindBrkCls') 
EXECUTE CREATE_PERMISSIONS ('TAHolidayCal_FindBrkCls')
Deepika
  • 1
  • 1
  • I remember using Oracle DB a few years ago and getting that missing right bracket error. If I remember correctly my problem was that I forgot an opening bracket "(" somewhere. The error message had me confused for a while. – Patrick Mar 02 '20 at 05:51
  • I have not missed the right parenthesis. Also, I have drilled it down to order by clause 'order by 1' is causing this error. If I remove it then it runs fine. But I need to order the elements – Deepika Mar 02 '20 at 06:10

1 Answers1

0

As you commented, if you remove order by 1, it works. So - remove it. Saying that you need to order elements is OK, but - you're doing it at the wrong place.

Here's an example. Based on Scott's sample DEPT table, I'm returning rows that belong to departments 10,20:

SQL> select dname, deptno, loc from dept
  2  where 1 = 1
  3    and deptno in (select regexp_substr('10,20', '[^,]+', 1, level)
  4                   from dual
  5                   connect by level <= regexp_count('10,20', ',') + 1
  6                   order by 1           --> this is where you put ORDER BY clause
  7                  );
                 order by 1
                 *
ERROR at line 6:
ORA-00907: missing right parenthesis


SQL>

As you know - it is wrong. This is what that subquery returns:

SQL> select regexp_substr('10,20', '[^,]+', 1, level)
  2                   from dual
  3                   connect by level <= regexp_count('10,20', ',') + 1
  4                   order by 1;

REGEXP_SUBSTR('10,20
--------------------
10
20

SQL>

As you use it in IN, what difference does it make if you have

where deptno in (10, 20)

or 

where deptno in (20, 10)

You'll still get the same result. But, you want to sort the final result! So move ORDER BY out, to the outmost SELECT:

SQL> select dname, deptno, loc from dept
  2  where 1 = 1
  3    and deptno in (select regexp_substr('10,20', '[^,]+', 1, level)
  4                   from dual
  5                   connect by level <= regexp_count('10,20', ',') + 1
  6                  )
  7  order by 1;

DNAME              DEPTNO LOC
-------------- ---------- -------------
ACCOUNTING             10 NEW YORK
RESEARCH               20 DALLAS

SQL>

Now it works and does what you - I believe - wanted.

In your query, that would be

<snip>
AND TAHolidayCal_FindBrkCls.holCalID in 
  (
    SELECT REGEXP_SUBSTR((extract(value(NHC), 'NotificationHolidayCalendar/text()')).getStringVal(),'[^,]+',1,LEVEL)
    FROM table(xmlsequence(extract(XMLType(b.BreakClauseDefn, 1), '/Definitions/BreakDefn/NotificationHolidayCalendar'))) NHC
    CONNECT BY REGEXP_SUBSTR((extract(value(NHC), 'NotificationHolidayCalendar/text()')).getStringVal(),'[^,]+',1,LEVEL) IS NOT NULL 
  )
ORDER BY 1;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    Thanks for the suggestion. I tried what you suggested but it is doing the same thing that it was doing when I removed order by clause. That is, The procedure compiled without any error but when I call it from code it gives error "ORA-22950: cannot ORDER objects without MAP or ORDER method". – Deepika Mar 02 '20 at 08:54