Re: retrieving CLOBs through Java/Oracle with package functions

Exact Oracle8i version? Oracle client software and JDBC driver version? What that GetXML() function returns? A BLOB? A CLOB? A string (VARCHAR2)? Did you try to remove AS keyword leaving only the alias for the function result (this is pretty legal in Oracle, in fact it doesn't like AS keyword sometimes. :) Did you try to enclose the alias for the function into double quotes (i.e. "tony1" or "TONY1")? Did you try to alias the table itself? If all this fails - did you try with the latest JDBC driver?

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Tony Cantara" <tonycantara123_at_hotmail.com> wrote in message
news:3d0c2563.0301220947.759cfe0e_at_posting.google.com...


> jdk1.3, Win2K, Oracle8i enterprise.

>

> All, have a bit of a problem, details below.

> Any relevant thoughts?

> Tony.

>

>

>

> DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

> Connection conn=DriverManager.getConnection("jdbc:oracle:oci8:@dbtest","scott","tiger");

>

> PreparedStatement stmt=conn.prepareStatement("SELECT

> C$GFXLUTL1.UtlParse.GetXML(blobfile) AS tony1 FROM tblblobtests WHERE

> blobid=?");

> stmt.setString(1,"A10042");

> ResultSet rset=stmt.executeQuery;

>

> I get the following error trace.

>

>

> java.sql.SQLException: ORA-00904: invalid column name

>

> void oracle.jdbc.dbaccess.DBError.throwSqlException(java.lang.String,

> java.lang.String, int)

> DBError.java:168

> int oracle.jdbc.oci8.OCIDBAccess.check_error(int,

> oracle.jdbc.oci8.OCIDBStatement)

> OCIDBAccess.java:1597

> oracle.jdbc.dbaccess.DBColumn[]

> oracle.jdbc.oci8.OCIDBAccess.parseExecuteDescribe(oracle.jdbc.dbaccess.DBStatement,

> byte[], oracle.jdbc.dbaccess.DBDataSet)

> OCIDBAccess.java:798

> void oracle.jdbc.driver.OracleStatement.doExecuteQuery()

> OracleStatement.java:1674

> void oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()

> OracleStatement.java:1870

> java.sql.ResultSet oracle.jdbc.driver.OracleStatement.executeQuery(java.lang.String)

> OracleStatement.java:538

>

>

> When I execute "SELECT SELECT C$GFXLUTL1.UtlParse.GetXML(blobfile) AS

> tony1 FROM tblblobtests WHERE blobid='A10042'" from within SQL/Plus, I

> get the expected result without errors. When I omit the "AS tony1" in

> the Java source, same error.