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,345 Comments

  1. Hi, my question is related to ORDS.
    Is there any way to instruct ORDS to make JSON Reference as relative URIs?
    Example:
    From: “$ref”: “http://localhost/ords/agreements/
    To: “$ref”: “/ords/agreements/
    I’ve read JSON reference draft as APEX docs mention that JSON references are based on it, and this draft contemplates relative URIs in section: 4. Resolution.

    References:
    https:// http://www.thatjeffsmith.com/archive/2017/05/ords-restful-services-and-uri/
    https:// http://www.oracle.com/tools/technologies/rest-data-services.html#about_uris
    https:// tools.ietf.org/html/draft-pbryan-zyp-json-ref-03#section-4

  2. We have a large database that was developed using Oracle Designer and are looking to start using Data Modeler for future development work. In Designer you could define an entity short name, and when generating out to a table there was an option to use this as a column prefix as well as for keys – eg to generate a column called PROD_DESCRIPTION instead of just DESCRIPTION. We have used this approach to give us uniquely named columns across all our tables . I can’t find a way of doing this in Data Modeler, unless we actually assign the prefix at attribute level (but can’t do that for domain based attributes). Is there a way of assigning a prefix to a column, preferably when engineering from logical to relational model, or otherwise at the point of generating DDL ?

    • thatjeffsmith

      Yeah you can define default naming patterns in the design preferences. I’m on vacation, but someone should be able to help you on the forums.

    • I know about Templates in Design Preferences which gives you control over the naming of various types of keys / constraints/indexes. But I can’t find anything that allows you to define rules for actual attributes when engineering to columns.

  3. We have just started to use sql developer, having previously used TOAD. We connected to a database and then did file open to bring in an sql file. We did a shift-F4 to see the describe on a table but it returned saying connection required. So several questions come to mind.
    1) How do we know if the tab we are viewing is “connected” to a database?
    2) after opening a file, how do we connect to the database without having to run it.
    3) Are there any settings in preferences that allow the session to stay connected?

    Regards
    Jeff

    • thatjeffsmith

      After you open the file, upper right hand corner, use the connection selector to choose the connection you want to execute the file against.

  4. Hi, Jeff,

    I can’t create NEW connections; previously existing connections work fine.
    AppData folder and subfolders for my user have full admin perms.

    I get this error: “Could not load connection type:Oracle
    null.” Recently upgraded to Oracle SQL Developer 19.1.0.094
    Same issue with 18.3 (which worked fine for months until recently).

    Any help is much appreciated.

    • thatjeffsmith

      See if it will let you export your connections.

      If so, delete the connections.json file and then do an import.

      If that doesn’t work, rename your system19.1 directory, start the tool and say yes to import your settings from 18.3 again.

  5. Hi Jeff,

    is there a way to tell SQL Developer to also treat // as comments? I am copying SQL back and forth between SQL Developer and an old ETL tool hat only supports // to mark inline comments, not –. Doing a search&replace twice every time is tedious, unfortunatly.

    Thanks & regards
    Chris

    • thatjeffsmith

      Sorry, no.

      And that’s a driver/DB thing, not us.

      And if we did, you’d share your code with someone, and then it’s break bc they were using something other than SQLDev, etc

    • thatjeffsmith

      It’s not SQL Developer, it’s Java. Whatever JDK you’re using determines if it’s 32 or 64 bit.

  6. Help! Why is SQL DEVELOPER just showing me the file name where the query exists instead of the actual SQL run? I am changing the SQL in that file and running the SQL as needed but it is not in SQL History.

    • in a file but I am executing each query individually using CTRL-Enter

    • Under SQL
      file:C:\folder\folder\blah.sql
      Under Type it says Script regardless if I run a single sql or multiple. If there is a place I can send a screenshot let me know.

    • Version 19.1.0.094
      Build 094.2042

      Just as an FYI…If I copy the full script out and into the Worksheet created when Connecting…All the SQL shows up in SQL History even if I run it as a script.

  7. Jack Applewhite Reply

    I have had and love sqlcl 4.2 for about 2 years. Now on the same OEL 11gR2 database servers. I’m trying to get sqlcl 19.1 running, but keep getting this error “Could not initialize class oracle.jdbc.OracleDriver”. When run from my Ubuntu 18.04 client, 19.1 works fine, I *think* because no ORACLE_HOME is set. I found a couple of old posts on this on TechNet, but those suggestions don’t work for me.

    In the 19.1 “sql” shell script, this function definition forces JAVA_HOME to be the one in the ORACLE_HOME tree, but that’s the 1.7 JDK and not the 1.8.0 version in /opt/java I’ve been using for sqlcl 4.2. That forcing a JAVA_HOME isn’t in the 4.2 sql shell script.

    function checkADE {
    #
    # Resolve java path for development builds
    #
    if [ “m$ORACLE_HOME” != “m” ]; then
    if [ -d “$ORACLE_HOME/jdk/jre” ]; then
    JAVA_HOME=$ORACLE_HOME/jdk/jre
    PATH=$JAVA_HOME/bin:$PATH
    fi
    export SQLPLUS_CLASSIC=true
    fi
    }

    So, what do I do?

    Further testing results on the DB server, trying to connect to a local DB.

    I realized that I may need to install the exact same JDK that I installed on my Ubuntu machine for SQLDev and sqlcl 19.1, so I installed it on the DB server. No luck, same error.

    If I run the sqlcl/bin/ sql script, as provided, I get this:
    Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file
    Which is to be expected, since the ORACLE_HOME JDK is 1.7.

    If I force JAVA_HOME to the correct one for sqlcl by doing this in the checkADE function in the sql script
    # JAVA_HOME=$ORACLE_HOME/jdk/jre
    JAVA_HOME=/usr/java/jdk1.8.0_211-amd64
    I still get this:
    ERROR: Could not initialize class oracle.jdbc.OracleDriver

    Now, if I force JAVA_HOME to be the correct one, and unset ORACLE_HOME in the script, I can connect to a remote DB. Also, I can connect to a local DB if I pretend it’s remote by using “[email protected]”. It works, but will require us to modify all our scripts that use sqlcl for the special reasons it’s superior to sqlplus.

    So, being a DBA and not a Java-head or super-scripter, I don’t know what to do next to allow “normal” connection to a local DB. Advice welcome.

    Also, in the “run” function in the script I tack on this to run glogin to set the command prompt automatically.
    @”$ORACLE_BASE/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql”
    I was using $ORACLE_HOME/sqlplus/admin, but can’t now because I have to unset ORACLE_HOME for sqlcl to even work. I’ve looked at the sqlcl docs and can’t see any sqlcl equivalent to glogin.sql. Is there?

    Thanks.
    Jack

  8. Hallo Jeff,

    I am using Oracle based RESTfull service within the Oracle Apex 18.2 environment.

    I am using Oracle 18.2 and Oracle ORDS 18.1 and Oracle APex 18.2
    I have a table with 3 fields.

    When using the apex_web_service.make_rest_request from the database a record is inserted without any problem.

    When i try this within Oracel Apex 18.2 using the ORDS Websources like below :
    apex_exec.execute_web_source(
    p_module_static_id => ‘AutoREST_service_ins_case’,
    p_operation => ‘POST’,
    p_parameters => l_parameters
    );

    Then i am getting HTTO 400.

    I create the modules and web sources from within Oracle Apex 18.2.

    Have you had done a post using apex_exec.execute_web_source?

    Regards,

    Anibal

  9. Joseph Couture Reply

    Jeff,

    Is there any reason we couldn’t use the ORDS_PUBLIC_USER proxy account to authenticate via LDAP?

    If, in the db, I create an account for myself, such as:

    CREATE USER joe IDENTIFIED GLOBALLY AS ‘CN=Couture\, Joe,OU=co-users,OU=CO,OU=ROC,DC=som,DC=state,DC=ms,DC=us’;

    ALTER USER joe GRANT CONNECT THROUGH ords_public_user AUTHENTICATED USING DISTINGUISHED NAME;

    I have yet to discover whether it would be possible to achieve this in the Standalone ORDS environment which ships with the SQL Developer IDE. I issued the db commands and tried put in my AD credentials in the Basic Authentication dialog rendered by the browser, unsuccessful.

  10. Hi Jeff,

    I have created a web source which will need to post (insert) a record in a table.
    Executing the url in the Resting tool in Firefox a record is being inserted correctly.
    When trying this from within Apex 18.1 using ORDS ( Version 18.1.1.95.1251 ) web source by submitting the page which activate a process i am getting the error:

    ORA-20999: Web Source returned an HTTP error: HTTP 500: Internal Server Error.

    Here i am using HTTP.

    The Apex instance ‘REQUIRE_HTTPS’ is set to NO.

    Any help or hint will be much appreciated.

    Regards,

    Anibal

    • thatjeffsmith

      Check the ORDS server logs when the 500 occurs, you’ll see the insert and the underlying ORA message.

      Also, compare how the POST is constructed in Firefox vs APEX

  11. I have a query that returns CLOBs from dbms_metadata.get_ddl. In SQL Developer, I save this query output as a CSV file. It shows up fine in the output window, but when I save it, most of the time the CLOB values are saved as [email protected] Lately, I’ve been getting this result more than the actual CLOB values.

    I’ve tried spooling and using /*csv*/, but that option tends to break the code in strange places and not keep it all in the same column of output when importing into Excel.

    Any ideas? I’m using version 18.4.0.376.

    • thatjeffsmith

      Why would you want hundred line DDL exported as CSV? As soon as it hits a comma or line break, it’ll break everything.

    • I use it for doing schema compares across platforms and environments. I export as CSV, then have a PowerShell script that breaks it into files and folders by object type and object. Then I can use a tool to compare. The builtin SQL Developer export tool leaves out some things that I want to compare (jobs, for example), so I wrote my own. It works perfectly when the output actually exports the CLOBs instead of the [email protected]

    • — Specify schema to extract in object_owner function a few lines down

      WITH
      — Converts DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type
      — Java object names may need to be converted with DBMS_JAVA.LONGNAME – that code is not included

      — ========================================================================================================================
      — Owner to search database for, NOT case sensitive
      FUNCTION object_owner RETURN VARCHAR2 IS
      BEGIN
      RETURN UPPER (‘AFDSUSR’);
      END object_owner;

      — Trims leading and trailing spaces, and leading line feed characters
      FUNCTION trim_string (p_clob IN CLOB) RETURN CLOB IS
      BEGIN
      RETURN TRIM (LEADING CHR (10) FROM TRIM (TRIM (LEADING CHR (10) FROM p_clob)));
      END trim_string;

      — ========================================================================================================================
      — System generated types have to be handled differently
      system_gen_types AS (
      SELECT ‘TYPE’ object_type, name object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, text).EXTRACT (‘//text()’)
      ORDER BY line).GetClobVal(), 1) text
      FROM dba_source
      WHERE owner = object_owner
      AND type = ‘TYPE’
      AND name LIKE ‘SYS_PLSQL_%’
      GROUP BY ‘TYPE’, name),

      — Used to aggregate user granted roles in alphabetic order
      user_roles AS (
      SELECT ‘USER’ object_type, ‘ROLES’ object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, ‘GRANT “‘ || granted_role || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
      ORDER BY granted_role).GetClobVal(), 1) text
      FROM dba_role_privs
      WHERE grantee = object_owner),

      — Used to aggregate system grants in alphabetic order
      system_grants AS (
      SELECT ‘USER’ object_type, ‘SYSTEM GRANTS’ object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, ‘GRANT “‘ || privilege || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
      ORDER BY privilege).GetClobVal(), 1) text
      FROM dba_sys_privs
      WHERE grantee = object_owner),

      — Used to aggregate object grants in alphabetic order
      object_grants AS (
      SELECT ‘USER’ object_type, ‘OBJECT GRANTS’ object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, ‘GRANT ‘ || privilege || ‘ ON “‘ || owner || ‘”.”‘ || table_name || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
      ORDER BY owner, table_name, privilege).GetClobVal(), 1) text
      FROM dba_tab_privs
      WHERE grantee = object_owner
      OR owner = object_owner
      OR grantor = object_owner),

      — Referential integrity constraints
      ref_constraints AS (
      SELECT ‘REF CONSTRAINT’ object_type, table_name object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, trim_string (dbms_metadata.get_ddl (‘REF_CONSTRAINT’, constraint_name, owner)) || ‘;’ || CHR (10)).EXTRACT (‘//text()’)
      ORDER BY constraint_name).GetClobVal(), 1) text
      FROM dba_constraints
      WHERE owner = object_owner
      AND constraint_type in (‘R’) — Referential integrity constraints
      GROUP BY ‘REF CONSTRAINT’, table_name),

      — Constraints
      constraints AS (
      SELECT ‘CONSTRAINT’ object_type, table_name object_name,
      DBMS_XMLGEN.CONVERT
      (XMLAGG
      (XMLELEMENT (e, trim_string (dbms_metadata.get_ddl (‘CONSTRAINT’, constraint_name, owner)) || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
      ORDER BY constraint_name).GetClobVal(), 1) text
      FROM dba_constraints
      WHERE owner = object_owner
      AND constraint_type in (‘U’, ‘P’, ‘C’) — Unique, Primary, Check Constraints
      GROUP BY ‘CONSTRAINT’, table_name),

      — ========================================================================================================================
      — General database objects
      objects AS (
      SELECT dbao.owner,
      CASE WHEN dbao.object_name LIKE ‘MLOG$_%’ AND dbao.object_type = ‘TABLE’ THEN
      (SELECT master
      FROM dba_mview_logs
      WHERE log_owner = dbao.owner
      AND log_table = dbao.object_name)
      ELSE
      dbao.object_name
      END object_name,
      CASE WHEN dbao.object_name LIKE ‘MLOG$_%’ AND dbao.object_type = ‘TABLE’ THEN
      ‘MATERIALIZED VIEW LOG’
      ELSE
      dbao.object_type
      END object_type,
      dbao.object_name metadata_object_name,
      CASE
      WHEN dbao.object_type IN (‘JOB’, ‘RULE SET’, ‘RULE’, ‘EVALUATION CONTEXT’, ‘CREDENTIAL’,
      ‘CHAIN’, ‘PROGRAM’) THEN ‘PROCOBJ’
      WHEN dbao.object_type = ‘DATABASE LINK’ THEN ‘DB_LINK’
      WHEN dbao.object_type = ‘PACKAGE’ THEN ‘PACKAGE_SPEC’
      WHEN dbao.object_type = ‘PACKAGE BODY’ THEN ‘PACKAGE_BODY’
      WHEN dbao.object_type = ‘TYPE’ THEN ‘TYPE_SPEC’
      WHEN dbao.object_type = ‘TYPE BODY’ THEN ‘TYPE_BODY’
      WHEN dbao.object_type = ‘MATERIALIZED VIEW’ THEN ‘MATERIALIZED_VIEW’
      WHEN dbao.object_type = ‘MATERIALIZED VIEW LOG’ OR (object_type = ‘TABLE’ AND object_name LIKE ‘MLOG$_%’)
      THEN ‘MATERIALIZED_VIEW_LOG’
      WHEN dbao.object_type = ‘QUEUE’ THEN ‘AQ_QUEUE’
      WHEN dbao.object_type = ‘JAVA CLASS’ THEN ‘JAVA_CLASS’
      WHEN dbao.object_type = ‘JAVA TYPE’ THEN ‘JAVA_TYPE’
      WHEN dbao.object_type = ‘JAVA SOURCE’ THEN ‘JAVA_SOURCE’
      WHEN dbao.object_type = ‘JAVA RESOURCE’ THEN ‘JAVA_RESOURCE’
      WHEN dbao.object_type = ‘XML SCHEMA’ THEN ‘XML_SCHEMA’
      ELSE dbao.object_type
      END metadata_object_type
      FROM dba_objects dbao
      WHERE dbao.owner = object_owner
      — These objects are included with other object types
      AND object_type NOT IN (‘INDEX PARTITION’, ‘INDEX SUBPARTITION’, ‘LOB’,
      ‘LOB PARTITION’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
      — Ignore system-generated types that support collection processing
      AND NOT (object_type = ‘TYPE’ AND object_name LIKE ‘SYS_PLSQL_%’)
      — Ignore objects associated with materialized views
      AND NOT (object_type = ‘INDEX’ AND object_name LIKE ‘I_MLOG$_%’)
      AND NOT (object_type = ‘TABLE’ AND object_name LIKE ‘RUPD$_%’)
      — Exclude nested tables, their DDL is part of their parent table
      AND (owner, object_name) NOT IN (SELECT owner, table_name FROM dba_nested_tables)
      — Exclude overflow segments, their DDL is part of their parent table
      AND (owner, object_name) NOT IN (SELECT owner, table_name FROM dba_tables WHERE iot_type = ‘IOT_OVERFLOW’)),

      — ========================================================================================================================
      — Combining all objects together for final SELECT
      schema_code AS (
      — General objects
      SELECT object_type, object_name,
      trim_string (dbms_metadata.get_ddl (metadata_object_type, metadata_object_name, owner)) object_ddl
      FROM objects
      UNION ALL

      — System generated types
      SELECT object_type, object_name, text
      FROM system_gen_types
      UNION ALL

      — Referential integrity constraints
      SELECT object_type, object_name, text
      FROM ref_constraints
      UNION ALL

      — Other constraints
      SELECT object_type, object_name, text
      FROM constraints
      UNION ALL

      — User
      SELECT ‘USER’, ‘CREATION’,
      trim_string (dbms_metadata.get_ddl (‘USER’, username)) object_ddl
      FROM dba_users
      WHERE username = object_owner
      UNION ALL

      — Tablespace quota
      SELECT ‘USER’, ‘TABLESPACE QUOTA’,
      trim_string (dbms_metadata.get_granted_ddl (‘TABLESPACE_QUOTA’, username)) object_ddl
      FROM dba_ts_quotas
      WHERE username = object_owner
      AND ROWNUM = 1
      UNION ALL

      — Roles
      SELECT object_type, object_name, text
      FROM user_roles
      UNION ALL

      — System grants
      SELECT object_type, object_name, text
      FROM system_grants
      UNION ALL

      — Object grants
      SELECT object_type, object_name, text
      FROM object_grants
      UNION ALL

      — Default role
      SELECT ‘USER’, ‘DEFAULT ROLE’,
      trim_string (dbms_metadata.get_granted_ddl (‘DEFAULT_ROLE’, grantee)) object_ddl
      FROM dba_role_privs
      WHERE grantee = object_owner
      AND default_role = ‘YES’
      AND ROWNUM = 1
      UNION ALL

      — Non-default profile creation script
      SELECT ‘USER’, ‘NON-DEFAULT PROFILE’,
      trim_string (dbms_metadata.get_ddl (‘PROFILE’, profile)) object_ddl
      FROM dba_users
      WHERE username = object_owner
      AND profile ‘DEFAULT’)

      — ==========================================================================================================================
      SELECT object_type, object_name, object_ddl
      FROM schema_code
      ORDER BY object_type, object_name;

    • Just wanted to check back and see if you’d had a chance to look at the query you requested for the issue I was having. Thanks!

  12. When writing multiple lines of code it is often difficult to determine if row 60 lines up with row 20 or row 10. Is there a visual hint, such as a line, that will appear when you put your cursor at the first character of row 60 so that you can scroll up to see which rows begin in the same column? It is especially difficult when reviewing someone else’s code who indents only two spaces for each section. After a number of rows they just blend into each other.

  13. Anderson Bestteti Santos Reply

    Hi Jeff,

    I’m a former Oracle Designer user and I decided to use SQL Dev Data Modeler (DM) in order to keep applications’ ER model in maintenance and new application. I was wondering how DM manages object sharing among designs. Let’s suppose that we have two designs (D1 and D2) and I’d like to share the Entity PERSON, belonging to Design D1, to Design D2. How can I do that with DM? If it is possible, will DM reflect the changes made on Entity PERSON in Design D2?

    Thank you in advance for your attention and support.

    Kind regards,
    Anderson Bestteti.

  14. Hi Jeff,
    I’ve been using the SQL Developer Real Time SQL Monitoring for a while now, but for some odd reason, on the same databases as I was working with previously, when I am in the monitoring window, it is not refreshing. Even if I manually click the Refresh button, nothing happens.
    As I look at the screen now, it is 16:06pm. Yet the SQL at the top, is 13:54pm. Auto refresh is set to 10s. The red pin is vertical, which I believe means not frozen. Anyway, even if I click it, and wait, still nothing changes!
    Even if I run something in the SQL worksheet, nothing changes in Real Time SQL Monitor.
    When I click the refresh button, it gets framed in blue but nothing changes.

    Any ideas??? Its driving me nuts.

  15. Marcus Rangel Reply

    Hi Jeff, I work in a lot of places and companies, and I need to keep track of the pl/sql code (versioning control and further comparisons) developed in each one of this places, I’m having some difficult to “save” this codes, especially the pl objects, I also tried to use the git integration, but I see that it is more efficient to deal with scripts, any sugesttion? I’am a great fan of your work, thanks in advance!

  16. Hi Jeff

    We have two servers with ORDS. Both have exact the same names and definitions of schema, modules, templates and handlers.

    If I try to reach the URL defined as “GET”-method on the first server, I get JSON and everything is OK. But if I try to reach similar URL (only name of host differs), I get 404.

    in APEX-interface I see all the modules and templates, and all results I get selecting from ords_metadata are the same. Just like at the first server. But at the first server It works, at the second – does not. I also tried to create new module, template and handler at the second server in the APEX-interface. I created them but request still gives 404.

    What we also tried: re-start ORDS, validate ords.war. Nothing helped.

    I also asked my ora-admin switch on the screen-debug. It says “dispatcher not found”. What does it mean? What should we do?

    • thatjeffsmith

      get the standard output from ords running on the 2nd server, will show same you would see when printing debug to screen

    • Excuse me, Jeff, screen debug works really well, but I can’t understand what does it mean “dispatcher not found” in this log. I can also see that it maps to the correct database by the URI-part /myschema/ so it is hard for me to understand what is wrong? Even by ssh from this server I cant perform GET-method to itself. Maybe the reason is lack of some grants? But as I can see in PROXY_USERS and DBA_SYS_PRIVS – everything is the same as on server, on which ORDS works

    • I also switched on the debug-screen on the server, where ORDS works well.
      I got such message:
      mapped request using: /myschema/* to: -MYSCHEMA|apex|rt|,

      On the second server I have this:
      mapped request using: /myschema/* to: -APEX_PUBLIC_USER|apex|rt|,

      WHY? I have already done ENABLE_SCHEMA, what I did wrong?

  17. Vivek Gupta Reply

    Hello Jeff – We are using standalone ORDS 19.1 and wondering if there is any way to display progress bar in browser while ORDS is running plsql code or dbms_scheduler jobs in background? Few of our APIs runs for 2-3 minutes and looking at empty white browser for 3 minutes is quite depressing!! and wondering if there is any way to get a progress bar to track the %age completion?

    Thanks,
    Vivek

    • thatjeffsmith

      For long running requests better that your response be a 200 with a link to follow for status updates

  18. Hi Jeff

    I’m using SQL Developer Data Model 4.1.5 (907) and I’m facing some issues when generating the Relationald Model and Physical DDL . I’ve tried several sites and documentation but I could’n find yet an answer. I hpoe you can help me .

    The issues are :
    1) On Logical model I have two relationship for the same table, in a master-detail relationship. When generating te Relational model it is created two fields in the child table as the FK for the master table., as expected. However the detail table fields names are mastercode and mastercode1 , for instance. Then I change the names to and . The next time I generate the Relational model again those tw filds are renamed to the previous name mastercode and mastercode1.
    ==> How to avoid the relational model generation changes these names ?

    2) I use Sequence and table trigger on each table to generante my number primary keys values. In Relational Model on each table, in PK field properties “automatic increment” , I have to set the trigger style as ALWAYS because the default is WHEN NULL.
    ==> Is there a way to change the default trigger style to ALWAYS , instead of WHEN NULL ?
    ==> Is it possible to set the automatic increment property for the PK fields in Logical Model ? If so, how to do that ?

    Thank you so much for your instructions and directions !

  19. In my company, the SQL developer is widely used. Due to the standardization of our work, we want to use the same code templates for each developer. Is there a way to export and import the templates or outsource them to a shared file?

  20. Hi,
    It’s possible to exclude virtual hidden in Database diff tools from SQLdeveloper 19.1.0.094 ?

    I think that Virtual hidden it’s a mechanism internal oracle when i use stats.

    I need to compare two schema in db but the result it’s alway different because virtual hidden appears.

    • thatjeffsmith

      the database packages we use for the ddl gen and DIFFs don’t offer anything to help with virtual columns, so you’ll need to remember to uncheck them in your reports to the alter scripts aren’t generated for those items

    • Thank for you feedback.

      In my case we have almost 100 tables, because virtual hidden and the others case because differents colums, types, etc.

      You advise me in the report to scroll each diff table and uncheck to be made by hand only vritual hidden ?

      You think that case can be add in next released, select option for ignore Virtual Hidden like storage, high values, etc ?

      Best regards

    • thatjeffsmith

      I would look for a different way to do your compares, that, and you need to raise an ER to the database team.

    • Hi Jeff
      Is there a way in the formatter to force a line break after a line comment? i see an option to add a line break before a comment, but couldn’t figure out how add a line break after a comment

      WITH
      /*This is a line comment which describes what this next portion of code does.*/ example AS(
      SELECT
      MIN(post_date)AS min_post_date
      FROM
      trend_table
      )
      SELECT
      *
      FROM
      example;

    • Sorry Michel. I didn’t mean to post under your question

  21. Question: Is there a way to force a line break before semicolon (or statement delimiter) in SQL developer formatter?

    Background: I usually write my query without too much formatting and then press Ctrl+F7 to format it in a easy-to-read way, however I noticed that the auto-formatting usually delete the my line break before semicolon , so the semicolon will be at the end of last line in my statement after formatting. I found this a little inconvenient because when I comment out the last line, the semicolon would be commented out all together, result in execution error when I hit Ctrl+Enter to run the statement.
    I am using Version 18.1.0.095, looked through all the option in ‘Code Editor > Format’, but didn’t found a way to force line break before semicolon. Can you please help me with this? Much appreciated.

    • Hi Jeff,
      Just a reminder to see if you were able to check the question in the original post?

      Appreciate any help!

    • thatjeffsmith

      No I do not see a way to make this happen with the current formatter GUI for options, so you would need to remember to add the ‘;’ or select the query you want executed before hitting ctrl-enter…

  22. Anibal Lopez Reply

    Hi Jeff,

    Is there any new features for ORDS in SQL Developer 19.1 compare to SQL Developer version 18.4?

    And general are there new features in SQL Developer 19.1 that are not in SQLDeveloper version 18.4?

    Thanks in advance for your comments.

    Regards,

    Anibal

    • thatjeffsmith

      there are new features in sql developer 19.1

      there are new features in ords 19.1

      I don’t know of any new features in SQL Developer for ORDS in 19.1 though other than SQL Developer 19.1 now includes ORDS 19.1 vs ORDS 18.4

  23. Hi Jeff,

    For one particular use case I am considering using the autorest functionality.

    When doing an insert via POST I could not find a way to utilize an identity column and if I leave any columns out of the POST to try let them default or auto-populate in the case of the identity column I get an error as per below.

    Works fine when I define all columns values in the payload, wondering if this is a limitation of autorest

    Header:Error-Reason Cannot contain CRLF Charcters

    • IDENTITY Column is defined as below

      myid NUMBER generated by default as identity (nocache),

      This is with ords.3.0.9.348.07.16

    • thatjeffsmith

      If it were BY DEFAULT ON NULL then you could send the body with
      {“IDENTITY_COL”:null,…}

      And it will work.

      If that’s not possible, you’ll need to write a custom POST endpoint to do the INSERT for you, using your own SQL.

    • Overlooked that option thanks for the advice that will work.

Write A Comment