#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    2
    Rep 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();
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Moved, from General Database Discussion forum.

    I think you'll have more luck getting an answer on this in the Oracle forum
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11

    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.
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11

    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;
    /
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    1
    Rep Power
    0
    after the insert, DON'T make the "commit", just do the select.
    Good luck

IMN logo majestic logo threadwatch logo seochat tools logo