Re: global temporary table --> invalid ROWID?

Is a temporary table really more expensive than a regular table? Actually, I prefer a temporary table, because my application serves multiple users simultaneously. I am concerned about concurrency issues that arise if I use
just a regular table.

Speaking of which, what if I use a temporary BLOB rather than a temporary table? I am trying to do this now, but I can't figure out how to get the modified BLOB object back to Java after the PLSQL block has finished executing.

I do:

BLOB bl = BLOB.createTemporary(db,true,BLOB.DURATION_SESSION); CallableStatement plsqlblock = conn.prepareCall(

   "declare\n"+
   "  stuff... \n"+
   "begin\n"+
   "  procThatModifiesBLOB(?);\n"+
   "end;\n");

bl = plsqlblock.getBlob(1); //**** doesn't work.

How do I retrieve the modified value of "bl" after "plsqlblock" finishes executing?

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<Pxffb.485819$Oz4.329946_at_rwcrnsc54>...

 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
 

> "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
>

> Received on Fri Oct 03 2003 - 23:29:55 CEST