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

  1. kennminitaus Reply

    Hi Jeff,
    I already had some look into xml-extensions and the great possibilities with them

    Nevertheless I still haven’t found a solution for following problem.
    By selecting some data from a table, there may also be some IDs of foreign-keys there. Would be find to have som kind of a e.g. a context menu in the result query which leads me to the foreign.key table of the currently selected column.

    Any idea how to handle that.
    Thx in advance,
    B rgds,
    k.

    • thatjeffsmith

      I don’t have a solution for this, I think we’d have to build it for you.

      You’re not the first to ask for this.

  2. Hello Jeff,

    I cannot do a POST of a JSON file with Oracle ORDS version 18.2.0.r1831332.
    While my handler method ‘GET’ works fine.

    I see in one of your example that that you have a source type as ‘RESPONSE’.
    In the above mentioned version i only can select source types as ‘HEADER’ or ‘URL’.
    I am a getting ‘500 Internal Server Error’.

    I can rember at some point time that the variable names shouldhas started as X-APEX-STATUS-CODE!
    Even using that it is not working.

    Do you maybe have any idea how i need to setup this?

    Any hint or help wil be much appreciated.

    Regards,

    Anibal

  3. Geert De Paep Reply

    When you rest-enable a table or view, is there a way to add “Access-Control-Allow-Origin: *” to the headers of the response? I see there is a ORDS.SET_MODULE_ORIGINS_ALLOWED procedure, but it only works for modules. When I rest-enable a table (just ORDS.enable_schema and ORDS.ENABLE_OBJECT) I don’t have a module.
    Thanks,
    Geert

  4. Daniel Looby Reply

    In SQL Developer 19.4 is there any way to set up Real Time SQL Monitoring to ONLY look for a specific user? I know I can order output either ascending or descending, but I really want to monitor just one user.

    • Daniel Looby

      My issue is that it appears to lock up, even after setting a high/long refresh rate. Filtering out users could help. Where would one submit this for product enhancement?

    • thatjeffsmith

      It shouldn’t be ‘locking up’ – how many reports are there?

      You can formally send in ER’s to MOS. But I’d rather see a bug on the ‘locking up’ part, and we could fix that.

    • thatjeffsmith

      But, I have dozens of Data Modeler posts…which one? This would have been easier if you had asked the question on the post you had actually read 🙂

  5. Hello Jeff,

    BTW I love your blog. They say the picture is worth a thousand words, but the video is definitely worth a million words. Regardless I followed your straight instructions and cannot get it to work I can add the tables but there is no relation added automatically. Also I cannot add a table and related tables by holding CTRL key and dragging the table into empty space. There are no keys created as well ( no “P” besides the EMPLID). I picked the basic tables from Peoplesoft SYSADM schema. I tried PS_JOB and PS_PERSON. Is there something missing in the setup or configuration?

    Thanks for you input
    Mirek

    • thatjeffsmith

      Hey Mirek, thanks for the kind words!

      This post has more than 5,000 comments, and my blog has more than 900 posts, so I’m not sure exactly that instructions you’re following.

      It sounds like they’re trying to create a relational data model diagram for your peoplesoft schema?

  6. Hello Jeff,

    I would like to ask if something changed according to SSO login using SQL Developer and Windows AD Kerberos.
    I tried search your website and current SQL Developer docs too, but nothing about SSO.
    I have read the Laurent Schneider blog https://laurentschneider.com/wordpress/2014/01/the-long-long-route-to-kerberos.html.
    But for enterprise usage for many users is not easy going task.
    I know that DB version 18c has some new features for supporting Active Directory authentication (without OID), but I cannot find any step-by-step document relevant to SQL Developer.
    Any hint would be very much appreciated.
    Thank you.

    Marian

    • thatjeffsmith

      we support OID, OUD, and Kerberos

      you can use it with a thick or thin oracle connection

      in Database Advanced preferences there’s a Kerberos Thin Config section – that’s what you would use if you want to just use the regular jdbc driver, otherwise you get it going with an Oracle Home (thick) connection, and it should just ‘work’

    • Marian Bednar

      thanks Jeff,
      it looks like easy task… 🙂
      but, I have found this doc on Oracle My Support
      Kerberos Authentication With Oracle JDBC Thin Driver And Microsoft Active Directory (Doc ID 1523651.1)
      and this is 26! pages long white paper. This is the ONLY way to configure AD Kerberos authentication?
      Hasn’t Oracle newer documentation? Because in 2020 using doc with mentioning Windows 2003 is quite unusual.

      Thanks in advance.

      Regards,
      Marian

    • thatjeffsmith

      I’m not a Kerberos or OID/OUD ‘guy’…so I’m pretty much in the dark on this stuff.

      I can ask around internally and find the RIGHT people, but I would need an exact question as to where you are getting hung up, as they are likely to just point me back to the Docs.

    • Marian Bednar

      Thanks Jeff.
      My exact question is:
      Exist any current Oracle docs how to configure SQL Developer (on Windows) to authenticate to the Oracle database 18c or 19c using Single-Sign-on with Active Directory Kerberos?
      Similar to the 1523651.1. but more actual – db version 18c or 19c.

      Thanks for the effort.
      Marian

    • thatjeffsmith

      But, it depends.

      Do you already have it working with SQL*Plus? Because if so, you’d would go the Thick connection route.

      If not, and you want to stay pure Java, you’d go with the JDBC Docs and the kerberos thin config files in SQLDev, then go with the Kerberos type of USER in your connection properties.

      This is all assuming you ALREADY have Kerberos configured with your Oracle Database.

    • Marian Bednar

      Jeff, thank you, I’ll try to configure it!

  7. Amin Adatia Reply

    V 19.4.0
    Monitor Sessions
    The query to get the Active SQL has not changed since v19.1 when I reported that there is a missing condition
    between gv$session s and gv$sql q
    and s.inst_id = q.inst_id

    We have 6 nodes and the query goes to neverland as before. You had said this would be fixed in 19.4!

    • thatjeffsmith

      Somehow I missed it. In the meantime you can build your own custom Sessions Monitor and put in the correct SQL. I’ve logged the bug with a note to check for this on ALL of the GV$ reports.

  8. Avinash Nagawade Reply

    Hi Jeff,

    could you please explain us that how can we setup/create sqldeveloper connection with oracle database using SSL.

    Thanks,
    Avinash

    • thatjeffsmith

      The easiest way is to set it up for SQLPlus, then tell SQLDev to use a thick connection and point it at the same Oracle Home.

      You can also do it with thin jdbc, and the Oracle docs have examples you can look at.

  9. Hello,
    once more my problem with large table request:
    Table with 125000 records, defaults.xml 150000
    Why do I get 404?

    debugPrint:

    footer,header{display:block;}
    html{font-family:sans-serif;-ms-text-size-adjust:100%;-webkit-text-size-adjust:100%;}
    body{margin:0;}
    h1{font-size:2em;margin:0.67em 0;}
    pre{font-family:monospace, serif;font-size:1em;}
    pre{white-space:pre-wrap;}
    img{border:0;}
    html{font-family:-apple-system,BlinkMacSystemFont,”Segoe UI”,”Helvetica Neue”,Arial,sans-serif;font-size:0.875em;}
    body{background-color:#f9fafa;color:#333;}
    a{color:#045fab;line-height:inherit;text-decoration:none;}
    a:visited{color:#045fab;}
    a:hover,a:focus{text-decoration:underline;}
    a:active{color:#034379;font-weight:normal;border-radius:1px;}
    h1,h3{color:#252525;font-family:inherit;font-style:normal;font-weight:normal;margin:8px 0;padding:0 0 2px;}
    h1{font-size:2.429rem;font-weight:200;}
    h3{font-size:1.429rem;font-weight:300;}
    ul{margin:12px 0;padding-left:40px;}
    ul li{line-height:1.25;}
    .oj-text-sm{font-size:0.857rem;}
    .oj-text-secondary-color{color:#4d4d4d;}
    .oj-margin{margin:10px;}
    .oj-panel{border:1px solid #d6dfe6;background-color:#fff;border-radius:1px;box-sizing:border-box;overflow:hidden;padding:0.8571rem;position:relative;}
    .oj-panel-alt4{background-color:#ffe4e1;border-color:#ffcdc8;}
    .oj-flex-bar{position:relative;}
    .oj-flex-bar,.oj-flex-bar-middle,.oj-flex-bar-end{box-sizing:border-box;display:-webkit-flex;display:flex;}
    .oj-flex-bar-middle{-webkit-flex:auto;flex:auto;}
    html:not([dir=”rtl”]) .oj-flex-bar-end{margin-left:auto;}
    @media print, screen{
    .oj-sm-align-items-center{-webkit-align-items:center;align-items:center;}
    }
    .oj-web-applayout-body{overflow-y:scroll;}
    .oj-web-applayout-page{display:-webkit-flex;display:flex;-webkit-flex-direction:column;flex-direction:column;box-sizing:border-box;min-height:100vh;}
    @media screen and (-ms-high-contrast: active), (-ms-high-contrast: none){
    .oj-web-applayout-page{height:100vh;min-height:0;}
    }
    .oj-web-applayout-max-width{margin-left:auto;margin-right:auto;width:100%;max-width:1440px;box-sizing:border-box;}
    .oj-web-applayout-header,.oj-web-applayout-footer{-webkit-flex:0 0 auto;flex:0 0 auto;}
    .oj-web-applayout-header{background-color:#f0f0f0;box-shadow:0 3px 3px rgba(0,0,0,0.1);}
    .oj-web-applayout-header>div:first-child{box-sizing:border-box;}
    .oj-web-applayout-header>div:first-child::before{content:””;display:inline-block;min-height:3.143rem;visibility:hidden;}
    .oj-web-applayout-header>div,.oj-web-applayout-footer>div{padding-left:20px;padding-right:20px;}
    .oj-web-applayout-header-title{font-size:1.143rem;font-weight:normal;color:#4d4d4d;line-height:0;padding:0;margin:0;}
    .oj-web-applayout-content{-webkit-flex:1 0 auto;flex:1 0 auto;padding:10px;}
    .oj-web-applayout-footer{min-height:3.571rem;box-sizing:border-box;border-top:1px solid #d9dfe3;background-color:#f0f0f0;padding-top:10px;padding-bottom:10px;}
    .oj-web-applayout-footer-item{box-sizing:border-box;display:-webkit-flex;display:flex;-webkit-flex-wrap:wrap;flex-wrap:wrap;-webkit-flex:0 1 auto;flex:0 1 auto;}
    @media screen and (max-width: 767px){
    .oj-web-applayout-footer-item{-webkit-justify-content:center;justify-content:center;text-align:center;}
    }
    .oj-web-applayout-footer ul{margin:0;}
    html:not([dir=”rtl”]) .oj-web-applayout-footer ul{padding-left:0;}
    .oj-web-applayout-footer li{list-style:none;display:inline-block;}
    html:not([dir=”rtl”]) .oj-web-applayout-footer li{margin-right:10px;margin-left:0;padding-right:10px;border-right:1px solid #d9dfe3;}
    html:not([dir=”rtl”]) .oj-web-applayout-footer li:last-child{margin-right:0;padding-right:0;border-right:none;}
    .traceBox{overflow-x:auto;overflow-y:auto;max-height:60vh;padding:0.8571rem!important;margin:0;}
    .panelCollapsible{border:1px solid #d6dfe6;background-color:#fff;border-radius:1px;box-sizing:border-box;overflow:hidden;position:relative;}
    .titleCollapsible{padding:0.8571rem!important;border:1px solid #d6dfe6;border-width:0 0 1px;margin:0;}
    @font-face{font-family:”Segoe UI”;font-weight:100;src:local(“Segoe UI Light”);}
    @font-face{font-family:”Segoe UI”;font-weight:200;src:local(“Segoe UI Semilight”),local(“Segoe UI Light”);}
    @font-face{font-family:”Segoe UI”;font-weight:300;src:local(“Segoe UI Semilight”),local(“Segoe UI Light”);}
    @font-face{font-family:”Segoe UI”;font-weight:400;src:local(“Segoe UI”);}
    @font-face{font-family:”Segoe UI”;font-weight:500;src:local(“Segoe UI Semibold”);}
    @font-face{font-family:”Segoe UI”;font-weight:600;src:local(“Segoe UI Semibold”);}
    @font-face{font-family:”Segoe UI”;font-weight:700;src:local(“Segoe UI Bold”);}
    @font-face{font-family:”Segoe UI”;font-weight:800;src:local(“Segoe UI Bold”);}
    @font-face{font-family:”Segoe UI”;font-weight:900;src:local(“Segoe UI Bold”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:100;src:local(“HelveticaNeue-Ultralight”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:200;src:local(“HelveticaNeue-Thin”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:300;src:local(“HelveticaNeue-Light”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:400;src:local(“Helvetica Neue”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:500;src:local(“HelveticaNeue-Medium”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:600;src:local(“HelveticaNeue-Medium”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:700;src:local(“HelveticaNeue-Bold”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:800;src:local(“Helvetica-Bold”);}
    @font-face{font-family:”Helvetica Neue”;font-weight:900;src:local(“Helvetica-Bold”);}
    @font-face{font-family:”Arial”;font-weight:100;src:local(“sans-serif-thin”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:200;src:local(“sans-serif-thin”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:300;src:local(“sans-serif-light”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:400;src:local(“sans-serif-regular”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:500;src:local(“sans-serif-medium”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:600;src:local(“sans-serif-medium”),local(“Arial”);}
    @font-face{font-family:”Arial”;font-weight:700;src:local(“sans-serif-bold”),local(“Arial Bold”);}
    @font-face{font-family:”Arial”;font-weight:800;src:local(“sans-serif-bold”),local(“Arial Bold”);}
    @font-face{font-family:”Arial”;font-weight:900;src:local(“sans-serif-bold”),local(“Arial Bold”);}

    .oj-collapsible {
    margin-bottom: 0 !important;
    }

    .header-grid {
    display: grid;
    grid-template-columns: auto 1fr;
    grid-column-gap: 1rem;
    grid-template-areas: “ords-logo oracle-logo” “ords-logo product-name”;
    }
    .ords-logo {
    grid-area: ords-logo;
    }
    .oracle-logo {
    grid-area: oracle-logo;
    padding-top: 0.2rem;
    }
    .product-name {
    grid-area: product-name;
    font-size: 1rem;
    padding-bottom: 0.2rem;
    }

    Not Found

    REST Data Services

    404

    Not Found

    Debug Trace

    mapped request using: /a95/* to: +A95|apex|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
    Could not find any dispatcher to handle request:
    --Attributes--
    oracle.dbtools.http.ecid = zIhZ8jhvjAqY4Lu6vopRGg..
    ECID-Principal = ECIDPrincipal [ecid=zIhZ8jhvjAqY4Lu6vopRGg..]
    oracle.dbtools.common.di.Services = URL Mapped Scope
    oracle.dbtools.plugin.api.di.InstanceLocator = [email protected]
    --Attributes--
    GET /ords/a95/ncicode1?q={"$orderby":{"fdacode":"ASC"}}&offset=0&limit=150000 HTTP/1.1
    Host: srv03.forschung.local
    user-agent: PostmanRuntime/7.21.0
    accept: */*
    cache-control: no-cache
    postman-token: cdc4d132-0db5-4c8b-b49a-5cc32a4bf527
    host: srv03.forschung.local:8082
    accept-encoding: gzip, deflate
    connection: keep-alive
    
    
    [email protected]
    mapped request using: /a95/* to: +A95|apex|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
    Could not find any dispatcher to handle request:
    --Attributes--
    oracle.dbtools.http.ecid = zIhZ8jhvjAqY4Lu6vopRGg..
    ECID-Principal = ECIDPrincipal [ecid=zIhZ8jhvjAqY4Lu6vopRGg..]
    oracle.dbtools.common.di.Services = URL Mapped Scope
    oracle.dbtools.plugin.api.di.InstanceLocator = [email protected]
    --Attributes--
    GET /ords/a95/ncicode1?q={"$orderby":{"fdacode":"ASC"}}&offset=0&limit=150000 HTTP/1.1
    Host: srv03.forschung.local
    user-agent: PostmanRuntime/7.21.0
    accept: */*
    cache-control: no-cache
    postman-token: cdc4d132-0db5-4c8b-b49a-5cc32a4bf527
    host: srv03.forschung.local:8082
    accept-encoding: gzip, deflate
    connection: keep-alive
    
    
    [email protected]
    [TE] GET /ords/a95/ncicode1?q=%7B%22$orderby%22:%7B%22fdacode%22:%22ASC%22%7D%7D&offset=0&limit=150000 start: 2020-01-15T07:17:15.685Z duration: 47ms
    mapped request using: /a95/* to: +A95|apex|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
    Choosing: oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData as current candidate with score: JDBCResourceHandlerScore [score=MetaDataScore [matchedMethod=		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    , matchedPattern=	/ncicode1/
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    		POST: POST /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    , metadata=
    common: CommonMetaData [accepts=[], allowedOrigins=[], cors=ALLOW, documentation=AUTHORIZED, frameOptions=SAME_ORIGIN, pageSize=null, pagination=null, requiresPrivilege=, transport=ANY]
    pathTemplates:
    	/ncicode1/
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    		POST: POST /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    	/ncicode1/batchload
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		POST: POST /ncicode1/batchload
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    	/ncicode1/:id
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		PUT: PUT /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    
    ]]
    Choosing: oracle.dbtools.rest.resource.cache.CachedResourceDispatcher as current candidate with score: JDBCResourceHandlerScore [score=MetaDataScore [matchedMethod=		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    , matchedPattern=	/ncicode1/
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    		POST: POST /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    , metadata=
    common: CommonMetaData [accepts=[], allowedOrigins=[], cors=ALLOW, documentation=AUTHORIZED, frameOptions=SAME_ORIGIN, pageSize=null, pagination=null, requiresPrivilege=, transport=ANY]
    pathTemplates:
    	/ncicode1/
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
    		POST: POST /ncicode1/
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    	/ncicode1/batchload
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		POST: POST /ncicode1/batchload
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    	/ncicode1/:id
    	common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
    	methods:
    		DELETE: DELETE /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		GET: GET /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    		PUT: PUT /ncicode1/:id
    		common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
    
    ]]
    select * from (
    select q_.* , row_number() over (order by 1) RN___ from (
    SELECT MAIN_QUERY.* FROM (select
     ROWID "$.id",
     "FDACODE",
     "FDACODENAME",
     "FDACODEDEFINITION",
     "LEVEL1",
     "LEVEL2",
     "LEVEL3",
     "LEVEL4",
     "LEVEL5",
     "LEVEL6",
     "TYPE",
     "CCODE"
    from
     "NCICODE1") MAIN_QUERY ORDER BY fdacode ASC
    ) q_
    ) 
    where RN___ between :row_offset and :row_count declares the following explicit parameters, but does not reference them: fetch_offset, fetch_size, page_size
    [TE] dispatch start: 2020-01-15T07:17:15.763Z duration: 5000ms
    
    		

    Stack Trace

    DispatcherNotFoundException [statusCode=404, reasons=[]]
    	at oracle.dbtools.http.entrypoint.Dispatcher.choose(Dispatcher.java:92)
    	at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:103)
    	at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:243)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)
    	at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:161)
    	at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:95)
    	at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:156)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.rt.authentication.apex.ApexSessionQueryRewriteFilter.doFilter(ApexSessionQueryRewriteFilter.java:58)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:83)
    	at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)
    	at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:74)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)
    	at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
    	at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
    	at oracle.dbtools.http.filters.Filters.filter(Filters.java:67)
    	at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:82)
    	at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)
    	at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)
    	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:609)
    	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:810)
    	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1623)
    	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    	at java.lang.Thread.run(Unknown Source)
    
    		


    About Oracle


    Contact Us


    Legal Notices


    Terms Of Use


    Your Privacy Rights

    Copyright © 2014, 2018 Oracle and/or its affiliates All rights reserved.

    function noReason() {
    if(document.getElementById(“reasons”).children[1] !== undefined) {
    document.getElementById(“reasons”).removeChild(document.getElementById(“reasons”).children[0]);
    }
    else if(document.getElementById(“reasons”).children[0].children[0] === undefined){
    document.getElementById(“reasons”).remove();
    }
    }

    noReason();

  10. Steve Karetny Reply

    Hi Jeff,

    I’m using v19.1 SQL*Developer.

    When I select the option File > Open… off of the SQL*Developer’s menu bar, a lot of recently used yellow folders appear in the left pane of the Open dialog box. How can I delete or reset the dialog box to show only the folder that has been set under Tools > Preferences > Database > Worksheet > Select default path to look for scripts?

    I tried to search for a configuration entry in the (*.conf or *.xml) file in the SQL*Developer installation folder, but was unable to locate the folder entries.

    Could you point me in the right direction?

    Thanks in advance,

    Steve Karetny
    [email protected]

    • thatjeffsmith

      You’re seeing the last 5 or so directories you have browsed to for opening or saving a file using that editor, in this case, the worksheet.

  11. Hello Jeff,

    I’m using ORDS 19.1 with Auth2.0 authentication.

    I have created a pre-hook function to log the cqalls to the ORDS Services as described on this link:
    https://www.jmjcloud.com/blog/using-the-ords-prehook-function

    The function provides some web session details using the “OWA_UTIL.get_cgi_env” function, but unfortunately no ORDS client details. I’ve tried to use the SYS_CONTEXT to get additional client information, but that gives back only RDBMS session details.
    Is there a way to get in the pre-hook function the ORDS client details (e.g. user_ords_clients.name, user_ords_clients.client_id)?

    Thank you in advance.
    Kind regards,
    Igor

  12. I’m using SQL Developer 19.4 on Windows 10 with Dark Mode enabled on at the OS level. Does SQL Developer support W10 dark mode? If not, is there a way to use prefs to fully simulate?

  13. Hi Jeff – Thanks for all your great posts! very helpful.

    I have create a custom JAASRealm which ORDS is using in order to authenticate users (basic authentication from a browser modal login window) but I am not able to properly configure Tomcat/ORDS so that it uses a custom login form. Can you provide me with some information on how to do this?

    Many thanks. Rob.

  14. Solomon Ragu Reply

    Hello Jeff,
    I am learning SQL privately and can sure do with technical help.
    As I searched for the solution upon getting a certain error message in aggregate functions, I bumped into your web page Jeff Smith Blog. The link for the page is shown here-below.
    https://weblogs.sqlteam.com/jeffs/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate/
    I was impressed at the simple way you explain the error using clear explanation from day to day event. I figured that you could probably provide some guidance on recommending some simple SQL books, notes or even website. I need your clear down-to-earth kind of explanations on SQL.
    Professionally, I am a civil engineer. In 2012, I designed a database using MS Access which I used for my church for several years. It is from this work that I developed interest in learning SQL.
    Solomon Ragu

    • thatjeffsmith

      That blog post you found is from a, believe-it-or-not, a DIFFERENT Jeff Smith.

      If you’re looking for a nice, easy to understand place to learn SQL, try this resource (LiveSQL).

      It’s not new, but Kevin Kline’s SQL in a Nutshell is an easy read, and it’s helped thousands of people learn SQL.

  15. Hello Jeff-
    How can I use ORDS to query an external web service to import JSON into my Oracle database?
    Thank You

    • thatjeffsmith

      No…ORDS is there to provide HTTPS access to an Oracle Database.

      What you’re asking is how to get data from outside an Oracle Database, from the database itself, via HTTP. And there’s a DB package for that. You’d want to create a stored procedure and use the HTP package. You can use it to do a GET to find your JSON, then you can use the JSON packages in the database to transform that into relational rows.

      I’m sure someone has an example of doing just that already.

  16. HI Jeff , I would like to know if is possible using SQL Developer to generate an export from an On premise database environment, and import into an Autonomous Database ?

  17. Hello Jeff,
    I’ve used unittests in SQL Developer for some years, but now I’ve got a new computer with Windows 10 instead of Windows 7. When I tried to select my actual repository, I see that the menu-item is grayed out like all other menu-items for unittests. So I can’t use my unittests anymore. Have you any idea what I have to do to resolve this?
    Best regards
    Peter

    • Hello Jeff,
      yes, the repository is there since we have not change the database.
      But I have to say Sorry because 30 second after I send the question I found in the View-Menu an Item Unittest too. If I select it, I can connect the actual repository in the unittest-menu under Extras (= German, perhaps in English it has an other name). Thank you very much for your fast reply!
      Peter

  18. Hi ,
    When export table DDL from SQL developer(Tools => Data base Export), I did not get Constraints in the DDL.
    So, I would like to know how can I get the DDL including constrains like NOT NULL.

    • thatjeffsmith

      Database Tools – Export

      It somewhat depends on what options you have set for the export. Are you checking both constraints and referential integrity constraints? Are you asking for constraints as ALTERs? And do you see the constraints when you open the SQL tab for your table when viewing it in the tree?

  19. Jason McCaul Reply

    Jeff,

    while we use Oracle as our DBMS we use liquibase to deploy our changes, I am wanting to use SQL datamodler but change the DDL transformation so we can output our standard liquibase format.

    I can find various examples of simple transformations but I am struggling to find anything that documents the attributes and methods of the transformation objects i.e. these:

    – model – relational model instance
    – pModel – physical model instance
    – table – the table in relational model
    – tableProxy – table definition in physical model

    The documentation that comes with sqlmodeler does not seem to adhere to the examples i.e. getAppView() method used in many examples does not appear to be in the documentation.

    Can you advise of any resources where this is documented.

    Regards

  20. Hi Jeff,

    Reposting this in case you missed it after I commented on the wrong message.
    When I make an error in a SQL script I do not get line and column number returned in the error message anymore. It just says
    ORA-00936: Ontbrekende uitdrukking.
    00936. 00000 – “missing expression”
    *Cause:
    *Action:

    Any idea how to reenable the “Error at Line: 99 Column: 9” message after Action:

    I’ve tried several SQL Developer versions (also 19.2).
    I’ve enabled the OCI instant client.

    • thatjeffsmith

      We’re printing everything that comes back from the db for error messages.

      I just tried

      CREATE TABLE blah
       dates DATE);

      And got back

      Error starting at line : 1 in command -
      ...
      
  21. Jeroen Rutte Reply

    Hello Jeff,

    We are using ORDS to create services for an Angular frontend on our database, which currently has a Forms interface.
    Working with ORDS is a pleasure, but I would like to make a suggestion for an addition.

    We have a template for services that are defined as pl/sql blocks and that works fine.
    We can detect the user who is logged on (if any), the name of the service and all of the url parameters.
    Using these parameters we have added some logic that uses sets of data to overrule the database values for some configuration tables.
    We also use these parameters to conditionally turn on our instrumentation, and log the results to a database table.
    But for a json/collection and a json/item service we cannot use this functionality to the full extent.

    What I would like to have is the option to specify a pl/sql block before and ideally also after (for the instrumentation) any kind of service, especially the json/collection and json/item kind of services.
    These block should run in the same transaction that the handler does, just like the GET to check the eTag runs in the same transaction as a PUT.
    So that I could specify a handler somewhat like this:

    ORDS.DEFINE_HANDLER(
    p_module_name => ‘test’,
    p_pattern => ‘test/:url_par/test’,
    p_method => ‘GET’,
    p_source_type => ‘json/collection’,
    p_items_per_page => 25,
    p_mimes_allowed => ”,
    p_comments => NULL,
    p_source => ‘select dummy
    from dual
    where a = :url_par
    and (:search_id is null or b = :search_id)’,
    p_code_before => ‘begin
    test_pkg.init_handler
    (p_module => ”test”
    ,p_pattern => ”test/”
    ,p_method => ”GET”
    ,p_q_par => :q
    ,p_par_name_1 => ”url_par”
    ,p_par_value_1 => :url_par
    ,p_par_name_2 => ”search_id”
    ,p_par_value_2 => :search_id
    );
    end;’
    p_code_after => ‘begin
    test_pkg.close_handler
    (p_status => :ords_http_status
    ,p_json => :ords_json_body
    );
    end;’
    );
    ORDS.DEFINE_PARAMETER(
    p_module_name => ‘test’,
    p_pattern => ‘test/:url_par/test’,
    p_method => ‘GET’,
    p_name => ‘search_id’,
    p_bind_variable_name => ‘search_id’,
    p_source_type => ‘URI’,
    p_param_type => ‘STRING’,
    p_access_method => ‘IN’,
    p_comments => NULL);

    As stated above, working with ORDS is a pleasure. Please feel free to do whatever you like (or nothing at all) with this suggestion.

    Kind regards,

    Jeroen

  22. Anthony Febbraro Reply

    our developers and analysts want to be able to see the code package header AND body in SQL Developer (as well as procedures and functions) in SQL Developer. Our internal audit is questioning why we granted select_catalog_role and want us to come up with the minimal required privileges that would still allow them to see the code, bt noy have that select_catalog_role (and certainly not execute). IS there a way to do this? what are the minimal privileges that must be granted to have SQL Developer work ?

    • thatjeffsmith

      If I understand your question correctly, no. But you could use that client to talk to a local SVN you setup with SQL Developer.

Write A Comment