Oracle Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesOracle Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 3rd, 2003, 11:07 AM
Corrine123 Corrine123 is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 2 Corrine123 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question 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();

Reply With Quote
  #2  
Old September 5th, 2003, 10:57 AM
rycamor rycamor is offline
Gödelian monster
Dev Shed Regular (2000 - 2499 posts)
 
Join Date: Jul 1999
Location: Central Florida, USA
Posts: 2,306 rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60
Moved, from General Database Discussion forum.

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

Reply With Quote
  #3  
Old September 15th, 2003, 11:10 AM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 10
temporary table inaccessible

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.

Reply With Quote
  #4  
Old September 15th, 2003, 08:36 PM
shafique shafique is offline
Senior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Canada
Posts: 305 shafique User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 10
temporary table inaccessible?

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;
/

Reply With Quote
  #5  
Old October 7th, 2003, 04:55 PM
maurito maurito is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2003
Posts: 1 maurito User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
after the insert, DON'T make the "commit", just do the select.
Good luck

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesOracle Development > temporary table inaccessible?

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap