java.sql.SQLException: ORA-00904: invalid identifier
laredotornado
Hi,
I'm using Java 1.5 with WebLogic 9.2.2 connected to an Oracle 10 db.
I'm getting the below error but what is odd is that I can run the
offending SQL statement fine using PL/SQL Developer. I have verified
that the connection information is the same. Any way to tell exactly
what is the "Invalid identifier"?
Thanks, - Dave
java.sql.SQLException: ORA-00904: "NPS_LOG"."PKG_NPSLOG"."GET_LKUP":
invalid identifier
at
weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:211)
at
weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:224)
at
weblogic.jdbc.rmi.internal.PreparedStatementImpl_weblogic_jdbc_wrapper_PreparedStatement_oracle_jdbc_driver_T4CPrepar
edStatement_922_WLStub.executeQuery(Unknown Source)
at
weblogic.jdbc.rmi.internal.PreparedStatementStub_weblogic_jdbc_rmi_internal_PreparedStatementImpl_weblogic_jdbc_wrapp
er_PreparedStatement_oracle_jdbc_driver_T4CPreparedStatement_922_WLStub.executeQuery(Unknown
Source)
at
weblogic.jdbc.rmi.SerialPreparedStatement_weblogic_jdbc_rmi_internal_PreparedStatementStub_weblogic_jdbc_rmi_internal
_PreparedStatementImpl_weblogic_jdbc_wrapper_PreparedStatement_oracle_jdbc_driver_T4CPreparedStatement_922_WLStub.executeQuery(U
nknown Source)
at
com.myco.nps_history.dao.NPSHistoryDAO.getKeyTypes(NPSHistoryDAO.java:
210)
at
com.myco.nps_history.beans.SearchBean.getKeyTypes(SearchBean.java:106)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
org.apache.myfaces.el.PropertyResolverImpl.getProperty(PropertyResolverImpl.java:
459)
at
org.apache.myfaces.el.PropertyResolverImpl.getValue(PropertyResolverImpl.java:
85)
at org.apache.myfaces.el.ELParserHelper
$MyPropertySuffix.evaluate(ELParserHelper.java:539)
at
org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:145)
at
org.apache.myfaces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:
386)
at
javax.faces.component.UISelectItems.getValue(UISelectItems.java:108)
at
org.apache.myfaces.shared_impl.util.SelectItemsIterator.hasNext(SelectItemsIterator.java:
127)
at
org.apache.myfaces.shared_impl.renderkit.RendererUtils.internalGetSelectItemList(RendererUtils.java:
451)
at
org.apache.myfaces.shared_impl.renderkit.RendererUtils.getSelectItemList(RendererUtils.java:
429)
at
org.apache.myfaces.shared_impl.renderkit.html.HtmlRendererUtils.internalRenderSelect(HtmlRendererUtils.java:
303)
at
org.apache.myfaces.shared_impl.renderkit.html.HtmlRendererUtils.renderListbox(HtmlRendererUtils.java:
265)
at
org.apache.myfaces.shared_impl.renderkit.html.HtmlListboxRendererBase.encodeEnd(HtmlListboxRendererBase.java:
77)
at
javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:
775)
at
javax.faces.webapp.UIComponentTag.encodeEnd(UIComponentTag.java:678)
at
javax.faces.webapp.UIComponentTag.doEndTag(UIComponentTag.java:546)
$HOSTNAME:"$PWD"->
$HOSTNAME:"$PWD"->tail -f /export/third-party/etsbea/application_logs/
wls_9.2.2/nps_history_gui_logs/nps_history_gui.log
at
weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:550)
at weblogic.rmi.internal.BasicServerRef
$1.run(BasicServerRef.java:440)
at
weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:
363)
at
weblogic.security.service.SecurityManager.runAs(SecurityManager.java:
147)
at
weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:
436)
at weblogic.rmi.internal.BasicServerRef.access
$300(BasicServerRef.java:58)
at weblogic.rmi.internal.BasicServerRef
$BasicExecuteRequest.run(BasicServerRef.java:975)
... 2 more
---
* Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24
joe.we...@gmail.com
> Hi,
>
> I'm using Java 1.5 with WebLogic 9.2.2 connected to an Oracle 10 db.
> I'm getting the below error but what is odd is that I can run the
> offending SQL statement fine using PL/SQL Developer. I have verified
> that the connection information is the same. Any way to tell exactly
> what is the "Invalid identifier"?
>
> Thanks, - Dave
>
> java.sql.SQLException: ORA-00904: "NPS_LOG"."PKG_NPSLOG"."GET_LKUP":
> invalid identifier
Hi. Show the SQL and the JDBC code that sets the parameters and the
parameter values, and maybe the DDL of the table(s) being queried.
Joe Weinstein
laredotornado
wrote:
Sure thing. The Java code in question is
try {
String sql = "SELECT * FROM
TABLE(PKG_NPSLOG.get_lkup(?))";
stmt = conn.prepareStatement(sql);
logger.debug("Calling: " + sql);
stmt.setString(1, "CLIENT_KEY");
rs = stmt.executeQuery();
while (rs.next()) {
String keyType =
rs.getString("CLIENT_KEY");
keyTypesVec.add(keyType);
}
rs.close();
stmt.close();
conn.close();
conn = null;
} catch (SQLException sqle) {
logger.error("SQL statement failed.",
sqle);
}
The Oracle package-function in question is
CREATE OR REPLACE PACKAGE BODY PKG_NPSLOG
AS
...
FUNCTION GET_LKUP (p_code_type IN VARCHAR2)
RETURN LOG_LKUP_TBL PIPELINED
AS
CURSOR cur_lkup IS
select code_name
from txn_log_code_lkup
where code_type = p_code_type
order by code_name;
BEGIN
FOR lkup_rec IN cur_lkup
LOOP
PIPE ROW(lkup_rec);
END LOOP;
RETURN;
END GET_LKUP;
...
Thanks, - Dave
joe.we...@gmail.com
OK, is the actual table column you're comparing the input parameter
to, defined as a CHAR(XX) as opposed to a VARCHAR(XX) or VARCHAR2(XX)?
If so, can you change it to be a VARCHAR2?
Try:
String sql = "SELECT * FROM TABLE(PKG_NPSLOG.get_lkup('CLIENT_KEY'))";
stmt = conn.prepareStatement(sql);
logger.debug("Calling: " + sql);
rs = stmt.executeQuery();
while (rs.next()) {
String keyType = rs.getString("CLIENT_KEY");
keyTypesVec.add(keyType);
}
The DBMS is easier converting fixed strings like 'client_key'
to compare to fixed-char columns of different lengths, when
it is compiling SQL, but can't do it later with parameters...
Joe Weinstein at Oracle