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

Apparently somehow, somewhere in the passthru query the NLS_DATE_FORMAT settings are getting ignored and default settings (usually DD-MON-YY) are being applied.
This seems to be a problem of the Oracle ODBC driver (a post from last week was referring to this) and the Microsoft driver was not having this problem.

Hth,

--
Sybrand Bakker, Oracle DBA
<saneish_at_hotmail.com> wrote in message
news:380b5e7e.414620402_at_news01.pipe.nova.ca...
> 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 - 14:49:58 CDT