Hello, in the past I’ve been able to use the query below to export file attachments stored in a Clob field. I would right click on the ls_value field, select Export Blobs (Longs, Raw) and would export to the destination I would select. Now Toad exports between 2,000 - 3,000 attachments and stops or exports blank files. I need to export over 700,000 attachments. Does anyone have any suggestions?
select ls_value, ‘(’ || pad.pr_id || ') ’ || substr(s_value, instr(s_value, ‘’, -1, 1)+1, length(s_value) - instr(s_value, ‘’, -1, 1)) nme
from pr_addtl_data pad, data_fields df
where PAD.DATA_FIELD_ID = df.id
and df.name = ‘File Attachment(s)’
and PAD.PR_ID < 500010
What version of Toad are you on? Were you on the same version when it worked? If not, did something else change between now and then? Are you sure there is data in all rows the column that you are exporting? CLOBs can get really big - when you say it stops, are you sure it’s not just working on a really big one?
That feature was renamed to “Single Column Export” several versions ago… I just tested it in the newest version and exported about 300,000 rows and it worked fine for me. I’ll retry in your version once I know what that is.
-John
If you are on 11.6 or older (sounds like you are) then watch your memory as it exports - if memory usage is going up and you are running out of memory before it completes, then upgrading to Toad 12.0 or newer will solve the problem. 12.0 is when I redid that window and renamed it to “Single Column Export”. You won’t run out of memory in the new version.
Hi John, thank you very much for the information. We are on 11.5.0.56. I did notice the memory was running out. I tried this on my laptop and also on Windows servers a few times. I’ll see if I can get a newer version and will try that. Thanks again!!