temporary table inaccessible?

Hi,

I am trying to create a temporary table, insert an empty blob into it, and then select the empty blob from the table into a blob object. I am doing this using a plsql block that Java calls, using JDBC.

The creating and inserting parts execute, but when I do the select on the temporary table, it says the table or view does not exist.

What's going on here? What am I doing wrong?

Here's the code I'm using:

CallableStatement plsqlblock = db.prepareCall(
"declare\n"+
"lb blob;\n"+
"begin\n"+
"execute immediate 'create temporary table tmp_table (blobid NUMBER unique, blob_col BLOB)';\n"+
"execute immediate 'commit'; \n"+
"execute immediate 'insert into tmp_table values (1, empty_blob())';\n"+
"execute immediate 'commit'; \n"+
"select blob_col into lb from tmp_table where blobid = 1;\n"+
"end;");
plsqlblock.execute();

Moved, from General Database Discussion forum.

I think you'll have more luck getting an answer on this in the Oracle forum

As i understand your syntax is wrong, if you are using Oracle, you must write like that
'CREATE GLOBAL TEMPORARY table ........'
if it does work then return back thanks to me, otherwise try to create any temporary table without BLOB type and insert some data.

This mail is related to my last email. Just make the following modification in your PL/SQL block.

declare
lb blob;
begin
execute immediate 'create global temporary table tmp_table (blobid NUMBER unique, blob_col BLOB) on commit preserve rows';
execute immediate 'insert into tmp_table values (1, empty_blob())';
execute immediate 'commit';
execute immediate 'select blob_col from tmp_table where blobid = 1' into lb;
end;
/

after the insert, DON'T make the "commit", just do the select.
Good luck