Home » SQL & PL/SQL » Client Tools » Exporting CLOB data in a file and importing using SQL Loader
Exporting CLOB data in a file and importing using SQL Loader [message #642583] Tue, 15 September 2015 03:03 Go to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Hi All,

I am trying to import CLOB data from one instance to another instance. Following is my table structure

CREATE TABLE TEST_CLOB
(ID NUMBER,
  ENTITY_NAME VARCHAR2(20),
  XML_FIELD CLOB,
  HTML_TAGS CLOB
 )


I am trying to export the data from above table in csv or txt file but the clob fields are not exporting properly. Also I want to import the same file using the same export file by SQL loader.

Please let me know how should I export and import the table data including clob fields.

Thanks & REgards,
Amit Sonar
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642587 is a reply to message #642583] Tue, 15 September 2015 03:29 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
What do you mean by "export"? How are you trying to do this?
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642589 is a reply to message #642587] Tue, 15 September 2015 04:06 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Hi John,

Thank you for reply. I am trying to export the data in csv or excel file using third party tool like toad. However, I am not finding the solution. Is there any other way to achieve it?

Thanks & Regards,
Amit Sonar.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642590 is a reply to message #642589] Tue, 15 September 2015 04:09 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Data Pump would seem to be the obvious answer. Why use a third party product?

Why go via CSV files? Excel is impossible; how many LOBs do you think you can store in a spreadheet?
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642594 is a reply to message #642590] Tue, 15 September 2015 04:19 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Yes! datapump is an option. However, I am trying to do it via file. I tried taking spool of the data including LOBS columns but it didnt work.

is there any other way to export?

Thanks & Regards,
Amit
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642596 is a reply to message #642589] Tue, 15 September 2015 05:41 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
amit.sonar

I am trying to export the data in csv or excel file using third party tool like toad. However, I am not finding the solution.


- in Schema Browser, right-click table name. Select "Export Data"
- set "Excel Instance" as the Export format
- remove checkbox from CLOB in "Columns to exclude"
- click "OK"

/forum/fa/12836/0/

That's how it goes; however, as you've already been told, you might have problems if there's a lot of data to be exported that way.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642611 is a reply to message #642596] Tue, 15 September 2015 09:08 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Thanks Littlefoot for your reply. I think this will help me and could resolve the issue. However, It won't work for big data as taking export of the data will not be feasible.

Is there any other way to it except datapump?

Thanks & Regards,
Amit Sonar.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642614 is a reply to message #642611] Tue, 15 September 2015 09:17 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you trying to avoid datapump?
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642649 is a reply to message #642614] Wed, 16 September 2015 04:53 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Hi Cookiemaster,

Sometimes to take datapump of online data require so many approvals and it is time consuming. To save the time, I am finding the other way to do the same.

Thanks & Regards,
Amit Sonar
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642655 is a reply to message #642649] Wed, 16 September 2015 06:27 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the approval process to use datapump to get the data is time consuming but the approval process to get the same data some other way isn't then there is something seriously wrong with the process.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642748 is a reply to message #642655] Fri, 18 September 2015 02:42 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
What I mean, I can query on the data and by exporting only data if the work is getting done then I dont have to seek any consent.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642771 is a reply to message #642748] Fri, 18 September 2015 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
why not use DBLINK?
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642791 is a reply to message #642649] Sat, 19 September 2015 06:34 Go to previous messageGo to next message
EdStevens
Messages: 983
Registered: September 2013
Senior Member
amit.sonar wrote on Wed, 16 September 2015 04:53
Hi Cookiemaster,

Sometimes to take datapump of online data require so many approvals and it is time consuming. To save the time, I am finding the other way to do the same.

Thanks & Regards,
Amit Sonar



I agree with Cookiemonster (msg #642655). datapump is just another client tool. As far as the database is concerned it is no different than sqlplus or Toad or SQL Developer or some Office app using ODBC. It is just another client process, connected to the database with whatever credentials (username/schemaname) you provide. It gives you no privileges that you don't have explicitly granted anyway, and unless you are doing a FULL export (which you are not) does not require any special privs.

Besides, the above statement regarding approvals seems to contradict your earlier statement, "datapump is an option. However, I am trying to do it via file."

And what did you mean by "do it via file"? ANY kind of export produces a file. That is the whole purpose of export. And Toad isn't bringing any "magic" to the process. There is no magic. Toad is just a GUI front-end. Everything it does with the database is done by issuing SQL. And while I haven't used TOAD in years, I strongly suspect that even its 'export' is just invoking oracle's own exp utility behind the GUI.
Re: Exporting CLOB data in a file and importing using SQL Loader [message #642796 is a reply to message #642791] Sat, 19 September 2015 09:17 Go to previous message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suspect that the major issue here is the result (export file's) location. EXPDP creates file on a server, which means that person - who performs export - has to know directory name, as well as have access to that directory. If your DBA doesn't particularly like you (for some reason) or you maybe aren't even supposed to export any data, it is painful to ask the DBA for required privileges. Therefore, you look for another way of performing export so that you avoid DBA's assistance.
Previous Topic: ORA-00942: table or view doesn't exist.
Next Topic: forms and reports Windows 8.1
Goto Forum:
  


Current Time: Fri Oct 19 19:33:26 CDT 2018