variable v_status VARCHAR2;
variable v_errortext VARCHAR2;

exec ecoursework.insert_exm_additional_samples('crx360',2009, 'MAY','026753','ENGLISH A1','ENGLISH A1','HL','ENGLISH','INTERNAL ASSESSMENT (ORAL)',:v_status,:v_errortext);
print v_status;
print v_errortext;

PROCEDURE insert_exm_additional_samples( v_candidate_id_list ibis.candidate_component_reg.candidate%type,
                                         v_year ibis.candidate_component_reg.year%type,
                                         v_month ibis.candidate_component_reg.month%type,
                                         v_examiner_code ibis.examiner_moderator_allocation.examiner_code%type DEFAULT '',
                                         v_subject ibis.candidate_component_reg.subject%type,
                                         v_subject_option ibis.candidate_component_reg.subject_option%type DEFAULT '',
                                         v_lvl ibis.candidate_component_reg.lvl%type DEFAULT '',
                                         v_language ibis.candidate_component_reg.language%type DEFAULT '',
                                         v_component ibis.candidate_component_reg.component%type DEFAULT '',
                                         v_status    OUT VARCHAR2,
                                         v_errortext OUT VARCHAR2)												  
AS
     
BEGIN

  v_status := 'SUCCESS';
  v_errortext := 'SUCCESS';

        INSERT INTO ibis.exam_sample_mark_additional(year,month,nolang_code,examiner_code,moderator_code,candidate,session_id) 
        SELECT 
            ccr.split_session_year as year, 
            ccr.split_session_month as month, 
            ep.no_lang_code, 
            ema.examiner_code,
            ema.moderator_code,
            ccr.candidate as candidate_code,
            get_cand_session_number(ccr.split_session_year,ccr.split_session_month,ccr.candidate) as candidate_session_number
         FROM 
            ibis.candidate_component_reg ccr 
        INNER JOIN ibis.candidate_examiner_allocation cea
           ON ccr.split_session_year= cea.year
          AND ccr.split_session_month=cea.month
          AND ccr.paper_code= cea.paper_code
          AND ccr.candidate	=	 cea.candidate 
          AND ccr.assessment_school	=	 cea.assessment_school
          AND cea.examiner_code  = v_examiner_code
        INNER JOIN ibis.examiner_moderator_allocation ema 
           ON ccr.split_session_year =ema.year 
          AND ccr.split_session_month =ema.month 
          AND ema.examiner_code  = cea.examiner_code 
        INNER JOIN ibis.examination_paper ep 
           ON ep.year= ccr.split_session_year 
          AND ep.month = ccr.split_session_month 
          AND ep.paper_code  = ccr.paper_code
        WHERE    
              ccr.split_session_year =v_year 
          AND ccr.split_session_month =v_month
          AND ccr.subject=v_subject
          AND ccr.subject_option=nvl(v_subject_option,ccr.subject_option)
          AND ccr.language=nvl(v_language, ccr.language)
          AND ccr.component=v_component
          AND ccr.lvl=v_lvl
          AND ccr.candidate IN (SELECT * FROM TABLE(SPLIT(v_candidate_id_list)));
       
       
  
        
        COMMIT;
  
	    
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
              ROLLBACK;
              v_status := 'FAILED';
              v_errortext := 'No data found: '||sqlerrm; 
          WHEN OTHERS THEN
              ROLLBACK;
              v_status := 'FAILED';
              v_errortext := 'Others: '||sqlerrm;

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 3212494293                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                             
--------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                         
| Id  | Operation                          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                         
|   0 | SELECT STATEMENT                   |                               |     1 |   343 |  1961   (5)| 00:00:30 |                                                                                                                                                                                         
|*  1 |  HASH JOIN SEMI                    |                               |     1 |   343 |  1961   (5)| 00:00:30 |                                                                                                                                                                                         
|   2 |   NESTED LOOPS                     |                               |     1 |   341 |  1949   (5)| 00:00:30 |                                                                                                                                                                                         
|   3 |    NESTED LOOPS                    |                               |     1 |   300 |  1947   (5)| 00:00:30 |                                                                                                                                                                                         
|   4 |     NESTED LOOPS                   |                               |     1 |    72 |  1944   (5)| 00:00:30 |                                                                                                                                                                                         
|   5 |      TABLE ACCESS BY INDEX ROWID   | CANDIDATE_EXAMINER_ALLOCATION |     1 |    49 |  1942   (5)| 00:00:30 |                                                                                                                                                                                         
|*  6 |       INDEX RANGE SCAN             | RFL                           |     1 |       |  1941   (5)| 00:00:30 |                                                                                                                                                                                         
|*  7 |      INDEX RANGE SCAN              | PK_EXAMINER_MODERATOR_ALLOC   |     1 |    23 |     2   (0)| 00:00:01 |                                                                                                                                                                                         
|*  8 |     TABLE ACCESS BY INDEX ROWID    | CANDIDATE_COMPONENT_REG       |     1 |   228 |     3   (0)| 00:00:01 |                                                                                                                                                                                         
|*  9 |      INDEX RANGE SCAN              | SPLIT_SESSION_CANDIDATE       |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                         
|  10 |    TABLE ACCESS BY INDEX ROWID     | EXAMINATION_PAPER             |     1 |    41 |     2   (0)| 00:00:01 |                                                                                                                                                                                         
|* 11 |     INDEX UNIQUE SCAN              | EXAMINATION_PAPER_PK          |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                         
|  12 |   COLLECTION ITERATOR PICKLER FETCH| SPLIT                         |       |       |            |          |                                                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                             
   1 - access("CCR"."CANDIDATE"=VALUE(KOKBF$))                                                                                                                                                                                                                                                               
   6 - access("CEA"."YEAR"=2009 AND "CEA"."MONTH"='MAY')                                                                                                                                                                                                                                                     
       filter(TO_NUMBER("CEA"."EXAMINER_CODE")=026753)                                                                                                                                                                                                                                                       
   7 - access("EMA"."YEAR"=2009 AND "EMA"."MONTH"='MAY' AND "EMA"."EXAMINER_CODE"="CEA"."EXAMINER_CODE")                                                                                                                                                                                                     
   8 - filter("CCR"."SUBJECT"='ENGLISH A1' AND "CCR"."COMPONENT"='INTERNAL ASSESSMENT (ORAL)' AND                                                                                                                                                                                                            
              "CCR"."LVL"='HL' AND "CCR"."SUBJECT_OPTION"=NVL('ENGLISH A1',"CCR"."SUBJECT_OPTION") AND                                                                                                                                                                                                       
              "CCR"."LANGUAGE"=NVL('ENGLISH',"CCR"."LANGUAGE") AND "CCR"."ASSESSMENT_SCHOOL"="CEA"."ASSESSMENT_SCHOOL")                                                                                                                                                                                      
   9 - access("CCR"."CANDIDATE"="CEA"."CANDIDATE" AND "CCR"."PAPER_CODE"="CEA"."PAPER_CODE" AND                                                                                                                                                                                                              
              "CCR"."SPLIT_SESSION_YEAR"=2009 AND "CCR"."SPLIT_SESSION_MONTH"='MAY')                                                                                                                                                                                                                         
  11 - access("EP"."YEAR"=2009 AND "EP"."MONTH"='MAY' AND "EP"."PAPER_CODE"="CCR"."PAPER_CODE")                                                                                                                                                                                                              

33 rows selected