This discussion is archived
8 Replies Latest reply: Nov 6, 2013 5:37 PM by pedrobl RSS

Exporting CLOBS BLOBS with SQL developer

603548 Newbie
Currently Being Moderated
Has anyone had any luck doing this yet... I still get 'Error while registering Oracle JDBC Diagnosability MBean.'
while attempting this. Would love to export CLOBs BLOBs etc with SQL Developer because I have a character set conversion to Unicode from American and would love to avoid the pain/fun of rolling my own code to export binary data from the old database.
  • 1. Re: Exporting CLOBS BLOBS with SQL developer
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    What version of SQL Developer are you using?

    How exactly are you trying to export them?
  • 2. Re: Exporting CLOBS BLOBS with SQL developer
    603548 Newbie
    Currently Being Moderated
    Latest version 3.2.1 trying to use the export utility that can do sql loader or inserts...
  • 3. Re: Exporting CLOBS BLOBS with SQL developer
    Barry McGillin Journeyer
    Currently Being Moderated
    This looks to be a problem referencing drivers from you ORACLE_HOME/jdbc/lib directories
    You can try unsetting your oracle home or starting SQL Developer in a place where there is no Oracle home set.
    Otherwise, You can try taking the ojdbc.jar and putting it in the OH/jdbc/lib directory.

    B
  • 4. Re: Exporting CLOBS BLOBS with SQL developer
    603548 Newbie
    Currently Being Moderated
    I went to the sqldeveloper directory under the home directory and used the bat file sqldeveloper.bat to start the app, it used the jdk which was included in the product and said it was the version 1.6xxxx and not the required 1.7xxx.
    I then unset my ORACLE_ HOME, SID environment variables and still get the same output

    Notice that the attribute SCHUNK is a BLOB and the DDL picks that up, but the generated INSERT puts CUSTID in its place instead.
    I use a modified rawtohex hextoraw method to accomplish this but would rather have the tool do. I believe that this is a bug and needs to reconize BLOB/CLOB fields and either it needs to use DBMS_LOB
    or convert it to hex on the export and convert back from hex to raw on the inport
    Example

    CREATE TABLE "TESTUSER"."TEST"
    (     "CHATID" NUMBER(19,0),
         "SUBSESSIONID" NUMBER(10,0),
         "SES_TIME" NUMBER(10,0),
         "CUSTID" NUMBER(10,0),
         "SCHUNK" BLOB
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS"
    LOB ("SCHUNK") STORE AS BASICFILE (
    TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
    NOCACHE LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
    REM INSERTING into TESTUSER.TEST
    SET DEFINE OFF;


    -- Generated from test data puts "4" instead of dumping the file or converting it to hex with a hextoraw() function surrounding it
    Insert into TESTUSER.TEST (CHATID,SUBSESSIONID,SES_TIME,CUSTID) values (91,33039,2924500,4);
  • 5. Re: Exporting CLOBS BLOBS with SQL developer
    603548 Newbie
    Currently Being Moderated
    Currently I am successfully generating INSERTS from PL/SQL

    INSERT INTO test (TalkID, SessionID, custID, session_time, sessionBlob) VALUES (131,33039,2944500,4,hextoraw('7F1087480E9B36281A01D366F90340353A3048603F9FFF40BCDF0F400388996C'));


    I realize with the DBMS_LOB pl/sql package there are many ways to do this... however if you look at the doc on how to convert from other database the dump HEX and the hextoraw() function is recommended and if you notice there is no field in this table that describes what kind of file it is or anything like that. So what file extension should I put on this? Exactly! So I figure since I have dump the BLOB/CLOBS without knowing what the data is I use the rawtohex and hextoraw and it works nicely, at least the characters in hex are exactly the same.

    I would rather have a tool like SQL developer do this, it does export regular data as Inserts quite well.

    Edited by: user600545 on Nov 4, 2012 11:46 AM
  • 6. Re: Exporting CLOBS BLOBS with SQL developer
    rp0428 Guru
    Currently Being Moderated
    >
    Currently I am successfully generating INSERTS from PL/SQL

    INSERT INTO test (TalkID, SessionID, custID, session_time, sessionBlob) VALUES (131,33039,2944500,4,hextoraw('7F1087480E9B36281A01D366F90340353A3048603F9FFF40BCDF0F400388996C'));
    >
    You are just going to confuse things if you start using different code in different answers. Edit your post and use the same table structure for your replies. You are now using columns that don't exist in the code you posted earlier so how do we know you are even using the same table or server?
    >
    I went to the sqldeveloper directory under the home directory and used the bat file sqldeveloper.bat to start the app, it used the jdk which was included in the product and said it was the version 1.6xxxx and not the required 1.7xxx.
    >
    Under what 'home' directory? The database home?

    What 'required 1.7xxx' are you referring to? Java 1.7xxx is not only NOT required it is NOT recommended.

    So please start over and provide information about what tools you are actually using and what DB you have, where it is installed.

    1. sql developer version? You earlier said 'Latest version 3.2.1' - is that the version you are using? Is it the ONLY version on the machine?
    2. what is the complete folder path to that version of sql developer?
    3. what is the complete path and name of the file you are now using to launch sql developer?
    4. is there an Oracle installation on the same machine? How many databases? what is the Oracle home full path?
    5. what ojdbcxxx.jar version are you using?
    6. how are you connecting to the database? OCI or thin driver?
    7. what is the os version and platform?
  • 7. Re: Exporting CLOBS BLOBS with SQL developer
    603548 Newbie
    Currently Being Moderated
    I have been exporting the Blobs/Clobs with the SQL Loader option, however it doesn't chunk meaning it can only go up to 32K characters in size and no greater.
    I am using the latest version, I believe its 3.2.0.3 or whatever is directly downloadable from this site. So for Blobs/Clobs greater than 32K ( Clobs have a problems with 4K
    when trying to write them out so one much chunk in 4K intervals ). It would be great is SQL Developer had the capability to handle Blobs/Clobs of any size within reason by
    "Chunking" and have good performance too. The reason why I was interested in the Insert Statement creation because I was interested in converting this to HEX.
    HEX is the best way to get around character set conversions and I am working with a client that has dirty binary data in their USA7ASCII database that they want to
    migrate to Unicode. I realize that if the dirty data is exported/imported in HEX that the dirty data will look differently in Unicode than it did in the American character set
    by virtue of the character set interpretation, however raw HEX wise they would be identical. So the plan is to use SQL Loader with the cp1252 ( windows western char set )
    which gave better results then UTF8/UTF-8 charset, the unloader in SQL D forces the user to pick a char set instead of having a dump to HEX option which would preserve the
    integrity of the original characters and not have them be exposed to conversions. For larger Blob/Clobs another routine will be employed and then the SQL Loader Blob/Clob will
    be overwritten/renamed with the new full sized Blob/Clob versions and then the SQL Loader script produced from the SQL Developer will be run to import them.
  • 8. Re: Exporting CLOBS BLOBS with SQL developer
    pedrobl Newbie
    Currently Being Moderated

    I'm aware this message it's a little old, but as the problem still exists,someone may find it useful.

     

    I've successfully exported and imported clob fields using sqldeveloper in twodifferent ways:

    1. If you only have a few tables this probably is your method. Using the default Tools > Database Export on tables with CLOB fields skips those fields, but if you run a select * from that same table, right click on the Query Results pane and select Export... (Inserts) the CLOB fields are included, and as noted by previous posts, will include 32K data.
    2. Using loader files (in multiple files). This was trickier, as the content of the clob files is stored in multiple files and I found no simple way of loading it. Instead, I loaded the name of the files containing the data for each field, and load it using a procedure. Let me know if anyone is interested in the procedure.

     

    Method 1 has the added difficulty that the single quote characters are not escaped (''), and need to be done manually. HTH,

     

    Pedro.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points