Oracle Database Job Failing on PL/SQL job type calls procedure with parameters

[Warren Tolentino]

i have this oracle db job that is using PL/SQL as job type and calls a procedure with parameters. this is failing and giving me output of

Task (100 - Create BulkLoad WIPPER File - Oracle) failed launch (java.sql.SQLException: ORA-06550: line 3, column 49:

PLS-00201: identifier 'TST1_' must be declared

ORA-06550: line 3, column 3:

PL/SQL: Statement ignored

)

Completed at 10/9/2014 6:00 AM

<SFS BulkLoad File PreFix.359> is a variable and has a value of 'TST1_'.
<SFS BulkLoad File Suffiix.360> is a variable and has a value of 'a'.

when i don't use a parameter and pass a value instead the job is working fine. please advise. thank you. - warren.

[Derrick Au]

Hi Warren,

Try adding a declare section, ie,

DECLARE

  myPrefix VARCHAR2 (5);

  mySuffix VARCHAR2 (1);

BEGIN

  myPrefix := pPrefix;

  mySuffix := pSuffix;

and then pass myPrefix and mySuffix variables as parameters to WRITE_TO_FILE.

BR,

Derrick Au

[Warren Tolentino]

thank you derrick. i tried your example and it did not work.

Task (500 - Create BulkLoad WIPPER File - Oracle) failed launch (java.sql.SQLException: ORA-06550: line 5, column 15:
PLS-00201: identifier 'TST1_' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 15:
PLS-00201: identifier 'A' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
)

Completed at 10/15/2014 11:22 AM

[Derrick Au]

Hi Warren,

Interesting, these are parameters and not identifiers.

In Parameters tab, let's try replacing variable <SFS BulkLoad File PreFix.359> with the actual value 'TST1_' and <SFS BulkLoad File Suffiix.360> with the actual value 'a'

[Warren Tolentino]

i tried and it did not work. so i tried wrapping a single quote to the actual value from the variable definition and that works.

[Derrick Au]

Hi Warren,

Try adding a declare section, ie,

DECLARE

  myPrefix VARCHAR2 (5);

  mySuffix VARCHAR2 (1);

BEGIN

  myPrefix := pPrefix;

  mySuffix := pSuffix;

and then pass myPrefix and mySuffix variables as parameters to WRITE_TO_FILE.

BR,

Derrick Au