Re: global temporary table --> invalid ROWID?

From: Jim Kennedy <kennedy-down_with_spammers_at_no_spam.comcast.net>
Date: Fri, 03 Oct 2003 14:17:19 GMT
Message-ID: <Pxffb.485819$Oz4.329946_at_rwcrnsc54>


"Oracle_Technet" <oracle_technet_at_yahoo.com> wrote in message news:c3e04899.0310022230.799c3a82_at_posting.google.com...
> Reason as per my understanding: java front end would be using
> connection pooling, and the session is not bound. so once the back end
> proc gives the output back to front end, and then the front end tries
> to access the blob stored in the global temp table, it will give
> error, as global temp table is session specific. (so rowid not found).
> Same type of errors would be encountered with ref cursors based on
> global temp tables.
> So, don't use Global Temporary tables for returning resultsets to java
> front end.
> Hope this helps.
>
> JavaDeveloper_at_volcanomail.com (Corrine) wrote in message
 news:<54774824.0309290654.58fb3f16_at_posting.google.com>...
> > Hi,
> >
> > I am creating a global temporary table that is session-specific. I
> > insert a BLOB into this table, and then select the BLOB from this
> > table into a ResultSet. The ResultSet sees this BLOB object, and I am
> > able to get the binary input stream from this blob. However, when I
> > invoke InputStream.read(byte[]) on this input stream, I get the
> > following exception:
> >
> > java.io.IOException: ORA-01410: invalid ROWID
> > ORA-06512: at "SYS.DBMS_LOB", line 751
> > ORA-06512: at line 1
> >
> >
> > at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:625)
> >
> > at

oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.jav a:179)
> >
> > at

 oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:113)
> >
> > at

 oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:91)
> > ...
> >
> > Just for clarity, here is basically the code I'm using:
> >
> > CallableStatement cstmt2 = db.prepareCall("commit");
> > CallableStatement cstmt = db.prepareCall("create global temporary
> > table temp_table (blobid NUMBER unique, blob_col BLOB) on commit
> > preserve rows");
> > cstmt.execute();
> > cstmt.close();
> > cstmt2.execute();
> > CallableStatement cstmt1 = db.prepareCall("insert into temp_table
> > values (1, empty_blob())");
> > cstmt1.execute();
> > cstmt1.close();
> > cstmt2.execute();
> > cstmt2.close();
> >
> > CallableStatement plsqlblock =
> > db.prepareCall(stringThatFillsInEmptyBlobInTempTable);
> > plsqlblock.execute();
> > plsqlblock.close();
> >
> > PreparedStatement pstmt1 = db.prepareStatement("select blob_col from
> > temp_table where blobid = 1");
> > ResultSet rset = pstmt1.executeQuery();
> > BLOB bl;
> > if (rset.next())
> > {
> > System.out.println("success."); //<--- This prints out.
> > bl = ((OracleResultSet)rset).getBLOB(1);
> > }
> > pstmt1.close();
> >
> > InputStream bis = bl.getBinaryStream();
> > int numBytes = 0;
> > byte[] theBytes = new byte[appropriateLength];
> > numBytes = bis.read(theBytes); ///*******EXCEPTION HAPPENS
> > HERE!!!!
> >
> >
> > I would also like to mention that when I change temp_table to be just
> > a regular table, everything works correctly.
> >
> > What's the problem, and how do I fix it?
> >
> > Thanks,
> >
> > Corrine

If it works on a regular table then why are you making things more expensive by putting it in a temp table first? Just get it out of the regular table. Jim Received on Fri Oct 03 2003 - 16:17:19 CEST

Original text of this message