Skip to Main Content
  • Questions

  • Blob insert together with sequence numbers

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Gediminas.

Asked: December 01, 2000 - 1:48 am UTC

Last updated: July 07, 2006 - 9:12 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

I slightly modified your example. It's important for me to use sequence numbers.

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class test {

public static void main (String args []) throws SQLException , FileNotFoundException, IOException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@lupa.vu.lt:1521:oras8i","scott",
"tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.execute( "delete from demo" );
System.out.println( "deleted from demo" );

stmt.execute( "insert into demo (id,theBlob) values (s_enr.nextval,empty_blob())" );
// System.out.println( "inserted into demo" );

conn.commit();
System.out.println( "committed" );

ResultSet rset = stmt.executeQuery ("SELECT theBlob FROM demo where id =s_enr.currval FOR UPDATE");
System.out.println( "Executed Query" );

if(rset.next())
{
System.out.println( "Fetched row " );

BLOB l_mapBLOB = ((OracleResultSet)rset).getBLOB(1);

File binaryFile = new File("e:\\free\\jo.jpg");
FileInputStream instream=new FileInputStream(binaryFile);

int chunk = 32000;

System.out.println( "Chunk = "+ chunk );

byte[] l_buffer = new byte[chunk];

int l_nread = 0;

OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall( "begin dbms_lob.writeappend( :1, :2, :3 ); end;" );

cstmt.registerOutParameter( 1, OracleTypes.BLOB );
while ((l_nread= instream.read(l_buffer)) != -1)
{
cstmt.setBLOB( 1, l_mapBLOB );
cstmt.setInt( 2, l_nread );
cstmt.setBytes( 3, l_buffer );

cstmt.executeUpdate();

l_mapBLOB = cstmt.getBLOB(1);
}
instream.close();

conn.commit();
rset.close();
stmt.close();

}
}
}

When I run program I get

ava.sql.SQLException: ORA-02287: sequence number not allowed here
void oracle.jdbc.dbaccess.DBError.throwSqlException(java.lang.String, java.lang.String, int)
void oracle.jdbc.ttc7.TTIoer.processError()
void oracle.jdbc.ttc7.Oall7.receive()
void oracle.jdbc.ttc7.TTC7Protocol.doOall7(byte, byte, int, byte[], oracle.jdbc.dbaccess.DBType[], oracle.jdbc.dbaccess.DBData[], int, oracle.jdbc.dbaccess.DBType[], oracle.jdbc.dbaccess.DBData[], int)
oracle.jdbc.dbaccess.DBColumn[] oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(oracle.jdbc.dbaccess.DBStatement, byte[], oracle.jdbc.dbaccess.DBType[], oracle.jdbc.dbaccess.DBData[])
void oracle.jdbc.driver.OracleStatement.doExecuteQuery()
void oracle.jdbc.driver.OracleStatement.doExecute()
void oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()
java.sql.ResultSet oracle.jdbc.driver.OracleStatement.executeQuery(java.lang.String)
void test.main(java.lang.String[])
Exception in thread main

How could I use sequence numbers with blob ?



and Tom said...

It really has nothing to do with the blob -- it is the use of a sequence in the WHERE clause of a query:


tkyte@TKYTE816> select s.nextval from dual;

NEXTVAL
----------
1

tkyte@TKYTE816> select s.currval from dual;

CURRVAL
----------
1

tkyte@TKYTE816> select * from dual where s.currval = 1;
select * from dual where s.currval = 1
*
ERROR at line 1:
ORA-02287: sequence number not allowed here


What you'll want to do is:

select s.nextval from dual;
(fetch that into a host variable in your java program)

insert into demo(id,theBlob) values (?,empty_blob() );
(bind in that host variable from the select from dual above)

select theBlob from demo where id = ? for update;
(bind in that host variable from the select from dual above)






Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Robert Boyle, July 11, 2002 - 6:30 am UTC

Tom, sorry for posting here, but this should be a simple follow on from this question.

Can you not use the RETURNING clause in the insert of this example to retrieve the sequence number? In other words, are you restricted to only getting the sequence number by select the sequence.nextval before you do the insert?

Thanks
Robert

Tom Kyte
July 11, 2002 - 7:36 am UTC

sure, in fact, they do not even need to get the sequence technically, just the blob.


They could have used returning on the blob column as well.

Robert, July 11, 2002 - 9:38 am UTC

Tom,
Can you give an example of this?

We tried this in our code before without much luck.(SQLJ) We even tried running it as an anonymous plsql block but still no luck. We never received an error, and the row inserted ok, but we were never successful in returning the sequence id (which we need to update the blob later on in the code - although according to your statement we wouldn't need that..would the blob indicator do the job?).

Sorry for the follow up, but being having a limited knowledge of Java I am trying to put what I know from PLSQL into practise in SQLJ and I know it doesn't always work.

Cheers
R.

Tom Kyte
July 12, 2002 - 7:38 am UTC 

Sure, in SQLJ, it would look like this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence t_seq;

Sequence created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace
  2     and compile java source named "returningTest"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  
  7  public class returningTest
  8  {
  9  public static void run() throws SQLException
 10  {
 11  int   id = 0;
 12  
 13      #sql { begin
 14             insert into t values ( t_seq.nextval ) returning x into :out id;
 15             end;
 16           };
 17  
 18      System.out.println( "java says " + id );
 19  }
 20  
 21  }
 22  /

Java created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> show errors
No errors.
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure returningTest
  2  as language java
  3  name 'returningTest.run()';
  4  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set serveroutput on
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output(100000)

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec returningTest;
java says 1

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec returningTest;
java says 2

PL/SQL procedure successfully completed.

Do you have example for jdbc?

Winston, April 17, 2003 - 9:28 pm UTC

Hi Tom,

Is there any way to directly retrieve the value from returning clause in java withou putting the "insert into ... returning" into a stored function? I tried something like this:

PreparedStatement ps = conn.prepareStatement( " insert into ttt values (tmp_seq.nextval) returning c1 into ? ");
ps.executeQuery();
rs = ps.getResultSet();
while (rs.next()) {
rs.getInt(1);
}

but I got "java.sql.SQLException: ORA-01008: not all variables bound
"


Tom Kyte
April 18, 2003 - 10:43 am UTC

conn.prepareStatement( "begin insert into
ttt values (tmp_seq.nextval) returning c1 into ?; end;");


will do that.

have to use callableStatement

winston, April 21, 2003 - 1:51 pm UTC

CallableStatement cs =
conn.prepareCall(
"begin insert into ttt values (tmp_seq.nextval) returning c1 into ?; end;");
int obj = 0;

cs.registerOutParameter(1, OracleTypes.INTEGER);
cs.execute();
obj = cs.getInt(1);

And I have benchmarked "returning clause" vs. 'insert + select seq from dual'. Using "returning clause" is Much faster and more scalable

missing JDBC method implementation ...

Klaus, September 08, 2003 - 10:33 am UTC

Hi all,

some questions about this topic, because the provided implementation is a bit tricky and not really obvious!

Why for example is the JDBC Standards defined method getGeneratedKeys not implemented for such purposes?(returning drawn Sequences-Id's while executing an Insert-Statement)

public ResultSet getGeneratedKeys() returns with a SQLException mentioning something like "feature not implemented!"

Why do we have to use an anonymous PL/SQL-Block, when
the SQL i'm executing is not PL/SQL-Block?

any ideas for future developments/corrections of the issue?

any comments/hints/etc are truly welcome!

kind regards
K:) UNGER




Tom Kyte
September 08, 2003 - 12:37 pm UTC

sorry, not really a java programmer -- I'm a database developer.

but -- we (oracle) don't have "generated keys" -- YOU (the developer) populated these keys explicitly. we cannot implement that which does not exist.

Another layer

George, December 10, 2003 - 11:01 am UTC

Suppose that you had a java program that called a stored procedure that contained an insert that you wanted to return the sequence num. to the java program (using the returning clause)

eg:

java calls the procedure
the procedure contains an insert that uses a sequence in the values clause, the java program needs that sequence number for future updates. What would the code look like for that?


Thanks for everything Tom, you're the man


Tom Kyte
December 10, 2003 - 3:47 pm UTC

java would prepare a callable statement:

begin p( ...., ? ); end;

where that last ? is the sequence as an out parameter and the ... are the rest of the inputs to the procedure to insert.

procedure would be:


procedure p( ....., p_seq OUT number )
is
begin
insert into t ( .... ) values ( .... ) returning ID into p_seq;
end;


Thanks!

George, December 10, 2003 - 4:47 pm UTC


blob insert examples in oracle provided examples

bob, May 06, 2004 - 9:28 am UTC

Tom,

LOB/jdbc docs specifically say that insert empty_blob(), followed by selecting for update the lob locator is the incorrect way to do it, that you should use "insert ... returning lob into ?" However, all the examples on otn and even the jdbc doc examples that I saw show the exact opposite approach. (insert followed by select for update)

I can't find the oracle doc I was reading that included two paragraphs highlighting the sequence of steps for inserting a blob the correct and incorrect way anymore, but shouldn't something like this work:

OracleCallableStatement cs =
conn.prepareCall(
"begin insert into table values (empty_blob()) returning theBlob into ?; end;"
);
cs.registerOutParameter(1, OracleTypes.BLOB);
ocs.execute();
oracle.sql.BLOB blob = ocs.getBLOB(1);
...usual jdbc blob loading stuff....

The doc mentioned that returning the lob locator made the "select for update" unnecessary.

Tom Kyte
May 06, 2004 - 9:33 am UTC

it is more efficient (less round trips) to insert/return

Yes, you can do the above (make sure to set that stupid "autocommit" 'feature' of jdbc OFF or it won't work.


You never needed the select for update if you just did the insert (you already have the row locked). You need the select for update whenever you retrieve the lob later in life (when you don't have the row locked) in order to modify it.

Inserting large blob through stored procedure interface

Tom, July 04, 2006 - 2:00 pm UTC

Hi Tom,

I'm in the middle of writing a piece of functionality which uses java to insert large binary documents into the database and I've hit a bit of a snag. I was happily following best practices and creating a stored procedure layer to encapsulate each of my logical transactions when I found nested deep within the jdbc developer's guide

<quote>
If you pass a BLOB to a PL/SQL procedure, the BLOB must be no bigger than 32K - 7. If you pass a BLOB that exceeds this limit, you will receive a SQLException.
</quote>

Given this, what would be your recommended approach to updating a blob column in the database?

I thought about writing the blob to a temp table then calling the stored procedure, but will this not lead to the [large] document being written to disk twice?

Equally, I considered inserting an empty blob and returning a blob handle from the stored procedure but I assume this would require me to grant insert and update on the table the blob is being inserted into rather than just granting execute on the package.

Is there any way to maintain the nice security layer a plsql interface provides and still have java insert blobs more than 32k?

Tom Kyte
July 07, 2006 - 9:12 pm UTC

have the blob return the newly created lob locator to the java program and let it "stream" the data in using whatever size it wants.

eg: java does not try to pass 2gig of data to Oracle, java calls the procedure and gets a "stream" - much like a file handle, well exactly like one - and "writes the data" to the stream.

or, if the security implications (need update on the COLUMN of that TABLE - grant update (column) on t to java_id;) don't work for you - you can provide you own "streaming" API - where the java app sends you up to 32k at a time plus the lob locator to write to.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here