I have a requirement to export an extremely large file using the pipe delimiter.
I find that for extremely large files, SQLCL works much better than SQLDeveloper, in that, it doesn't crash or hang ;-).
It's a pretty good tool in many other ways, but I digress.
I see a /*delimiter*/ option for SQLCL but I don't know how to set the delimiter to pipe in SQLCL.
See this link for an explanation of the formats available using a 'comment' in the query
Using the loader format is close to what I need but it leaves out the headings and I'm loathe to add another query just for headings due to the nature of what I'm doing.
I don't understand that at all. What does 'the nature' of what you are doing have to do with it?
Adding a simple query to get the headers is SIMPLE compared to unloading large amounts of data without error. You only need the headers once so you can easily store them in a file and just concatenate that one line file eveytime you export.
>>I see a /*delimiter*/ option for SQLCL but I don't know how to set the delimiter to pipe in SQLCL
You can't. If you want to change up the delimiter, it's back to SQL Developer. So not to go off-topic, but I'd like to help you get it working there as well. If it's hanging or crashing - two very different things - we should find out why and make sure it's not a bug.
True, adding a simple query is indeed not difficult but I'm trying to avoid it because primarily I want to use inbuilt functionality as much as possible and avoid having to change queries just because of different outputs. Also, there are a ton of tables with a lot of columns and while you can easily generate headers using SQL Developer or SQL itself, I'll try to avoid it if I can.
I'll try and quantify the issue. I've had some luck today by adding AddVMOption -Xmx2048M to sqldeveloper.conf so I'll continue to monitor the situation.
Some of the files I'm creating are 800MB plus and SQLDeveloper would jbecome unresponsive after a while whereas SQLCL would happily chug along. I don't know if it's the overhead of all the things that SQLDeveloper is doing. Typically I have 3-4 tabs open and looking at it now, my SQLDev is using 854MB.
1 person found this helpful
SQLcl is definitely doing less, it's only a 12MB download.
However, it should still be able to generate very large export files though. I've generated multi-gig files when exporting data in the cart to SQL*Loader formats - and it's basically the same code used to do delimited format.