oracle - 由于 out 参数分配错误，dbms_scheduler 作业失败

Question

我正在从我正在执行第二个存储过程（具有 IN 和 OUT 参数）作为 dbms_scheduler 包中的 plsql_block 作业类型的过程创建一个一次性调度作业。现在，当我运行创建作业的主程序并调用第二个程序时，我收到以下错误并且作业失败：第 4 行，第 35 列：

> PLS-00363: expression '<null>' cannot be used as an assignment target
> ORA-06550: line 5, column 35: PLS-00363: expression '<null>' cannot be
> used as an assignment target ORA-06550: line 2, column 11: PL/SQL:

  


 -- Main Procedure

CREATE or replace PROCEDURE HBK_test_JOB 
( date_1 in date, date_2 in date,
                              O_RETURN_CD      OUT NUMBER,
                                      O_RETURN_MESSAGE OUT VARCHAR2) as
    
    LV_Error varchar2(100);
    
    begin
      
    begin
        O_RETURN_CD := 1;
        O_RETURN_MESSAGE := 'Success';
        
    insert  into test_trans_job
     values ( date_1, date_2, 'JKL');
     commit;
     exception
       when others then 
        LV_error := sqlerrm || sqlcode;
        
        O_RETURN_CD := 100;
        O_RETURN_MESSAGE := 'insert failed' || LV_error;
        
     end;
     
     update test_trans_job c
     set name = 'XYZ'
     where c.date1 = date_1
     and c.date2 = date_2;
     
    commit;
     
     exception
     when others then 
       LV_error := sqlerrm || sqlcode;
        
        O_RETURN_CD := 1001;
        O_RETURN_MESSAGE := 'failed'|| LV_error;
       
        end; ```
     
    
--JOB CALLING PROCEDURE
  ```  Create or replace procedure hbk_test_run_job ( date_1 in date, date_2 in date,
                                   OUT_REF_CUR_1 OUT SYS_REFCURSOR )
                                     as
        lv_run_id number;  
        LV_ERROR_MSSG varchar2(100);
        
                  
                   D1 date;
                   D2 date;
                    OUT_CD number;
                                   OUT_MSSG varchar2(500);
                 
     Begin
               SELECT MAX(H.RUN_ID)into LV_RUN_ID
                FROM SY_DSA_RUN_STATUS_HLSIL H;
                
                D1 := date_1;
                D2 := date_2;
               
                
                   DBMS_SCHEDULER.CREATE_JOB(
              JOB_NAME => 'TRANSACTION_DOWNLOAD_JOB_'||LV_RUN_ID,
              JOB_TYPE => 'PLSQL_BLOCK',
              JOB_ACTION =>        'begin
              hlsil.HBK_test_JOB('''||D1||''',
                                      '''||D2||''',
                                      '''||OUT_CD||''',
                                    '''||OUT_MSSG||''');                                                                  
                                      END;',
               NUMBER_OF_ARGUMENTS => 0,                        
              START_DATE => SYSTIMESTAMP + 1/1440,                        
              ENABLED => TRUE,
              AUTO_DROP => TRUE,
              COMMENTS => 'ONE TIME TRANS DWLD JOB RUN');
              
              OPEN OUT_REF_CUR_1 FOR 
              SELECT 'TRANSACTION DOWNLOAD JOB STARTED RUNNING IN THE BACKGROUND'
status
              FROM DUAL;
              
              EXCEPTION
                WHEN OTHERS THEN 
                  LV_ERROR_MSSG := SQLERRM(SQLCODE);
                  OPEN OUT_REF_CUR_1 FOR 
                  SELECT 'ERROR WHILE CREATING TRANS DOWNLOAD JOB' ||LV_ERROR_MSSG error
                   FROM DUAL;
              END;  ```     
    
          
      
         
     

标签： oracledbms-scheduler