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.

Now what I want!
Not what I want!

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 will always start with 'INSERT' and 'Clipboard' - woohoo!
Now I will always start with ‘INSERT’ and ‘Clipboard’ – woohoo!

Now, I can also go INTO the preferences for each of the different formats to save me a few more clicks.

I prefer pointy hats (^) for my delimiters, don't you?
I prefer pointy hats (^) for my delimiters, don’t you?

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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

55 Comments

    • Thanks for the article – this will save me tons of time from dinking with settings every time i export!

  1. Gaby Fröhlich Reply

    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.

    • thatjeffsmith

      Nope. You can export to say excel or csv and then use that to import to your access DB I suppose.

    • Gaby Fröhlich

      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

    • thatjeffsmith

      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’

  2. 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?

    • thatjeffsmith

      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!

  3. 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?

  4. 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?

    • Wolfgang

      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. 😉

    • thatjeffsmith

      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.

    • Wolfgang

      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!

  5. Oldie but goodie – exactly what I was looking for and had not dug that deep in the Preferences, Thansk Jeff

    • thatjeffsmith

      You’re VERY welcome Alan – thanks for the feedback and taking the time to leave a note!

  6. 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.

  7. Thank you so much for this tip. All that repeated clicking was driving me crazy.

  8. Hi,

    Can anyone tell me, how to export the results of sql query to excel in sql developer?

  9. 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.

  10. Bob Gajewski Reply

    Is there a way to default the query name as the file name when exporting, instead of the generic ‘export’?

    • thatjeffsmith

      queries have names?

      there’s no way, although you could use sqlformat and spool and then name the file yourself in the script

    • Bob Gajewski

      I guess I meant the script name … thanks!

  11. 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?

  12. 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

    • thatjeffsmith

      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?

  13. 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

    • thatjeffsmith

      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?

  14. 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…

    • thatjeffsmith

      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.

  15. 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?

    • thatjeffsmith

      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.

Write A Comment