I am attempting to call an Oracle procedure with multiple paramters, including 2 dates. The purpose of this procedure is to load a table from which I will produce a report. Before anyone asks, the proc is very complicated and already existed prior to my having to go this route. To redevelop in WebFOCUS would take a lot of cycles.

Anyway, I am using the following to connect:

  
ENGINE ORACLE SET CONNECTION_ATTRIBUTES ...
ENGINE ORACLE SET SERVER ...

ENGINE SQLORA
EX DBO.PROC_NAME '&FR_DATE', '&TO_DATE', 'Q2_2007'
;
END

where ... is the appropriate settings.

&FR_DATE and &TO_DATE are the date fields.

If the proc is run from within Oracle, the values of the two dates will look something like this:

01/01/2007 12:00:00 AM

....AND the proc works.

When attempting to run the proc from within WebFOCUS, my dates give me an error if I supply them with values OTHER THAN:

"01-01-2007"

The error I'm receiving is :

(FOC1400) SQLCODE IS 1843 (HEX: 00000733)
: ORA-01843: not a valid month
: ORA-06512: at line 1
L (FOC1405) SQL PREPARE ERROR.

The reason I ask this is that if I use the date format of 01-01-2007 the proc does functionally run. However, it doesn't do what it's supposed to do.

Has anyone encountered any trouble with issuing calls to Oracle procs using dates and have experienced any trouble similar to this?

Ken

Ken, this is probably not going to help, but have you tried passing the dates as yymd:

EX DBO.PROC_NAME '2007/01/01 12:00:00 AM', '2007/01/31 12:00:00 AM', 'Q2_2007'

It sounds like you have a formatting problem. Is there some reason why you use '/' in one date and '-' in the other?

When you are passing date strings like '01/01/2007 12:00:00 AM' or '01-01-2007', they are not dates. To compare them with a date, the Oracle proc has to convert them using the TO_DATE function. Part of the function is format. It looks to me that your proc wants date and time (all Oracle dates are date and time, if no time is stored, the time is midnight, 00:00:00.000 or as you have 12:00:00 AM)

You need to examine the Oracle proc and insure the your parameters are in the correct format, that includes the '/' and '-'.

jg is right your problem is with Oracle not accepting the dates as dates. If you can't change the Oracle proc try passing the values as an alpha string. I think the syntax would be:

-SET &FR_DATE1='TO_DATE(''&FR_DATE'',''MM/DD/YYYY'')';

Oracle is like wf in that it will allow date string assignment/comparison provided the string is in the accepted form for the site default eg MM/DD/YYYY HH:MM:SS am in the US.

As others correctly point out if not in the accepted form then TO_DATE must be used.

Hence your behaviour.

I am guessing your behaviour is because you have

WHERE FR_DATE <= arg_fr_date in your proc

instead of

WHERE FR_DATE <= TO_DATE[arg_fr_date,'MM/DD/YYYY'); in your proc


The solutions are

1. Pass the date in the expected format. Use dm to manipulate the date into the sites format.

2. Pass the conversion as well as PBrightwell's quite ingenious solution is doing. I don't know if this will work because it depends on how the compile of the stored proc is carried out but it is worth a try.

Otherwise reformat the datestring you pass to your Oracle sites default.