How are the uses of dump func tion
A reader, October 09, 2001 - 3:45 pm UTC
Tom
Can you tell us how and where we can make use of the dump function, in our day to day programming.
Thank you
Help to interpret dump function output
A reader, September 07, 2005 - 7:41 pm UTC
Tom,
As you can see from the below query, order_status column (number(2) datatype) value is 0 (zero), but the dump output shows Len=2, so there is a bad data in it.
Due to this bad data, java application using 9205 jdbc driver always throws "java.lang.ArrayIndexOutOfBoundsException:"
How to interpret this dump output :
Typ=2 Len=2: 193,1
What Typ, 193,1 means ?
1* select iom_order_id, order_status, length(order_status), lengthb(order_status), dump(order_status) dump from iom_order_bvoip where package_id=62048
SQL> /
IOM_ORDER_ID ORDER_STATUS LENGTH(ORDER_STATUS) LENGTHB(ORDER_STATUS) DUMP
------------ ------------ -------------------- --------------------- ---------------
47869 0 1 1 Typ=2 Len=2:
193,1
1* select * from nls_database_parameters
SQL> /
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 9.2.0.5.0
1* select iom_order_id, order_status, length(order_status), lengthb(order_status), dump(order_status) dump from iom_order_bvoip where package_id=62048
SQL> /
IOM_ORDER_ID ORDER_STATUS LENGTH(ORDER_STATUS) LENGTHB(ORDER_STATUS) DUMP
------------ ------------ -------------------- --------------------- ---------------
47869 0 1 1 Typ=2 Len=2:
193,1
September 08, 2005 - 8:29 am UTC
well, the number 1 in there would be 193,2 -- so "technically", 193,1 is zero (but we would store zero as 128)
What process is inserting/updating these nubmers -- what does that code look like?
A reader, September 08, 2005 - 9:22 am UTC
Tom,
Thanks.
iom_order_bvoip.order_status record is inserted/updated through stored procedures which are getting the inputs using ObjectType. Actual value of this column is coming from Front end screen.
Update statement written in Java code can also update this column.
when i tried to update to 0 manually, and dumped the column value, it is showing as "128" . Is it fair to say that zero is stored with some hidden character in our case.
What bad character stored in this ?
How to remove that bad character ?
September 08, 2005 - 9:57 am UTC
I'd have to see the code that did the update.
There are a couple of ways to update information - you can bind a normal datatype (normal host type - like a float, int, double, string, etc) and we'll convert it correctly into the internal Oracle number format.
Or, you can be using something that tries to emulate the oracle number format directly - I see it with DATES all of the time, host programs binding the 7 byte binary format and giving us "bad dates"
if you bind the internal format, no check is performed on the data, we just accept it.
A reader, September 08, 2005 - 10:34 am UTC
Tom,
We are looking into the code which sets/populates the order_status=0.
Also, this "exception" happens only for zero value not for other order status like 1,2,4 etc..
During problem investigation, we also found one more thing, if we used 8174 JDBC drivers to query the same data (without changing any manual data update to zero), it worked.
But when we tried with our current 9205 Jdbc drivers / 10.2.0. JDBC drivers, it did not work until the data is corrected. So it was so strange. Just curious, Why it is behaving inconsistent with drivers particularly with "number" stored as zero.
September 08, 2005 - 3:48 pm UTC
well, the number is stored "wrong", it should be 128 -- not what it is.
Found something interesting
A reader, September 08, 2005 - 7:25 pm UTC
Tom,
This is the article i found on metalink which talks about when number zero will be stored in incorrect format.
Doc ID: Note:311346.1
Subject: Incorrectly stored numbers in a database
</code>
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=311346.1 <code>
In our case, looks like in Java object, order_status field was defined as "string" whereas in Objecttype and stored procedure part, it was defined as number(2), so i think that is what caused the data zero stored incorrectly.
Would it be better to convert the string (zero) to number(string) in stored procedure insertion part Or
modify the datatype to "int" in Java object definition part ?
September 08, 2005 - 7:31 pm UTC
I'm not sure that note applies to java in this case (definitely not a string to number conversion -- that is like what sqlplus does).
I do not believe you've found the culprit -- any C code lurking out there?
(it is what I described above, with the binding of the internal number format, you have a string - not the internal number format. If anything it would be a jdbc driver bug if only java accesses this number)
Thanks
A reader, September 08, 2005 - 11:23 pm UTC
Tom,
I think you are right.
Our application is purely Java based, no C program involved.
<<
If anything it would be a jdbc driver bug if only java accesses this number)
>>
Only Java code accesses this number (zero).
September 09, 2005 - 6:55 am UTC
we'd need to try and reproduce, that is why we need the code that modifies this (as simple as possible) to see the api calls, the conversions, etc..
Here is the code which does the update
A reader, September 09, 2005 - 7:42 pm UTC
Tom,
Here is the java code which does the update order_status.
updateOrderStatus is the method. Also, we tested with this code and could not reproduce this invalid storing of zero in database. We are looking into other sources of data coming in as well.
=====
import Constants;
import java.sql.*;
import java.util.*;
public class Test {
public static void main(String[] args) {
boolean ret = updateOrderStatus("47863", Constants.IO_ORDER_STATUS_PENDING );
}
public static boolean updateOrderStatus( String order_id, int order_status )
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "update order_bvoip set ORDER_STATUS=? where ORDER_ID=?";
try
{
conn = DBPool.getConnection("DB");
if(conn!=null)
stmt = conn.prepareStatement(sql);
if(stmt==null)
{
return false;
}
stmt.setInt(1, order_status );
stmt.setString(2, order_id );
stmt.execute();
stmt.close(); stmt = null;
return true;
}catch(Exception ex)
{
return false;
}finally {
}
}
}
==
Constants.java
/iom_order.order_status
public static final int IO_ORDER_STATUS_PENDING = 0;
public static final int IO_ORDER_STATUS_COMPLETED = 1;
public static final int IO_ORDER_STATUS_CANCELLED = 2;
public static final int IO_ORDER_STATUS_REJECTED = 3;
public static final int IO_ORDER_STATUS_IN_PROGRESS = 4;
public static final int IO_ORDER_STATUS_CANCEL = 5;
public static final int IO_ORDER_STATUS_SUSPENDED = 6;
public static final int IO_ORDER_STATUS_QUEUED = 7;
public static final int IO_ORDER_STATUS_CANCELLED_IN_EFMS = 59;
public static final int IO_ORDER_STATUS_CANCELLED_IN_BRASS = 60;
public static final int IO_ORDER_STATUS_CANCELLED_IN_GIOM = 61;
public static final int IO_ORDER_STATUS_LAUNCH_BRASS = 62;
public static final int IO_ORDER_STATUS_LOAD_GIOM_SR = 63;
public static final int IO_ORDER_STATUS_PRE_OS_PROCESS_PENDING = 64;
public static final int IO_ORDER_STATUS_PRE_OS_PROCESS_COMPLETED = 65;
public static final int IO_ORDER_STATUS_CANCEL_REJECTED_IN_BRASS = 66;
===
September 09, 2005 - 8:54 pm UTC
yeah, that looks ok -- and since it is not all of the zeroes (is it?) it could be some other source
Anyway to reproduce this ?
A reader, September 12, 2005 - 11:32 am UTC
Tom,
We have no luck in reproducing this problem. But still not 100% sure we identified the source yet.
Is there any way to reproduce this "incorrectly storing zero" in the database (from sqlplus or java) ?
Developer is also asking, Is there anyway to find out the special characters/invalid characters in that "zero"
(193,0) which might help in deugging.
Appreciate your help.
September 12, 2005 - 2:20 pm UTC
from pro*c or OCI - I can, just use the native, internal type (many C or Cobol programs have done that with disasterous results).
193,1 is just one off of 193,2 - 193,2 is the #1. However, 128 is how Oracle represents zero - that is why we would suspect rogue code doing this - if you look at 1, 2, 3, 4, 5, .... there is a pattern, if you take that pattern to the logical conclusion - zero should be 193,1 - but it isn't.