Ask A Question

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

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

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

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

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

Go!

6,932 Comments

  1. Avatar

    When I call the procedure through PL/SQL , it works fine but when i call the same through ORDS with same input , it throws the below error. Please correct me.

    SQL Error Code: 6550, Error Message: ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to ‘ESB_PAYC_DETAILS’ ORA-06550: line 2, column 8: PL/SQL: Statement ignored.

    p_source =>
    ‘begin
    TCT.PKG_bala.pay_details(finp_type => nptype, finp_value => :npvalue, fIscard => :card, p_verrcode => :code,
    p_verrdesc => :description, v_refcur => :v_refcur);
    end;’
    );

    Parameter definition
    ——————————
    IN Parameter
    —————–
    nptype ,npvalue and card —> ‘STRING’

    OUT Parameter
    ——————-
    code – INT
    description -STRING
    v_refcur – RESULTSET

    Post man Input
    ——————–
    {
    “nptype”:”C”,
    “npvalue”:”4123123412341234″,
    “card”:”Y”
    }

    • Avatar

      Sorry, It’s typing mistake here……

      It is like the below in code

      finp_type => :nptype

    • Avatar

      BEGIN
      ORDS.ENABLE_SCHEMA(
      p_enabled => TRUE,
      p_schema => ‘TestAPI’,
      p_url_mapping_type => ‘BASE_PATH’,
      p_url_mapping_pattern => ‘TestAPI’,
      p_auto_rest_auth => FALSE);

      ORDS.DEFINE_MODULE(
      p_module_name => ‘pay’,
      p_base_path => ‘/pay/’,
      p_items_per_page => 0,
      p_status => ‘PUBLISHED’,
      p_comments => NULL);
      ORDS.DEFINE_TEMPLATE(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_priority => 0,
      p_etag_type => ‘HASH’,
      p_etag_query => NULL,
      p_comments => NULL);
      ORDS.DEFINE_HANDLER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_source_type => ‘plsql/block’,
      p_items_per_page => 5,
      p_mimes_allowed => ”,
      p_comments => NULL,
      p_source =>
      ‘begin
      TCTD.PKG_BALA.BALA_payc_details(
      finp_type => :nptype,
      finp_value => :npvalue,
      fIsPaycard => :card,
      p_verrcode => :code,
      p_verrdesc => :description,
      v_refcur => :v_refcur);
      end;’
      );

      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘nptype’,
      p_bind_variable_name => ‘nptype’,
      p_source_type => ‘HEADER’,
      p_param_type => ‘STRING’,
      p_access_method => ‘IN’,
      p_comments => NULL);
      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘npvalue’,
      p_bind_variable_name => ‘npvalue’,
      p_source_type => ‘HEADER’,
      p_param_type => ‘STRING’,
      p_access_method => ‘IN’,
      p_comments => NULL);
      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘card’,
      p_bind_variable_name => ‘card’,
      p_source_type => ‘HEADER’,
      p_param_type => ‘STRING’,
      p_access_method => ‘IN’,
      p_comments => NULL);
      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘code’,
      p_bind_variable_name => ‘code’,
      p_source_type => ‘RESPONSE’,
      p_param_type => ‘INT’,
      p_access_method => ‘OUT’,
      p_comments => NULL);
      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘description’,
      p_bind_variable_name => ‘description’,
      p_source_type => ‘RESPONSE’,
      p_param_type => ‘STRING’,
      p_access_method => ‘OUT’,
      p_comments => NULL);
      ORDS.DEFINE_PARAMETER(
      p_module_name => ‘pay’,
      p_pattern => ‘/inquiry’,
      p_method => ‘POST’,
      p_name => ‘v_refcur’,
      p_bind_variable_name => ‘v_refcur’,
      p_source_type => ‘RESPONSE’,
      p_param_type => ‘RESULTSET’,
      p_access_method => ‘OUT’,
      p_comments => NULL);
      COMMIT;
      END;

    • Avatar

      Postman JSON Input…
      {
      “nptype” : “S”,
      “nptypevalue” : “23456”,
      “card” : “Y”

      }

      When I call the same procedure with below input , it is working fine.

      DECLARE
      verrcode Number(10);
      verrdesc varchar2(100);
      v_refcur SYS_REFCURSOR;
      BEGIN
      TCT.PKG_bala.pay_details( (‘S’,’23456′,’Y’,verrcode,verrdesc,v_refcur);
      END;

    • thatjeffsmith

      I’m going on vacation for the week, so I recommend you move this to our public forums over at community.oracle.com

  2. Avatar

    How can we make a call to SQL function in ORDS….

    I am trying with this option ….Is this correct? Please correct me .

    p_source =>
    ‘begin
    returnvalue := pkg_bala.bala_function(
    serno => : serialnumber,
    fEmail => : email,
    ferrcode => : code,
    fStatus => : status);
    end;’
    );

    i am capturing function return value in out parameter like the below

    ORDS.DEFINE_PARAMETER(
    p_module_name => ‘real’,
    p_pattern => ‘/registration’,
    p_method => ‘POST’,
    p_name => ‘returnvalue’,
    p_bind_variable_name => ‘returnvalue’,
    p_source_type => ‘RESPONSE’,
    p_param_type => ‘STRING’,
    p_access_method => ‘OUT’,
    p_comments => NULL);

    • thatjeffsmith

      You’re close

      p_bind_variable_name => ‘returnvalue’

      And then in your begin block do this

      :returnvalue := pkg_bala.bala_function(...

      returnvalue is a :bind variable you’ve declared via the DEFINE_PARAMETER() call.

  3. Avatar

    When I try to call function in ORDS, I am getting the below error. Please give your suggestions.

    ORDS code for function call
    ————————————-
    Defined ORDS.ENABLE_SCHEMA, DEFINE_MODULE,template,handler etc
    ——————————————–
    ‘begin
    ReturnValue := (TCT.pkg_mgr.realreg(
    Serno => : serialnumber,
    fEmail => : email,
    ferrcode => : code,
    ferrdesc => : desc,
    fAction => : action,
    fStatus => : status
    ));

    end;’

    Defined return value also like this apart from other IN and OUR parameters….
    ORDS.DEFINE_PARAMETER(
    p_module_name => ‘time’,
    p_pattern => ‘/registration’,
    p_method => ‘POST’,
    p_name => ‘ReturnValue’,
    p_bind_variable_name => ‘ReturnValue’,
    p_source_type => ‘RESPONSE’,
    p_param_type => ‘STRING’,
    p_access_method => ‘OUT’,
    p_comments => NULL);

    Error details:
    —————-
    403 Forbidden
    2022-07-26T15:31:19.940356Z | DsFMi8TKh3tml1blMmFE5g |

    The request could not be processed because a function or procedure referenced by the PL/SQL statement being evaluated is not accessible or does not exis

    • thatjeffsmith

      I already have at least one Fusion SaaS making their databases available to customers via SQL Developer Web.

      This decision is up to the Fusion team, not us.

  4. Avatar

    In SQL Developer Web, the displayed output appears to be automatically rounded to 3 decimal places. This can be rather confusing when your key value is actually held to 5 decimal places.

    Is there a setting to disable this feature or is this a bug that would need an SR to resolve?

    • thatjeffsmith

      I’m going to file a bug for you. If you execute as a script, you’ll see the full value, so you have a workaround.

    • Avatar

      Thanks Jeff. That’s much appreciated. I’ll keep an eye out for any patch updates.

  5. Avatar

    Hi, I’m recovering from a little surgery and decided to spend some time looking at ORDS. I watched this video https://youtu.be/prkcgJLsfw4 and when I got at the SQL statement backing the REST service (39:52) I simply got stuck. I have never seen a cursor nested into a select statement in 15 years of PL/SQL (that’s Oracle baby, you’ll never, never know enough).
    But why such an exotic (to me) statement in a simple REST example? I decided to dive deeper and use the same construct on my own data and use it in my REST homework. And, surprise surprise, I find out the resulting JSON was a beautiful structured one, the answer to an old question about how to get a master-detail-subdetail response instead of a flat repetitive resultset. Anyway, this leads me to some considerations:
    the way we write the backing code affects the JASON format returned
    the way we write the backing code also affects the performance, which is vital in a REST service
    so we must be very careful when deciding what we want and how we get it. The question is: what are the relations between the JSON structure and the underlying query, and, Is there any guideline or pattern to drive us to the right solutions?

    • thatjeffsmith

      The ‘cursor’ trick is interesting, it does come in handy when you want a hierarchical response in your JSON without having to write any PL/SQL code.

      There are OTHER ways, see this post – it’s quite fun!

      The question is: what are the relations between the JSON structure and the underlying query, and, Is there any guideline or pattern to drive us to the right solutions?

      What a great question. I’m going to do my best to answer it, but feel free to reply with follow-ups if you want to go deeper.

      • if you can do it with SQL, use SQL
      • if you don’t like our JSON, you can generate your own, but:
      • you’ll have to take care of the paging, and link generation
    • Avatar

      Hi Jeff,
      I red Stew’s post and yes, that’s really interesting, I have to work on it. By the way, I’m using Database Actions on my always-free OCI account. To make the REST handler work with “cursor” I have to set the page size to zero and that’s fine, but I noticed that every time I change and save the handler SQL code, the REST call ends up with “ORA-22902: CURSOR expression not allowed” because the page size property has changed to null(?) in the handler. Setting it back to zero fixes the problem, until the next code change.

  6. Avatar

    Hi Jeff

    Currently using SQL Developer 22.2.0.173 Build 173.2018

    I can connect to any of our Oracle 19c databases however when I attempt to modify profiles the dialog box fails to appear for certain patch versions. Not all DBs are patched to the same patch level.

    Does not work (i.e. presents dialog box for profile to edit): “Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.14.0.0.0”

    Works (Profile Edit Dialog box appears) : “Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.11.0.0.0”

    Any thoughts or have I missed an update and this is a possible bug?

    Kind regards

    Steve

  7. Avatar

    Hi Jeff,
    What version of sql developer is compatible or supported for Windows Server 2019? The Platform Support for 21.4 shows this:
    Oracle SQL Developer 21.4 is available for Windows 7,8,10 and Windows Server 2008/2012
    Thank you,
    David

    • thatjeffsmith

      depends on the Java support.

      if windows server 2019 is supported with oracle jdk 8 and 11, you can run pretty much any version of SQL Developer

  8. Avatar

    SQLDeveloper 22.2
    Is it possible to disable the “Query Builder”? Accidently clicking on it in a worksheet with a complex query causes SQLdeveloper to lock up.

  9. Avatar

    in SQL developer, we have more than a hundred of database that we are supporting. i entered all of these connections and group it by folders according to it’s applications. there are times that i do not know which folder that the database belongs to and i had to go through each of them to find it.

    is there a better way like find/search box in the SQL developer? please advise.

    thank you.

  10. Avatar
    Catalin Cernita Reply

    Hi,

    We implemented a service and for some parameters it takes longer that statementTimeout setting.
    When the timeout occurs the client receives code 500 (not very helpfull).
    Internally in logs we see ORA 1013 – The user cancelled the request.

    How can we catch this error and return a code like 217 for example so the user know it must change the parameters to split the request in smaller chunks?

    Thank you

  11. Avatar
    Phil Winfield Reply

    Hi Jeff,
    I’m trying to use the journal table method but can not find documentation for it
    This no longer works:
    https://www.thatjeffsmith.com/archive/2016/05/sql-developer-data-modeler-adding-journaling-tables/
    I’m sure it is out there but please can you point me in the direction of where it now is?
    Also, is it an idea to use a hash to see if an update actually updates a row and not audit it if not? Eg an update of setting all values of a table col to 1 without a where clause looking for where it it null or not 1? Extra overhead in the update but fewer rows and data in the journal.
    TIA
    Phil

  12. Avatar
    wim de groot Reply

    Hi Jeff,
    I “suddenly” get an additional symbol added to my browser symbols: a yellow dot in a green cirkel,
    The design itself now got a suffix [develop] to the name.
    I browsed through manuals but failed to find a clue.
    Can you help me out here?
    Thanks in advance.
    Cheers
    Wim

  13. Avatar

    Hi Jeff,
    When I want to generate links in the response (for request with GET Method) it is pretty easy if I use the handler like this (it is enough to use the proper alias i.e. “$.id”):
    begin
    ords.define_handler(
    p_module_name => ‘links.example’,
    p_pattern => ‘:id’,
    p_source_type => ords.source_type_collection_item,
    p_source => ‘select emp.empno “$.id”, emp.*, decode(emp.mgr, null, null, ‘^/managers/’ || emp.mgr) “$related” from emp where empno = :id’);
    commit;
    end;

    But I want to use PL/SQL procedure (not SQL SELECT statement ) for the handler for GET Method (not POST method)!!!

    In this case while defining the handler:
    p_method is ‘GET’
    p_source_type is ORDS.source_type_plsql
    p_source is ‘BEGIN get_emp_json2; END;’

    And the procedure itself is like this:

    create or replace PROCEDURE get_emp_json2(p_empno IN emp.empno%TYPE DEFAULT NULL) AS
    l_clob CLOB;
    BEGIN

    select json_object( ’employees’ value json_arrayagg(json_object (
    ’empno’ value e.empno,
    ‘ename’ value e.ename,
    ‘job’ value e.job,
    ‘mgr’ value e.mgr,
    ‘hiredate’ value to_char(e.hiredate,’YYYY-MM-DD’),
    ‘sal’ value e.sal,
    ‘comm’ value e.comm,
    ‘deptno’ value e.deptno ) ) returning clob ) as json_doc
    INTO l_clob
    FROM emp e
    WHERE e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

    OWA_UTIL.mime_header(‘application/json’);
    HTP.print(l_clob);

    END;
    /

    How can I generate the hyperlinks in this case ?
    Can I generate them only manually ? If YES, so how to generate server name:server port and schema name for such a link not hardcoding the names but rather generating them automatically?
    http://localhost:8080/ords/ordstest

    Best regards
    Krzysztof

    • thatjeffsmith

      There is a way to generate links in plsql handlers automatically.

      But, your code is generating the JSON and printing it vs letting ords handle that.

      So now you’ll have to generate the links yourself.

      You can probably grab bits off the request headers to reconstruct the URL for your link.

      My advice would be to let ords generate as much of the response for you. That’s the beauty, just write SQL or call plsql, we do the rest.

    • Avatar
      wim de groot

      In the browser top before Design[1] there is e.g. symbol for maps/folders
      I referred to these as “symbols”.
      Maybe should have called it “icons”
      Anyhow, I do not see where I can upload a printscreen so I hope this clarifies it; -)
      Cheers
      Wim

    • thatjeffsmith

      Oh, you mean Data Modeler? Also there are plenty of sites out there you can upload pictures to… including our product forums.

  14. Avatar

    Interactive report CSV downloads do not always work on the first attempt with APEX application. Instead of returning a file for download, APEX sometimes returns the normal page.

    Steps to reproduce the issue:

    Log in to APEX QA.
    Go to application Reports
    Click Actions > Download > CSV > Download.

    The page will redirect to http://qa.casms.am.edwards.lcl/ords/f?p=104:64:SESSION_ID:CSV_N::::. The “CSV_N”

    • thatjeffsmith

      I’m not on the APEX team, and while I might be able to help, I don’t have the bandwidth. The APEX community is amazing, I suggest you go there for help with APEX.

  15. Avatar

    Hi, is there a way to get a fresh timestamp in the prompt each time you hit enter in SqlCl? In SQLPlus I was able to use this as my prompt:

    SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER _PRIVILEGE _DATE> ”

    but in SqlCl, it seems like the timestamp is set once when you connect, but doesn’t change after that.

  16. Avatar

    Hello Jeff,
    how to permamently disable (to not show this box) Save dialog for Worksheet when i close worksheet or close application?
    “… has been modified. Save changes?”

  17. Avatar

    Using SQLDeveloper, is there a way to write a hierarchical report that can expand and collapse nodes in a tree structure in a similar manner as the connections expands to shemas and its objects.
    If not directly in SQLDeveloper, any suggestions on the minimalistic approach could be.

  18. Avatar

    Hi Jeff,

    I am starting to use ORDS to REST enable my PL/SQL APIs, but I have a couple of questions.

    1) Eventually, I will need to enable authentication in order for my users to use the web services. What would be the best OAUTH method to implement authentication for internal users?

    2) Is there any way that the database can automatically “know” who the user is using the rest service when using OAUTH? This information would be very valuable for database auditing etc. along with any additional home-grown application security that may be used during the rest call. I know the owning schema and proxy user information is available, but this is still generic.

    Many thanks in advance,
    Mark.

    • Avatar

      Hi Jeff,

      Sorry to nudge, but did you get chance to look at this question?

      Many thanks,
      Mark.

    • thatjeffsmith

      I looked at it, and I thought about it.

      Here’s what I would do.

      Build a REST API to return a nice little JSON doc. Then use that with a REACT/Python/Whatever app you like to build your report.

    • Avatar

      Hi Jeff,

      Thanks so much taking the time out of your day to reply!

      Unfortunately I am not after a report in this case and I apologise if my question wasn’t clear.
      My question was more about what ORDS does automatically after successful OAUTH authentication with respect to setting any database environment variables (e.g. SYS_CONTEXT) in the connection from the pool before executing the REST call. Using standard Oracle database auditing, I would like to see the user/client who initiated the REST API through ORDS.

      Many thanks in advance,
      Mark.

    • thatjeffsmith

      Sorry, you had asked multiple questions, my answer was for your other question on hierarchical…

    • thatjeffsmith

      I think you want to look at the pre-hook feature. It allows you to implement finer-detailed authentication, and lots of customers use this for example to enable things like Virtual Private Databases (VPD) – where a user only gets access to certain rows in a table. Here’s a great external write-up on that.

      The authenticated user for an oauth2 client is the CLIENT ID associated with that client, so whoever you gave that client to…unless you could pull additional info off the request header.

    • Avatar

      Hi Jeff,

      Just what I was looking/hoping for!!

      Thanks

  19. Avatar

    Please can you tell me how to preserve blank lines in 20.4 as this used to be possible but I’ve looked and used the search feature but it seems to have been dropped?

  20. Avatar

    A Data Modeler question – when importing a model from an Oracle db data dictionary, is it possible to filter on a list of tables from a text file? There are several thousands of tables in a schema and I want to only import few hundred based on a list I have in Excel.

    Thank you advance for any help!

  21. Avatar

    Total newbie question here, I’m afraid. Apologies for that. I’m a sysadmin, not a DBA, and my DBA asked me to install ORDS (22.1) against an Oracle 19c DB (or at least she did the configuration once I’d installed it). The install went fine, and I even got it running in Tomcat without any issues, but every time we try going to http://:8080/ords/ all we get back is

    404 Not Found
    2022-06-24T10:54:13.286453205Z | 9oJxaAU6iB0lWtiUOCPb5A | ORDS-22001

    The procedure named apex could not be accessed, it may not be declared, or the user executing this request may not have been granted execute privilege on the procedure, or a function specified by security.requestValidationFunction configuration property has prevented access. Check the spelling of the procedure, check that the execute privilege has been granted to the caller and check the configured security.requestValidationFunction function.

    Now, we don’t have APEX installed in this DB at all, and the docs say it doesn’t need to be. So what might I/we be missing, please? We’ve tried a simple DB connection with the ORDS_PUBLIC_USER and the password extracted from

    ords –config /etc/ords/config config get –secret db.password

    and it worked, so it’s not a password thing. Any thoughts, please?

    With thanks,

    Ben

    • thatjeffsmith

      Welcome newbie! Don’t apologize or feel bad.

      /ords is the base URI for Tomcat to know you’re talking to the ORDS web app.

      Now, if you’re not using APEX, there’s nothing for ORDS to serve at that URI.

      Once you have a schema REST enabled, you can use that account to login to SQL Developer Web if you enabled that on the install.

      Go to localhost:8080/ords/sql-developer

      Then supply the schema name, and then again with the password.

      Then go to the REST panel. from there you can enable a table or hand-code a REST API.

      Before you can use ORDS for anything, you’ll need to enable a schema and publish a REST API on that database. Then you can call that.

  22. Avatar

    How can I get SQLDeveloper to set roles after login so the connection and schema browser show the objects accessible via those roles? I.e., apply the role to EACH session that SQLDeveloper establishes with the database?

    I’ve looked and cannot find where I can tell SQLDeveloper which roles to apply upon login. The user id does NOT have the roles default in the database; they have to be turned enabled after login to the database (by database manager policy because we don’t want every session to have the roles; only enable when needed and intentionally turned on).

    Or is that the requirement… that the roles are default so the database enables them.

Write A Comment