DBLookup to Stored Procedure Returns Confusing error

[kashush]

The map uses dblookup to call a stored procedure. The .dbl file shows the correct number of parameters in the correct format but it also contains, "Returned status: (-1091) An_output_date_was_of_an_incorrect_format"


The procedure works. It runs from Toad correctly. It has one output parameter.

I put a return statement before all the code in the stored procedure. I got the same error from Mercator.

I changed the map to only pass nulls and a ? for the one output parameter. I got the same error.

 

[eyetry]

how is your date field formated in the tree verses you sp?

 

[kashush]

OK, I found that I made the stored procedure input parameter a text field instead of a date field. Then I passed the date as CCYYMMDD. Inside the parameter I used the SQL function TO_DATE(PARM1, 'RRRRMMDD') to convert the text to date. I didn't want to spend a huge amount of time figuring out how to pass a value to a date input parameter. I am already aware that the natural was to format a date in Oracle is dd-mon-yyyy hh24:mm:ss but using FROMDATETIME and putting the value in that format didn't work.