Re: perplexing problem....

In article <KJOp7.2118$9j.667039_at_news1.telusplanet.net>, "Glen says...
>
>Thanks
>
>I should have been clearer, the 7.2.3 is running on AIX and the 7.3.3 is
>running on NT4 sp5, the client apps are all on 2000
>
>I'll check in to the nls_date_format and change it.
>

I think it would be better to be EXPLICIT in the code -- don't rely on the nls_date_format (suppose some other application does and their format is different -- best to be explicit)

first -- you when I see this:

"WBS             b0c-91c ENTER SQLExecDirect
  HSTMT               0AA51930
UCHAR *             0x0012ECE8 [      -3] "begin PROCEDURE('01-Sep-2001',
'15-Sep-2001', 15); end;\ 0"
  SDWORD                    -3

I just "shudder". you are NOT using bind variables which is perhaps the single most important things ALL applications should do. That statement should be:

begin procedure( :x, :y, :z ); end;

with the strings and numbers bound to x, y, z. Your system will need tons less ram and lots less CPU.

Next, it should really be:

begin procedure( to_date(:x,'dd-mon-yyyy'), to_date(:y,'dd-mon-yyyy'), :z ); end;

that way, regardless of the client settings, YOUR date format will be used.
>
>>
>> First of all: please don't post in HTML. Many newsreaders don't know how
>to
>> deal with it.
>> Secondly: your *real* problem is you are running 7.3.3 and 7.2.3 on
>> platforms (win2k) for which it was *never* certified
>> Thirdly : evidently the problem is the setting of NLS_DATE_FORMAT which is
>> by default DD-MON-YY, and as you are not using the correct format mask in
>> your procedure (ie you are relying on client defaults which you should
>> *never ever* do), you get this problem.
>>
>> IMO, your problem is not perplexing at all.
>>
>> Hth,
>>
>> Sybrand Bakker, Senior Oracle DBA
>>
>>
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp