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!

Comments 4,249

  1. QUESTION: What sequence of commands must I use in Terminal in macosn to start using sqlcl?

    BACKGROUND:

    Oracle SQL Developer 18.2.0.183 is working just fine
    and connected to an 11g instance in Linux 7 in Virtualbox 5.2.12.

    The connection as defined for SQL Developer:

    connection name: localrpo
    username: rpo
    password: XXXXX
    connection type: basic
    role: default
    hostname: local
    port: 1521
    SID: orcl

    The sqlcl folder (created 25-Jun-2018) resides in:

    Macintosh HD/Applications/

    1. thatjeffsmith Post
      Author

      Open a BASH terminal window.

      Type ‘sql’

      This assumes your Macintosh HD/Applications/sqlcl/bin folder is in your PATH. If not, then add it, or manually CD over to where it is, and run the SQL program over there.

      1. Great. It got me very close but not quite there. Here’s the resulting dialog, followed by my comments::

        SQLcl: Release 18.2 Production on Wed Jul 18 17:02:35 2018

        Copyright (c) 1982, 2018, Oracle. All rights reserved.

        Username? (”?) rpo
        Password? (**********?)
        USER = rpo
        URL = jdbc:oracle:thin:@localhost:1521/orcl
        Error Message = ORA-01005: null password given; logon denied
        USER = rpo
        URL = jdbc:oracle:thin:@localhost:1521/xe
        Error Message = Listener refused the connection with the following error:
        ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
        Username? (RETRYING) (‘rpo/’?) rpo
        Password? (RETRYING) (**********?) *******
        Connected to:
        Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
        With the Partitioning, OLAP, Data Mining and Real Application Testing options

        Comments: manually supllying the username “rpo”and then the password got me in, but how can I avoid having to reenter these bits? The strongest clue is probably in the statement “TNS:listener does not currently know of service requested in connect descriptor” But how can I supply that knowledge to the listener? (what listener?)

      2. thatjeffsmith Post
        Author

        Herb, there’s a lot to go through there. But the main thing: there’s really no way to get into the database w/o your user credentials. Without a password, you’re not going anywhere near the data.

  2. ‘undefine’ works with Oracle, but not Postgres.
    2-line script which returns only 3 rows (for simplicity’s sake)

    undefine cc ;
    select unit_of_measure_name, &&cc cc, &&cc+1 “cc+1” from unit_of_measure;

    In Oracle, running the ‘select’ only prompts me once, as expected. Re-running it uses the same value. If I want to re-run it with a different value, I execute the ‘undefine cc’ first, then the ‘select’. And I’m prompted again. All good.

    But if I’m accessing Postgres, the ‘select’ behaves as expected. But the ‘undefine cc’ errors, as if it’s invalid:

    Error starting at line : 1 in command –
    undefine cc
    Error report –
    ERROR: syntax error at or near “undefine”
    Position: 1

    Does ‘undefine’ only work with Oracle? For Postgres, is there another way to specify a different value for the variable?

    Tearing my hair out….

    1. thatjeffsmith Post
      Author
    2. figured out a working, but inelegant solution:

      execute the following:

      exit;

      Then re-run the query. Voila – prompting happens again

      1. thatjeffsmith Post
        Author
  3. Hi Jeff – When using spool to generate a CSV file is there a way to set the encoding type for the file? In SQL Developer v17.4 (and previous) it was ANSI and starting with version v18.1 it is UTF8. The problem that I am having is French characters are not displaying properly in Excel with spool files generated with versions 18.1 or 18.2. Thanks – Mark

    1. thatjeffsmith Post
      Author
      1. Excel will read the file properly and display the correct French characters if it is UTF8-BOM (or ANSI). Example: the word “série” appears as “série” if the file is encoded in UTF8. The really issue here is that it appears SQL developer changed the file encoding type from ANSI to UTF8 with v18.1.

      2. thatjeffsmith Post
        Author
  4. According to your blog on ORDS.
    you said “ORDS doesn’t require APEX, and you can run APEX without ORDS”.

    I know ORDS doesn’t required APEX. But, How APEX run without ORDS?
    I know that APEX should be run on only ORDS or ORDS+WLS.

    SK.

    1. thatjeffsmith Post
      Author

      In 2018, you’re right. APEX previously supported the xmldb listener in the Database.

      Today, you should really be running ORDS.

      WLS is overkill though. Most of our customers seem to like Apache and Tomcat, although the standalone ords config seems to be taking off.

  5. What’s the easiest way to find out what the Number of Database Tables in Oracle SQL Developer without having to count them manually? 🙂

    1. thatjeffsmith Post
      Author
  6. I have SQL*Developer 18.1.0.095 (Build 095.1630) installed on my desktop. I see from the SQL*Developer website that 18.2 is available. So in SQL*Developer I click on Help -> Check for Updates… Automatically check for updates as startup is not checked. Search Update Centers button is selected and the only box checked is for ‘Oracle SQL Developer’ (http://apex.oracle.com/pls/apex/dbtools/usage/cfu). I click on ‘Next’, the ‘Select updates to install’ window displays with ‘Loading…’ for a few seconds and then…nothing. There are no updates listed. Why?

    1. thatjeffsmith Post
      Author
  7. Hi,

    sqldeveloper 18.2 aborts during startup on windows7 64-bit.

    Any idea where to start looking ?

    I downloaded this version Windows 64-bit with JDK 9 included?

    Knut

    1. thatjeffsmith Post
      Author
      1. It’s aborting when it starts loading the extensions.

        Renaming did work. Guess I do need then to move connections and snippets manually.

        Knut

      2. thatjeffsmith Post
        Author

        yeah, that’s why I advised you ‘rename’ that folder vs nuke it. And by ‘manually’ – just copy the files/folders over, you should be fine.

  8. Hi Jeff,
    If I save my function or package in file system ( c:\abc\func1.sql or c:\abc\pkg.sql ) .
    I really want to work on that files as we do in toad .
    What happening in sqldeveloper , I need to compile this as function , and then work on that function . But those changes are in database not saved to file . Then development is over , I need to export that file to save as c:\abc\func1.sql.

    In this approach , problem is that , without notice , dba go ahead refresh database and I loose my work. Hence when I save file , it should save c:\abc\func1.sql, then if I want to compile I can compile in sqldeveloper.

    How to do that, I am not understand that.
    Please help
    Amey

    1. thatjeffsmith Post
      Author

      You can do what you want, in SQL Developer.

      Open your file. Do your work – save as you need. We even keep a local file history for you.

      When you’r ready to compile, assign your editor a connection.

  9. Hi Jeff,

    First, thanks for Oracle SQL Developer, I love this tool.

    Now the problem I have: “desc” command in SQL Worksheet fails with some tables, getting the error “ERROR: object MYTABLE does not exist”, but when I do the same from Sql*Plus Instant Client it works fine. I can reproduce this issue with any table having a TRIGGER with the same name than the table:

    select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from dba_objects where object_name=’MYTABLE’;

    OWNER OBJECT_NAME OBJECT_TYPE STATUS
    ——————– ——————– ——————– ———————
    PUBLIC MYTABLE SYNONYM VALID
    MYUSER MYTABLE TABLE VALID
    MYUSER MYTABLE TRIGGER VALID

    I think this is a bug with SQL Developer, because it works fine with Sql*Plus.

    SQL Developer version is “Versión 18.2.0.183”

    Thank’s in advance

    Best regards

    1. No bug.

      I am guessing you need to review what DESC is actually doing when it is called and the ownership of the object it is being asked to DESCRIBE. Your select statement is invalid as proof as you are assuming DESC and SELECT return information via the same mechanism/source. This is not the case. You will find this is consistent using sqlplus as well. All comes down to synonyms and privs.

      Regards

      1. Thank you very much Steve,

        May be I didn’t explain clearly:

        If you create a table “mytable” with a trigger “mytable” (yes, same name), and then you run “desc mytable” from Sql*Plus, it works fine. But it fails when you run “desc mytable” from Oracle SQL Developer, with the same user in both cases.

        If you drop the trigger, it works fine with both tools.

        If you create the trigger with a different name than the table, for example “trg_mytable”, it also works fine. So the problem with “desc mytable” from Oracle SQL Developer is when there is a trigger with the same name than the table.

        You can try this script and next run “desc mytable” from Sql*Plus and Oracle SQL Developer to see the issue:

        CREATE TABLE mytable (
        c1 NUMBER,
        c2 VARCHAR2(2)
        );

        CREATE SEQUENCE seq_mytable CACHE 5;

        CREATE OR REPLACE TRIGGER mytable BEFORE
        INSERT ON mytable
        FOR EACH ROW
        BEGIN
        IF inserting THEN
        IF :new.”C1″ IS NULL THEN
        SELECT
        seq_mytable.NEXTVAL
        INTO :new.”C1″
        FROM
        dual;
        END IF;
        END IF;
        END;

        Thank’s in advance

        Best regards

  10. Did you notice too or is it just me …

    Using SQL-Developer I created a user defined report using “vertical combination chart”.
    The report shows correctly only in the preview page.
    After saving and opening the report in normal reporting mode it shows the 5 graphs as if their diagram type never were configured: bar, line, area, bar, line.
    I tried this with different versions of SQL-Developer (18.2, 17.2, 4.1.2) and on different machines. I always get the same result and behavior: good in preview but not good when using the report.

    Do you encounter the same?

  11. Hi Jeff,

    How to add privileges in the MODEL (Oracle Data Modeler 4.2.0 Build 932).
    When importing from the database to the MODEL, the privileges are not assigned to the roles or to the users.

    I would like to upload the DDL file (grants, roles and users) to the model. Can you do this?

    Assigning privileges one by one is a very crippling task.

    Thanks in advance.

    1. thatjeffsmith Post
      Author
  12. NLS_DATE_FORMAT question. I’m using a database that has a login trigger, once any user is logged in, their NLS_DATE_FORMAT gets set to YYYY-MM-DD via the login trigger.

    If I log in using SQL Developer, I issue “show parameter nls_date_format” and it shows me this:

    SQL> show parameter nls_date_format
    NAME TYPE VALUE
    ————— —— ———-
    nls_date_format string YYYY-MM-DD

    Good, makes sense so far.

    I check v$parameter:

    SQL> select value
    2 from v$parameter
    3 where name = ‘nls_date_format’;

    VALUE
    ———-
    YYYY-MM-DD

    1 row selected.

    All good there. But when I run the following query, the results are somewhat surprising.

    SQL> select sysdate from dual;

    SYSDATE
    ———
    10-JUL-18

    1 row selected.

    I’m not sure what’s going on here. A Wireshark trace of running the query from SQL Developer shows that the nls_date_format for the query is definitely, definitely set to YYYY-MM-DD. So the only thing I can think of is that SQL Developer must be playing with my date. What’s going on?

    1. thatjeffsmith Post
      Author

      What does this show?

      SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT';

      On session being established we do several ALTER SESSIONs…one of the is setting the data format to what you have set in preferences, a la –

      1. SQL> SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter = ‘NLS_DATE_FORMAT’;

        PARAMETER VALUE
        —————————— —————————————-
        NLS_DATE_FORMAT DD-MON-RR

        1 row selected.

        I guess I don’t have an understanding of how this works. How is NLS_SESSION_PARAMETERS showing me something different than v$parameter or SHOW PARAMETER?

      2. thatjeffsmith Post
        Author
      3. Why is it different for nls_date_format vs anything else?

        SQL> col name for a30
        SQL> col value for a10
        SQL> select name, value
        2 from v$parameter
        3 where name = ‘db_file_multiblock_read_count’;

        NAME VALUE
        —————————— ———-
        db_file_multiblock_read_count 128

        1 row selected.

        SQL>
        SQL> alter session set db_file_multiblock_read_count = 8;

        Session altered.

        SQL>
        SQL> select name, value
        2 from v$parameter
        3 where name = ‘db_file_multiblock_read_count’;

        NAME VALUE
        —————————— ———-
        db_file_multiblock_read_count 8

        1 row selected.

      4. thatjeffsmith Post
        Author
  13. I just downloaded SQL Developer 18.2.0.183.1748. I looks awful with Windows font size increased to 140%.
    How can I switch back to the old style?

    1. thatjeffsmith Post
      Author
      1. My monitor is an old HP LP2065 with 1600×1200 resolution.

        Thank you for your advice. Java 9 jdk causes the problem. I went back to java 8 jdk and the problem was gone.

  14. Hi Jeff,

    I have a question regarding to recent files, there is a way to increase the number of recent file in recent files folder?

    Thanks

    Roberto

  15. Hi Jeff

    Just downloaded 18.2 and i can’t find the snippets tool from the view menu – it was there in 18.1 – has it been renamed or removed in 18.2

    Thanks
    Paul

    1. thatjeffsmith Post
      Author
      1. Cheers Jeff that’s sorted it – keep forgetting about that setting
        Its always been fine installing before
        Thanks

  16. Hi Jeff.
    I know there is external tools within sql developer and I was wondering what it would take to hook up sqlcl to sqldevloper so I can right click on a connection within sql developer and it opens up in sqlcl?

    Thanks!

    1. thatjeffsmith Post
      Author
  17. I have a quick question on SQLcl.

    What is the process for it checking that Java (and the correct version) is ‘present’? Particularly on Windows. For example Does it check a registry key?

    Can I use a ‘non-installed’ version of Java that doesn’t appear in the list of programs? I have copied a 8u171 Java installation to a location, set JAVA_HOME and my PATH accordingly.

    java -version works. I figured out that SQLcl is 32-bit so I used a 32-bit JRE but I still can’t get it to work.

    I consistently get ‘This application requires a Java Runtime Environment 1.8.0_150’ and the aggressive automatic browser launch. On another machine with Java ‘installed’ it’s fine.

    Thanks
    Graeme

  18. Hi Jeff,

    I need to implement an e-mobility industry protocol that requires results to be delivered in a predefined JSON format.
    I have created a set of PL/SQL packages and some ORDS modules, templates and handlers based on source type ords.source_type_plsql. This all works fine, however, the protocol requires to have the following optional parameters: limit and offset.
    These parameters are pre-defined ORDS parameters to be used with Collection Query, so I am unable to use them in relation with handlers based on PL/SQL; it leads to 405 Bad Request errors.

    Is there a possibility to use limit and offset as parameters for PL/SQL based handlers?

    1. thatjeffsmith Post
      Author
      1. How are you generating JSON responses?
        I just print the response using htp.p commands.

        Are you using the auto PL/SQL?
        No, we have to provide GET methods, and as far as i know auto PL/SQL is only possible for POST methods.

        Writing your own PL/SQL?
        Yes, using cursors to select data out of our tables and print the results using htp.p.

        What does your PL/SQL do?
        Two tables: LOCATIONS and PHYSICAL_OBJECTS, with a 1 to many relationship.
        It basically selects locations from the location table and selects objects linked to that location from an objects table.
        It prints a JSON message for each location including one or more objects.

        For instance, a simplified example:

        { “locations”:
        [
        { “id”: 1,
        “type”: “STREET”,
        “name”: “location 1”,
        “objects”: [ { “id”:1,
        “name”:”object 1″,
        “status”:”AVAILABLE”,
        “height”:220
        },
        { “id”:2,
        “name”:”object 2″,
        “status”:”RESERVED”,
        “height”:210
        }
        ]
        }
        ]
        }

        Can you share your handler code?
        ( yes, in simplified form, see next reply)

      2. /* create module */
        BEGIN
        ords.define_module(p_module_name => ‘protocol1’,
        p_base_path => ‘protocol1/’,
        p_items_per_page => 10,
        p_status => ‘PUBLISHED’,
        p_comments => ‘Protocol 1 Module’);
        COMMIT;
        END;

        /* define template */
        BEGIN
        ords.define_template(p_module_name => ‘protocol1’,
        p_pattern => ‘locations’,
        p_comments => ‘Locations template’);
        COMMIT;
        END;

        BEGIN
        ords.define_handler(p_module_name => ‘protocol1’,
        p_pattern => ‘locations’,
        p_method => ‘GET’,
        p_source_type => ords.source_type_plsql,
        p_source => ‘begin get_locations(:limit,:offset);end; ‘,
        p_comments => ‘locations GET handler’);
        COMMIT;
        END;

        /* create table locations */
        create table locations
        ( id number(10)
        , location_type varchar2(20)
        , name varchar2(50)
        , CONSTRAINT locations_pk PRIMARY KEY (id)
        );

        insert into locations (id, location_type, name) values (1,’STREET’,’Location 1′);

        /* create table objects */
        create table objects
        ( id number(10)
        , name varchar2(50)
        , object_status varchar2(20)
        , height number(5)
        , location_id number(10)
        , CONSTRAINT objects_pk PRIMARY KEY (id)
        , FOREIGN KEY (location_id) REFERENCES locations(id)
        );

        insert into objects (id, name, object_status, height, location_id) values (1,’object 1′,’AVAILABLE’,220,1);

        insert into objects (id, name, object_status, height, location_id) values (2,’object 2′,’RESERVED’,210,1);

      3. /* create procedure get_locations */

        create or replace procedure get_locations (p_limit in number, p_offset in number) as

        cursor c_locations (p_l_limit number, p_l_offset number) is
        select id
        , Name
        , location_type
        from locations
        order by id
        OFFSET p_l_offset ROWS FETCH NEXT p_l_limit ROWS ONLY;

        cursor c_objects (p_l_location_id number) is
        select id
        , Name
        , object_status
        , height
        from objects
        where location_id = p_l_location_id
        order by id
        ;

        l_limit number := 1000;
        l_offset number := 0;

        l_first_location number :=0;
        l_first_object number :=0;
        begin

        /* start printing the output */
        htp.p(‘{ “locations”: [‘);

        /* only set limit and offset if parameter is not null */
        if p_limit is not NULL
        then
        l_limit := p_limit;
        end if;

        if p_offset is not NULL
        then
        l_offset := p_offset;
        end if;

        /* fetch locations */
        for r_locations in c_locations (l_limit, l_offset ) loop

        if l_first_location = 1
        then
        /* print a comma */
        htp.p (‘,’);
        end if;
        if l_first_location = 0
        then
        /* make sure the first time, no comma is printed */
        l_first_location := 1;
        end if;

        htp.p(‘{ “id”:’||to_char(r_locations.id)||’,’);
        htp.p(‘ “type”:”‘||r_locations.location_type||'”,’);
        htp.p(‘ “name”:”‘||r_locations.name||'”,’);
        htp.p(‘ “objects”:[ ‘);

        for r_objects in c_objects(r_locations.id)
        loop

        if l_first_object = 1
        then
        /* print a comma */
        htp.p (‘,’);
        end if;
        if l_first_object = 0
        then
        /* make sure the first time, no comma is printed */
        l_first_object := 1;
        end if;

        htp.p(‘{ “id”:’||to_char(r_objects.id)||’,’);
        htp.p(‘ “name”:”‘||r_objects.name||'”,’);
        htp.p(‘ “status”:”‘||r_objects.object_status||'”,’);
        htp.p(‘ “height”:’||to_char(r_objects.height));
        htp.p(‘}’);
        end loop;
        /* print the closing brackets for object */
        htp.p(‘ ]’);
        htp.p(‘}’);

        end loop;

        /* print the closing brackets for location */
        htp.p(‘ ]’);
        htp.p(‘}’);

        end get_locations;

      4. thatjeffsmith Post
        Author

        Cool, thanks for sharing!

        I have your scenario running on my box now.

        So, you just want to also add an offset and limit value pair at the bottom, or do you also want a link for NEXT based on the offset?

    2. If i call this service without parameters:

      https://www.test.nl/ords/api/protocol1/locations

      {
      “locations”: [{
      “id”: 1,
      “type”: “STREET”,
      “name”: “Location 1”,
      “objects”: [{
      “id”: 1,
      “name”: “object 1”,
      “status”: “AVAILABLE”,
      “height”: 220
      },
      {
      “id”: 2,
      “name”: “object 2”,
      “status”: “RESERVED”,
      “height”: 210
      }
      ]
      }]
      }

      If I call this service with parameter limit:

      https://www.test.nl/ords/api/protocol1/locations?limit=1

      400 – Bad Request

      1. thatjeffsmith Post
        Author

        I TOTALLY missed the point of your question, I was thinking you wanted to use them in the response, not in the parameters, although you very clearly stated so.

        They are unfortunately reserved by ORDS. I don’t suppose you can tack a 1 at the end of yours? Otherwise I can log an ER for your scenario.

      2. Yes, indeed, I was talking about the parameters. But thanks a lot for you fast response, much appreciated.
        Unfortunately, indeed, the protocol we are using defined the use of the parameters “limit” and “offset”, so I cannot use other words.
        Another solution I am thinking about is using URL rewrite rules to change the parameters from limit into limit1 and offset into offset2, but this has some drawbacks, so I would be very happy if you could log my scenario as an Enhancement Request. Thanks again.

  19. Hi Jeff, I was wondering, who gave you the idea for adding this super-successful page to your blog?

    Seriously, I am new to ORDS but have an advanced issue. [insert zinger here]

    If I define a handler, and protect it with OAuth2 using a grant type of “client credentials”,
    Is there a way that ORDS can provide the CLIENT_ID to the hander or allow the handler to query ORDS to retrieve the CLIENT_ID?

    Thank you.

  20. When I trying to debug Oracle 12c stored procedure using SQL Developer 18.1 there is an error:

    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘10.1.5.16’, ‘58700’ )
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: on “SYS.DBMS_DEBUG_JDWP”, line 68
    ORA-06512: on line 1

    The origin of this error was described in many blogposts and required steps are well known.

    But DBA could decline because of in some other popular IDEs (such as TOAD, PL/SQL Developer etc… the debugging is working). Is it possible to switch from debugging via JDWP to mechanism used in other IDEs (I believe it is related to OCI).

    Maybe there is some strong pros of JDWP if it is the only option in SQL Developer?
    Thanks!

    1. thatjeffsmith Post
      Author

      Toad used to support the JDWP debugger interface, but they dropped that a few years ago.

      You can use the old, Probe API debugger in SQL Developer, but we discourage it because it’s basically seen very little active development over the years and it doens’t support the ability to do things like automatically watch your PL/SQL tables. In the old debugger you have to declare in your code, local variables, to hold the contents of your PL/SQL tables and watch those.

      ACL is not a debugger thing, it just affects the debugger.

      Most shoppes have an official, supported database tool. Does yours?

      1. Thank you for your answer!

        I believe that in our case each single developer is using the tool preferred by himself. And most of database developers are fond of PL/SQL Developer or Toad. A couple of devs are using Jetbrains Datagrip, but my opinion that official Oracle tool would be the best choice for Oracle database development, but I’m primarily Java server-side developer.

      2. thatjeffsmith Post
        Author

        IF they use the debugger A LOT, let them know there’s more they can get out of it – assuming you’ll be willing to open up the ACL on your development machines.

  21. Hi Jeff,

    During modelling in SQL Developer Data Modeller I found out that I cannot define range subpartitions in composite HASH-RANGE level. Could you please clarify on that, because nothing mentioned in the docs.

    Thank you.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
  22. Hello, Jeff 🙂

    Yesterday I posted a question about “Meeting some issues when using SQLcl 18.1.1” on the subspace SQLcl – “https://community.oracle.com/community/database/developer-tools/sql_developer/sqlcl” on ODC database forum, and my thread is here – “https://community.oracle.com/thread/4153283”.

    Please take a moment to notice,thanks in advance!

    Best Regards
    Quanwen Zhao

    2018/06/20

  23. Hi,

    I’m trying to do the following:
    1. Make a snapshot of the schema
    2. Insert some data (not through SQL scripts but from middle-layer interface).
    3. Make another snapshot.
    4. Do the diffs on the snapshots to see what tables were affected and how (Possibly generate sql Data Objects to re-create the same behavior through SQL scripts?)

    Possible to do this with SQLDeveloper? What to look at?

    1. thatjeffsmith Post
      Author

      Our schema snapshots do not cover data. So you need a way to log your mid tier calls so you can replicate said data changes after you implement the schema definition scripts (DDL)

      1. Thanks for the reply.
        What about exporting table DDLs? Will that work and give me DDLs reflecting the changes made to a table?

      2. thatjeffsmith Post
        Author
  24. So my question is about SQL Developer Unit Test Suite. I am using a windows machine , SQL Developer version 4.1.2.20 and Java version 1.8. I am creating a unit test for a procedure with ref cursor output, when I run my test which is testing for an exception of NULL input value I get an error: java.lang.NullPointerException:null.. please could you point me in the right direction to work around this error. I was told for earlier version for Java and SQL Developer Unit Test this error does not persist. Thanks.

    1. thatjeffsmith Post
      Author
      1. Here is the use case:

        Sample Procedure:
        PROCEDURE get_person(userid IN person.userid%TYPE,
        person_data OUT SYS_REFCURSOR) AS
        exc_invalid EXCEPTION;
        lower_userid person.userid%TYPE;

        BEGIN
        IF userid IS NULL THEN
        RAISE exc_invalid;
        END IF;

        lower_userid :=lower(user_id);

        OPEN person_data FOR
        SELECT *
        FROM person_table
        WHERE lower(user_id) = lower_userid;

        EXCEPTION
        WHEN exc_invalidTHEN
        –code here
        END get_person;

        Unit Test for if Userid is NULL:
        Specify parameter:
        Parameter Datatype in/out input Result TestResult
        USERID VARCHAR2 IN Userid
        PERSON_DATA REF CURSOR OUT PERSON_DATA$

        Dynamic Value Query:
        select NULL as USERID , NULL as PERSON_DATA$ from dual

        Expected Result Exception 2001 Enter expected error number or “ANY”

        Process Validation:
        Query returning no row(s)
        Apply
        select * from person_table
        where lower(t_userid) = ‘{USERID}’;

        When I run this unit test I get:
        Implementation – NegativeTestCase_InputIsNull ERROR 89.0 java.lang.NullPointerException: null
        oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:366)
        oracle.jdbc.driver.OracleCallableStatement.getCursor(OracleCallableStatement.java:2645)
        oracle.jdbc.driver.OracleCallableStatementWrapper.getCursor(OracleCallableStatementWrapper.java:956)
        oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCStatement$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.getCursor(Unknown Source)
        oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingCURSOR.customOutputByPosition(CallableBindingCURSOR.java:53)
        oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingDatumAtName.customOutput(CallableBindingDatumAtName.java:123)
        oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.getOutput(CallableBindingImpl.java:74)
        oracle.dbtools.unit_test.testObjects.UtTestImplArgs.getOutput(UtTestImplArgs.java:320)
        oracle.dbtools.unit_test.runner.Runner.executeRunnerObject(Runner.java: …
        Operation Call ERROR 89.0 java.lang.NullPointerException: null
        oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:366)
        oracle.jdbc.driver.OracleCallableStatement.getCursor(OracleCallableStatement.java:2645)
        oracle.jdbc.driver.OracleCallableStatementWrapper.getCursor(OracleCallableStatementWrapper.java:956)
        oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCStatement$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.getCursor(Unknown Source)
        oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingCURSOR.customOutputByPosition(CallableBindingCURSOR.java:53)
        oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingDatumAtName.customOutput(CallableBindingDatumAtName.java:123)
        oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.getOutput(CallableBindingImpl.java:74)
        oracle.dbtools.unit_test.testObjects.UtTestImplArgs.getOutput(UtTestImplArgs.java:320)
        oracle.dbtools.unit_test.runner.Runner.executeRunnerObject(Runner.java: …
        IN Parameter #1 – USERID Value: [null]
        OUT Parameter #2 – PERSON_DATA

        I tried to replicated my user case the best I can. Please let me know if you can help with this.

      2. thatjeffsmith Post
        Author
  25. Jeff,

    I created a cluster in develpment, like:
    create cluster dd_cluster (region number(6),cust number(10),invoice number(10)) size 1028;
    and the tables that use the cluster.

    When I go to use Cart, I find no way to add the cluster. I add the tables figuring the dependent objects will be added. But DDL for the cluster is not generated. Is there something I am missing so I can navigate to clusters and add to cart?

    I am on 18.1. Export database does not appear to generate ddl for clusters either.

  26. My employer has decided that all staff are moving from SVN to GIT over the next few months. We are also in the process of converting away from Oracle Designer (yes, the old, unsupported one) with the decision to use Oracle SQL Developer Data Modeler. I’ve seen in older posts from 2016 that SD-DM doesn’t support GIT. Has this changed over the last two years? Is it possible to use SD-DM with a GIT repository through BIT Bucket?
    Thank you in advance for your insight!

      1. thatjeffsmith Post
        Author

        That won’t be an option for Modeler users. Sure, they could use it for their 31,257 design XML files, but in the Modeler SVN kit, you can simply do work on the design itself, and not worry about the underlying file structure.

        This bespoke implementation is why we haven’t had time to re-implement it into Git…also that the nature of Git doesn’t lend itself to what we want to do very easily either

  27. I’m trying to find the option for ‘hiding the Connection Navigator’ until I hover over it; having the extra real estate is good even with a large monitor. I’m using version 17.4.0.355 — it’s working fine some of the time, but other times I’ll launch SQL*Developer and see the Connections pane open and hogging the lefthand-side.

    1. thatjeffsmith Post
      Author
  28. “Hang Proofing” Oracle SQL Developer

    I’m used to working with lots of tabs and going back to reuse queries that I don’t want to save to file. Oracle SQL Developer just freezes up after a few days work. I was searching for a feature that would save all open tabs content and open it after a restart. That way I would close the app more often, because it hangs usually because it uses up too much memory or something else…
    Please make SQL Developer more freeze proof – if it has to be killed because it’s not responding, open the tabs that were open before. I don’t care about result sets, just give me back my Worksheet content. Digging up queries from SQL History is very unpractical. I’m used to Chrome restoring tabs after a restart and most Mac apps usually open back up just the way I left them before a crash.

    1. thatjeffsmith Post
      Author

      I’ve had instances of SQL Developer going for week. I’m currently on Day4 now, and that’s with repeated Windows sleep/hibernations.

      I have a hunch your issue is connection related.

      Do me a favor and try this next time…

      Once you come back to sqldev tomorrow morning, don’t touch the worksheets.

      FIRST, go to the connections panel and do a ‘reconnect’ on your open ones first.

      Or, when you leave for the day, leave your tabs open but disconnect your sessions first.

      As for SQL History, try the filter. It will make it much more usable.

      1. Thanks for a quick reply.

        I agree that connections drop often. Even SQL Developer often opens a message that connection has been reset. I also use shared connections (one query running on connection at a time) and have defined connection for each schema. SQL Developer rarely freezes between days. Most often it freezes while I work with it. I think that Java just eats up resources and doesn’t release then even if I close result tabs or worksheets.
        It might get better if I disconnect, but disconnecting also closes up some edited objects.

        I’m a very visual person and leaving tabs open makes me remember quickly what I was working on and what I want to continue or do next . This is where coming back to environment just as I left it, sparks my ideas how to continue my work. I do a lot of customer support and leaving queries open makes it easier to reuse queries if there is a followup on my support reply. Freezing of SQL Developer makes me “loose the thread”. Saving files and reopening is just too clumsy, because I already have tons of files of saved queries that are hard to manage.

        I really miss that SQL Developer doesn’t have a “Save Workspace” feature.

      2. thatjeffsmith Post
        Author

        Try increasing the max memory available for the JVM.

        And next time it freezes, grab a jstack – and we can see exactly where the time is going.

      3. Our work remote environment uses Cisco VPN and the network team has TCP idle session timeout disconnect set somewhere between 15-30 minutes. When VPN server detects and disconnects an idle SQLDdev db connection, then closing SQLD main window or closing a worksheet attached to the victim db connection will clock forever and forces task manager kill.

        Multiply this times scores of contractors and employees working daily from home or other countries making this phenomena a daily productivity killer at scale.

        Only as a point of reference, I asked the AllRound PL/SQL Developer programmers back in the early 2000’s to add a feature to send a packet ping to each TCP connection every n seconds (configurable). We had everyone using PLSQLDev enable idle connection ping every ‘600’ seconds and db connections stay alive all day until main VPN session times out after 12 hours.

        Worked great for years until the free alternative came on the scene!

        Tim…

      4. thatjeffsmith Post
        Author

        3 things:
        1)then closing SQLD main window or closing a worksheet attached to the victim db connection will clock forever and forces task manager kill
        Have you tried right-clicking on the connection and doing a ‘reconnect?’

        2)Your VPN is setup to kill idle sessions. Your asking the tool to fake activity to bypass a network policy. Instead of asking tool to lie for you, why not ask the VPN folks to add a waiver or exception for SQL*Net/JDBC traffic?

        3)You could do this on your own by enabling DBMS OUTPUT polling or by opening a SELECT 1 FROM DUAL with a 2 minute refresh.

        I would go to the network folks, b/c then your solution will work across ALL your db tools. SQL*Plus, SQL Developer, whatever.

  29. Hi
    When I am connecting to oracle 11gR2 remote database with Toad version 12.5. , Its getting connected. When trying to connect the same using Oracle sql developer version 3.0 its getting connected . however whenever I am trying to connect using Oracle sql developer higher version ie Greater than 3 its not getting connected. Its giving the error “Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection” .
    I even tried with the latest version 18.1 and facing same problem. Why is it so.

    Thanks,

    1. thatjeffsmith Post
      Author
  30. So my question is about sock proxy on mac os. For some time this worked with sqlcl but a recent update may have stopped it but don’t know that for sure. I would also like for this to work in SQL developer also. It can be challenging to set up a tunnel for each connection.

    I have looked at the docs for Java and have not been able to find a solution yet. 🙁
    for some reason
    -DsocksProxyHost=localhost -D socksProxyPort =1080
    or
    -Ddbtools.system_socks_proxy=localhost:1080
    are not working

    also it does not look like detect_macosx_proxy function by jmcginni in bin/sql (line 381..415) look for socks connections.

    my network global proxies look like this.

    cat ${scutil_out}
    {
    SOCKSEnable : 1
    SOCKSPort : 1080
    SOCKSProxy : localhost
    __SCOPED__ : {
    en0 : {
    SOCKSEnable : 1
    SOCKSPort : 1080
    SOCKSProxy : localhost
    }
    }
    }

    This is the changes I have made to test.

    —————————————————————————-
    diff –git a/bin/sql b/bin/sql
    index 58ff74b..adea77e 100755
    — a/bin/sql
    +++ b/bin/sql
    @@ -295,7 +295,10 @@ DetectSystemHttpProxySetting()

    if [ ! -z “$socks_proxy_tmp” ] ; then
    # jargs=”-Dnetbeans.system_socks_proxy=\”$socks_proxy_tmp\” $jargs”
    – AddVMOption -Ddbtools.system_socks_proxy=$socks_proxy_tmp
    + # AddVMOption -Ddbtools.system_socks_proxy=$socks_proxy_tmp
    + AddVMOption -DsocksProxyHost=$socks_proxy_host
    + AddVMOption -DsocksProxyPort=$socks_proxy_port
    +
    fi

    }
    @@ -390,28 +393,14 @@ show State:/Network/Global/Proxies
    close
    EOF

    – if /usr/bin/grep “ProxyAuto.*: *1” ${scutil_out} >/dev/null 2>&1; then
    – if /usr/bin/grep “ProxyAutoConfigEnable.*: *1″ ${scutil_out} >/dev/null 2>&1; then
    – http_proxy_tmp=”PAC `/usr/bin/grep ProxyAutoConfigURLString ${scutil_out} | /usr/bin/awk ‘END{print $3}’`”
    – rm ${scutil_out}
    – return 0
    – fi

    – rm ${scutil_out}
    – return 1
    – fi

    – if /usr/bin/grep “HTTPEnable *: *1” ${scutil_out} >/dev/null 2>&1; then
    – http_proxy_host=`/usr/bin/grep HTTPProxy ${scutil_out} | /usr/bin/awk ‘END{print $3}’`
    – http_proxy_port=`/usr/bin/grep HTTPPort ${scutil_out} | /usr/bin/awk ‘END{print $3} ‘`
    – http_proxy_tmp=$http_proxy_host:$http_proxy_port
    + if /usr/bin/grep “SOCKSEnable *: *1″ ${scutil_out} >/dev/null 2>&1; then
    + socks_proxy_host=`/usr/bin/grep SOCKSProxy ${scutil_out} | /usr/bin/awk ‘END{print $3}’`
    + socks_proxy_port=`/usr/bin/grep SOCKSPort ${scutil_out} | /usr/bin/awk ‘END{print $3} ‘`
    + socks_proxy_tmp=$socks_proxy_host:$socks_proxy_port
    rm ${scutil_out}
    return 0
    fi

    – http_proxy_tmp=”DIRECT”
    – rm ${scutil_out}
    – return 0
    }

    #
    ———————————————————————————–

    Any help would be greatly appreciated!

  31. Hello. I am currently using Version 4.0.3.16. I currently deal with tables with million + rows. They take several minutes to populate the data tab when opened. Is there a way to preset a table filter on an indexed column so the data tab responds quicker? Or turn off the initial population of the data tab until a filter is entered? Thank you for your time.

    1. thatjeffsmith Post
      Author
  32. Hi Jeff,

    I’m using 18.1.0 at the moment and I’ve been trying to export data from a query that can take some time. Twice now I have successfully run the query, gotten back the initial results and when I try to export the data, after some time…I get a java.io error that I’ve never had on previous versions with this particular query.

    Once you’ve read the below, I was hoping you might be able to suggest somewhere to start with my fault finding as I have no idea where to start.

    The error is as follows:
    java.io.IOException: The handle is invalid
    at java.io.FileOutputStream.writeBytes(Native Method)
    at java.io.FileOutputStream.write(FileOutputStream.java:326)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at sun.nio.cs.StreamEncoder.implFlush(StreamEncoder.java:297)
    at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:141)
    at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:229)
    at oracle.dbtools.raptor.format.ResultsFormatter.checkAndFlush(ResultsFormatter.java:601)
    at oracle.dbtools.raptor.format.ResultsFormatter.write(ResultsFormatter.java:590)
    at oracle.dbtools.raptor.format.DelimitedFormatter.printColumn(DelimitedFormatter.java:148)
    at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:281)
    at oracle.dbtools.raptor.format.ResultsFormatter.print(ResultsFormatter.java:555)
    at oracle.dbtools.raptor.format.ResultsFormatter.doPrint(ResultsFormatter.java:769)
    at oracle.dbtools.raptor.export.ExportAPI.writeExportJTable(ExportAPI.java:854)
    at oracle.dbtools.raptor.export.ExportAPI.access$900(ExportAPI.java:65)
    at oracle.dbtools.raptor.export.ExportAPI$1.doWork(ExportAPI.java:1253)
    at oracle.dbtools.raptor.export.ExportAPI$1.doWork(ExportAPI.java:1238)
    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

    1. thatjeffsmith Post
      Author

      Something bad happened while we were writing the file.

      Does this happen with other queries? Or just this one particular one? Is it long-running AND a large data set? What data types are involved? What type of file? Where are you writing the file to, local or network drive?

      1. Thanks for the response Jeff. Answers as follows:

        Does this happen with other queries or just this one particular one?
        So far, after about a month of using this version, it has only been this particular query.

        Is it long-running AND a large data set?
        It is usually a long-running query but it depends on certain activities to generate the records over the previous month. It’s usually somewhere between 40 and 55 hours to run.
        Data set usually ends up around 10k rows and 23 columns with average length of 150.

        What data types are involved?
        Types are varchar2, date and number; nothing too outlandish.

        What type of file?
        Exporting to default .csv file

        Where are you writing the file to, local or network drive?
        It’s a networked shared drive. You’re going to suggest I try the local right? You’ve already helped 🙂 I will try this and see what happens.

        FYI – I ended up hitting Ctrl-A to pull all data out instead of just an initial sample and then copied it manually as a workaround which SQL Dev had no issues with.

      2. thatjeffsmith Post
        Author

        50 hrs? Yikes.

        Have you looked into getting help for tuning that?

        When you do an export, we rerun the query, so another 50 hrs…I’m not surprised it fails.

        Have you considered creating a MV?

      3. ‘Yikes’ indeed. It is a query that has provided some frustration.

        Tuning is something that it probably needs but it is inherited and I know the previous owner refined it a couple of times already. I have been considering it and there are some people I can talk to but there’s just a lot going on right now and it’s not exactly high on the agenda, unfortunately.

        When you do an export, we rerun the query, so another 50 hrs…I’m not surprised it fails.
        Yes, but (and this is the whole reason I posted here) the query has not been an issue in the past, the previous version of SQL Dev it ran on never seemed to have any issues with it. There is probably a myriad of different things that could be impacting but wasn’t sure if this might possibly be an issue with the current build or not.

        Materialised View? No, didn’t even know what it was until you suggested it. I’ve started reading and will see if that is something that might work in this particular situation.

  33. Back in April 2013 you did a blog post titled ‘Die! Or How to Cancel Queries in Oracle SQL Developer’ Very helpful, but SQL*Developer has gotten more mature and I wonder if it needs to be revisited. Back then you had a section titled ‘But Why Won’t My Cancel Work?’ where you install the OCIJDBC and configure by simply clicking on Tools -> Preferences -> Database (expand)->Advanced and then check the ‘Use OCI/Thick driver’. Did that work for all types of connection types (i.e.: Basic, TNS, etc.) or not? Now with 1.8.1 you can’t check the’ Use OCI/Thick drive’r unless you click on ‘Use Oracle Client’ and configure it. Or has the requirement overall changed. Please clarify. And thanks for all that you do for the SQL*Developer community!

    1. thatjeffsmith Post
      Author
  34. After disable SSH, NoSQL and Cloud extensions I’m not able to launch data modeler any longer.

    I’m getting the following errors:

    Product extension oracle.datamodeler could not be loaded. The product cannot start.
    Disabled extensions:
    oracle.sqldeveloper.onsd: Disabled by user
    oracle.sqldeveloper.cloud: Disabled by user
    oracle.sqldeveloper.ssh: Disabled by user
    oracle.datamodeler: Missing dependencies: oracle.sqldeveloper.ssh, oracle.sqldeveloper.cloud, oracle.sqldeveloper.onsd

    PRODUCT=Oracle SQL Developer Data Modeler
    VERSION=18.01000821035f
    VER=18.1.0
    VER_FULL=18.1.0.082.1035
    BUILD_LABEL=082.1035
    BUILD_NUM=082.1035

    Though I’ve downloaded the data modeler again, the error persists.
    Is there any way to re enable the extensions without using the IDE, so I can still using Oracle Data Modeler again?

    Thanks

    1. By the way, I also tried removing the AppData/Roaming, but still getting the error and not launching SQLData Modeler,

      Thanks

      1. Solved,

        Also removed the directory “Oracle SQL Developer Data Modeler” within AppData\Roaming dir.
        I didn’t remove that one. Sorry

        Thanks

      2. thatjeffsmith Post
        Author

        Sorry, you caught me on a plane, glad to hear you got it going.

        There might be a bug here, either it should work w/o those features, or we shouldn’t let you disable them.

  35. Hi Jeff,

    How to add PROFILE in the ddl to create user (or Create Like user) in SQL Developer 17.2?
    It includes, system privileges, and object privileges (when I check the box) but doesn’t include default profile.
    I want to select the same profile of the user which I am using to create alike.

    Thanks in advance.

    Shoaib

    1. thatjeffsmith Post
      Author
  36. Hello,

    When I’m trying to open a table with SQL Developer throw Oracle Client I get this error:

    An error was encountered performing the requested operation:

    ORA-01460: unimplemented or unreasonable conversion requested
    01460. 00000 – “unimplemented or unreasonable conversion requested”
    *Cause:
    *Action:
    Vendor code 1460

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        >>Oracle 10.2.0
        That’s your problem, we only support 11gR2 and higher.

        You can try an older copy of SQL Developer, maybe 3.0, if not, maybe 2.1.

  37. When I press up arrow in SQLcl to recall history, my terminal just beeps. I think it may not understand the encoding or emulation I’m using? Any suggestions? I’m on Mac High Sierra and using iTerm2. I’ve tried running it locally and on a Linux server with the same results.

    1. Status update… it appears to be related to “normal” vs “application mode” cursor keys. Programs like vim will change into application mode and then change back to normal mode when returning control to the terminal. SQLcl appears to require application mode cursor keys to work, but doesn’t set that up for you.

      I tried making a bash script to invoke it as follows:
      tput smkx
      ~/sqlcl/bin/sql “[email protected]
      tput rmkx

      That works fine until I try to use “edit” within SQLcl, which passes control over to vim, and unsets application mode cursor keys when it exits, at which point my up arrow is broken again.

      Bug?

  38. Sometimes I need to stop a query / procedure running in a sql-worksheet. Then I have to kill the session.
    It would be nice to see SID, SERIAL# of each worksheet session. Is there a way to get that information in the GUI ?

    1. thatjeffsmith Post
      Author

      By SQL it’s easy

      SELECT sid, serial#, 
        FROM V$SESSION
       WHERE AUDSID   = USERENV(
          'sessionid'
      );

      By GUI, the most straightforward way is the Tools – Monitor Sessions page. It has a ‘Kill Session’ feature built in as well.

      1. Hi Jeff,
        thanks for your answer. I want to describe the situation more detailed.

        Usually I have mor than one worksheet opened, e.g. ten worksheets. In one or two of that worksheets
        I get a problem and want to kill the sessions. Then it’s too late to start the sql query you suggested.
        Therefore it would be helpful to identify SID, SERIAL# inside the worksheet either in case a process is hanging.
        Another idea would be a configuration which allow to run automatically an sql-statement when I open a new worksheet. Then I can run your sql-stmt initially.

      2. thatjeffsmith Post
        Author

        The monitor session page shows you the current SQL, so you know for sure which one to kill.

        We have the login feature where you can have a query/script executed at connection time.

  39. Jeff,

    Is there any way to make a SQLDEV:LINK that
    a) drops you right into a table’s editable data grid and
    b) applies a filter to the grid

    I imaging a query result where clicking on a column would take you over to edit that particular row.

    I wish there was just some sort of documentation on creating links in SQLDeveloper!

    Thanks!

    1. thatjeffsmith Post
      Author

      Ummmm, no.

      I think for now it’s gonna be manually done where you copy the WHERE clause, ctrl+click into the table, and paste that into the Data tab filter input area…

  40. In SQL*Developer I can write a statement in a worksheet like ‘SELECT * FROM MY_TABLE’ and see the results in Query Results. I can then right click on a column header and hide various columns from the query results so that I can export the data, except those columns, to an file as insert statements. One of the columns that I want to export is an ‘activity date’ column where each row has a different date. But for the insert statements, I want to substitute ‘sysdate’ instead of the actual date from the record (so when inserted it will reflect the date/time that the statement was executed. But I don’t see in the export wizard where I can modify column values for the export. Is such possible?

    1. thatjeffsmith Post
      Author
      1. I want the insert script to reference ‘sysdate’ when it does the insert to get the current date/time when the script is run, not reference ‘sysdate’ for when the data is extracted. If I
        select sysdate activity_date from my_table;
        I get the date/time that the data is being exported. If I
        select ‘sysdate’ activity_date from my_table:
        I get in the script:
        Insert into MY_TABLE (ACTIVITY_DATE) values (‘sysdate’);
        which when executed results in:
        Error starting at line : 5 in command –
        Insert into MY_TABLE (ACTIVITY_DATE) values (‘sysdate’)
        Error report –
        ORA-01858: a non-numeric character was found where a numeric was
        expected
        So I need to ‘alias’ the data upon export, not when selected into the ‘Query Results’ panel. Or am I missing something?

      2. thatjeffsmith Post
        Author

        easier just to make the default value for your column definition to be SYSDATE, then when your script inserts a NULL for that value, SYSDATE will be used. will give you exactly what you want.

      3. I was also trying to avoid writing a query (or using query builder) that references seventy-nine columns and aliases one for ‘sysdate’ by using a ‘SELECT *’ and then hiding the two columns that shouldn’t be included in the export… while still being able to alias one column.

  41. I downloaded the new sql Developer version 18.1. The font is way too small in most of the areas that appear to be unchangeable, examples: titles, help screens, table names, etc. Is there any way to change these fonts? I know where to change the fonts for the code I type in myself.
    I reinstalled version 17.4 which is fine for me.

    1. thatjeffsmith Post
      Author
  42. I’m wondering if the following problem reproduces for anyone using SQLD 18.1.0.095.

    This morning when I logged into a database SQLDev prompted that my password would expire in 4 days. Fine, I clicked Ok and went about my task. Afterwards executing any sql statement returned ‘/ by zero’ and logging window shows the entries below. Changed my password in sqlplus in another session, came back to SQLDev, reconnected, and now all is well.

    Tim…
    =====
    SEVERE 139 19 oracle.dbtools.raptor.utils.Connections
    SEVERE 138 0 oracle.dbtools.raptor.utils.Connections / by zero

  43. I have version 17.2.0.188 running on windows 7 64bit. When I open SQL Developer, about 50 files open up in the tab. How do I restrict it to the last 5 ? Thanks.

    1. thatjeffsmith Post
      Author
  44. To save time!

    We usually sent many jobs simultaneously in order to update tables, create materialized views, recreate indexes, et al… and ,at the end of all these, with all that done, we generate hundreds of EOM end of month reports for management.
    (We had done this in previous Dev versions without problems).

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author

        We severely recommend doing as much as you can in a single instance of the tool, and if you need to run multiple copies, setup multiple installs.

        But you should be able to do everything you need in a single instance, including looking at > 1 object at once, running more than 1 query at once….

        I have no idea why you could run 3 copies but not 4, unless you’ve exhausted the memory on your machine.

  45. I am having an issue to open more than 1 instance of Dev 18.1 I used to open as many of 4 or 5 instances simultaneously on an oldier 4.20 version. Now, I got stuck constantly when opening the next ones (sqldeveloper64w.exe) in the SIGN IN screen and nothing in it functions, not even the X in the upper right corner. Please fix.

    1. thatjeffsmith Post
      Author

Leave a Reply

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