"Invalid column name" inserting a row with weblogic/oracle
bsaastad
Hello,
Environment
Weblogic 8.1, Oracle 9.0.1, JDK1.4
I am attempting to migrate from WLS 6.1 to WLS 8.1. All appears to work
with the exception of database inserts. They ALWAYS fail and return an
"ORA-00904: invalid column name". I am connecting to the database via a
pooled data source. This code worked fine under WLS6.1 using the
Weblogic JDriver. The JDriver performs the inserts under 8.1 but the
Statement.executeBatch() command returns a 0-length int[] (an
acknowledged bug in the JDriver.) BEA says to use the drivers supplied
by Oracle. I have tried both the type 4 driver and the type 2 oci
driver with exactly the same result.
I can query the database via the connection and dump the contents of the
queried row along with the column names using the ResultMetaData.
ex.
ResultSet rs = stmt.executeQuery( sqlCmd );
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println( "Results of '" + sqlCmd + "'");
while ( rs.next() )
{
for( int i = 1; i <= rsmd.getColumnCount(); i++)
{
System.out.println( "'" + rsmd.getColumnName(i) +
"=" + rs.getString( i ) + "' " );
}
}
.. produces:
Results of 'SELECT POOL_ID, POOL_NM, POOL_DESC, REV_BY, REV_DT FROM
POOL_R WHERE POOL_ID = 11'
'POOL_ID=11'
'POOL_NM=COMM'
'POOL_DESC=Communications Servers'
'REV_BY=saastabp'
'REV_DT=2002-12-16'
Then when I turn around and try to insert a row like this:
String sqlCmd = "INSERT INTO POOL_R (POOL_ID, POOL_NM, POOL_DESC,
REV_BY, REV_DT) VALUES (100, 'FRED', 'TEST POOL NAME FOR FRED', 'test-
user', {ts '2003-10-24 16:00:26.703'})";
System.out.println( "[" + sqlCmd + "]");
stmt.executeUpdate( sqlCmd);
.. I get the exception below. Has anyone seen this before? Any ideas?
Thanks,
-Brian
[INSERT INTO POOL_R (POOL_ID, POOL_NM, POOL_DESC, REV_BY, REV_DT) VALUES
(100, 'FRED', 'TEST POOL NAME FOR FRED', 'test-user', {ts '2003-10-24
16:00:26.703'})]
java.sql.SQLException: ORA-00904: invalid column name
at weblogic.rjvm.BasicOutboundRequest.sendReceive(BasicOutbound-
Request.java:108)
at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef-
.java:138)
at weblogic.jdbc.rmi.internal.StatementImpl_weblogic_jdbc_wrapp-
er_Statement_oracle_jdbc_driver_OracleStatement_811_WLStub.exec-
uteUpdate(Unknown Source)
at weblogic.jdbc.rmi.internal.StatementStub_weblogic_jdbc_rm-
i_internal_StatementImpl_weblogic_jdbc_wrapper_Statement_ora-
cle_jdbc_driver_OracleStatement_811_WLStub.executeUpdate(Unk-
nown Source)
at weblogic.jdbc.rmi.SerialStatement_weblogic_jdbc_rmi_internal-
_StatementStub_weblogic_jdbc_rmi_internal_StatementImpl_weblogi-
c_jdbc_wrapper_Statement_oracle_jdbc_driver_OracleStatement_811-
_WLStub.executeUpdate(Unknown Source)
at com.billing.tm.persist.BatchTest.doInsert(BatchTest.java:93)
at com.billing.tm.persist.BatchTest.main(BatchTest.java:185)
Caused by: java.sql.SQLException: ORA-00904: invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError-
.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol-
.java:1094)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleSta-
tement.java:2132)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStat-
ement.java:2015)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(Orac-
leStatement.java:2877)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleState-
ment.java:858)
at weblogic.jdbc.wrapper.Statement.executeUpdate(Statement-
.java:328)
at weblogic.jdbc.rmi.internal.StatementImpl_weblogic_jdbc_wrapp-
er_Statement_oracle_jdbc_driver_OracleStatement.executeUpdate(U-
nknown Source)
at weblogic.jdbc.rmi.internal.StatementImpl_weblogic_jdbc_wrapp-
er_Statement_oracle_jdbc_driver_OracleStatement_WLSkel.invoke(U-
nknown Source)
at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef-
.java:466)
at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef-
.java:409)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(Aut-
henticatedSubject.java:353)
at weblogic.security.service.SecurityManager.runAs(SecurityMana-
ger.java:144)
at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServ-
erRef.java:404)
at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecu-
teRequest.java:30)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
--
Posted via http://dbforums.com
Joe Weinstein
bsaastad wrote:
> Hello,
>
>
>
> Environment
>
> Weblogic 8.1, Oracle 9.0.1, JDK1.4
>
>
>
> I am attempting to migrate from WLS 6.1 to WLS 8.1. All appears to work
> with the exception of database inserts. They ALWAYS fail and return an
> "ORA-00904: invalid column name". I am connecting to the database via a
> pooled data source. This code worked fine under WLS6.1 using the
> Weblogic JDriver. The JDriver performs the inserts under 8.1 but the
> Statement.executeBatch() command returns a 0-length int[] (an
> acknowledged bug in the JDriver.) BEA says to use the drivers supplied
> by Oracle. I have tried both the type 4 driver and the type 2 oci
> driver with exactly the same result.
Odd. Would you pleaes download the latest appropriate version of
oracle's driver and either run this code in a standalone program
getting your connection directly from the driver, or get the driver
to the front of the -classpath argument in the startWeblogic script,
and then run this code in the server again?
thanks,
Joe Weinstein at BEA
bsaastad
Joe,
I've done that. This grew out of a problem with the weblogic JDriver in
that the addBatch() method provided with WL 8.1 has a bug in that it
returns a 0-length integer array even though the inserts work (bea case
452407, if you're interested in the history.) The recommendation from
support was to download the latest ojdbc14.jar file from Oracle and make
sure it is at the front of the classpath. I've done that to no avail.
-Brian
Joe Weinstein
bsaastad wrote:
Hmmmmm.... So you've added the latest oracle thin driver so it comes
before all standard weblogic stuff in the server's classpath argument,
as constructed by the startWeblogic script, and you defined your pool to
use that driver, or you made a direct connection using that driver, and
got a zero-length int[]?
Please first verify that the oracle driver you downloaded either behaves
as you want or has the problem, when it is used in a small standalone
program with no weblogic in the classpath. Weblogic should/can provide
you with the same behavior.
Joe
bsaastad
Joe,
It looks like this is a problem with either the oracle driver or
possibly the oracle/jdbc/jvm installation. When I do as you suggested
(explicitly load the oracle driver and get my connection from there)
taking Weblogic completely out of the picture I still get the "invalid
column name" error with a much more concise exception:
[INSERT INTO POOL_R (POOL_ID,POOL_NM,POOL_DESC,REV_BY,REV_DT) VALUES
(100, 'FRED', 'TEST POOL NAME FOR FRED', 'test-user', {ts '2003-10-28
11:02:00.328'})]
java.sql.SQLException: ORA-00904: invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError-
.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol-
.java:1094)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleSta-
tement.java:2132)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStat-
ement.java:2015)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(Orac-
leStatement.java:2877)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleState-
ment.java:858)
at com.billing.tm.persist.BatchTest.doInsert(BatchTest.java:97)
at com.billing.tm.persist.BatchTest.main(BatchTest.java:189)
I've also posted this to Oracle's MetaLink forum.
-Brian