Skip to Main Content
  • Questions

  • Export to CSV and store it as a blob column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sakthikumar.

Asked: September 29, 2021 - 10:04 am UTC

Last updated: October 21, 2021 - 1:32 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi Tom,

I have a need to export the data as csv from a table based on a query and store it in database column as blob.
I need a plsql procedure for this and the same will be called inside oracle scheduler.
Below are my challenges.

1. The table has over 350 columns and hardcoding column names inside plsql to get the csv is not practical.
2. The table has over 400 million rows and query may retrieve around 7 million rows.

I have already tried solution here https://asktom.oracle.com/pls/apex/asktom.search?tag=export-to-csv , because of the no. of columns, the solution is not working.

Sakthi.

and Connor said...

Check out this video



You might be thinking "But that just spools to a file".

But once you've spooled to a file, just use DBMS_LOB to load it from file into the database blob.

The scheduler can call SQL scripts directly to spool out your data.

https://connor-mcdonald.com/2018/06/20/the-death-of-utl_file/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sakthi, September 30, 2021 - 2:05 pm UTC

This is a great solution, Happy If I can implement this way.
But I am working on serverless ADW. How do I get the file and save it as a blob.
Chris Saxon
October 01, 2021 - 4:40 pm UTC

Are the source and target tables both in the same ADW instance?

If so, why do you need to create the CSV file first? Can you load the data straight into the target?

sakthikumar periyasamy periyasamy, October 08, 2021 - 3:26 pm UTC

We wanted to retrieve the results of the query and upload it as a csv in the table.
Reason is, we have an Apex Frontend, we give the users a download option to download the results of the query.
Connor McDonald
October 21, 2021 - 1:32 am UTC

You might be better using DBMS_CLOUD to unload the data to an object store file.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here