rcc50886
asked on
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
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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