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.
- 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.
- 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.
- 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,864 Comments
Hi Jeff, thank you for your posts on ORDS multitenant installation (https://www.thatjeffsmith.com/archive/2022/07/install-ords-sql-developer-web-for-multitenant-part-one/). I was able to access the PDBs API from CDB as you showed!
And now I have plenty of endpoints to query and I’m a bit lost. I just need to get the list of schemas available in a PDB and I haven’t found it in this LONG list
https://docs.oracle.com/en/database/oracle/oracle-database/21/dbrst/api-data-dictionary.html
Can you give me a hint?
Thanks
Yeah you’d want this
https://website.com/ords/admin/_/db-api/latest/database/security/users/ — every user in this database (PDB) will come back, looks like this:
{"items": [
{
"username": "SYS",
"user_id": 0,
"password": null,
"account_status": "OPEN",
"lock_date": null,
"expiry_date": null,
"default_tablespace": "SYSTEM",
"temporary_tablespace": "TEMP",
"local_temp_tablespace": "TEMP",
"created": "2019-05-06T08:41:00Z",
"profile": "DEFAULT",
"initial_rsrc_consumer_group": "DEFAULT_CONSUMER_GROUP",
"external_name": null,
"password_versions": "11G 12C ",
"editions_enabled": "N",
"authentication_type": "PASSWORD",
"proxy_only_connect": "N",
"common": "YES",
"last_login": null,
"oracle_maintained": "Y",
"inherited": "YES",
"default_collation": "USING_NLS_COMP",
"implicit": "NO",
"all_shard": "NO",
"password_change_date": null,
"mandatory_profile_violation": "NO",
"protected": "NO",
"locked_account": 0,
"expired_account": 0,
"open_account": 1,
"days_left": null,
"links": [
{
"rel": "self",
"href": "https://pvqhdhmzqnn1hi4-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/security/users/SYS"
}
]
},
{
"username": "AUDSYS",
"user_id": 8,
"password": null,
"account_status": "LOCKED",
"lock_date": "2020-06-11T01:36:01Z",
"expiry_date": null,
"default_tablespace": "DATA",
"temporary_tablespace": "TEMP",
"local_temp_tablespace": "TEMP",
"created": "2019-05-06T08:41:01Z",
"profile": "DEFAULT",
"initial_rsrc_consumer_group": "DEFAULT_CONSUMER_GROUP",
"external_name": null,
"password_versions": null,
"editions_enabled": "N",
"authentication_type": "NONE",
"proxy_only_connect": "N",
"common": "YES",
"last_login": null,
"oracle_maintained": "Y",
"inherited": "YES",
"default_collation": "USING_NLS_COMP",
"implicit": "NO",
"all_shard": "NO",
"password_change_date": null,
"mandatory_profile_violation": "NO",
"protected": "NO",
"locked_account": 1,
"expired_account": 0,
"open_account": 0,
"days_left": null,
"links": [
{
"rel": "self",
"href": "https://pvqhdhmzqnn1hi4-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/db-api/latest/database/security/users/AUDSYS"
}
]
},
{
"username": "SYSTEM",
"user_id": 9,
"password": null,
"account_status": "LOCKED",
"lock_date": "2021-12-20T22:30:40Z",
"expiry_date": "2021-06-04T12:55:19Z",
"default_tablespace": "SYSTEM",
"temporary_tablespace": "TEMP",
"local_temp_tablespace": "TEMP",
"created": "2019-05-06T08:41:01Z",
"profile": "DEFAULT",
"initial_rsrc_consumer_group": "DEFAULT_CONSUMER_GROUP",
"external_name": null,
"password_versions": null,
"editions_enabled": "N",
"authentication_type": "NONE",
"proxy_only_connect": "N",
"common": "YES",
Hi,
We’ve got updated to Version 22.2.0.173. Looks OK, but there is one issue i noticed.
Assume i have five code templates that starts with
In previous version when i typed id in code window i got three templates to chose from and three dots to expand entire list of templates started with . And it worked fine.
In new version EXPAND option does not work. It moves highlight to the first option and does not open entire list of templates to chose from…
I will appreciate any reply to this…
starts with “id”
I have 3 that start with ssf..and I’m able to choose from any of those 3 using 22.2.1
Hi, Jeff
Thnks for reply.
Yes, if you have three it works fine. You can get any of them, but if you have more, you can’t get 4th, 5th and so on…
When you choose THREE DOTS (show more) it throw you to the top of the list (three first statements). In older version, when you chose THREE DOTS you got entire list of templates (4, 5, etc…)
Thnks
Mike.
P.S. Unfortunately can’t attach picture to the post…
You can use services like https://imgur.com/ to share pictures, there’s literally a hundred+ ways to do that if you want…
I added 2-3 more SSF templates. When I double-click the ‘. . .’ in the dropdown, I get the rest of the templates, in 21.4.
In 22.2, that’s not working, so yeah that would be a bug.
Thnks, Jeff. Will find workaround for it…
Thank you for the quick answer Jeff!!
But I tried both
http://localhost:8080/ords/admin/_/db-api/latest/database/security/users/
and
http://localhost:8080/ords/admin/_/db-api/latest/ORCLPDB1/security/users/ (my PDB name)
getting only 404 errors…
Where I’m doing wrong?
Thanks again
is your database user actually named ‘admin’ ?
That’s going to be a rest enabled schema with a DBA and PDBDBA role at least.
Also, the db name comes after ords, so if you did a CDB install with 2 PDBs it would be
/ords/pdb1/user/_/db-api/latest…
or if you just have the one database/pool
/ords/user/_/db-api/latest…
Also, you have to make sure you’ve enabled the DB-API
How do I deal with a connection which requires two-factor-authentication to connect to the database?
The only thing technically supported by the jdbc driver right now is appending the challenge code to the end of your password.
Sorry Jeff, I replied back to you but I cannot see it in the comments. Should I resend the additional questions I raised? Thanks
Yes, sorry it must have gotten lost in the spam filter.
Hi Jeff,
your question about users is actually triggering additional questions.
I actually followed your posts (https://www.thatjeffsmith.com/archive/2022/07/install-ords-sql-developer-web-for-multitenant-part-one/ and two).
My current setup is:
* Default pool
* CDB installation
* DB user: C##DBAPI_CDB_ADMIN -> db.cdb.adminUser
* ORDS user: ordspdbadmin
* ORDS DB user: ORDS_PUBLIC_USER
* 1 PDB database
and I REST enabled one schema in the PDB.
Then to which db user are you referring?
But the main question is about scoping.
>That’s going to be a rest enabled schema with a DBA and PDBDBA role at least.
I need to stay at PDB level to get the list of schemas available; I cannot narrow donw the scope from PDB to the schema.
Am I right? My goal is to get the list of schemas available at PDB level. Should I REST enable all the schemas?
Which user should I place here
/ords/user/_/db-api/latest…
?
Thanks
Which user should I place here
/ords/user/_/db-api/latest…
The DBA level account that you REST Enable. That’s the user that will be used to get the list of schemas in the database (PDB).
We have several applications that access a database, each with its own schema. Due to the development process, not all applications are developed with the same ords version. For each application we have a separate ords deployment on the Weblogic server.
Question: Can we support multiple client ords versions on the one database? Can ORDS_METADATA handle multiple client versions of ords?
In general, yes. What’s the min and max versions you have floating around?
Thanks for responding! At the moment we have Versions from 20.2.0.r1781804 to 22.2.1.202.1302. So we can use this setup for production env?
Probably, yes. We don’t officially sanction this, but we design ORDS to be backwards compatible as our Cloud updates are rolling and we have multiple nodes servicing each database.
I would caution: test this first, and endeavor to keep that delta between versions as small as possible.
If two years from now you’re still using ORDS 20, that’s not good.
We do many, many functional and performance bug fixes and enhancements every 3 months.
That helped, thank you very much for the quick support!
>The DBA level account that you REST Enable
>/ords/user/_/db-api/latest…
Not sure I got it…
Which is the “The DBA level account that you REST Enable”?
Is it PDBADMIN as indicated in
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.2/orddg/enabling-ords-database-api.html#GUID-1EFCB8AB-4260-48BB-917E-31997C15C76C
?
Should I REST-enable the PDBADMIN account to get the list of schemas in a PDB?
sqlplus [email protected]
GRANT PDB_DBA TO PDBADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => ‘PDBADMIN’);
END;
/
Is this what you mean?
Thanks
You get to use whatever database account you want to access the DB REST API. Just make sure that a) it’s REST Enabled and b) it has the proper privs.
So if you rest enabled PDBADMIN, then your api calls would be
/ords/pdbadmin/_db-api/…
And you’d authenticate with PDBADMIN/password using basic AUTH in your requests.
I have examples on the blog here, just use the search.
Not sure I got it Jeff…
Which is the “The DBA level account that you REST Enable”?
Is it PDBADMIN as indicated in
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.2/orddg/enabling-ords-database-api.html#GUID-1EFCB8AB-4260-48BB-917E-31997C15C76C
?
Should I REST-enable the PDBADMIN account to get the list of schemas in a PDB?
sqlplus [email protected]
GRANT PDB_DBA TO PDBADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => ‘PDBADMIN’);
END;
/
Is this what you mean?
Thanks
Whichever account you want so long as it’s not SYS or SYSTEM.
Hello ,
I am trying to explore ORDS to build some web interface to manage database infrastructure. Here, I am planning to build our own API using ORDS to initiate expdp and other database administrative jobs. I would appreciate if you could guide me a Road map from documentation to practical example to achieve my goals.
I am wondering if I install ORDS and using HTTP client to initiate EXPDP using pl/sql data pump API.
Regards
Data Pump APIs already exist with ORDS, you can simply start using them. I have examples here on my blog, including this one.
Hi Jeff,
In response to my previous question about, Can APEX Workspace have access to only ‘RESTful Services’, you had suggested to use SQL Web Developer. We have Oracle EBS suite. The challenge is for production instance where DB user password cannot be shared with developers. That was the reason we wanted to use APEX Workspace. In production instance we do not want any access to data manupulation capability except for just working with ‘RESTful Services’. Any help is appreciated.
Thanks !
Developer having access to production at all is an interesting topic. If you give them access to APEX< they'll be able to do things in the schema using the SQL tools for their workspace(s), right?
Hi Jeff,
Is there a way to automatically replace text as you type it into SQL Developer? For example, we have replaced Schema 1 with Schema 2, and Schema 1 was a lot easier to type than Schema 2. Let’s say Schema 1 is ABC and Schema 2 is ABC_DEFGH_IJKLMONP. Obviously having Schema 1 is much more convenient when typing SQL code. Is there anyway to have SQL Developer automatically detect you typing ABC and replace it with ABC_DEFGH_IJKLMONP? I saw this old post but did not see the options pictured in the screenshot.
https://www.thatjeffsmith.com/archive/2014/02/auto-replace-in-oracle-sql-developer/
I’m on SQL Developer version 21.2.1.204.
Thanks!
That feature caused more problems than it solved. You can replace on demand with ctrl+spacebar today. So it’s replace, but not ‘Auto’ replace.
Our next gen platform for SQL Dev will accommodate what you’re looking for quite nicely – so stay tuned!
Hi Alex,
I use Code Templates for that. You can trigger it with CTRL+Spacebar, but there’s an option to automatically apply it. Works O.K.
Hi Jeff,
We are installing ORDS and going to use APEX to create REST service. Can APEX Workspace have access to only ‘RESTful Services’? Currently in order to access RESTful Services, under setup Workspace > Component Availability, we have to set Allow SQL Workshop = Yes. By doing this the Workspace also gets access to Object Browsers, SQL Commands, SQL Scripts, Utilities.
Thanks
Rakesh
You should be using SQL Developer Web to developer your REST Services.
Hello Jeff
Im looking towards a way to programatically call SQLDeveloper, more specifically the export database function to generate the whole DDL for a given schema, with the option to skip data
I got the impression that its feasible. I gave a quick shot for SQLDeveloper API, but couldnt find it yet.
have i misunderstood the link below?
https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable
My pet peeve is that DBMS_METADATA is surely great. Hands down, but big definitions gets truncated, and besides this. scripting a bunch of objects which depends on each other (views,functions,procedures, and so on) is not a get-go script
Way over the top to ask if DBMS_METADATA could be expanded to add this functionality? to generate the right sequence of object definition?
Thanks a lot for your time 🙂 Appreciated
Hi Jeff,
This might be a slightly strange query but I am currently looking to connect Azure DataFactory to an Oracle APEX Restful Service. I’m happy that everything is working as intended and have confirmed access with authentication in postman.
However, as far as I can tell, Azure DataFactory is rejecting the authorization procedure to the REST service because the response from APEX has “token_type” as “bearer” and Azure is explicitly looking for “Bearer” (uppercase B).
The response that APEX provides is as follows;
{“access_token”:”redacted”,”token_type”:”bearer”,”expires_in”:3600}
and my error on Azure is
“The toke type ‘bearer’ from your authorization server is not supported, supported types: ‘Bearer’.
I have raised a case with Microsoft but thought I would see if anyone has run into the issue before and if there is any method to get around this on the Oracle APEX side? It seems odd that the response would be lowercase if the standard notes it should be “Bearer”.
Would appreciate any thoughts you might have,
Seb
It’s NOT an APEX RESTful Service, it’s an ORDS REST API. APEX is just your current front-end for building the APIs.
if the standard notes it should be “Bearer”
Please share the standard you are referring to, and I’ll take a look.
Apologies for messing up the terminology.
My understanding was taken from the RFC documents here; https://www.rfc-editor.org/rfc/rfc6750.html
is the reference to “Bearer” across it.
Admittedly, it does not expressly state that the authentication response should be capitalised, only that when passing the token type post authentication it should be capitalised as “Authorization: Bearer [TOKEN]” (2.1).
It does seem that Azure takes a strict approach of the authentication response needing to be capitalised as far as I can tell. I suppose my use case is slightly niche and I will continue to follow up with Microsoft but if you do have any other insight it would be appreciated.
In fact, I have now located the section in RFC-6749 (5.1) where it states the token_type is case insensitive, so standards wise ORDS is fine. I guess its over to Microsoft 🙁
https://www.rfc-editor.org/rfc/rfc6749#section-5.1
You could ask them to file a bug? Hopefully they’ll understand where you’re coming from.
Hi Jeff, a quick clarification for me please.
Is this SQL Developer Web (via ORDS) is not only just for the OCI autonomous database? sorry I am getting mixed info from various blogs on this.
say I have an on premise RAC database can I have this SQL Developer Web running from it . The data can stay where it is – don’t need to move it into OCI and probably stays on premise. interested in using the latest features regarding scheduling automated jobs and pipelines (chain). Can we grant specific access to a role on only a certain schema(s) etc (ie if different teams are using different schemas – I see why not right?)?
Or does the desktop SQL Developer has these features already too?
Thank you in advance.
Is this SQL Developer Web (via ORDS) is not only just for the OCI autonomous database? sorry I am getting mixed info from various blogs on this.
No, no, no. Which blog or blogs are leading you astray? Let me know so I can fix that.
If you have exec privs on DBMS_SCHEDULER, you’re going to be able to use our scheduler interface in both the desktop or Web versions. Much of what is in the Web interface is available in the Desktop – including visualization of Scheduler Chains. The Web version is more powerful, has more features than the desktop version.
Thanks for your reply and answers for my initial confusion, I am all good now after further reading last night.
I was initially reading them in various order I think and when I first read the one https://www.thatjeffsmith.com/archive/2019/12/sql-developer-web-is-now-available/ and before reading further all the whole details I got this and thought oh this is not good for me
“Wait, what is SQL Developer Web?
We built it FOR the Cloud, the Oracle Cloud. It was in available in our first DBaaS offering in OCI-Classic, and now it’s available in the Oracle Autonomous Database. We have plans to make it available throughout ALL of OCI, regardless of where or how your Oracle Database is running, but for now, you can also stage your own copy of ORDS and host SQL Developer Web wherever you’d like.”
yes so the first sentence ‘We built it FOR the Cloud….” got me off further reading, But then later when i got more time, I read further and concluded I should be able to get this even from my SQLDev desktop – just a matter of getting the right privileges for dbms_scheduler etc to use the visualisation of scheduler chains.
Yes and I then also read the home page here for SQL Developer Web” and got it confirmed “SQLDev Web (Docs) is available automatically in the Oracle Cloud for Autonomous Database subscribers (presented as Database Actions), but it’s also available for any customer who has ORDS configured for their databases. Some features in SQLDev Web may not be available in the Autonomous Database.”
So Thank you very much. All good. I just need to convince the DBA team now and try this out if I can.
Just 1 last question on this. Can I use the Web version to connect to an inhouse on prem database as we do not have OCI databases? probably not straight from OCI but may be on inhouse installed SQL Developer WEB on an app server
?
It started in Cloud at first.
Yes, you can use it for any Oracle Database, anywhere you have access for config and network.
Jeff:
I am trying to use SQL Developer 22.2. It starts to load but then stops and another window opens which looks like a text editor.
I have jdk 11 installed. I have SQL Developer 21 on the machine which work fine.
Thanks.
What does that look like? Have you tried launching via the exe in the /bin folder?
Hi Jeff,
Thanks for the wonderful session. Am trying to use SQL join using ORDS setup. How can I use multiple aliases on same table in ORDS sql query ? Am facing error like “ORA-00918: Column ambiguously defined” . For example have country table which has states and cities maintained in the same table. So want to use join query on the same table with different alias to fetch the countries, states and cities on different inputs. Am unable to define like below.
Country table has id, name
select a.name, b.name, c.name from country a inner join on country b where b.id=’STATEXXX’ inner join on country c where c.id=’CITYXX’;
Here I want to use alias a to fetch country name , alias b to fetch state name, alias c to fetch city name. Something like this. If I use same query on sql developer it gives me correct output without any error.
Only on ORDS sql query setup gives me this error “ORA-00918: Column ambiguously defined”. Please let me know how to resolve this.
Thanks.
Regards,
Srinivas J.
Hi Jeff,
On my setup it seems that the auto-completion (ie completion insight) does not work.
I am on Mac version 21.4.2.018 build 018.1606.
Would be good for me to be able to use Control + Space.
My question is, does completion insight work on Macs?
If so – what setup do I need to set?
Jennifer
On my Mac with 22.2, it seems to be working as expected.
To help you i’ll need an example.
Can you try in a blank worksheet, with the most basic of queries, say
select * from a — invoke insight here – do you get a list of views/tables in your schema and others starting with the letter A?
Hi Jeff,
Thanks for your reply and suggestions.
It seems now it works!
Sorry for the trouble.
Jennifer