If you’re searching for “export Oracle BLOB”, the article, by Jeff Smith, titled “Exporting Multiple BLOBs with Oracle SQL Developer” using Oracle SQL Developer” is usually at the top of the search result. The SQL Developer features the Shopping Cart without using scripts to export BLOBs out of a database. I don’t want to go into detail as Jeff already explained well in his post what it is and how to use it. One main issue of using this approach is that sometimes you want the actual file names instead of the exported names. This can be overcome easily using a post-run script. I wrote this simple script in Python as it suites well with name manipulation. (I’m not a Python expert, but it is one of the programming languages that is very easy to learn.)
The script is just reply read from the FND_LOBS_DATA_TABLE.ldr file, which contains information about original filename and new exported filename (in the format of FND_LOBS_DATA_TABLExxxxx).
[code lang=”python”]
# Sample data
# 1889399|"CF.xlsx"|"application/octet-stream"|FND_LOBS_DATA_TABLE694b44cc-0150-1000-800d-0a03f42223fd.ldr|2014-05-20 12:11:41||"FNDATTCH"||"US"|"WE8MSWIN1252"|"binary"|{EOL} 1889403|"PriceList_quotation (20 May 2014) cust.xls"|"application/vnd.ms-excel"|FND_LOBS_DATA_TABLE694b4587-0150-1000-800e-0a03f42223fd.ldr|2014-05-20 12:18:02||"FNDATTCH"||"US"|"WE8MSWIN1252"|"binary"|{EOL} 1889807|"MS GROUP NORTH AMERICA INC1.pdf"|"application/pdf"|FND_LOBS_DATA_TABLE694b4613-0150-1000-800f-0a03f42223fd.ldr|||||"US"|"AL32UTF8"|"binary"|{EOL}</pre>
# 1st = File ID (Media ID)
# 2nd = Original File Name
# 4th = Exported File Name
# The remaining information is not relevant.
[/code]
The script separates all information, which is stored in a single line, by string {EOL} into multiple lines. It continues to split into each column based positions. The information we’re interested in is in the 1st, 2nd and 4th position. It then just calls the operating system to rename the file.
The content of the script rename.py as follows:
[code lang=”python”]
from sys import argv
import string
import shutil
import os
# Script to rename exported BLOB files from Oracle SQL Developer tool
#
# Pre-requisite: Python 3.x https://www.python.org/downloads/
#
# Execution:
# (1) Copy the script to the folder containing mapping file – "FND_LOBS_DATA_TABLE.ldr" and all exported files.
# (2) Execute the script as follows
# C:\&gt; cd deploy
# C:\&gt; rename.py FND_LOBS_DATA_TABLE.ldr
# Take parameters
script, filename = argv
# Open file in read-only mode
file = open(filename, ‘r’, encoding="utf8")
# Sample data – everything is stored in one line.
# 1889399|"EPR – CF.xlsx"|"application/octet-stream"|FND_LOBS_DATA_TABLE694b44cc-0150-1000-800d-0a03f42223fd.ldr|2014-05-20 12:11:41||"FNDATTCH"||"US"|"WE8MSWIN1252"|"binary"|{EOL} 1889403|"PriceList_quotation_murata (20 May 2014) cust.xls"|"application/vnd.ms-excel"|FND_LOBS_DATA_TABLE694b4587-0150-1000-800e-0a03f42223fd.ldr|2014-05-20 12:18:02||"FNDATTCH"||"US"|"WE8MSWIN1252"|"binary"|{EOL} 1889807|"MGS GROUP NORTH AMERICA INC1.pdf"|"application/pdf"|FND_LOBS_DATA_TABLE694b4613-0150-1000-800f-0a03f42223fd.ldr|||||"US"|"AL32UTF8"|"binary"|{EOL}
# 1st = File ID (Media ID)
# 2nd = Actual/Original File Name
# 3rd = File Type
# 4th = Exported File Name
# The remaining = Not relevant
# First, split each by string {EOL}
splitted_line = file.read().split(‘{EOL}’)
# For each splitted line, split into each word, separated by |
for s in splitted_line:
# Split by |
splitted_word = s.split(‘|’)
# If reaching the last line, which contains only [”], exit the loop.
if len(splitted_word) == 1:
break
# The Original file name is in the 2nd word (list position #1)
# Strip out double quotes and leading & trailing spaces if any
orig_name = splitted_word[1].strip(‘"’).strip()
# The Exported file name is in the 4th word (list position #3)
exported_name = splitted_word[3].strip() # Strip out leading & trailing spaces if any
# We plan to prefix each file with its unique FILE_ID.
# This is to avoid file name collision if two or more files have the same name
# Also, strip out leading & trailing spaces if any
file_id = splitted_word[0].strip()
# Rename file
# Adjust the new file name according to your needs
os.rename(exported_name, file_id + ‘_’ + orig_name)
[/code]
After unzipping the deploy.zip, which is the default exported file from SQL Developer, copy the rename.py into this unzipped folder.
C:\> cd deploy C:\> dir
02/23/2016 07:57 PM 2,347 rename.py 02/23/2016 07:57 PM 34,553 export.sql 02/23/2016 07:52 PM 1,817 FND_LOBS.sql 02/23/2016 07:57 PM 276 FND_LOBS_CTX.sql 02/23/2016 07:57 PM 614 FND_LOBS_DATA_TABLE.ctl 02/23/2016 07:52 PM 88,193 FND_LOBS_DATA_TABLE.ldr 02/23/2016 07:57 PM 78,178 FND_LOBS_DATA_TABLE10fa4165-0153-1000-8001-0a2a783f1605.ldr 02/23/2016 07:57 PM 27,498 FND_LOBS_DATA_TABLE10fa4339-0153-1000-8002-0a2a783f1605.ldr 02/23/2016 07:57 PM 17,363 FND_LOBS_DATA_TABLE10fa43c5-0153-1000-8003-0a2a783f1605.ldr 02/23/2016 07:57 PM 173,568 FND_LOBS_DATA_TABLE10ff189d-0153-1000-8219-0a2a783f1605.ldr : :
C:\> rename.py FND_LOBS_DATA_TABLE.ldr
C:\> dir
02/23/2016 07:57 PM 2,347 rename.py 02/23/2016 07:57 PM 34,553 export.sql 02/23/2016 07:52 PM 1,817 FND_LOBS.sql 02/23/2016 07:57 PM 276 FND_LOBS_CTX.sql 02/23/2016 07:57 PM 614 FND_LOBS_DATA_TABLE.ctl 02/23/2016 07:52 PM 88,193 FND_LOBS_DATA_TABLE.ldr 02/23/2016 07:57 PM 78,178 689427_DATACOM SOUTH ISLAND LTD.htm 02/23/2016 07:57 PM 27,498 698623_lincraft.htm 02/23/2016 07:57 PM 17,363 772140_275131.htm 02/23/2016 07:57 PM 173,568 3685533_RE 新办公室地址.MSG
: :
Thank you for this. This is very helpful!