Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #276

Re: java.sql.SQLException

Show all headers | View raw

  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

Back to comp.lang.java.databases | Previous | Next | Find similar

Thread

Re: java.sql.SQLException "laredotornado" <laredotornado@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

csiph-web