I was testing a reported bug in SQL Developer today – so the bug I was looking for wasn’t there (YES!) but I found a different one (NO!) – and I was getting frustrated by having to check the same boxes over and over again.
What I wanted was INSERT STATEMENTS to the CLIPBOARD.
I’m always doing the same thing, over and over again. And I never go to FILE – that’s too permanent for my type of work. I either want stuff to the clipboard or to the worksheet. Surely there’s a way to tell SQL Developer how to behave?
Oh yeah, check the preferences
So you can set the defaults for this dialog. Go to:
Tools – Preferences – Database – Utilities – Export
Now, I can also go INTO the preferences for each of the different formats to save me a few more clicks.
So, spend a few minutes and set each of these to what you’re normally doing and save yourself a bunch of time going forward.
59 Comments
Is it possible to save export wizard settings, so they can be reused/edited as needed?
For doing Cart exports, yes.
For regular grid exports, set your defaults for each export type as close as you can to most commonly used scenarios.
Thank you. When using the cart, is it possible to select only certain columns and change down the road as needed? What would you recommend for this? I am looking for a reusable approach via the GUI.
We made automation possible for almost everything for an export…EXCEPT for the selection of columns. The workaround would be to have a VIEW you could use.
Thanks, Jeff! I wish I’d looked for this a long time ago!
Thanks for the article – this will save me tons of time from dinking with settings every time i export!
I am just checking if it is possible to export data from Oracle SQL Developer to an existing Access Database. I want to add the data to an existing table in this database.
This was no problem in TOAD but I don’t find the option in Oracle SQL Developer.
Nope. You can export to say excel or csv and then use that to import to your access DB I suppose.
Thank you for your answer.
I used Toad since years now and scheduled hundreds of jobs via Toad, exported data to Excel, Access and csv.
One important thing was that we could append data to existing tables in Access.
I’m checking right now if it is possible to export data to Excel with schedule jobs in Oracle SQL Developer.
I didn’t find a solution. Can you give me a hint?
Regards
Gaby
you can use the Cart to define tables to be exported to Excel
you can use SDCLI exe in the bin directory at the command line to kick off a sqldev session to process cart operations
you can use your OS scheduler to do a .bat script to run the command – so, i’m going to say ‘yes, you can do this’
I’m exporting records to an regular excel (.xls) file and the dates are being treated as characters and not as a date in the spreadsheet so users can’t sort the data correctly. I’ve tried Excel’s format cells to change them to dates but the only things that seems to work is doubling-clicking on each field then moving to the next field.
Is there a way make sure the date fields export to Excel as dates and not as character fields?
Formatting the Excel columns to dates should work, but that’s an Excel thing.
I have a standing ER to export dates as dates in Excel.
I just wanted to thank you for the posts! I didn’t need the help that you were asked about, but while I was reading how you fix this other issue, I found where to change the default export location on your picture! Thanks again!
Good to hear – thanks for the feedback!
I regularly export some PL/SQL packages on different databases (Dev, Test, Prod) for documentation, comparison and further development.
It would be helpful, if I could store and reuse the settings in the export tool. Mainly the selection of the right packages again and again is annoying.
Is it possible to store the settings for exports?
Look at the cart.
Thank you for quick answer! I did’nt know this feature so far. Very useful!
Very useful info – thank you! Unfortunately, there seems to be no switch that influences the format of numbers when exporting to Excel (xlsx).
I’m having an issue where Excel displays a number as 1E+15 while also turning the last digit into a 0, i.e. 1000000282586396 becomes 1000000282586390. The column the data is coming from is defined as NUMBER(19,0). Another column which is defined as VARCHAR2(23 CHAR) contains an even longer number, but is displayed fine.
What I am looking for, I guess, is a switch that tells the export to treat all NUMBER columns as if they were VARCHAR2. Is there such an option?
No
Too bad… But anyway, thanks for answering so quickly! The issue, as it is, is one of Excel rather than of SQLDeveloper.
You would’nt happen to have a hint for a workaround, though? The only idea I can come up with – “ALTER TABLE MODIFY (column VARCHAR2(15));” before and back to NUMBER after the export – seems a bit drastic, especially on a production DB. 😉
fix it with SQL? Use a query to o the export, so use to_char calls?
I just ran select to_char(1000000282586396) from dual and exported to Excel. The 6 was preserved in the Excel file.
Thanks again, Jeff, that suggestion actually does solve my problem!
Thank you for quick answer! I did’nt know this feature so far. Very useful!
Oldie but goodie – exactly what I was looking for and had not dug that deep in the Preferences, Thansk Jeff
You’re VERY welcome Alan – thanks for the feedback and taking the time to leave a note!
You just saved me a ton of time, thanks!
Nice tool, but… How can I change the decimal separator for exported numeric columns? I got no effect from replacing comma by point in Preferences > Database > NLS > Decimal Separator.
Thank you so much for this tip. All that repeated clicking was driving me crazy.
Glad to hear that, Chad. Thanks for the feedback!
Hi,
Can anyone tell me, how to export the results of sql query to excel in sql developer?
ummmm, yeah
Is there any where to control BLOB inclusion? I’m exporting data to CSV and it keeps putting ‘(BLOB)’ in the relevant column rather than including the ASCII data that is in the column.
BLOBs are binary files – you just happen to only be storing text in yours. So in a query, convert them to text, and then export the results of your query. Or create a view, and export from there.
The only way to export BLOBs from SQL Developer is into a SQL*Loader format, and then each BLOB will come out as a file so it can be loaded up again somewhere else.
Is there a way to default the query name as the file name when exporting, instead of the generic ‘export’?
queries have names?
there’s no way, although you could use sqlformat and spool and then name the file yourself in the script
I guess I meant the script name … thanks!
hello, when you export excel, developer automatically generate another sheet with SQL script, is it possible to configure developer to prevent generation of this sheet?
yes, there’s a checkbox on the export dialog to include the SQL statement, disable that
TY
Hi Jeff,
I do not see “include the SQL statement” option neither in your images nor in my sql developer software (v4.0). Can you please give me the full path?
Thanks.
Look at the Excel options, my screenshots are showing INSERTs
I do not see that option anywhere. Sorry, to bother you.
Thanks.
Wow!! Fantastic…
I appreciate you, Man. Thanks.
I would never have found that option. Thanks!
No worries Brian, glad you found this helpful!
any idea why the thing manages to create the export file on the pc and then completely fail to copy anything into it – apart from it being utter crap to start with
I would guess user error, but you sound like a reasonable, and forgiving user.
I need more info.
What version are you running – SQLDev, JDK, and OS?
What kind of export are you trying to generate – CSV, Excel, Inserts?
How many rows are you exporting – 1, 1,000,000, a bazillion?
Thx
How much data will be exported by oracle default??
All of it.
Please is there any way to export rows selected in Query Result grid?
Not today.
Whenever I export the query results to a CSV ,I find that SQL developer creates a seprate worksheet and puts the query there and name the worksheet as SQl,which is definitely nice as without the query someone else cannot do the validation.I want to know if this is a default option in SQL developer
it’s setup to run as you have defined in the preferences, tools > preferences > database > utilities > export > CSV
On export it sounds like you have Save As set to ‘Worksheet’
You can have it go to a file or the clipboard instead.
What exactly are you looking to change?
Hmm, why can’t I set that selecting ‘excel’ export should automatically select “single file”? I end up with “xls/worksheet” combo because of my current “insert/worksheet” defaults, but that makes very little sense…
Yeah, I don’t know. We could make those formatter styles like PDF and Excel only valid for ‘Single File’ I suppose. For now, you’ll need to pick the format you use most and adjust the default. Or use the /*insert*/ trick and copy up to the worksheet and set the default to File for your Excel work.
Well, that’s definitely nice. I usually end up exporting to the worksheet, so it would save me some hassle. Can’t wait for 4.0 to become stable enough to use at work! We use packages of 10k+ lines, so breadcrumbs is a must.
On a somewhat related note, did export hints get fixed? That is, if I specify “select /*csv*/…”, would the delimiters and enclosures I specified in settings be applied?
We’ve got an outstanding bug on the breadcrumb/procedure editor stuff – once that is fixed, you’ll be good on version 4 🙂
The export format hinting hasn’t been enhanced to go beyond the defaults in the preferences…yet.