Ask A Question

4.5+ million 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!

5,631 Comments

  1. Mohammad AlShaabi Reply

    How can I write a stored procedure to run a select statement and save the result in CSV format? I must be able to specify the following in the procedure:
    – destination path where it should save the CSV result.
    – Format: which should be CSV.
    -Left and right enclosure: which will be double qouts “.
    – Encoding: UTF-8.

  2. I want to create the create a model which has 50 odd tables and 1000+ attributes. I want to check if there is a way we can import an excel that can help us save time in creating tables and attributes manually. I am only use oracle data modeler tool.

  3. Hi Jeff,

    We package up SQL Developer and a JDK separately for mass rollout – with the former pointing to the latter. There are plans afoot to update the JDK package quarterly from now on though which means users will get prompted for the new JDK location each time this happens (which we’d like to avoid). Is there a way to make SQL Developer aware via a Windows environment variable that could be reset upon JDK update where the new JDK folder location is for it? Something like:

    JDK_LOC=C:\Program Files\Java\jdk1.8.0_162

    We could code up upon an updated JDK package rollout to touch any user’s product.conf file with the new location, but was hoping there might be another way to make the deployed SQL Developer package abstracted from this and not require any direct conf file editing.

    A Unix alias would be perfect but I don’t think Windows has an equivalent that we can use here.

    Thanks in advance!

    Mikel

    • thatjeffsmith

      Are you rolling out the JDK specifically FOR SQLDev?

      If so, I suggest you embed the JDK inside the SQLDev package. Then have the java deployments update the sqldev folder, and the base path would never change…

      Or when you roll out the new java home, have it be a generic directory name so the path doesn’t need updated in sqldev.

    • Unfortunately no – the packaged JDK is intended for a few different applications, and machines might have a few installed at any point in time, hence our admin people being keen to retain a version specific folder naming approach.

      I liked your idea of a generic directory and had suggested the same, but couldn’t get it agreed to, so I think we’ll just have to educate the userbase to expect to repoint SQL Dev once a quarter to the new JDK once an updated package has landed.

      Thanks for your reply!

  4. Hi Jeff,

    I am using sql developer 19.4 and the format code of function and procedure is
    BEGIN
    apps.fnd_global.apps_initialize (
    user_id => 1804,
    resp_id => 20707,
    resp_appl_id => 201,
    x_resp_appl_id => 44
    );
    END;

    but i want it to become

    BEGIN
    apps.fnd_global.apps_initialize (
    user_id => 1804,
    resp_id => 20707,
    resp_appl_id => 201,
    x_resp_appl_id => 44
    );
    END;

    Regards,
    El-Sayed

    • thatjeffsmith

      that appears to be a bug in 19.4 that’s fixed for version 20.Next

      BEGIN
          apps.fnd_global.apps_initialize(
                                         user_id => 1804,
                                         resp_id => 20707,
                                         resp_appl_id => 201,
                                         x_resp_appl_id => 44
          );
      END;
  5. Jens Gerhardt Reply

    Hi Jeff,
    I have a small issue with the SQL Developer. When I start a long running query and want to open a new window with a new connection, it will wait until the long running sql has finished before opening the new window and the new connection.
    Is this expected behaviour?

    • thatjeffsmith

      I don’t think so, no.

      I was just able to

      on Connection 1, run this:

      begin
       dbms_lock.sleep(15);
      end;
      /
      

      As that was running in the UI, open a connection on DB 2.

      This on version 19.4

  6. Kaushik Mahida Reply

    Hi Jeff, When I try to configure ORDS, I get a failure to bind error. It ran successfully for the first time and ORDS was configured but when I try to restart it, it gives me a failure to bind error in the standalone mode. The database is DBCS.

    • thatjeffsmith

      It’s saying the port is already being used by another application. You need to free up that port, or change it to a different port.

    • Kaushik Mahida

      Hi Jeff, are you talking about the 1521 port?

  7. Wrushasen Dakhane Reply

    We are on below database\app server versions.

    SELECT * FROM v$version;
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    PL/SQL Release 12.1.0.2.0 – Production
    CORE 12.1.0.2.0 Production
    TNS for Solaris: Version 12.1.0.2.0 – Production
    NLSRTL Version 12.1.0.2.0 – Production

    WebLogic Server Version: 12.2.1.3.0

    I want to enable ORDS on the database, can you please share some pointers.
    1. Do we need to have APEX (mandatory)
    2. once ORDS is enabled I want to use the rest api in my OracleJET app using offline-persistent-toolkit. (Will this approach work)

  8. Dave Campbell Reply

    Hi Jeff/team

    We have an issue when we call a rest service returning a Query – the query is a pipelined function and within that function we are trying to access the CGI env data.

    This works when using a PLSQL type REST, but not via Query.

    Further testing shows any local SQL in the REST code calling the CGI functions within the SQL fails.

    We are using APEX 19.2 to create the calls.

    example tests:
    — this works:
    SELECT ‘TEST’ as “x_apex_base”
    FROM dual

    — this fails:
    SELECT owa_util.get_cgi_env(‘X_APEX_BASE’) as “x_apex_base”
    FROM dual

    any ideas appreciated.

    many thanks

    Dave C

    • Wrushasen Dakhane

      Thanks Jeff,

      Once I install ORDS and it is running on sqldeveloper I do not see the REST Services context menu option on HR schema and Enable REST Services option on table context menu is disabled.

      Followed all steps still unable to solve this.

      $ java -Doracle.net.tns_admin=D:\app\dakhanws\product\12.2.0\dbhome_1\network\admin -jar ords.war validate
      Enter the name of the database server [localhost]:localhost
      Enter the database listen port [1522]:
      Enter the database SID [orcl]:
      Requires to login with administrator privileges to verify Oracle REST Data Services schema.

      Enter the administrator username:sys
      Enter the database password for SYS AS SYSDBA:
      Confirm password:

      Retrieving information..

      Your database connection is to a CDB. ORDS will be validated in the CDB and PDBs.
      PDB PDB$SEED – validate ORDS 19.2.0.r1991647 (mode is READ ONLY, open for READ/WRITE)

      Enter 1 if you want to validate ORDS or 2 to exit [1]:1

      Validating Oracle REST Data Services schema version 19.2.0.r1991647 in PDB$SEED
      … Log file written to C:\Users\dakhanws.CALEGIS\ords_cdb_validate_core_PDB_SEED_2020-04-07_070749_00517.log
      Completed validating Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:00:03.966

      Completed CDB validation Oracle REST Data Services version 19.2.0.r1991647.
      Total Elapsed time: 00:00:04.578

    • thatjeffsmith

      you’ve already asked this question on the forums, so i’m assuming you can get what you need from over there

      but

      you have to login AS hr and on the HR connection, you enable the schema. and, you do this AFTER you configure ords for the database.

      you can always run ords_admin.enable to enable the HR schema from an admin account if you want, a la

      BEGIN -- this part is so I can login as HR via SQL Developer Web
          ords_admin.enable_schema (
              p_enabled               => TRUE,
              p_schema                => 'JEFF',
              p_url_mapping_type      => 'BASE_PATH',
              p_url_mapping_pattern   => 'tjs', -- this flag says, use 'tjs' in the URIs for JEFF
              p_auto_rest_auth        => TRUE   -- this flag says, don't expose my REST APIs
          );
          COMMIT;
      END;
      /
  9. Hi Jeff, I get in SQLDev 19:* strength characters in script output.
    For example:
    select sysdate from dual;
    select sysdate+1 from dual;
    select sysdate+2 from dual;

    SYSDATE
    ——————-
    26.03.2020 10:36:12
    
    ⁦潲捥彰物湴૾＀ഀ਀匀夀匀䐀䄀吀䔀⬀㄀          ഀ਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀഀ਀㈀㜀⸀ ㌀⸀㈀ ㈀  ㄀ 㨀㌀㘀㨀㄀㈀ഀ૾＀ਠ景牣敟灲楮琊
    SYSDATE+2
    ——————-
    28.03.2020 10:36:12

    What is wrong ? In older versions it was correct.
    Thank you very much

    • no. here an example:
      CREATE TABLE TAB (COL number(1));
      insert into tab (col) values (1);
      insert into tab (col) values (2);
      insert into tab (col) values (3);
      insert into tab (col) values (4);
      insert into tab (col) values (5);
      commit;
      Script output:

      Table TAB created.

      ⁦潲捥彰物湴૾＀਀㄀ 爀漀眀 椀渀猀攀爀琀攀搀⸀਀ਠ景牣敟灲楮琊
      1 row inserted.

      ⁦潲捥彰物湴૾＀਀㄀ 爀漀眀 椀渀猀攀爀琀攀搀⸀਀ਠ景牣敟灲楮琊
      1 row inserted.

      ⁦潲捥彰物湴૾＀਀㄀ 爀漀眀 椀渀猀攀爀琀攀搀⸀਀ਠ景牣敟灲楮琊
      Commit complete.

      ⁦潲捥彰物湴�

      Best regards,
      Ewgeni

    • Hi Jeff, is it only my problem?
      Thank you

    • thatjeffsmith

      As far as I can tell, yes. Something weird is happening in your system – the first thing I would check is for triggers defined in your db with that text in them.

    • but in SQLDev v.4.2 script output is ok…

    • thatjeffsmith

      I need a reproducible test case. Check for maybe a login.sql in your newer version. In a worksheet, do a

      show login

      and then if a script comes back, look at it for things that might cause this

    • Wrushasen Dakhane

      Thanks for you reply.

      But don’t know why ORDS related packages are not showing up.

      Dont know where is it getting created.

      Log does show installation successful:-

      INFO: Configuration properties for: |apex|pu|
      database.api.enabled=true
      db.hostname=localhost
      db.password=******
      db.port=1522
      db.sid=orcl
      db.username=ORDS_PUBLIC_USER
      feature.sdw=true
      resource.templates.enabled=true
      restEnabledSql.active=true

    • thatjeffsmith

      If it’s a multitenant/cdb install, they’re in an ORDS_METADATA schema in each of your PDBs.

      That is true IF those PDBs were open when you did the install.

      You should also have a public synonym called ‘ORDS’ granted to PUBLIC that points to the ORDS package in that schema.

  10. Peggy Van Langenhove Reply

    Hi Jeff,

    When I run a query – with errors – in SQL PLUS I get feedback about the line that’s causing the error

    SQL> SELECT
    2 ‘A’ AS a1,
    3 10 AS a2,
    4 ‘B’ AS a3
    5 FROM
    6 dual
    7 WHERE
    8 1 = TO_NUMBER( ‘A1’ );
    1 = TO_NUMBER( ‘A1’ )
    *
    ERROR at line 8:
    ORA-01722: invalid number

    Sql developer (19.4) just tells me something is wrong …

    ORA-01722: invalid number
    01722. 00000 – “invalid number”
    *Cause: The specified number was invalid.
    *Action: Specify a valid number.

    Possible to get the line numbers as in SQLPLUS ?

    Kind regards
    Peggy

    • thatjeffsmith

      This is what I get in version 19.4

      ORA-00923: FROM keyword not found where expected
      00923. 00000 – “FROM keyword not found where expected”
      *Cause:
      *Action:
      Error at Line: 5 Column: 1

    • Peggy Van Langenhove

      He Jeff,

      For errors on a wrong table name we get a guide error message

      eg :
      SELECT 1
      FROM DUALS
      WHERE 1 = 1;

      ORA-00942: table or view does not exist
      00942. 00000 – “table or view does not exist”
      *Cause:
      *Action:
      Error at Line: 2 Column: 6

      For others like ORA-01722: invalid number

      SELECT 1
      FROM DUAL
      WHERE 1 = ‘A’;

      ORA-01722: invalid number
      01722. 00000 – “invalid number”
      *Cause: The specified number was invalid.
      *Action: Specify a valid number.

      When the SQL is only a few lines the error is easy to find but on bigger SQL this gives us plenty of search time

      Kind regards
      Peggy

    • thatjeffsmith

      We show whatever the jdbc driver and the database gives us… Maybe try a thick connection and see if that helps.

    • Wrushasen Dakhane

      Looks like my ORDS installation happening is multitenant/cdb install.

      How can I control it to get installed into normal orcl schema.

      Retrieving information..

      Your database connection is to a CDB. ORDS will be uninstalled in the CDB and PDBs.
      Root CDB$ROOT – uninstall ORDS
      PDB PDB$SEED – uninstall ORDS 19.2.0.r1991647 (mode is READ ONLY, open for READ/WRITE)

      Enter 1 if you want to uninstall ORDS or 2 to exit [1]:

    • thatjeffsmith

      Do you have a PDB? Is it open? The easiest way to get going is to run the installer against your PDB service directory, and skip the CDB.

  11. Steven Hertogs Reply

    Hi Jeff,

    We have a question about the NLS settings in sql developer.
    In our oracle client the setting for NLS_LANG is AMERICAN_AMERICA, but in sql developer it always stays at DUTCH.
    Is there a possibilty that sql developer automatically takes the NLS setting of the oracle client?

    Thank in advance

    With kind regards
    Steven Hertogs

  12. Anibal Lopez Reply

    Hi Jeff,

    This is question is related to Basic authentication.
    Thus just a uername and password in the header os the response request.

    I have cretaed a HTTPS POST webservice.
    Is it possible in Oracle ORDS to add a username and password in the header of response?

    This is without using OAuth2 Client Credentials.

    Regards,
    Anibal

    • thatjeffsmith

      Why would you want the password exposed in your response header?

      Adding the user is trivial, the authenticated user is available as a :user bind in your rest handler code block.

Reply To Stuart Higgins Cancel Reply