Mostly Making oracle easy, mostly:
Search for:

Ask A Question

Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  1. I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
  2. I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
  3. I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.

Go!

7,793 Comments

  1. HoB Reply

    Hello Jeff,

    when I Export a result set in SQL Developer as INSERT statements I get following result:

    Insert into EMP (EMPNO, ENAME, SAL) values (‘7839′,’KING’,’5000′);
    Insert into EMP (EMPNO, ENAME, SAL) values (‘7698′,’BLAKE’,’2850′);

    The number are in single quotation marks. When I insert the rows in sqlplus everything is fine.
    But it does not look right to insert numbers with single quotation marks.
    How can we configure SQL Developer to avoid single quotation marks in Export?

    Thanks & regards
    HoB

    • thatjeffsmith Post Author

      Version of SQLDev?

      Easiest explanation is your table has those columns defined as text fields.

    • HoB

      Hello Jeff,

      The table is EMP and the column EMPNO and SAL are NUMBER type
      SQL Developer is Version 18.3.0.277

      Thanks
      HoB

    • thatjeffsmith Post Author

      v19.2, querying HR.EMPLOYEES

      SELECT /*insert*/ * FROM employees
 
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,COLUMN1) VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',to_date('23-APR-98','DD-MON-RR'),'SA_REP',17251.56,0.2,149,80,NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,COLUMN1) VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',to_date('24-MAY-99','DD-MON-RR'),'SA_REP',14380.48,0.15,149,NULL,NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,COLUMN1) VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',to_date('04-JAN-00','DD-MON-RR'),'SA_REP',12739.85,0.1,149,80,NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,COLUMN1) VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876',to_date('24-JAN-98','DD-MON-RR'),'SH_CLERK',6587.5,NULL,120,50,NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID,COLUMN1) VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877',to_date('23-FEB-98','DD-MON-RR'),'SH_CLERK',6382.43,NULL,120,50,NULL);
    • HoB

      Hello Jeff,

      we found out it is not related to the SQL Developer version.
      It is related to the NLS Settings. When we change them as follows:
      alter session set NLS_NUMERIC_CHARACTERS = ‘. ‘;
      alter session set NLS_TERRITORY= ‘AMERICA’;

      Then we receive number inserts without quotation mark:
      Insert into EMP (EMPNO,ENAME,SAL) values (7839,’KING’,5000);
      Insert into EMP (EMPNO,ENAME,SAL) values (7698,’BLAKE’,2850);

      regards
      HoB

  2. Daniel Reply

    Hi Jeff,
    In various text editors, when the autocomplete/intellisense popup appears with suggestions, you can press ENTER or TAB to select the suggestion and add its text. Can you make suggestions work that way in SQL Developer?

  3. nitin Reply

    Hi Jeff
    I am using cart of sql developer to export the data and ddl of multiple tables in multiple exl files (one for each data files) in dev env. I need to move all data and tables into prd how can that will be acheived ?

    • thatjeffsmith Post Author

      Why Excel? You’ve just made it hard to put back into a database.

      Extract to delimited text files, then you can use external tables or sqlldr to put the data back in somewhere else.

    • nitin

      can that cannot be achived by sqldeveloper like exporting and importing of many tables data as sqlldr or external tables will involve mapping of data of each table while doing import . i am looking some method where we can do export by using cart and by using same cart we can do import ?

    • nitin

      can by using cart we can use loader or csv in sqldeveloper and can be copied by using sqldeveloper or datapump will be best solution in such a scenario Please advise

    • thatjeffsmith Post Author

      I need way more information that you’re giving me for a good answer. I have posts on all these topics that YES, show you how to do these things. You can use the solution that best fits your needs. When you have a specific question for doing one of these things, let me know.

  4. Reinhard Reply

    Is is possible (will it be in future) to generate Liquibase changelogsets from Oracle Data Modeler?

    We model databases by ODM and looking for an easy way to create changelogsets…

    Thanks for any input
    Reinhard

    • Reinhard

      Yes, exactly.

      Creating a new database – modeled by ODM – ends with a generated SQL export…

  5. Brian Reply

    Hi Jeff,

    I receive this exception in the latest (19.2.1.247.2212) SQL Developer when importing an Oracle 18c schema into the Data Modeler. My question is, how can i determine the schema object that is causing this exception? I’m guessing that the “MOHViewOracle” part is significant, but there seems to be no other diagnostics. Thanks!

    2019-10-04 14:37:37 SEVERE – Error in database import:
    java.lang.NullPointerException
    at oracle.dbtools.crest.imports.metadata.oracle.MOHViewOracle.postGenerateProcessing(MOHViewOracle.java:793)
    at oracle.dbtools.crest.imports.metadata.AbstractDBMExtractionHandler.generateDesign(AbstractDBMExtractionHandler.java:317)
    at oracle.dbtools.crest.imports.metadata.oracle.DBMExtractionHandlerOracle.generateDesign(DBMExtractionHandlerOracle.java:266)
    at oracle.dbtools.crest.imports.metadata.DBMExtractionController$Runner.run(DBMExtractionController.java:307)
    at java.lang.Thread.run(Thread.java:748)

  6. Anderson Bestteti Santos Reply

    Hi Jeff,

    I’d like to add the following code in the beginning of all DDL scripts generated by SQL Dev. Data Modeler:
    WHENEVER SQLERROR EXIT FAILURE;
    ALTER SESSION SET CURRENT_SCHEMA = <>;
    SET SCAN OFF;
    SET ECHO ON;
    SET TIMING ON;

    Having said that, the expected DDL script should looks like this:
    — Generated by Oracle SQL Developer Data Modeler 19.2.0.182.1216
    — at: 2019-10-02 17:15:58 BRT
    — site: Oracle Database 11g
    — type: Oracle Database 11g

    WHENEVER SQLERROR EXIT FAILURE;
    ALTER SESSION SET CURRENT_SCHEMA=<>;
    SET SCAN OFF;
    SET ECHO ON;
    SET TIMING ON;

    PROMPT CREATING TABLE ‘COUNTRIES’;
    CREATE TABLE countries(
    country_id CHAR(2)NOT NULL,
    country_name VARCHAR2(40)NULL,
    region_id NUMBER NULL,
    CONSTRAINT country_c_id_pk PRIMARY KEY(country_id)ENABLE VALIDATE
    )
    ORGANIZATION INDEX
    TABLESPACE users LOGGING PCTTHRESHOLD 50 NOCOMPRESS
    NOPARALLEL
    NOROWDEPENDENCIES;

    CREATE INDEX countries_region_id_idx ON
    countries(
    region_id
    ASC);

    <>>

    I was able to create the above code by customizing “Table DDL Transformation Scripts”. However, it is only possible to select a few options to push your customized code in the DDL script, as follows: Before create, Instead of create, End of Script. Then you must pick an arbitrary table in “Include Table DDL Scripts” panel from “DDL Generation Options” in order to run your customized JavaScript and then generate the desired SQL code. Is there a way to add my customized SQL code always in the beginning of the DDL script generated by DM, regardless of selecting an arbitrary table?

    Thank you in advance for your attention.
    Anderson Bestteti

    • thatjeffsmith Post Author

      Sorry, yes I did, but I got distracted.

      Why not just have a login.sql for your environment that runs this on connect, so it’s taken care of for you?

      Or short answer – I don’t see a way to achieve this w/o doing what you’re doing already with the pre/post table stuff.

  7. HoB Reply

    Hello Jeff,

    When I open a sourcecode file with extension .pkg in SQL Developer I cannot compile it.
    (I have to copy it to a worksheet for compliation)
    When I open same sourcecode file with extension .sql in SQL Developer I compile it.

    Why is that?
    How can I compile sourceode file ( .pkg, .pkb ) directly without copying to a worksheet?

    Thanks & regards
    Horst

  8. surya Reply

    Hi Jeff,
    How can I import excel data from SharePoint into Oracle database tables.
    Thanks in advance.

    • thatjeffsmith Post Author

      I don’t have SharePoint to play with, but I imagine saving local copies of the Excel files and then doing the imports would be the simplest way to go.

  9. Steven Andler Reply

    Jeff

    I am looking for a package that can take an Oracle query and convert it into a nice data model design.
    Can you recommend something?

    Thank you

    • Steven Andler

      Yes. I use it now, but as you know it is not a user friendly application as far as making any modifications to the look and size of the tables.

  10. Shaley Reply

    Hi Jeff

    Would you be able to outline for me ,on a high level what the differences between mod_plsql and Oracle REST are ?

    Also what are the advantages of Oracle ORDS over a mod_plsql configuration ? I would appreciate even a short and high level answer.

    • thatjeffsmith Post Author

      mod_plsql is deprecated

      the core role of ORDS is to present a REST model of interacting with your Oracle Database, via HTTP(s)

    • Shaley

      Thanks for the answer Jeff. Do you have a blog post or a good one that you can kindly direct me to so that I may read a in depth review of these technologies ?

    • thatjeffsmith Post Author

      Go to the top of this page, click the title menu where it says, ‘REST Data Services’ – there you can find a selection of my posts on ORDS.

  11. Houman Reply

    Hi Jeff,

    I want to develop a Windows application using Oracle Developer Studio on a Windows desktop. For this reason, I have downloaded and installed the IDE on my Windows machine. I have also created a Linux machine with Oracle IDE on it and all necessary development packages. The developer studio on Windows connects successfully to the Linux machine and is able to build and run the sample Apps that come with SQL Developer Studio however the executable resides on the Linux machine. Is there a way to tell the Developer Studio to generate the executable in the Windows code desktop so that I can start the app in the Windows environment? Just like the “SQL Developer” that is a Windows app?

    Thanks

  12. Duke Reply

    Hi,

    I use groovy with bind variables and use SQLDev to format my code,
    the bind variable is in CAPITAL letters when I reformat it is the lower case;
    the bind variable is formatted as :FK_SCHEMA_NAME
    Shouldn’t it stay in CAPS by default?

    Duke

  13. Santhosh Reply

    Hi Jeff,

    I have tried to import DB Objects from SQL Developer (Tools–> Data base Import).But I am getting ” in every Object Name for every type (“SCHEMA”.”OBJECT_NAME”). When I checked DBA_SOURCE I can see the same double quotes. But I did not get this ” when I imported objects from SQL Developer installed on my co-worker system and there is no ” in DBA_SOURCE view.
    So, please help me.

    • thatjeffsmith Post Author

      The quotes are there because we put them there in case your object names are case sensitive, contain reserved words, etc. – but once created, you don’t see those quotes in the data dictionary, they’re just required by the CREATE or ALTER statements.

    • Santhosh

      But ” were not used at the time of object creation and we imported same object from same data base from different sql developer on different system. Then there is no ” in the file.
      My question is is there any configuration needs to be done. Because only i am getting this no one in my team.

    • thatjeffsmith Post Author

      Show me what you’re talking about, based on your question, I’m left to guessing what you’re seeing.

    • Santhosh

      below is a scenario for easy understanding of the issue.
      I have created table with below script
      create table test (sno number, name varchar2(10), date1 date);

      When imported this object (Tools –> Data base Export ) I got below script. Not understanding why there are “. When I execute this script those will be compiled as case sensitive.
      ——————————————————–
      — File created – Tuesday-September-24-2019
      ——————————————————–
      ——————————————————–
      — DDL for Table TEST
      ——————————————————–

      CREATE TABLE “APPS”.”TEST”
      ( “SNO” NUMBER,
      “NAME” VARCHAR2(10 BYTE),
      “DATE1” DATE
      ) SEGMENT CREATION DEFERRED
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
      NOCOMPRESS LOGGING
      TABLESPACE “APPS_TS_TX_DATA” ;

    • thatjeffsmith Post Author

      The database adds the quote, to account for if your code had been create table “test” (“sno” number… ;

      Your object is all uppercase, so technically it’s not case sensitive, end of day, there’s zero difference between what you submitted, and what the database spits it back out as.

    • Santhosh

      My question is why only my sql developer generating these ” and also adding EDITIONABLE key word in create statement of stored procedure.
      Please let me know is there any settings need to be changed.my team not allowing these objects to be compiled.

    • thatjeffsmith Post Author

      Because that’s what DBMS_METADATA generates for the DDL. The quotes should cause no problems.

  14. Sunny Reply

    Hi Jeff,

    We have 19.1 ORDS running on WebLogic fronted by nginx.

    There are 90-100 GET services that a user calls to process a request. The anticipation is that we would have multiple users calling these services concurrently.

    My question would be is there a way to configure/ restrict the number of users calling the services at any given time?

    Alternatively if we were to configure the environment to handle as many connections as possible what tuning would be recommended I.e connection pools, WebLogic memory/ tuning and at database level?

    Regards

    Sunny

    • Sunny

      Would help if I replied to the correct post…:-)

      Thanks Jeff, will to do some investigation around those comments.

      Is there any way to compress the data being sent?

      We have scenarios were we are sending large volumes of data over a GET and want to compress that to the smallest size possible.

  15. keith Reply

    Hi,

    Formatting question – using version 19.2
    I want to format my variable definition so they are aligned as per below but can’t seem to find an option in the formatter to allow this

    eg
    C_THING CONSTANT VARCHAR2(100) := ‘MyThing’;
    C_OTHER_THING CONSTANT NUMBER :=’17’;
    C_YES CONSTANT VARCHAR2(1) := ‘Y’;
    C_NO CONSTANT VARCHAR2(1) := ‘N’;
    V_THING VARCHAR2;

    • keith

      oops – spaces got removed! CONSTATN should be vertically aligned and the VARCHAR2 for V_THING shoul also be vertically aligned

    • Sunny

      Thanks Jeff, will to do some investigation around those comments.

      Is there any way to compress the data being sent?

      We have scenarios were we are sending large volumes of data over a GET and want to compress that to the smallest size possible.

  16. Andre Reply

    Hello Jeff,

    (sql developer 19.1, windows 10, 64 bit executable)
    we want to change our workflow to be more lightweight so we checked out the possibilities of code templates in sql developer and git implementation. You did write some blog entries about templates. But i did not find what i was looking for. Our goal is to create package (and procedures, functions) stubs with some pre generated comments. A package template should have a similiar outcome like this:

    CREATE OR REPLACE PACKAGE schema_ab.pck_my_package AS

    / * ====================================================================
    last update: 01.09.2019 15:02:38
    * ====================================================================
    *
    * by: Marky Mark
    * Descrption:
    */
    END schema_ab.pck_my_package;

    So i wanted to use the #time# and #date# in the “last update” line and #user# in the “by” line.
    But, those tags doesnt seems to work at all and especially not in comments. Additionally it doesnt seem to be possible to place a cursor bracket [] within a comment properly. In the resulting code the cursor area mark is completly wrecked.

    Bonus question: is it somehow possible to insert the package name (like schema_ab.pck_my_package) inside a bracket placeholder only one time so the name at the end of the package is changed too?

    Could you please guide me through this?

    Thanks and Regards,
    André
    btw: i am a big fan of sql developer and use it since version 1. Again and again I’m trying to convince my collegues using sql developer with growing success.

    • André

      Hello Jeff,
      is it possible to give me a hint on this ? I’d like to introduce sql developer in a proper way in our company.

    • thatjeffsmith Post Author

      I’m at Open World this week, so a bit backed up.

      From what I can tell thee’s no way to use the #date and #time bits, and i’m not sure on your question about the object names, but also as far as I know, it can’t do that either.

  17. FEMYMELLWIN Reply

    Hi All,

    Greetings!

    How do I enable OAuth Authentication for AutoRest?

    Thanks

    • thatjeffsmith Post Author

      same as for any service, Todd talks a bit about that here

      When you rest enable a table, you can see the Privilege that’s created you can use to secure it.

  18. John P Reply

    Exported connections from 19.2 with a key so it would export with passwords too.
    But when I import the connections into another 19.2 it says “The specified decryption key does not match the key used to encrypt the passwords in the file”. I provide the same key from when I did the export.
    If I do the export/import without a key (so passwords are not migrated) it works great.
    Any ideas why it’s not happy with the key?
    Thanks,
    John P

    • thatjeffsmith Post Author

      Where is your ‘another 19.2’ – on a different machine? What version of Java is that copy of SQL Developer using?

    • John P

      Hi Jeff,
      the original 19.2.0.206.2117 is on Windows with Java 1.8.0_212
      the other version was actually 19.1 is on Solaris pointing to a JRE 1.8
      So I got the 19.2.0.206.2117 without a JRE version onto Solaris .
      I installed a JDK 1.8_221 onto the Solaris box and change my PATH to point to that Java.
      Uncommented out # AddVMOption -Dsun.java2d.xrender=false from the sqldeveloper.conf so the GUI would display.
      Then retried the Connection import and it worked and kept all the passwords.
      Thanks for making me think about the version and the JDK.
      John P.

  19. Barathi Reply

    Hello Jeff,

    In the find database object, i’m able to find my package. But when i click on it, it does not open the source code.
    We have NA and EMEA region DB separately. In NA this option of viewing source code works and EMEA it does not work. (In Toad EMEA instance i’m able to open this package )
    Is it something to with RAC environment. I have same DB privileges in both the DB regions.

    How do i address this issue

    • Michele

      Dear Jeff, I have ORDS 18.2 .
      In a handler with POST method and PL/SQL source is it possible to format output in raw text instead json?
      I obtain:

      {
      “variable”=“value”
      }

      but I need only:

      value

      I need to integrate a bank payment service that support only a raw response. That sucks!
      Thanks in advance,
      Michele

  20. Lori Reply

    Hi Jeff,
    Do you know if there are any tools available that support automated conversion of MSFT Sql queries to Oracle Sql? Thanks.

  21. Shams Reply

    Hi Jeff,

    I am an ORDS/Apeks enthusiast. We are moving onto the REST world at Harvard and I was trying to get troves of DB developers on board to create some momentum around our initiatives.

    I have a demo for them this week and was hoping to use your slide deck. I got the PDF version; but if you don’t mind sharing your ppt versions that would save me a lot of work.

    Thank you, Please feel free to contact me @ my work email.

    Regards
    Shams

    • thatjeffsmith Post Author

      I just sent you some slides, let me know if you don’t get them, it was an 11MB file attachment.

  22. Andrea Reply

    Hello Jeff,
    can sql developer supports custom JDBC Driver?
    I need connected a Database Middleware — MyCat, It is like I have our custom JDBC Driver,
    I add third-part JDBC , but no card can see it like mysql card. so I can’t input connection msg.
    I can connected this JDBC and run SQL through import conn by XML.But I can’t see database structure.

    How do i fixed this issue ?

    sincerely thank!

    • thatjeffsmith Post Author

      Not really an ORDS question, you just need to have your web server use the default port for HTTPS (443) – if that’s ORDS Standalone (Jetty) that setting is in the standalone.properties file – note that your OS may not like an app binding to 443 and you’ll have to configure it to allow so. But again, not really an ORDS question.

  24. Barathi Reply

    Hello Jeff,

    In the find database object, i’m able to find my package. But when i click on it, it does not open the source code.
    We have NA and EMEA region DB separately. In NA this option of viewing source code works and EMEA it does not work.
    Is it something to with RAC environment. I have same DB privileges in both the DB regions.

    How do i address this issue ?

  25. ayybates Reply

    (Version 17.4.0.355)
    I just changed the colors around in the SQL Developer Editor, and the only thing I can’t figure out how to change is the block highlight color in the margin. (When you select an “if”, for example, it will show a bar on the left side of the window that goes down to the corresponding “end if”.) In the default scheme, it was a blue-grey color that contrasted with the white background, but now that I’ve changed the background to black, it’s a dark grey color that barely contrasts with the black. I figured out that under Display, I can toggle “margin indicates current block”, but I cannot figure out how to change the color of that indication to be more visible.

Write A Comment