exception error on cursor update package 1

[diehippy]

Hi All,

I was wondering if someone could give me some advice on why my package keep failing on the exception that I have added to the end of two update statements. The error that I am getting is the following "PLS-00103: Encountered the symbol ""EXCEPTION"" when expecting one of the following: I do not understand why I am getting this error so if some could enlighten me, I will be most grateful. Here is the code:

CREATE OR REPLACE package sdc_ks_achieve_upload is

PROCEDURE sdc_upload_results;
PROCEDURE sdc_UPDATE_ETEST_RESULTS;


end;
/

CREATE OR REPLACE package body sdc_ks_achieve_upload IS

PROCEDURE sdc_upload_results IS
v_program_unit VARCHAR2(60) := 'sdc_ks_achieve_upload.sdc_upload_results';
n_error_code NUMBER(30);
v_error_message varchar2(150);


BEGIN

INSERT INTO sdc_keyskill_achievement_temp
(ocr_candidate_no, person_code, forename, surname, gender, date_of_birth, scheme_code, qual_code, scheme_title, keyskill_level, result, result_date, centre_no, dated_created)
SELECT
se.ocr_candidate_no,
p.person_code,
se.forename,
se.surname,
se.gender,
To_Date(se.date_of_birth, 'DD/MM/YYYY') date_of_birth,
se.scheme_code,
CASE WHEN se.scheme_code = '03794' AND se.unit_key_skills_level = 1 THEN '1000323X'
WHEN se.scheme_code = '03794' AND se.unit_key_skills_level = 2 THEN '10003071'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 1 THEN '10003265'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 2 THEN '10003253'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 3 THEN '10003241'
WHEN se.scheme_code = '03793' AND se.unit_key_skills_level = 1 THEN '10003289'
WHEN se.scheme_code = '03793' AND se.unit_key_skills_level = 2 THEN '10003290' ELSE null END qual_code,
se.scheme_title,
se.unit_key_skills_level,
se.result,
se.result_date,
se.centreno,
Trunc(SYSDATE)
FROM sdc_etest_ach_upload se,
people p
WHERE
To_Char(se.ocr_candidate_no) = p.rsa_number

;

COMMIT;

EXCEPTION
WHEN others THEN
rollback;
n_error_code := SQLCODE;
v_error_message := SUBSTR('Insert into SDC_KEYSKILL_ACHIEVEMENT_TEMP = ' || SQLERRM(n_error_code), 1,150);
alex_error_manager.log_error_raise_no_app_error(v_program_unit, n_error_code, v_error_message, null);


END sdc_upload_results;


PROCEDURE sdc_UPDATE_ETEST_RESULTS IS
v_program_unit VARCHAR2(60) := 'sdc_ks_achieve_upload.sdc_update_etest_results';
n_error_code NUMBER(30);
v_error_message varchar2(150);




CURSOR cur_etest_res_upload IS SELECT
distinct
e.person_code,
e.forename,
e.surname,
e.date_of_birth,
e.unit_instance_code,
e.qual_code,
e.cuio_id,
e.calocc_code,
e.progress_status,
e.exam_occurrence_id,
e.board_code,
e.exam_code,
e.board_occurrence,
e.first_grade,
e.passed,
e.updated_by,
e.updated_date,
kt.person_code k_person_code,
kt.forename k_forename,
kt.surname k_surname,
kt.date_of_birth k_date_of_birth,
kt.ocr_candidate_no,
kt.scheme_code,
kt.scheme_title,
kt.keyskill_level,
kt.qual_code k_qual_code,
kt.result,
kt.result_date
from
(SELECT
distinct
p.person_code,
p.forename,
p.surname,
p.date_of_birth,
pu.unit_instance_code,
qa.qual_code,
pu.uio_id cuio_id,
pu.calocc_code,
pu.progress_status,
ee.exam_occurrence_id,
ee.board_code,
ee.exam_code,
ee.board_occurrence,
ee.first_grade,
ee.passed,
ee.updated_by,
ee.updated_date
FROM
people p,
people_units pu,
people_units_special pus,
unit_instance_occurrences uio,
uio_qual_aims qa,
exam_occurrence_uios eou,
exam_occurrences eo,
exam_entries ee
WHERE
p.person_code = pu.person_code
AND pu.uio_id = uio.uio_id
AND uio.uio_id = qa.uio_id
AND qa.default_qa = 'Y'
AND pu.id = pus.people_units_id
AND pus.funding_year = (SELECT funding_year FROM funding_years WHERE SYSDATE BETWEEN start_date AND end_date AND fes_active = 'Y')
AND pu.unit_type = 'R'
AND pu.calocc_code = (SELECT occurrence_code FROM calendar_occurrences WHERE SYSDATE BETWEEN start_date AND end_date AND occurrence_code NOT LIKE '%X')
AND pu.progress_status = 'A'
AND uio.fes_user_5 = 'KSF'
AND ee.board_code = 'RSA'
AND ee.board_occurrence = 'ETEST0910'
AND uio.uio_id = eou.uio_id
AND eou.exam_occurrence_id = eo.id
AND pu.person_code = ee.person_code
AND eo.exam_code = ee.exam_code
AND eo.board_code = ee.board_code
AND eo.board_occurrence = ee.board_occurrence

ORDER BY 3) e,
(SELECT
skt.person_code,
skt.forename,
skt.surname,
skt.date_of_birth,
skt.ocr_candidate_no,
skt.scheme_code,
skt.scheme_title,
skt.keyskill_level,
skt.qual_code,
Decode(skt.result, 'Pass','PA', 'FL') result,
skt.result_date
FROM
sdc_keyskill_achievement_temp skt
WHERE
skt.dated_created = Trunc(SYSDATE)
)kt
WHERE
kt.person_code = e.person_code
AND kt.qual_code = e.qual_code;


BEGIN

FOR rec_etest_res_upload IN cur_etest_res_upload LOOP

update exam_entries ee
SET ee.first_grade = rec_etest_res_upload.result, ee.updated_by = 'FES', ee.updated_date = SYSDATE
WHERE
ee.exam_code = rec_etest_res_upload.exam_code
AND ee.person_code = rec_etest_res_upload.person_code
AND ee.board_code = rec_etest_res_upload.board_code
AND (ee.first_grade = 'FL' OR ee.first_grade is NULL)
AND ee.board_occurrence = rec_etest_res_upload.board_occurrence ;




UPDATE sdc_keyskill_achievement_temp skt1
set (skt1.updated_date, skt1.successfully_updated) =
(SELECT
distinct SYSDATE,
CASE WHEN ee1.updated_by = 'FES' AND Trunc(ee1.updated_date) = Trunc(SYSDATE) THEN 'Y' ELSE 'N' END
FROM exam_entries ee1
WHERE
ee1.exam_code = rec_etest_res_upload.exam_code
AND ee1.person_code = rec_etest_res_upload.person_code
AND ee1.board_code = rec_etest_res_upload.board_code
AND ee1.board_occurrence = rec_etest_res_upload.board_occurrence )
WHERE
EXISTS
(SELECT
1
FROM exam_entries ee2
WHERE
ee2.exam_code = rec_etest_res_upload.exam_code
AND ee2.person_code = rec_etest_res_upload.person_code
AND ee2.board_code = rec_etest_res_upload.board_code
AND ee2.board_occurrence = rec_etest_res_upload.board_occurrence
AND ee2.person_code = skt1.person_code) ;
COMMIT;



EXCEPTION WHEN OTHERS THEN ROLLBACK
n_error_code := SQLCODE;
v_error_message := SUBSTR(v_program_unit || 'Failed Update person/qual/result_date' ||skt1.person_code|| '/' ||skt1.qual_code|| '/' || skt1.result_date ||' '||SQLERRM(n_error_code), 1,150);
alex_error_manager.log_error_raise_no_app_error(v_program_unit, n_error_code, v_error_message, null);

END LOOP;
END;


END sdc_UPDATE_ETEST_RESULTS;

END;
/


Many Thanks in advance

diehippy

 

[carp]

It looks like you are missing a right parenthesis after the subquery in your first insert statement.

 

[Dagon]

You can't have the EXCEPTION inside the LOOP..END LOOP. If you want to do that, you will need to put everything in a sub-loop e.g.

FOR rec_etest_res_upload IN cur_etest_res_upload LOOP

begin
                                    update exam_entries ee
                                    SET ee.first_grade = rec_etest_res_upload.result, ee.updated_by = 'FES', ee.updated_date = SYSDATE
                                    WHERE
                                    ee.exam_code = rec_etest_res_upload.exam_code
                                    AND ee.person_code = rec_etest_res_upload.person_code
                                    AND ee.board_code = rec_etest_res_upload.board_code
                                    AND (ee.first_grade = 'FL' OR ee.first_grade is NULL)
                                    AND ee.board_occurrence = rec_etest_res_upload.board_occurrence ;




                                        UPDATE sdc_keyskill_achievement_temp skt1
                                        set (skt1.updated_date, skt1.successfully_updated) =
                                       (SELECT
                                        distinct SYSDATE,
                                        CASE WHEN ee1.updated_by = 'FES' AND Trunc(ee1.updated_date) = Trunc(SYSDATE) THEN 'Y' ELSE 'N' END
                                        FROM exam_entries ee1
                                        WHERE
                                        ee1.exam_code = rec_etest_res_upload.exam_code
                                        AND ee1.person_code = rec_etest_res_upload.person_code
                                        AND ee1.board_code = rec_etest_res_upload.board_code
                                        AND ee1.board_occurrence = rec_etest_res_upload.board_occurrence )
                                        WHERE
                                        EXISTS
                                       (SELECT
                                        1
                                        FROM exam_entries ee2
                                        WHERE
                                        ee2.exam_code = rec_etest_res_upload.exam_code
                                        AND ee2.person_code = rec_etest_res_upload.person_code
                                        AND ee2.board_code = rec_etest_res_upload.board_code
                                        AND ee2.board_occurrence = rec_etest_res_upload.board_occurrence
                                        AND ee2.person_code = skt1.person_code) ;
                                        COMMIT;


               
                                        EXCEPTION WHEN OTHERS THEN ROLLBACK
                                        n_error_code := SQLCODE;
                                        v_error_message := SUBSTR(v_program_unit || 'Failed Update person/qual/result_date' ||skt1.person_code|| '/' ||skt1.qual_code|| '/' || skt1.result_date ||' '||SQLERRM(n_error_code), 1,150);
                                        alex_error_manager.log_error_raise_no_app_error(v_program_unit, n_error_code, v_error_message, null);

END;
  END LOOP;
 END;

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

Sub-block is what I meant to say there.

For Oracle-related work, contact me through Linked-In.

 

[diehippy]

Hi Dagon,

Many Thanks for your advice, I have now chaged my package accordingly and it all works fine now

Many Thanks for your help again

Diehippy