Problem in calling PL/SQL from JAVA program

[dheeraj chhabra]

Hi,

I am calling PL/SQL from my java program to insert data into a table with 20 coloumns. I am using OCI driver to connect to DB. All columns are of type NUMBER. It works fine if I use 7 columns of the table to insert data. If I use more than 7 columns then I get this error while running the program:


java.sql.SQLException: Data size bigger than max size for this type: oracle.jdbc.oci8.OCIDBStatement@dc840f

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2389)

at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1674)

at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1921)

at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)

at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)

at src.ProcedureTest.<init>(ProcedureTest.java:190)

at src.ProcedureTest.main(ProcedureTest.java:230)

Closing down all connections...



********** Internal heap ERROR 17112 addr=03055BA4 *********



***** Dump of memory around addr 03055BA4:

30559A0 00000000 00000000 00000000 [............]

30559B0 00000000 00000000 00000000 00000000 [................]

Repeat 26 times

3055B60 00000000 20000015 030558C0 00000000 [....... .X......]

3055B70 00000000 00000001 2000002D 030558C0 [........-.. .X..]

3055B80 00000000 00000000 03055B94 00000010 [.........[......]

3055B90 03055BB4 35310018 6C754A20 30303220 [.[....15 Jul 200]

3055BA0 37302038 20000000 030558C0 00000000 [8 07... .X......]

3055BB0 00000000 03055BC0 00001000 03055BD4 [.....[.......[..]

3055BC0 00000000 2000002D 03055BA4 00000000 [....-.. .[......]

3055BD0 00000000 03055BE0 00001000 03055C00 [.....[.......\..]

3055BE0 00000000 00000000 00000000 00000000 [................]

3055BF0 20000021 03055BC4 00000000 00000000 [!.. .[..........]

3055C00 03055C0C 00001000 03055C20 00000000 [.\...... \......]

3055C10 20000021 03055BF0 00000000 00000000 [!.. .[..........]

3055C20 03055C2C 00001000 03055C40 00000000 [,\......@\......]

3055C30 20000021 03055C10 00000000 00000000 [!.. .\..........]

3055C40 03055C4C 00001000 03055C60 00000000 [L\......`\......]

3055C50 2000002D 03055C30 00000000 00000000 [-.. 0\..........]

3055C60 03055C6C 00001000 03055C8C 00000000 [l\.......\......]

3055C70 00000000 00000000 00000000 2000003D [............=.. ]

3055C80 03055C50 00000000 00000000 03055C98 [P\...........\..]

3055C90 00001000 03055CC8 03055C6C 0000000E [.....\..l\......]

3055CA0 03055C2C 03055C0C 00000000 00000000 [,\...\..........]

3055CB0 00000000 03055C4C 2000002D 03055C7C [....L\..-.. |\..]

3055CC0 00000000 00000000 03055CD4 00000010 [.........\......]

3055CD0 03055CF4 6F4E0002 00000000 00000000 [.\....No........]

3055CE0 00000000 20000021 03055CB8 00000000 [....!.. .\......]

3055CF0 00000000 03055D00 00001000 03055D14 [.....].......]..]

3055D00 00000004 2000002D 03055CE4 00000000 [....-.. .\......]

3055D10 00000000 03055D20 00001000 03055D40 [.... ]......@]..]

3055D20 6F4E0002 00000000 00000000 00000000 [..No............]

3055D30 20000021 03055D04 00000000 00000000 [!.. .]..........]

3055D40 03055D4C 00001000 03055D60 00000004 [L]......`]......]

3055D50 20000021 03055D30 00000000 00000000 [!.. 0]..........]

3055D60 03055D6C 00001000 03055D80 00000000 [l].......]......]

3055D70 20000021 03055D50 00000000 00000000 [!.. P]..........]

3055D80 03055D8C 00001000 03055DA0 00000001 [.].......]......]

3055D90 2000002D 03055D70 00000000 00000000 [-.. p]..........]

3055DA0 03055DAC [.]..]



******************************************************

HEAP DUMP heap name="Alloc environm" desc=00BCB700

extent sz=0x1024 alt=32767 het=32767 rec=0 flg=3 opc=3

parent=00BCB768 owner=00000000 nex=00000000 xsz=0x1024

EXTENT 0 addr=030558B8

Chunk 30558c0 sz= 740 free " "

Chunk 3055ba4 sz= 0 ERROR, BAD MAGIC NUMBER (20000000)

EXTENT 1 addr=030568E8

Chunk 30568f0 sz= 4144 free " "

EXTENT 2 addr=0305792C

Chunk 3057934 sz= 68 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 3057978 sz= 40 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 30579a0 sz= 84 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 30579f4 sz= 892 free " "

Chunk 3057d70 sz= 88 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 3057dc8 sz= 544 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 3057fe8 sz= 1292 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 30584f4 sz= 544 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 3058714 sz= 24 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 305872c sz= 24 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 3058744 sz= 524 freeable assoc with mark prv=00000000 nxt=00000000

EXTENT 3 addr=02FA3C84

Chunk 2fa3c8c sz= 14528 freeable assoc with mark prv=00000000 nxt=00000000

EXTENT 4 addr=03020DC4

Chunk 3020dcc sz= 2580 recreate "Alloc server h " latch=00000000

ds 2fa74e4 sz= 2580 ct= 1

Chunk 30217e0 sz= 172 freeable assoc with mark prv=00000000 nxt=00000000

Chunk 302188c sz= 1372 freeable assoc with mark prv=00000000 nxt=00000000

EXTENT 5 addr=00BCD1D0

Chunk bcd1d8 sz= 24 perm "perm " alo=24

Chunk bcd1f0 sz= 28 freeable assoc with mark prv=00000000 nxt=00000000

Chunk bcd20c sz= 84 freeable assoc with mark prv=00000000 nxt=00000000

Chunk bcd260 sz= 24 free " "

Chunk bcd278 sz= 88 freeable assoc with mark prv=00000000 nxt=00000000

Total heap size = 27908

FREE LISTS:

Bucket 0 size=272

Chunk bcd260 sz= 24 free " "

Bucket 1 size=528

Chunk 30558c0 sz= 740 free " "

Chunk 30579f4 sz= 892 free " "

Bucket 2 size=1040

Chunk 30568f0 sz= 4144 free " "

Total free space = 5800

UNPINNED RECREATABLE CHUNKS (lru first):

PERMANENT CHUNKS:

Chunk bcd1d8 sz= 24 perm "perm " alo=24

Permanent space = 24

******************************************************

Hla: 0



kgepop: no error frame to pop to for error 21500.




Please let me know the solution for this.


Thanks and Regards
Dheeraj Chhabra

[Paul Sturrock]

That error typically comes when you try to inset a value that is too wide for the data type of the field you are trying to insert into. What is the precision of the eighth column in your insert statement?

Alternatively, why are you using a 8i OCI driver? A good way to get rounnd mystery issues with Oracle drivers is just to upgrade the driver. The 11g drivers will work just fine against a 8i database (and have the added benefit of being supported by Oracle). Worth a try.
[ July 15, 2008: Message edited by: Paul Sturrock ]

[dheeraj chhabra]

Hi Paul, Thanks for your inputs.

First thing is the 8th colum(all the columns in fact) is also a number. So the width does not matter. and the amount of the data is also not very heavy.

And i hope by 11g driver you mean 'thin' driver. i tried using the same before opting to OCI. It was giving "Unsupported feature" exception.

thanks.

[Paul Sturrock]

It almost sounds like the OCI driver is passing the statement itself as a parameter and declaring the parameter with an incorrect size. I have seen something like this before with Oracle (though it was years ago and the query was huge). Is your SQL itself very large?

And i hope by 11g driver you mean 'thin' driver. i tried using the same before opting to OCI. It was giving "Unsupported feature" exception.

What feature was unsupported? I thought the latest driver was fully JDBC 4 complient?

[dheeraj chhabra]

Our SQL is not very large. For reference find the code snippet here.
-----------------------------------------------------------------------
1. OracleCallableStatement load=null;
load = (OracleCallableStatement) con.prepareCall (
"{call pm4.pminsert4(?,?,?,?,?,?,?,?,?)}"
);
2.load.setPlsqlIndexTable (1,deviceType, max, max, OracleTypes.NUMBER, 0);
3.load.setPlsqlIndexTable (2,neName, max, max, OracleTypes.NUMBER, 0);
4.load.setPlsqlIndexTable (3,tpName, max, max, OracleTypes.NUMBER, 0);
5.load.setPlsqlIndexTable (4,layerRate, max, max, OracleTypes.NUMBER, 0);
6.load.setPlsqlIndexTable (5,location, max, max, OracleTypes.NUMBER, 0);
7.load.setPlsqlIndexTable (6,granularity, max, max, OracleTypes.NUMBER, 0);
8.load.setPlsqlIndexTable (7, zeroSupr, max, max, OracleTypes.NUMBER, 0);
9.load.setPlsqlIndexTable (8, gmtDate, max, max, OracleTypes.NUMBER, 0);
10.load.setInt(9, batchSize);
-----------------------------------------------------------------------

If i use thin driver i am getting unsupported exception on line # 1 on setPlsqlIndexTable() method.
We are using jdk1.4 with ojdbc14.jar.

[dheeraj chhabra]

we have limitation that we can only use jdk1.4 as the complete project is jdk1.4.
[ July 15, 2008: Message edited by: dheeraj chhabra ]