How to use NULL as an argument in a dbms_scheduler.create_job script?
852343Jun 8 2011 — edited Jun 10 2011Hello All,
We are trying to pass a null argument as a parameter to a PL/SQL program after CURRENT_TIMESTAMP. However, every attempt we try is producing errors. Why we are passing the null is another story. Does anyone know of the correct syntax, if there is any, to accomplish passing the null argument as a parameter to a PL/SQL program?
We'd like to continue running with this same script setup and have the passing of the null working.
This is what we've tried and directly below are the results. Any help so far would be appreciated!
begin
dbms_scheduler.create_job ( job_name => 'LOAD_CRF_TRANSACTION',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DW_CRF.PKG_LPS_CRF_LOAD_TRANS.sp_load_target(''LOAD_CRF'', ''LOAD_LOAN_TRAN'', CURRENT_TIMESTAMP, );
end;',
start_date => sysdate +1/24/59,
enabled => TRUE,
auto_drop => TRUE,
comments => 'LOAD LOAN TRANSACTION TABLE IN DW_CRF ' );
end;
/
ORA-06550: line ORA-06550: line 2, column 115:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-q
, column :
---------------------------------------------------------------------------
begin
dbms_scheduler.create_job ( job_name => 'LOAD_CRF_TRANSACTION',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DW_CRF.PKG_LPS_CRF_LOAD_TRANS.sp_load_target(''LOAD_CRF'', ''LOAD_LOAN_TRAN'', CURRENT_TIMESTAMP);
end;',
start_date => sysdate +1/24/59,
enabled => TRUE,
auto_drop => TRUE,
comments => 'LOAD LOAN TRANSACTION TABLE IN DW_CRF ' );
end;
/
ORA-06550: line ORA-06550: line 2, column 21:
PLS-00306: wrong number or types of arguments in call to 'SP_LOAD_TARGET'
ORA-06550: line 2, column 21:
PL/SQL: Statement ignored
, column :
---------------------------------------------------------------------------------
begin
dbms_scheduler.create_job ( job_name => 'LOAD_CRF_TRANSACTION',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DW_CRF.PKG_LPS_CRF_LOAD_TRANS.sp_load_target(''LOAD_CRF'', ''LOAD_LOAN_TRAN'', CURRENT_TIMESTAMP, NULL);
end;',
start_date => sysdate +1/24/59,
enabled => TRUE,
auto_drop => TRUE,
comments => 'LOAD LOAN TRANSACTION TABLE IN DW_CRF ' );
end;
/
ORA-20050: ORA-20050: Error Loading PKG_LPS_CRF_LOAD_TRANS Data
ORA-06512: at "DW_CRF.PKG_LPS_CRF_LOAD_TRANS", line 958
ORA-06512: at line 2
---------------------------------------------------------------------------------
begin
dbms_scheduler.create_job ( job_name => 'LOAD_CRF_TRANSACTION',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DW_CRF.PKG_LPS_CRF_LOAD_TRANS.sp_load_target(''LOAD_CRF'', ''LOAD_LOAN_TRAN'', CURRENT_TIMESTAMP, "NULL");
end;',
start_date => sysdate +1/24/59,
enabled => TRUE,
auto_drop => TRUE,
comments => 'LOAD LOAN TRANSACTION TABLE IN DW_CRF ' );
end;
/
ORA-01858: a non-numeric character was found where a numeric was expected