0

I have created a package and calling its procedure in a different anonymous block. When I call the procedure, there is missing right parentheses error. I keep getting this error but did not find any missing parenthesis.

Package

    CREATE OR REPLACE PACKAGE BODY downloadInterface
AS
   PROCEDURE getList (
      pitxt_cc             IN     stand_value.val01%TYPE,
      pitxt_language       IN     stand_value.language%TYPE,
      piint_pn             IN     prt_req.pol_n%TYPE DEFAULT NULL,
      piint_cn             IN     prt_req.cal_no%TYPE DEFAULT NULL,
      piint_ctn            IN     prt_req.nm_id_no%TYPE DEFAULT NULL,
      potab_doc_list          OUT custDocTab,
      potxt_message_id        OUT error_det.ERROR_CODE%TYPE,
      potxt_message_text      OUT error_det.ERROR_TEXT%TYPE,
      potxt_error_text        OUT error_det.ERROR_TEXT%TYPE)
   AS
      ltxt_SQL_TYPE_stmt   VARCHAR2 (32000 CHAR);
      ltxt_SQL_stmt_f      VARCHAR2 (32000 CHAR);
      ltxt_SQL_stmt_s      VARCHAR2 (32000 CHAR);
      ltxt_SQL_stmt_t      VARCHAR2 (32000 CHAR);
      lint_error_log_id    error_det.error_id%TYPE;
      lint_agnt_id         gnw_cla_user_reg.agnt_id%TYPE;
      ltxt_group_id        gnw_cla_user_reg.GROUP_ID%TYPE;
   BEGIN
      potab_doc_list := NEW custDocTab ();



      ltxt_SQL_stmt_f :=
         'SELECT ci.pol_n,
       p.c01  agr_no,
       ci.cal_no,
       ci.nm_id_no,
       NULL emailId,
       ''TIT'' doc_det,
       ci.reqt_Id,
       pr.reqt_emode reque_status,
       ci.event_type eventType,
       ci.wrt_date docprtDate,
       pr.reqt_date docreqtDate,
       pr.prt_job prtJob,
       (pr.tter_salut || '' '' || pr.addressee || '', '' || pr.area_code)
          addressee,
       pr.language language,
       ci.prgm_id tterId,
       ci.file_name prtFile,
       pp.description file_name,
       lc.val08 description,
       lc.val07 document_type,
       fn.val02 orig_filename,
       fn.val03 TYPE,
       fn.val06 friendly_filename,
       fn.val07 friendly_type,
       fn.val08 scope,
       NVL (fn.val06, ''Generic Title'')  title,
       pp.business_area,
       (SELECT description
          FROM stand_value_cb
         WHERE     table_name =''BUSINESS_AREA''
               AND language = :pitxt_language
               AND code = pp.business_area)
          ba_description,
       pp.activity,
       (SELECT description
          FROM stand_value_cb
         WHERE     table_name = ''ACTIVITY''
               AND language = :pitxt_language
               AND code = pp.activity)
          act_description
  FROM event_item ci,
       prt_prgm pp,
       polcy p,
       prt_req pr,
       (SELECT *
          FROM stand_value  
         WHERE     table_name = ''DOC_NAMES''
               AND language = :pitxt_language) fn,
       (SELECT DISTINCT table_name,
                        val03,
                        val07,
                        val08
          FROM stand_value
         WHERE table_name = ''tter_CONFIGURATION'') lc, ';

      ltxt_SQL_stmt_s := '  WHERE     ci.tter_location = ''DISK''
       AND ci.prgm_id = pp.prgm_id
       AND ci.event_type = ''LTOT''
       AND ci.pol_n = p.pol_n
       AND p.pre_seq_no IS NULL
       AND ci.reqt_id = pr.reqt_id
       AND pp.prgm_id = lc.val03
       AND pp.prgm_id = fn.val04(+)
       AND  fn.val08 IS NOT NULL';



      ltxt_SQL_TYPE_stmt :=
         '  SELECT custDocTab
                                                    (   polcyNumber
                                                     ,  alan
                                                     ,  clmNumber
                                                     ,  cstmNumber
                                                     ,  emailId
                                                     ,  docSource
                                                     ,  reqtId
                                                     ,  reqtStatus
                                                     ,  eventType
                                                     ,  docprtDate
                                                     ,  docreqtDate
                                                     ,  prtJob
                                                     ,  addressee
                                                     ,  language
                                                     ,  tterId
                                                     ,  prtFile
                                                     ,  file_name
                                                     ,  description
                                                     ,  document_type
                                                     ,  orig_filename
                                                     ,  type
                                                     ,  friendly_filename
                                                     ,  friendly_type
                                                     ,  scope
                                                    )
                                     FROM (';


      IF piint_cust_no IS NOT NULL
      THEN
         SELECT agnt_id, GROUP_ID
           INTO lint_agnt_id, ltxt_group_id
           FROM gnw_cla_user_reg
          WHERE id_no = piint_cust_no;


         IF (generic_cl.iseIvalpl (pitxt_cc         => pitxt_cc,
                                        piint_agnt_Id    => lint_agnt_id,
                                        pitxt_group_id   => ltxt_group_id))
         THEN
            cript_idt.set_cript_idt (
               pitxt_cc             => pitxt_cc,
               pinum_id_no_orig     => piint_cust_no,
               piint_agnt_id        => lint_agnt_id,
               pitxt_group_id       => ltxt_group_id,
               potxt_message_id     => potxt_message_id,
               potxt_message_text   => potxt_message_text,
               potxt_error_text     => potxt_error_text);

            IF ( (potxt_message_id || potxt_message_text || potxt_error_text)
                   IS NOT NULL)
            THEN
               -- Abort.
               RETURN;
            END IF;


            ltxt_SQL_stmt_t :=
               ' AND ci.nm_id_no = nt.id_no(+) 
                    AND nt.id_no = :piint_cust_no
                    AND pp.business_area = 
                    event                                                    
                        WHEN fn.val07 = ''Claims'' THEN ''CL''
                        WHEN fn.val07 = ''polcy'' THEN ''PO''
                    END)';

            EXECUTE IMMEDIATE
                  ltxt_SQL_TYPE_stmt
               || ltxt_SQL_stmt_f
               || 'name_idt nt'
               || ltxt_SQL_stmt_s
               || ltxt_SQL_stmt_t
               BULK COLLECT INTO potab_doc_list
               USING IN pitxt_language,
                     pitxt_language,
                     pitxt_language,
                     piint_cust_no;
         ELSE
            EXECUTE IMMEDIATE
                  ltxt_SQL_TYPE_stmt
               || ltxt_SQL_stmt_f
               || 'name_dup nt'
               || ltxt_SQL_stmt_s
               || ltxt_SQL_stmt_t
               BULK COLLECT INTO potab_doc_list
               USING IN pitxt_language,
                     pitxt_language,
                     pitxt_language,
                     piint_cust_no;
         END IF;
      ELSIF piint_pol_n IS NOT NULL AND piint_cal_no IS NULL
      THEN
         EXECUTE IMMEDIATE
               ltxt_SQL_TYPE_stmt
            || ltxt_SQL_stmt_f
            || 'name_dup nt'
            || ltxt_SQL_stmt_s
            || 'and ci.pol_n = :piint_pol_n
       and pp.business_area = ''PO''
       AND ci.cal_no is null)'
            BULK COLLECT INTO potab_doc_list
            USING IN pitxt_language,
                  pitxt_language,
                  pitxt_language,
                  piint_pol_n;
      ELSIF piint_cal_no IS NOT NULL
      THEN
         EXECUTE IMMEDIATE
               ltxt_SQL_TYPE_stmt
            || ltxt_SQL_stmt_f
            || 'name_dup'
            || ltxt_SQL_stmt_s
            || 'and ci.pol_n = :piint_cal_no)'
            BULK COLLECT INTO potab_doc_list
            USING IN pitxt_language,
                  pitxt_language,
                  pitxt_language,
                  piint_cal_no;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         z_error ('Error while fetching documents =' || SQLERRM);


         potxt_message_id := 'GCDDIGDL009';

         lint_error_log_id :=
            utl_gss.save_error (ptxt_cc           => pitxt_cc,
                                ptxt_error_code   => potxt_message_id,
                                ptxt_error_text   => potxt_message_text);
         potxt_error_text :=
            utl_gss.format_fatal_error (
               pint_error_log_id   => lint_error_log_id);
   END getList;
END downloadInterface;
/

Block for calling the procedure where missing parenthesis error is throwing

DECLARE
   potab_doc_listt       custDocTab;
   potxt_message_idt     error_det.ERROR_CODE%TYPE;
   potxt_message_textt   error_det.ERROR_TEXT%TYPE;
   potxt_error_textt     error_det.ERROR_TEXT%TYPE;
BEGIN
   downloadInterface.getList ('KL',
                              'AIA',
                              NULL,
                              NULL,
                              521749999,
                              potab_doc_listt,
                              potxt_message_idt,
                              potxt_message_textt,
                              potxt_error_textt);
END;

Error

ORA-20210: generic_cl.iseIvalpl Error while fetching documents =ORA-00907: missing right parenthesis
ORA-06512: at "Z_ERROR", line 60
ORA-06512: at "downloadInterface", line 226
ORA-06512: at line 9

After putting logs at several places I found that the error is coming at first EXECUTE IMMEDIATE statement which is the below one

EXECUTE IMMEDIATE
                  ltxt_SQL_TYPE_stmt
               || ltxt_SQL_stmt_f
               || 'name_idt nt'
               || ltxt_SQL_stmt_s
               || ltxt_SQL_stmt_t
               BULK COLLECT INTO potab_doc_list
               USING IN pitxt_language,
                     pitxt_language,
                     pitxt_language,
                     piint_cust_no;
APC
  • 144,005
  • 19
  • 170
  • 281
Vasudha Dixit
  • 377
  • 8
  • 21
  • 3
    Did you tried to boil it down towards a *minimal* reproducible example? – Marmite Bomber Sep 28 '21 at 13:20
  • 2
    You should better use modern ANSI join syntax, instead of old Oracle join syntax. Use `DBMS_OUTPUT.PUT_LINE(...)` on the command string before you execute the command. The error seems to be quite obvious. – Wernfried Domscheit Sep 28 '21 at 13:22

1 Answers1

1

Dynamic SQL is hard because it turns compilation errors into runtime errors. That's what is happening here. Your chunks of SQL once assembled generate invalid SQL.

Debugging dynamic SQL is a pain in the neck. You should have a variable for the whole statement, then use DBMS_OUTPUT.PUT_LINE to print the entire thing to the screen. That makes it easier to spot the syntactical bloomer(s).

Alternatively, write the statements as static SQL, make sure they run and then turn them into chunks.

Finally, you can have this one for free. The string assigned to ltxt_SQL_stmt_t contains the word EVENT when the compiler is probably expecting CASE.

APC
  • 144,005
  • 19
  • 170
  • 281