Skip to Main Content
  • Questions

  • Random Oracle error from Java method How to Debug

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 22, 2010 - 12:08 am UTC

Last updated: September 21, 2010 - 3:32 pm UTC

Version: 9208

Viewed 1000+ times

You Asked

Hi Tom,
1) How to debug :
We have been struggling with this issue for a while.

3 Tier application using weblogic and 9208 database.

One of the Java method gives ORA-01858: a non-numeric character was found where a numeric was expected
randomly without consistency.

Method getUserDate has been running fine all these years

How to debug this issue.

From the server trace file two values of bind variables are passed to be statement.

SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1

DD-MM-YYYY and Test.

running it from sqlplus does not give any error.

Can it be related to connection pooling ? Could it be related to jdbc driver. How to find out ? It is randmly generated. But once it happens it keeps happening quite a few times consistently and then stops.


2) Is the sql statement incorrect causing the error randomly


SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1

Because I see it is using to_date on a Date , shouldnt it be To_char and then apply to_date.
But if it is already a Date column why even apply to_Date with a format mask, dont we do that to to_char ? or to explicit date strings and not Date columns retrieved from the database


Error from event 10046 trace file:

Please See the break/reset line though error ora- 01858 is not shown in trace

PARSING IN CURSOR #11 len=124 dep=0 uid=527 oct=3 lid=527 tim=7497294765624 hv=2539751018 ad='ed21e1e8'
SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1
END OF STMT
PARSE #11:c=10000,e=12983,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=7497294765619
BINDS #11:
bind 0: dty=1 mxl=32(10) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32 offset=0
bfp=9fffffffdfc95da8 bln=32 avl=10 flg=09
value="DD-MM-YYYY"
bind 1: dty=1 mxl=32(10) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32 offset=0
bfp=9fffffffdfee5350 bln=32 avl=10 flg=09
value="DD-MM-YYYY"
bind 2: dty=1 mxl=32(27) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=32 offset=0
bfp=9fffffffdf8868f8 bln=32 avl=09 flg=05
value="Test"
EXEC #11:c=0,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=7497294766373
WAIT #11: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0
WAIT #11: nam='SQL*Net message from client' ela= 824 p1=1952673792 p2=1 p3=0
FETCH #11:c=0,e=101,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=7497294767415
WAIT #11: nam='latch free' ela= 1903 p1=-4611686018103722008 p2=156 p3=1
WAIT #11: nam='SQL*Net break/reset to client' ela= 2 p1=1952673792 p2=1 p3=0
WAIT #11: nam='SQL*Net break/reset to client' ela= 789 p1=1952673792 p2=0 p3=0
WAIT #11: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0
WAIT #11: nam='SQL*Net message from client' ela= 1090 p1=1952673792 p2=1 p3=0
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 2378863 p1=1952673792 p2=1 p3=0
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1952673792 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 1892 p1=1952673792 p2=1 p3=0
XCTEND rlbk=0, rd_only=1
STAT #8 id=1 cnt=1 pid=0 pos=1 obj=75861 op='TABLE ACCESS BY INDEX ROWID OBJ#(75861) '
STAT #8 id=2 cnt=1 pid=1 pos=1 obj=279729 op='INDEX UNIQUE SCAN OBJ#(279729) '
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE '
STAT #9 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS '
STAT #9 id=3 cnt=0 pid=2 pos=1 obj=0 op='HASH JOIN '
STAT #9 id=4 cnt=0 pid=3 pos=1 obj=0 op='HASH JOIN '
STAT #9 id=5 cnt=10 pid=4 pos=1 obj=0 op='INLIST ITERATOR '
STAT #9 id=6 cnt=10 pid=5 pos=1 obj=75870 op='TABLE ACCESS BY INDEX ROWID OBJ#(75870) '
STAT #9 id=7 cnt=10 pid=6 pos=1 obj=279754 op='INDEX RANGE SCAN OBJ#(279754) '
STAT #9 id=8 cnt=8 pid=4 pos=2 obj=75871 op='TABLE ACCESS FULL OBJ#(75871) '
STAT #9 id=9 cnt=0 pid=3 pos=2 obj=75863 op='TABLE ACCESS FULL OBJ#(75863) '
STAT #9 id=10 cnt=0 pid=2 pos=2 obj=279723 op='INDEX UNIQUE SCAN OBJ#(279723) '
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS '
STAT #10 id=2 cnt=22 pid=1 pos=1 obj=0 op='HASH JOIN '
STAT #10 id=3 cnt=33 pid=2 pos=1 obj=75889 op='TABLE ACCESS BY INDEX ROWID OBJ#(75889) '
STAT #10 id=4 cnt=33 pid=3 pos=1 obj=279829 op='INDEX RANGE SCAN OBJ#(279829) '
STAT #10 id=5 cnt=407 pid=2 pos=2 obj=75940 op='TABLE ACCESS FULL OBJ#(75940) '
STAT #10 id=6 cnt=1 pid=1 pos=2 obj=282238 op='INDEX UNIQUE SCAN OBJ#(282238) '
STAT #11 id=1 cnt=1 pid=0 pos=1 obj=75977 op='TABLE ACCESS BY INDEX ROWID OBJ#(75977) '
STAT #11 id=2 cnt=1 pid=1 pos=1 obj=282222 op='INDEX UNIQUE SCAN OBJ#(282222) '
STAT #12 id=1 cnt=1 pid=0 pos=1 obj=75920 op='TABLE ACCESS BY INDEX ROWID OBJ#(75920) '
STAT #12 id=2 cnt=1 pid=1 pos=1 obj=280278 op='INDEX UNIQUE SCAN OBJ#(280278) '
STAT #27 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT UNIQUE '
STAT #27 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS '
STAT #27 id=3 cnt=0 pid=2 pos=1 obj=0 op='HASH JOIN '
STAT #27 id=4 cnt=0 pid=3 pos=1 obj=0 op='HASH JOIN '
STAT #27 id=5 cnt=10 pid=4 pos=1 obj=0 op='INLIST ITERATOR '
STAT #27 id=6 cnt=10 pid=5 pos=1 obj=75870 op='TABLE ACCESS BY INDEX ROWID OBJ#(75870) '
STAT #27 id=7 cnt=10 pid=6 pos=1 obj=279754 op='INDEX RANGE SCAN OBJ#(279754) '
STAT #27 id=8 cnt=8 pid=4 pos=2 obj=75871 op='TABLE ACCESS FULL OBJ#(75871) '
STAT #27 id=9 cnt=0 pid=3 pos=2 obj=75863 op='TABLE ACCESS FULL OBJ#(75863) '
STAT #27 id=10 cnt=0 pid=2 pos=2 obj=279723 op='INDEX UNIQUE SCAN OBJ#(279723) '
STAT #28 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT UNIQUE '
STAT #28 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS '
STAT #28 id=3 cnt=2 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #28 id=4 cnt=20066 pid=3 pos=1 obj=0 op='HASH JOIN '
STAT #28 id=5 cnt=814 pid=4 pos=1 obj=75940 op='TABLE ACCESS FULL OBJ#(75940) '
STAT #28 id=6 cnt=37460 pid=4 pos=2 obj=279830 op='INDEX FAST FULL SCAN OBJ#(279830) '
STAT #28 id=7 cnt=2 pid=3 pos=2 obj=282238 op='INDEX UNIQUE SCAN OBJ#(282238) '
STAT #28 id=8 cnt=2 pid=2 pos=2 obj=75920 op='TABLE ACCESS BY INDEX ROWID OBJ#(75920) '
STAT #28 id=9 cnt=2 pid=8 pos=1 obj=280278 op='INDEX UNIQUE SCAN OBJ#(280278) '
STAT #29 id=1 cnt=1 pid=0 pos=1 obj=75977 op='TABLE ACCESS BY INDEX ROWID OBJ#(75977) '
STAT #29 id=2 cnt=1 pid=1 pos=1 obj=282222 op='INDEX UNIQUE SCAN OBJ#(282222) '
STAT #29 id=3 cnt=1 pid=1 pos=2 obj=0 op='SORT UNIQUE NOSORT '
STAT #29 id=4 cnt=8544 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #29 id=5 cnt=8544 pid=4 pos=1 obj=0 op='MERGE JOIN CARTESIAN '
STAT #29 id=6 cnt=1 pid=5 pos=1 obj=0 op='NESTED LOOPS '
STAT #29 id=7 cnt=1 pid=6 pos=1 obj=282225 op='INDEX UNIQUE SCAN OBJ#(282225) '
STAT #29 id=8 cnt=1 pid=6 pos=2 obj=280254 op='INDEX RANGE SCAN OBJ#(280254) '
STAT #29 id=9 cnt=1 pid=8 pos=1 obj=75977 op='TABLE ACCESS BY INDEX ROWID OBJ#(75977) '
STAT #29 id=10 cnt=1 pid=9 pos=1 obj=282222 op='INDEX UNIQUE SCAN OBJ#(282222) '
STAT #29 id=11 cnt=8544 pid=5 pos=2 obj=0 op='BUFFER SORT '
STAT #29 id=12 cnt=8544 pid=11 pos=1 obj=282220 op='INDEX FAST FULL SCAN OBJ#(282220) '
STAT #29 id=13 cnt=8544 pid=4 pos=2 obj=280616 op='INDEX UNIQUE SCAN OBJ#(280616) '
STAT #30 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS '
STAT #30 id=2 cnt=41 pid=1 pos=1 obj=0 op='HASH JOIN '
STAT #30 id=3 cnt=407 pid=2 pos=1 obj=75940 op='TABLE ACCESS FULL OBJ#(75940) '
STAT #30 id=4 cnt=67 pid=2 pos=2 obj=279830 op='INDEX FAST FULL SCAN OBJ#(279830) '
STAT #30 id=5 cnt=0 pid=1 pos=2 obj=282238 op='INDEX UNIQUE SCAN OBJ#(282238) '

Java Code


public static int getUserDate(DbConnectionManager dbcm,
String UsrName, int ID) throws ServerException {

MethodDescriptor descriptor =
new MethodDescriptor("getUserDate", new Object[] {
UsrName, String.valueOf(ID) });
if (log.isDebugEnabled()) {
log.debug(descriptor);
}

final String userMessage = "Could not get the password expiration";
int expirationDays = 0;
PreparedStatement stmtPasswordExpirationDays = null;
ResultSet rsPasswordExpirationDays = null;
PreparedStatement stmtElapsedDays = null;
ResultSet rsElapsedDays = null;

try {
String schema = dbcm.getSchemaName(DbConnectionManager.DB_MAIN,
BigInteger.ZERO);


StringBuffer sql = new StringBuffer("SELECT NUMBER_VAL FROM ");
sql.append(schema);
sql.append(".CODETAB WHERE ID = ?");
stmtPasswordExpirationDays = dbcm.prepareStatement(
DbConnectionManager.DB_MAIN, BigInteger.ZERO, sql.toString());
stmtPasswordExpirationDays.setBigDecimal(1,
BigDecimal.valueOf(ID));
rsPasswordExpirationDays =
stmtPasswordExpirationDays.executeQuery();

if (rsPasswordExpirationDays.next()) {
// number of days...
expirationDays = rsPasswordExpirationDays.getInt("NUMBER_VAL");
if (log.isDebugEnabled()) {
log.debug("expirationDays=" + expirationDays);
}
if (expirationDays == -1) {
return expirationDays;
}
}

double timePassed = 1.1;
sql = new StringBuffer("SELECT TO_DATE(SYSDATE,'DD-MM-YYYY')");
sql.append(" - TO_DATE(HIREDATE,'DD-MM-YYYY') AS ELAPSED FROM ");
sql.append(schema);
sql.append(".EMP WHERE ENAME = ?");
stmtElapsedDays = dbcm.prepareStatement(
DbConnectionManager.DB_MAIN, BigInteger.ZERO, sql.toString());
stmtElapsedDays.setString(1, UsrName);
rsElapsedDays = stmtElapsedDays.executeQuery();

if (rsElapsedDays.next()) {
// number of days...
timePassed = rsElapsedDays.getDouble("ELAPSED");
} else {
throw new ServerException(
userMessage,
"Problems ."
+ descriptor.toString());
}

// time passed is returned in days...
if (timePassed > expirationDays) {
expirationDays = 0;
} else {
Double d = new Double(timePassed);
expirationDays = expirationDays - d.intValue();
}
} catch (SQLException sqle) {
throw new ServerException(userMessage, descriptor.toString(), sqle);
} finally {
dbcm.closeQuietly(rsPasswordExpirationDays);
dbcm.closeQuietly(rsElapsedDays);
dbcm.closeQuietly(stmtPasswordExpirationDays);
dbcm.closeQuietly(stmtElapsedDays);
}
return expirationDays;
}
Error from application server log:
com.pharmanet.server.common.ServerException: getUserDate(Test,7098608)
at com.pharmanet.server.common.UserDAO.getUserDate(UserDAO.java:1198)
at jsp_servlet.__pwdexprdays._jspService(__pwdexprdays.java:177)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:225)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:127)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:219)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:165)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3153)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:1973)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:1834)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1310)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:207)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:179)
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:484)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:982)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:100)
at com.pharmanet.server.common.UserDAO.getUserDate(UserDAO.java:1178)
at jsp_servlet.__pwdexprdays._jspService(__pwdexprdays.java:177)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:225)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:127)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:219)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:165)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3153)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:1973)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:1834)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1310)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:207)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:179)


and Tom said...

... From the server trace file two values of bind variables are passed to be statement.

SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1 ...


you are not passing any binds, you have the traditional "we don't care about your stinking database - we are Java programmers!" code. You are using cursor_sharing=force or simliar. You have SQL injection bugs in your code (and don't even know it) and you have performance issues!

and you have a huge bug in the above SQL. It quite simply shows the developer didn't spend 2 seconds to learn about the piece of software that will make or break their application.



You are to_date'ing a date. Do you know what inputs to_date takes? A string. A SYSDATE psuedocolumn or HIREDATE column is of what type? Date. To make a date to a string - can we do that - sure. How? by using to_char


so your

SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1

is really:

select to_date( TO_CHAR(sysdate),:"sys_b_0")- to_date( TO_CHAR(hiredate),:"SYS_B_1") as elapsed from scott.emp where ename = :1;


Now, what date format is being used for the to_char?

Well, that would be whatever date format is the default for the session.


This is entirely predicable (it took me about 5 seconds to see it, not even, I can PREDICT this will happen with regularity just by looking at the code!)


ops$tkyte%ORA10GR2> variable sys_b_0 varchar2(30)
ops$tkyte%ORA10GR2> variable sys_b_1 varchar2(30)
ops$tkyte%ORA10GR2> variable ename varchar2(30)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :sys_b_0 := 'DD-MM-YYYY'; :sys_b_1 := 'DD-MM-YYYY'; :ename := 'JONES';

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :ename;

   ELAPSED
----------
    -25913

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'MM-DD-YYYY';

Session altered.

ops$tkyte%ORA10GR2> SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :ename;
SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :ename
               *
ERROR at line 1:
ORA-01843: not a valid month


ops$tkyte%ORA10GR2> alter session set nls_date_format = 'MON-DD-YYYY';

Session altered.

ops$tkyte%ORA10GR2> SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :ename;
SELECT TO_DATE(SYSDATE,:"SYS_B_0") - TO_DATE(HIREDATE,:"SYS_B_1") AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :ename
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected



This is not a random error in Oracle, this is a predicable bug in your developed code since it was coded by someone that doesn't understand how the ANSI SQL DATE datatype works in a relational database, and that relies on - in fact forces the use of - implicit conversions.



And no, the fix is NOT to outlaw the use of ALTER SESSION, the fix is to outlaw the use of implicit conversions - to outlaw the use of SQL that just doesn't make sense - to outlaw code going production withoiut a fine toothed comb style code review taking place by a database developer :)


Sorry - but I just see this all of the time, over and over and over again...

And what date format would that be?

Well, that would be whatever date format the last person that grabbed your connection from the pool felt it should be.


So, you have default, implicit conversions happening here - useless, performance degrading implicit conversions (the best kind!) :)



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to find current session nls_date_format

A reader, April 22, 2010 - 11:03 am UTC 

Excellant !!!
1)
Tom but how to find the current nls_date_format for the session ?

We have different DB sessions for the connection pool.

How can prove this to the java guy that it happens due to NLS_DATE_FORMAT change at the session level.( I know its a bug in the code ) I need to first prove this and convince why it is happening randomly.

It seems given the various weblogic connection pool this  happens randomly only for those session whose NLS_DATE_FORMAT at session level is different.

How to trap this ?

I saw the windows registry at the client where weblogic is installed nothing is set for NLS_DATE_FORMAT.

Is there a way to monitor this ?


This is what sqlplus shows at the client.


SQL> show parameter nls_date_format 

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
nls_date_format                      string                           DD-MON-RRRR
SQL> select name, value from v$parameter where name = 'nls_date_format';

NAME                                                             VALUE
---------------------------------------------------------------- -----------------------------------
nls_date_format                                                  DD-MON-RRRR
SQL> SHOW PARAMETER CURSOR_SHARING

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR

SQL> select sysdate from dual ;

SYSDATE
---------------
22-APR-10

SQL>  select TO_DATE(sysdate,'DD/MM/YYYY') THE_DATE_DATE  from dual ;

THE_DATE_DATE
---------------
22-APR-10


2) Java guys keeps insisting why was this working for so many years and now it is happening ? How to answer that.
If only i can find where how the weblogic nls_date_format is changing and what is its current session format I can present the case to the java gang.

Tom Kyte
April 22, 2010 - 1:06 pm UTC

1) you missed my point :(

the point is - you should NOT EVER - as in *NOT EVER* rely on defaults. I wish we didn't have a default date format :(

We just proved it, make the code change - do the query correctly (come on - didn't I just give you enough ammo to make this coder look completely like a nube - they used to_date - ON A DATE - on a DATE!! - they to_date'd - a DATE. Man. Oh. Man.)

... How to trap this ? ..

we. just. did.


tell them to code:

SELECT SYSDATE - HIREDATE AS ELAPSED FROM SCOTT.EMP WHERE ENAME = :1

and watch it NEVER EVER EVER happen again.

for that query. But it'll still happen because they don't now what they are doing when they write SQL.

ops$tkyte%ORA11GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                dd-mon-yyyy hh24:mi:ss




2) This is what you say, in a deadpan:


Something changed dude. Do you get the fact that

a) your code is wrong

b) your code relies on defaults that you neither set nor check

c) your code is "not smart" (never use the s-word, use "not smart", you sound friendlier that way :) )

d) you implicitly convert a date into a string just so you can explicitly convert that implicitly converted string into a date - why do you do that? do you have a deal with Oracle to increase the number of cpu's we need?

e) all it takes is a new application coming along that changes the sessions nls_date_format and you code BREAKS - we just proved that in a manner that anyone can understand. Apparently someone has done that - and your code broke, your code was a bug just waiting to happen, it has now happened. Be happy you get an error and not a wrong answer. Imagine if you will what could happen if you implicitly converted the date to a string, got the month and day backwards from what you thought it should be and then explicitly converted it - think about that. now please - go back to your cube, sit down with all of your code, find out where else you have made this grievous, and now to you, obvious error - and fix them all - before they blow up, give the wrong answer or whatever.




they are just lucky that by accident, we consider MON and MM to be the same.


ops$tkyte%ORA11GR2> select to_date( '01-jan-2010', 'dd-mm-yyyy' ) from dual;

TO_DATE('01-JAN-2010
--------------------
01-jan-2010 00:00:00

ops$tkyte%ORA11GR2> select to_date( '01-jan-2010', 'fxdd-mm-yyyy' ) from dual;
select to_date( '01-jan-2010', 'fxdd-mm-yyyy' ) from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


since their current default format is dd-MON-yyyy but their explicit format is dd-mm-yyyy - if we used strict formats (as done with the fx modifier for format exact), it would have blown up a long long time ago.

In other words tell them "you have been lucky so far, now stop talking and start fixing - your bug"

just adding nls_parameter value

A reader, April 22, 2010 - 11:12 am UTC 

SQL>  select parameter
  2          , value
  3       from v$nls_parameters where parameter = 'NLS_DATE_FORMAT' ;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_DATE_FORMAT                                                  DD-MON-RR

fimd sesson nls settings for all sessions

A reader, April 22, 2010 - 11:19 am UTC 

SQL>    SELECT * from NLS_SESSION_PARAMETERS where parameter = 'NLS_DATE_FORMAT' ;

PARAMETER                                                                                  VALUE
------------------------------------------------------------------------------------------ ---------
NLS_DATE_FORMAT                                                                            DD-MON-RR


 From outside the session how can I find what the session values for nls_dae_format ?

I can log in as system user but how to find the session settings for different sessions without loggin into them.
The DB sesssion from the connection pool are already there, i need to find its nls_date_format session settings.

Is there a view or same concept like DBA_% and USER_% views for nls_parameters

Tom Kyte
April 22, 2010 - 1:10 pm UTC

not that I'm aware of.

Here is how you could "prove" it.

create a logon trigger
issue

execute immediate 'alter session set nls_date_format = ''"hello world"''';

sit back and watch what happens :)

don't do this in production please, because I'm 100% sure the application will end up corrupting most all of its date and date related data.

A reader, April 22, 2010 - 5:59 pm UTC

Does production code in the SCOTT schema ring alarm bells?

I wonder what the password is ...
Tom Kyte
April 23, 2010 - 8:34 am UTC

I'm thinking that was "the example sanitized for publication" - especially since it queried the EMP table...

Preventing implicit conversions in PL/SQL

Rob, April 23, 2010 - 4:19 am UTC

Tom, you show one of the most common errors I see in database code in doing TO_DATE on a DATE. Although there are reasons to allow implicit conversions in the SQL engine (ie being able to read your dates in Select statements) is there anyway to prevent the PL/SQL compiler from allowing implicit conversions as there is no reason to have them in compiled code either by throwing an error or a warning. As you point out implicit conversions are bugs in the code waiting to be found.
Tom Kyte
April 23, 2010 - 9:11 am UTC

Although there are reasons to allow implicit conversions in
the SQL engine (ie being able to read your dates in Select statements)

I would disagree, you can always use to_char with a format of your choosing to make the results 'readable' AND predicable. Use implicit conversions and some day you'll get 'hello world' as your sysdate output - just cause I'm mean that way.



Alexander, April 23, 2010 - 9:13 am UTC

"Java guys keeps insisting why was this working for so many years and now it is happening"

I wish I had some form of monetary compensation for every time I've heard that. And I haven't been working for that long.
Tom Kyte
April 23, 2010 - 9:22 am UTC

I laughed out loud when I read that - especially when I got to "and I haven't been working for that long" :)

What about implicit use of NLSSORT ?

Stew Ashton, May 01, 2010 - 8:07 am UTC


I have a new multilingual database and application. I am planning to use linguistic indexes to speed up OLTP queries.

I was intending to use a LOGON TRIGGER to set NLS_COMP and NLS_SORT appropriately, since this is much less tedious than using the NLSSORT function in every WHERE clause and every ORDER BY. Also, I would be able to switch NLS_SORT values in the indexes and in the LOGON TRIGGER without changing the calling application at all.

I doubt the will and the ability of the developers to implement the explicit NLSSORT function in every single query.

Do you think the "implicit" alternative is acceptable?
Tom Kyte
May 06, 2010 - 10:38 am UTC

in client server, it would stand a change.

in a connect pool - unlikely to work at all.

A date / null/ boolean logic idiosyncracy

David Wilson, May 13, 2010 - 11:38 am UTC

Hi Tom,
I do not understand why the first of the following 3 queries only selects four of the five records, while the following 2 select all five. The logic, superficially at least, seems to be the same for all. I suspect it has something to do with the fact that the date to be compared to is derived from a parameter table with an essentially untyped 'value' field even though I do explicitly use the to_date on the record I want to use.


TIA

David.

create table test_case (
job char(5),
id integer,
status char(1),
data1 varchar2(30),
data2 varchar2(30),
dated date,
processed_on date,
cancelled_on date,
corrects_job char(5),
corrects_id integer
);

insert into test_case
(job, id, status, data1, data2, dated, processed_on, cancelled_on,
corrects_job, corrects_id)
select 'aaaaa', 1, 'X', 'Hello', 'World', sysdate - (2*365 +10), sysdate - (2*365 +9), sysdate -(2*365 +8), null, null from dual
union
select 'aaaab', 1, 'X', 'Greetings', 'World', sysdate - (2*365 +8),sysdate - (2*365 +8), sysdate - (2*365 +7), 'aaaaa', 1 from dual
union
select 'aaaac', 1, 'X', 'Greetings', 'Earth', sysdate - (2*365 +7),sysdate - (2*365 +7), sysdate - (2*365 +6), 'aaaab', 1 from dual
union
select 'aaaad', 1, 'X', 'Goodbye', 'Earth', sysdate - (2*365 +6),sysdate - (2*365 +6), sysdate - (2*365 +5), 'aaaac', 1 from dual
union
select 'aaaae', 1, 'P', 'Goodbye', 'Cruel World', sysdate - (2*365 +5),sysdate - (2*365 +5), null, 'aaaad', 1 from dual
;
*/

/*
create table test_parameter
(
name varchar2(30),
value varchar2(240),
description varchar2(240)
);

insert into test_parameter
(name, value, description)
select 'CUTOFF_DATE', to_char(sysdate - (2*365), 'DD-MON-YYYY'), 'A cut-off date for selecting records old enough to be purged' from dual
union
select 'SLOGAN', 'HAVE a nice day :)', 'banal company slogan' from dual;
*/

Operating System Red Hat Enterprise Linux AS release 4 (Nahant Update 5) 2.6.9 55.0.9.ELsmp (64-bit)
Hardware Platform x86_64

sqlplus xxx/xxx@xxx

SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 13 12:27:17 2010

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> @test_case

SQL>
SQL> select
2 t.job,
3 t.id,
4 t.job || to_char(t.id) ji,
5 greatest(nvl(t.dated, nvl(t.processed_on, t.cancelled_on)), nvl(t.processed_on, nvl(t.cancelled_on, t.dated)), nvl(t.cancelled_on, nvl(t.dated, t.processed_on))) effective,
6 nvl(to_char(t.dated, 'DD-MON-YYYY'), '-'),
7 nvl(to_char(t.processed_on, 'DD-MON-YYYY'), '-'),
8 nvl(to_char(t.cancelled_on, 'DD-MON-YYYY'), '-'),
9 nvl(t.status, '-'),
10 t.data1,
11 t.data2,
12 t.corrects_job,
13 t.corrects_id,
14 t.corrects_job || to_char(t.corrects_id) cji,
15 to_char(p.acd, 'YYYYMMDD'),
16 LEVEL
17 from test_case t, (select to_date(value, 'DD-MON-YYYY') acd from test_parameter where name = 'CUTOFF_DATE') p
18 where
19 t.dated < p.acd
20 and
21 (
22 t.processed_on is null or
23 (
24 t.processed_on is not null
25 and t.processed_on < p.acd
26 )
27 )
28 and
29 (
30 t.cancelled_on is null or
31 (
32 t.cancelled_on is not null
33 and t.cancelled_on < p.acd
34 )
35 )
36 and
37 (
38 t.processed_on is not null or t.cancelled_on is not null
39 )
40 start with t.job = 'aaaaa' and t.id = 1
41 connect by prior t.job = t.corrects_job and t.corrects_id = prior t.id
42 ;

JOB ID JI EFFECTIVE NVL(TO_CHAR NVL(TO_CHAR NVL(TO_CHAR N DATA1 DATA2 CORRE CORRECTS_ID CJI TO_CHAR( LEVEL
----- ---------- --------------------------------------------- --------- ----------- ----------- ----------- - ------------------------------ ------------------------------ ----- ----------- --------------------------------------------- -------- ----------
aaaaa 1 aaaaa1 05-MAY-08 03-MAY-2008 04-MAY-2008 05-MAY-2008 X Hello World 20080513 1
aaaab 1 aaaab1 06-MAY-08 05-MAY-2008 05-MAY-2008 06-MAY-2008 X Greetings World aaaaa 1 aaaaa1 20080513 2
aaaac 1 aaaac1 07-MAY-08 06-MAY-2008 06-MAY-2008 07-MAY-2008 X Greetings Earth aaaab 1 aaaab1 20080513 3
aaaad 1 aaaad1 08-MAY-08 07-MAY-2008 07-MAY-2008 08-MAY-2008 X Goodbye Earth aaaac 1 aaaac1 20080513 4

4 rows selected.

SQL>
SQL>
SQL> select
2 t.job,
3 t.id,
4 t.job || to_char(t.id) ji,
5 greatest(nvl(t.dated, nvl(t.processed_on, t.cancelled_on)), nvl(t.processed_on, nvl(t.cancelled_on, t.dated)), nvl(t.cancelled_on, nvl(t.dated, t.processed_on))) effective,
6 nvl(to_char(t.dated, 'DD-MON-YYYY'), '-'),
7 nvl(to_char(t.processed_on, 'DD-MON-YYYY'), '-'),
8 nvl(to_char(t.cancelled_on, 'DD-MON-YYYY'), '-'),
9 nvl(t.status, '-'),
10 t.data1,
11 t.data2,
12 t.corrects_job,
13 t.corrects_id,
14 t.corrects_job || to_char(t.corrects_id) cji,
15 to_char(p.acd, 'YYYYMMDD'),
16 LEVEL
17 from test_case t, (select to_date(value, 'DD-MON-YYYY') acd from test_parameter where name = 'CUTOFF_DATE') p
18 where
19 t.dated < p.acd
20 and
21 (
22 t.processed_on is null or
23 (
24 t.processed_on is not null
25 and t.processed_on < p.acd
26 )
27 )
28 and
29 (
30 nvl(t.cancelled_on, p.acd -1) < p.acd
31 /*t.cancelled_on is null or
32 (
33 t.cancelled_on is not null
34 and t.cancelled_on < p.acd
35 ) */
36 )
37 and
38 (
39 t.processed_on is not null or t.cancelled_on is not null
40 )
41 start with t.job = 'aaaaa' and t.id = 1
42 connect by prior t.job = t.corrects_job and t.corrects_id = prior t.id
43 ;

JOB ID JI EFFECTIVE NVL(TO_CHAR NVL(TO_CHAR NVL(TO_CHAR N DATA1 DATA2 CORRE CORRECTS_ID CJI TO_CHAR( LEVEL
----- ---------- --------------------------------------------- --------- ----------- ----------- ----------- - ------------------------------ ------------------------------ ----- ----------- --------------------------------------------- -------- ----------
aaaaa 1 aaaaa1 05-MAY-08 03-MAY-2008 04-MAY-2008 05-MAY-2008 X Hello World 20080513 1
aaaab 1 aaaab1 06-MAY-08 05-MAY-2008 05-MAY-2008 06-MAY-2008 X Greetings World aaaaa 1 aaaaa1 20080513 2
aaaac 1 aaaac1 07-MAY-08 06-MAY-2008 06-MAY-2008 07-MAY-2008 X Greetings Earth aaaab 1 aaaab1 20080513 3
aaaad 1 aaaad1 08-MAY-08 07-MAY-2008 07-MAY-2008 08-MAY-2008 X Goodbye Earth aaaac 1 aaaac1 20080513 4
aaaae 1 aaaae1 08-MAY-08 08-MAY-2008 08-MAY-2008 - P Goodbye Cruel World aaaad 1 aaaad1 20080513 5

5 rows selected.

SQL>
SQL> select
2 t.job,
3 t.id,
4 t.job || to_char(t.id) ji,
5 greatest(nvl(t.dated, nvl(t.processed_on, t.cancelled_on)), nvl(t.processed_on, nvl(t.cancelled_on, t.dated)), nvl(t.cancelled_on, nvl(t.dated, t.processed_on))) effective,
6 nvl(to_char(t.dated, 'DD-MON-YYYY'), '-'),
7 nvl(to_char(t.processed_on, 'DD-MON-YYYY'), '-'),
8 nvl(to_char(t.cancelled_on, 'DD-MON-YYYY'), '-'),
9 nvl(t.status, '-'),
10 t.data1,
11 t.data2,
12 t.corrects_job,
13 t.corrects_id,
14 t.corrects_job || to_char(t.corrects_id) cji,
15 to_char(p.acd, 'YYYYMMDD'),
16 LEVEL
17 from test_case t, (select to_date(value, 'DD-MON-YYYY') acd from test_parameter where name = 'CUTOFF_DATE') p
18 where
19 t.dated < p.acd
20 and
21 (
22 t.processed_on is null or
23 (
24 t.processed_on is not null
25 and t.processed_on < p.acd
26 )
27 )
28 and
29 (
30 /*nvl(t.cancelled_on, p.acd -1) < p.acd */
31 t.cancelled_on is null or
32 (
33 t.cancelled_on is not null
34 and t.cancelled_on < sysdate - (2*365)
35 )
36 )
37 and
38 (
39 t.processed_on is not null or t.cancelled_on is not null
40 )
41 start with t.job = 'aaaaa' and t.id = 1
42 connect by prior t.job = t.corrects_job and t.corrects_id = prior t.id
43 ;

JOB ID JI EFFECTIVE NVL(TO_CHAR NVL(TO_CHAR NVL(TO_CHAR N DATA1 DATA2 CORRE CORRECTS_ID CJI TO_CHAR( LEVEL
----- ---------- --------------------------------------------- --------- ----------- ----------- ----------- - ------------------------------ ------------------------------ ----- ----------- --------------------------------------------- -------- ----------
aaaaa 1 aaaaa1 05-MAY-08 03-MAY-2008 04-MAY-2008 05-MAY-2008 X Hello World 20080513 1
aaaab 1 aaaab1 06-MAY-08 05-MAY-2008 05-MAY-2008 06-MAY-2008 X Greetings World aaaaa 1 aaaaa1 20080513 2
aaaac 1 aaaac1 07-MAY-08 06-MAY-2008 06-MAY-2008 07-MAY-2008 X Greetings Earth aaaab 1 aaaab1 20080513 3
aaaad 1 aaaad1 08-MAY-08 07-MAY-2008 07-MAY-2008 08-MAY-2008 X Goodbye Earth aaaac 1 aaaac1 20080513 4
aaaae 1 aaaae1 08-MAY-08 08-MAY-2008 08-MAY-2008 - P Goodbye Cruel World aaaad 1 aaaad1 20080513 5

5 rows selected.

SQL>
SQL>
SQL> spool off


Does not answer the question

Tim Jowers, September 20, 2010 - 2:30 pm UTC

Question was where the NLS_DATE_FORMAT is being set. It was not answered. Might be in the Java code or the driver. Some other pages mention the Windows registry.
Tom Kyte
September 21, 2010 - 3:24 pm UTC

what do you mean - the original question was "Random Oracle error from Java method How to Debug". It did not ask "where was this set". It asked "what is going wrong here, how do we figure it out"


and that - that was extensively answered in the form of "you have a pretty seriously bad bug there, you need to educate your developers about implicit conversions"


It does not really MATTER where it was set, it only matters that IT WAS.


there was a question "Tom but how to find the current nls_date_format for the session ?", which is the closest thing I can find to your reference (but it isn't the question you said was being asked, only the closest match possible). And that again - was answered - I showed the query to get that value.



It can be set in the environment, it can be set in the registry, it can be set in the init.ora on the server, it can be set in an on-login trigger, it can be set by the application itself, it can be set by many sundry 3rd party API's

It doesn't really matter WHERE - only that IT CAN BE - and therefore, if you rely on defaults - you will get burned, as they did here.

Weblogic driver

Tim Jowers, September 20, 2010 - 4:28 pm UTC

BEA driver was the issue. Use the Oracle driver. BEA driver puts wrong setting it seems.
Tom Kyte
September 21, 2010 - 3:32 pm UTC

and so might about 5,000 other things.


The CODE as developed was the problem - 100%. the code has a serious bug in it.

anyone that tries to "fix" this by setting a default somewhere is doing themselves and the end users and everyone on the planet really a disservice.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library