Defaults for Exporting Data in Oracle SQL Developer

thatjeffsmith SQL Developer 34 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 34

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

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  2. 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.

    1. thatjeffsmith Post
      Author
  3. Is there a way to default the query name as the file name when exporting, instead of the generic ‘export’?

    1. thatjeffsmith Post
      Author
  4. 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?

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
    1. thatjeffsmith Post
      Author
  5. 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

    1. thatjeffsmith Post
      Author

      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?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  6. 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

    1. thatjeffsmith Post
      Author

      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?

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *