Re: java.sql.SQLException
To: comp.lang.java.databases
On Oct 3, 10:33 am, "joeNOS...@BEA.com" <joe.weinst...@gmail.com>
wrote:
On Oct 3, 8:19 am, laredotornado <laredotorn...@zipmail.com> wrote:
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
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
---
* 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