PL/SQL

hi
i am getting following error while executing code.

error:
Enter value for appid: 1
old  34:   ls_AppID CHAR (6) := '&appid';
new  34:   ls_AppID CHAR (6) := '1';
  exception
  *
ERROR at line 264:
ORA-06550: line 264, column 3:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the
following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
ORA-06550: line 280, column 1:
PLS-00103: Encountered the symbol "<" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 287, column 1:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 381, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member co

set serveroutput on size 1000000
set timing on
set pagesize 0

declare

  ll_count  number default 0;
  cup_count number default 0;
  cnu_count number default 0;
  mm_count  number default 0;
  sub_count number default 0;
  cer_count number default 0;
  mer_count number default 0;
  ser_count number default 0;
  err_count number default 0;
  v_ban                   contract.ban%type;
  v_subscriber_no         contract.subscriber_no%type;
  v_ctn_seq_no            contract.cnt_seq_no%type;
  i_ban                   contract.ban%type;
  i_subscriber_no         contract.subscriber_no%type;
  i_commit_start_date     contract.COMMIT_START_DATE%type;
  i_commit_end_date       contract.COMMIT_END_DATE%type;
  i_commit_reason_cd      contract.COMMIT_REASON_CD%type;
  i_commit_orig_no_month  contract.COMMIT_ORIG_NO_MONTH%type;
  r1_ban                  contract.ban%type;
  r1_subscriber_no        contract.subscriber_no%type;
  r1_contract_type        contract.CONTRACT_TYPE%type;
  r2_ban                  contract.ban%type;
  r2_subscriber_no        contract.subscriber_no%type;
  r2_start_date    contract.COMMIT_START_DATE%type;
  r2_end_date      contract.COMMIT_END_DATE%type;
  r2_reason_cd     contract.COMMIT_REASON_CD%type;
  r2_orig_no_month contract.COMMIT_ORIG_NO_MONTH%type;

  ls_AppID CHAR (6) := '&appid';
  ls_SrvCD CHAR (5) := 'CSMFX';

  cursor cur_t1 is
select /*+ PARALLEL(c,8) */ c.BAN, c.SUBSCRIBER_NO, c.CNT_SEQ_NO
FROM contract c, subscriber sub
WHERE c.commit_start_date >
      (SELECT /*+ PARALLEL(SA,8) */ MIN(SA.EFFECTIVE_DATE) - 30
         FROM SERVICE_AGREEMENT SA, a_SOC S
        WHERE SA.BAN = c.ban
        AND SA.SUBSCRIBER_NO = c.subscriber_no
        AND SA.SERVICE_TYPE = 'P'
        AND S.SOC = SA.SOC
        AND S.EFFECTIVE_DATE <= SA.EFFECTIVE_ISSUE_DATE
        AND NVL(S.EXPIRATION_DATE,TO_DATE('47001231','YYYYMMDD')) >= SA.EFFECTIVE_ISSUE_DATE
        AND S.MYTHOS_CD = 'M'
        AND S.WAIVER_ELIGIBLE_CD = 'B')
AND NVL(C.CONTRACT_TYPE,' ') <> 'W'
AND NVL(C.VOID_IND, 'N') = 'N'
AND C.SUBSCRIBER_NO = SUB.SUBSCRIBER_NO
AND C.BAN = SUB.CUSTOMER_ID
AND C.EFFECTIVE_DATE >= SUB.INIT_ACTIVATION_DATE
AND SUB.SUB_STATUS in ('A','S')
AND (EXISTS (SELECT /*+ PARALLEL(c2,8) */  c2.* 
              FROM CONTRACT C2
             WHERE C2.BAN = c.ban
              AND C2.SUBSCRIBER_NO = c.subscriber_no
              AND NVL(C2.VOID_IND, 'N') = 'N'
              AND NVL(C2.CONTRACT_TYPE,' ') <> 'W'
              AND C2.CNT_SEQ_NO < C.CNT_SEQ_NO
              AND NVL(C2.COMMIT_ORIG_NO_MONTH,0) > 0)
  OR (C.COMMIT_START_DATE >= ADD_MONTHS(SUB.INIT_ACTIVATION_DATE,18)));

  cursor cur_t2 is
select /*+ PARALLEL(c,8) */ c.BAN, c.SUBSCRIBER_NO, c.CNT_SEQ_NO
from contract c
where c.application_id = ls_AppID
and c.OPERATOR_ID = to_number(to_char(sysdate,'YYYYMMDD'));

  cursor cur_t3 is
select /*+ PARALLEL(c,8) */ distinct c.BAN, c.SUBSCRIBER_NO
from contract c
where c.application_id = ls_AppID
and c.OPERATOR_ID = to_number(to_char(sysdate,'YYYYMMDD'));

  cursor cur_r1 is 
select /*+ PARALLEL(c,8) */ c.BAN, c.SUBSCRIBER_NO, c.CONTRACT_TYPE
from contract c
where c.application_id = ls_AppID
and c.OPERATOR_ID = to_number(to_char(sysdate,'YYYYMMDD'));

  cursor cur_r2 is 
select /*+ PARALLEL(s,8) */ s.CUSTOMER_ID, s.SUBSCRIBER_NO, s.COMMIT_START_DATE, s.COMMIT_END_DATE,
s.COMMIT_REASON_CODE, s.COMMIT_ORIG_NO_MONTH
from subscriber s
where s.application_id = ls_AppID
and trunc(s.SYS_UPDATE_DATE) = trunc(sysdate);

PROCEDURE A_Main (input_ban           contract.ban%type,
                  input_subscriber_no contract.subscriber_no%type,
                  input_cnt_seq_no    contract.CNT_SEQ_NO%type) is
BEGIN
  begin

     update contract c
        set c.CONTRACT_TYPE = 'W',
            c.APPLICATION_ID = ls_AppID,
            c.DL_SERVICE_CODE = ls_SrvCD,
            c.OPERATOR_ID = to_number(to_char(sysdate,'YYYYMMDD'))
      where c.BAN = input_ban
        and c.SUBSCRIBER_NO = input_subscriber_no
        and c.CNT_SEQ_NO = input_cnt_seq_no
        and exists (select /*+ PARALLEL(sa1,8) */ sa1.*
              from service_agreement sa1, a_soc s1
              where sa1.ban = c.ban
                and sa1.subscriber_no = c.subscriber_no
                and sa1.SERVICE_TYPE = 'P'
                and sa1.EXPIRATION_DATE IS NULL
                and sa1.soc = s1.soc
                and s1.EXPIRATION_DATE IS NULL
                and s1.MYTHOS_CD = 'M'
                and s1.WAIVER_ELIGIBLE_CD = 'B');
     IF sql%rowcount = 1 THEN
        cup_count := cup_count +1;
        goto processing_done;
     ELSE
        if(cnu_count = 0) then
          dbms_output.put_line('-----------------------------------------------------');
          dbms_output.put_line('CONTRACT NOT UPDATED -SUBSCRIBER NOT ON ELIGIBLE PLAN');
          dbms_output.put_line('-----------------------------------------------------');
          dbms_output.put_line('BAN      |CTN       ');
          dbms_output.put_line('---------|----------');
        end if;
           dbms_output.put_line(input_ban||':'||input_subscriber_no);
           dbms_output.enable(NULL);
           cnu_count := cnu_count +1;
           goto processing_done;
     END IF;
  exception 
    when others then
      if(cer_count = 0) then
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('CONTRACT NOT UPDATED -- ERROR       ');
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('BAN      |CTN       |CONTRACT SEQ NO');
        dbms_output.put_line('---------|----------|---------------');
      end if;
      dbms_output.put_line(input_ban||':'||input_subscriber_no||':'||input_cnt_seq_no);
      dbms_output.enable(NULL);
      cer_count := cer_count +1;
      goto processing_done;
  end;
<<processing_done>>
  null;
END A_Main;

PROCEDURE A_Memo (input_ban           contract.ban%type,
                  input_subscriber_no contract.subscriber_no%type,
                  input_cnt_seq_no    contract.CNT_SEQ_NO%type) is
BEGIN
  begin

insert into memo	
values (input_ban,
        MEMO_1SQ.NEXTVAL,
        sysdate,
        sysdate,
        99999, 
        ls_AppID,
        ls_SrvCD, 
        null,  
        sysdate,
        'CSM', 
        input_subscriber_no,
        'Fix Contract Waiver',
        'Ban:'||input_ban||' CTN:'||input_subscriber_no||' Contract:'||input_cnt_seq_no||' Modification made to accurately reflect account terms.',
        null,  
        null,  
        null,  
        null,  
        null);
  exception
    when others then
      if(mer_count = 0) then
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('MEMO NOT GENERATED -- ERROR         ');
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('BAN      |CTN       |CONTRACT SEQ NO');
        dbms_output.put_line('---------|----------|---------------');
      end if;
      dbms_output.put_line(input_ban||':'||input_subscriber_no||':'||input_cnt_seq_no);
      dbms_output.enable(NULL);
      mer_count := mer_count +1;
      goto processing_done;
  end;
<<processing_done>>
  null;
END A_Memo;

PROCEDURE A_Sub (input_ban           contract.ban%type,
                 input_subscriber_no contract.subscriber_no%type) is
BEGIN
  begin

    select /*+ PARALLEL(c,8) */ c.BAN, c.SUBSCRIBER_NO, c.COMMIT_START_DATE,
           c.COMMIT_END_DATE, c.COMMIT_REASON_CD, c.COMMIT_ORIG_NO_MONTH
       INTO i_ban, i_subscriber_no, i_commit_start_date, i_commit_end_date, 
            i_commit_reason_cd, i_commit_orig_no_month
      from contract c
     where c.BAN = input_ban
       and c.SUBSCRIBER_NO = input_subscriber_no
       and nvl(c.CONTRACT_TYPE, ' ') != 'W'
       and c.VOID_IND = 'N'
       and c.CNT_SEQ_NO = (select /*+ PARALLEL(c2,8) */ max(c2.CNT_SEQ_NO)
                             from contract c2
                            where c2.BAN = input_ban
                              and c2.SUBSCRIBER_NO = input_subscriber_no
                              and nvl(c2.CONTRACT_TYPE, ' ') != 'W'
                              and c2.VOID_IND = 'N'
                         group by c2.BAN, c2.SUBSCRIBER_NO);

    IF sql%rowcount = 1 THEN
       update subscriber
          set sys_update_date = sysdate,
              application_id = ls_AppID,
              DL_SERVICE_CODE = ls_SrvCD,
              COMMIT_START_DATE = i_commit_start_date,
              COMMIT_END_DATE = i_commit_end_date,
              COMMIT_REASON_CODE = i_commit_reason_cd,
              COMMIT_ORIG_NO_MONTH = i_commit_orig_no_month
        where SUBSCRIBER_NO = i_subscriber_no 
          and CUSTOMER_ID = i_ban
          and nvl(COMMIT_START_DATE,TO_DATE('01011964','MMDDYYYY')) != nvl(i_commit_start_date,TO_DATE('01011964','MMDDYYYY'));

        IF sql%rowcount = 1 THEN
           sub_count := sub_count +1;
           goto processing_done;
        ELSE
           if(err_count = 0) then
             dbms_output.put_line('------------------------------------------');
             dbms_output.put_line('SUBSCRIBER NOT UPDATED -- DATES are equal ');
             dbms_output.put_line('------------------------------------------');
             dbms_output.put_line('BAN      |CTN       |COMMIT START DATE    ');
             dbms_output.put_line('---------|----------|---------------------');
           end if;
           dbms_output.put_line(i_ban||':'||i_subscriber_no||':'||i_commit_start_date);
           dbms_output.enable(NULL);
           err_count := err_count +1;
           goto processing_done;
        END IF;
    END IF;
  exception
    when others then
      if(ser_count = 0) then
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('SUBSCRIBERS NOT UPDATED -- ERROR    ');
        dbms_output.put_line('------------------------------------');
        dbms_output.put_line('BAN      |CTN                       ');
        dbms_output.put_line('---------|--------------------------');
      end if;
      dbms_output.put_line(input_ban||':'||input_subscriber_no);
      dbms_output.enable(NULL);
      ser_count := ser_count +1;
      goto processing_done;
  end;
<<processing_done>>
  null;
END A_Sub;

-------------------------------------------
-- MAIN BODY
-------------------------------------------
BEGIN

  open cur_t1;
  loop
      fetch cur_t1 into v_ban, v_subscriber_no, v_ctn_seq_no;
      exit when cur_t1%notfound;

      ll_count := ll_count + 1;

      A_Main (v_ban, 
              v_subscriber_no,
              v_ctn_seq_no);
  end loop; 
  close cur_t1;

  if ll_count > 0 then
     open cur_t2;
     loop
        fetch cur_t2 into v_ban, v_subscriber_no, v_ctn_seq_no;
        exit when cur_t2%notfound;

        mm_count := mm_count + 1;
        A_Memo (v_ban,
                 v_subscriber_no,
                 v_ctn_seq_no);
     end loop;
     close cur_t2;

     open cur_t3;
     loop
        fetch cur_t3 into v_ban, v_subscriber_no;
        exit when cur_t3%notfound;

        A_Sub (v_ban,
               v_subscriber_no);
     end loop;
     close cur_t3;
  end if;     
  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('UPDATE REPORTS follow ');
  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('CONTRACT ENTRIES UPDATED            ');
  dbms_output.put_line('------------------------------------');
  dbms_output.put_line('BAN      |CTN       |CONTRACT_TYPE  ');
  dbms_output.put_line('---------|----------|---------------');

     open cur_r1;
     loop
        fetch cur_r1 into r1_ban, r1_subscriber_no, r1_contract_type;
        exit when cur_r1%notfound;

        dbms_output.put_line(r1_ban||':'||r1_subscriber_no||':'||r1_contract_type);
        dbms_output.enable(NULL);
     end loop;
     close cur_r1;

  dbms_output.put_line('--------------------------------------------------------------');
  dbms_output.put_line('SUBSCRIBER ENTRIES UPDATED                                    ');
  dbms_output.put_line('--------------------------------------------------------------');
  dbms_output.put_line('BAN      |CTN       |START_DATE|END_DATE |RSN_CD|ORIG_NO_MONTH');
  dbms_output.put_line('---------|----------|----------|---------|------|-------------');
     open cur_r2;
     loop
        fetch cur_r2 into r2_ban, r2_subscriber_no, r2_start_date, r2_end_date, r2_reason_cd, r2_orig_no_month;
        exit when cur_r2%notfound;

        dbms_output.put_line(r2_ban||':'||r2_subscriber_no||':'||r2_start_date||' :'||r2_end_date||':'||r2_reason_cd||'   :'||r2_orig_no_month);
        dbms_output.enable(NULL);
     end loop;
     close cur_r2;

  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('SUMMARY REPORT:                                                           ');
  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('CONTRACT WAIVER entries Available for update:     = '||ll_count);
  dbms_output.put_line('CONTRACT WAIVER UPDATED: On Eligible Plan:        = '||cup_count);
  dbms_output.put_line('CONTRACT WAIVER NOT UPDATED: NOT on Eligible Plan = '||cnu_count);
  dbms_output.put_line('CONTRACT WAIVER NOT UPDATED: ERROR OCCURED:       = '||cer_count);
  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('MEMO Entries generated for contract waivers:      = '||mm_count);
  dbms_output.put_line('MEMO Entries NOT GENERATED: ERROR OCCURED:        = '||mer_count);
  dbms_output.put_line('--------------------------------------------------------------------------');
  dbms_output.put_line('SUBSCRIBER entries updated:                       = '||sub_count);
  dbms_output.put_line('SUBSCRIBER NOT UPDATED due to having same date:   = '||err_count);
  dbms_output.put_line('SUBSCRIBER NOT UPDATED: ERROR OCCURED:            = '||ser_count);
  dbms_output.put_line('--------------------------------------------------------------------------');

END;
/

Open in new window

rcc50886Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
are you sure the code above is what generated the error?


you have reported an error on line 381, but your entire script, including whitespace and sql*plus declarations is only 355 lines

the pl/sql block appears to have begin/exception/end blocks correctly defined, the content within them might be incorrect but I obviously can't test it to confirm
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
is the posted code the complete script or only part/pseudocode is provided here ?

Though it is syntactically fine to have it like this in each of your local procedures defined in the block:

      goto processing_done;
  end;
<<processing_done>>
null;
end;

we can just use the below instead of  the above lines. Unless you have some code in the outer block before the declared label statement which you do not want execute, there is no real meaning and value in using the labels there for jumping/braching.

null;
end;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.