MS Access launches oracle procedure which call dbms.submit_job (unexpected results) help

I have an MSAccess '97 application which contains a passthru query to an oracle database release 7.3.2.3.3 running pl/sql release 2.3.2.3.0. on a UNIX server.

The passthru reads like:
BEGIN extract.dispatch_extract('T5234', 'MONTHLY', 1999, 2); END;

In dispatch_extract, it submits this job like:

PROCEDURE dispatch_extract (process_id,

		client_process_message.process_id%type,
		type_ind	IN 	VARCHAR2,
                           run_year	IN	NUMBER,
                           run_mth		IN	NUMBER) IS

job  			BINARY_INTEGER;
proc_name		VARCHAR2(250);

Begin

proc_name := 'hod.gmi_interface_extract.start_monthly_extract(' || run_year || ',' || run_mth || ');';

DBMS_JOB.SUBMIT(Job,

                  proc_name,
                  sysdate + (1/1440),
                  NULL,
                  FALSE);

• return control to the user interface . . .

The job gets submitted successfully.

When the job runs in 1 minute in Oracle, the job fails unexpectedly for various reasons. Initially, the reason it failed was because it said a global const I was using was invalid. So I thru in some hardcode to get past that.

Now the error ". . .day of month must be between 1 and last day of month. . ." comes up. As you see, the month is 2, which is not invalid. If I can somehow correct that in the code, as I have previous errors like this, I suspect something new will come up further into the code.

It's as if the memory is corrupted.

Now, the kicker. . .If I call dispatch_extract from the SQL prompt (with the identical statement as in the passthru), the job gets submitted as expected and, in a minute, runs the submitted job perfectly.

I checked in DBA_JOBS to see what both jobs look like (depending if I use the SQL prompt or an Access pass thru) and each field looks identical.

Does anyone have any clue what wrong? Received on Mon Oct 18 1999 - 13:28:13 CDT