oracle job 一直是scheduled,How to use variable in a Oracle scheduled job?

The following script ran in Oracle SQL developer. It will insert two rows into a table. However, when I put this script into a job under the scheduler tab in Oracle SQL developer, no row was inserted. When created the job, I used sys.Default_job_class, type of job is PL/SQL block.

Can someone help me to revise the script, so it can be run in a job? Thank you

define batchNo='123';

insert into TABLE_NAME (col1, col2, col3, col4) select col1, col2, col3, col4 from TABLE2 WHERE BATCH_NO = '&batchNo';

commit;

# Answer 1

Change your code so it's a PL/SQL block:

DECLARE

batchNo VARCHAR2(3) := '123';

BEGIN

insert into TABLE_NAME (col1, col2, col3, col4)

select col1, col2, col3, col4

from TABLE2

WHERE BATCH_NO = batchNo;

commit;

END;

define and &varname are used in SQL*Plus, but are not valid PL/SQL.

# Answer 2

Your syntax with "&batchNo" is specific to SQL*Plus. It will not work with PL/SQL.

PL/SQL would need something like this:

DECLARE

l_batchno number := 123;

BEGIN

insert into TABLE_NAME (col1, col2, col3, col4)

select col1, col2, col3, col4 from TABLE2 WHERE BATCH_NO = l_batchno;

commit;

END;

# Answer 3

If you want to execute with parameter you must do:

sqlplus -s $USER/$PASSWD @ script.sql

Your SQL Script must contain:

INSERT INTO TABLE_NAME (col1, col2, col3, col4)

SELECT col1, col2, col3, col4 FROM TABLE2 WHERE BATCH_NO = '&1';

COMMIT;

If you want to use more than 1 parameter...

sqlplus -s $USER/$PASSWD @ script.sql ...

You must put into script.sql

DECLARE

v_FIRST DATATYPE;

v_SECOND DATATYPE;

v_N DATATYPE;

BEGIN

v_FIRST := '&1';

v_SECOND := '&2';

v_N := '&N';

END;