Nearly 7,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!
7,802 Comments
On a model diagram, logical or physical, is it perhaps possible to draw rectangles around entities/tables? I would like to visually fence selection of entities to show that they are part of a particular subject areas.
Thanks in advance for any suggestions.
Yeah…I’ll try to get you an example on Monday.
Hello,
I’m using SQLcl Release 22.1 on Oracle Linux 7.
Problem: Home & End keys on keyboard don’t work on SQLcl command line (i.e. print [1~ or [1~4~].
Question: Can I remap the Home and End keys on the keyboard so the Home key moves the cursor to the beginning of the line and the End key moves the cursor to the end of the line? (FYI, they work fine in SQL*Plus / rlwrap).
Thanks.
-John
Quick update: I just tested this in SQLcl Release 21.1–works fine. Also tested in Release 22.3 (latest version)–does NOT work.
HOME is working or me on Windows 10 – i’m using Oracle Java 17, what are you using?
No, the kb key shortcuts aren’t configurable.
Hi Jeff,
Thanks for replying.
I’ve tested SQLcl with the following java versions:
java version “18.0.1.1” 2022-04-22
java version “19” 2022-09-20
For both SQLcl versions:
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.1.0.0 build: 21.1.0.104.1544
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.1.1.0 build: 22.1.1.131.0820
Results:
SQLcl version 21.1 works fine with either java version. Arrow keys, Home key and End key all function normally on the SQLcl command line.
SQLcl version 22.1 does not work with either java version. Arrow keys work, but Home key and End key print [1~ or [1~4~], etc.
Any ideas?
Thanks again!
-John
No one here has tested SQLcl with Java 18 or Java 19. Our support right now is for 11 and 17.
I’ll give it a go on Linux tomorrow on 22.3 and see what’s up.
Hi Jeff,
Quick update: issue has been resolved!
Here’s what worked for me:
export TERM=putty-256color (FYI, old setting was either xterm or xterm-256color)
Sorry I didn’t think of this sooner.
Hope it helps someone else.
Thanks again!
-John
Hi Jeff
How do we set aliases in SQLcl which are not SQL or PLSQL strings. For example I am trying to create an alias to set autotrace
SQL> alias group=trace trcon=set autotrace on traceonly
2*
SQL> alias list trcon
ALIAS-010 – alias trcon not found
SQL>
Appreciate your help. Thanks in advance.
SQL> alias traceonly=set autotrace trace;SQL> traceonly
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
SQL> select 1 from dual;
1 row selected.
PLAN_TABLE_OUTPUT
_____________________________________________
SQL_ID 520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual
Plan hash value: 1388734953
------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | FAST DUAL | | 1 |
------------------------------------------
Hi Jeff,
We are getting a java.lang.StackOverflowError when running lb genschema.
I’m not able to debug which package/procedure is causing the issue. I have tried using the filter option to run for large packages but, they run fine and the files get generated.
Any pointers on how to debug this issue would be helpful. Thanks in adavance.
SQL> lb ges -split -log -debug -sql
Parameters
———————————————
basecommand=lb grants=false split=true debug=true log=true runalways=false synonyms=false replace=false fail-on-error=false runonchange=false command=generate-schema sql=true
———————————————
End Parameters
–Starting Liquibase at 21:53:38 (version 4.15.0 #0 built at 2022-08-19 14:45+0000)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type – TYPE_SPEC]: 14385 ms
[Type – TYPE_BODY]: 273 ms
[Type – SEQUENCE]: 313 ms
[Type – DIRECTORY]: 113 ms
[Type – CLUSTER]: 4987 ms
[Type – TABLE]: 163972 ms
[Type – MATERIALIZED_VIEW_LOG]: 102 ms
[Type – MATERIALIZED_VIEW]: 32815 ms
[Type – VIEW]: 3816 ms
[Type – REF_CONSTRAINT]: 577 ms
[Type – DIMENSION]: 112 ms
[Type – FUNCTION]: 397 ms
[Type – PROCEDURE]: 6548 ms
[Type – PACKAGE_SPEC]: 2291 ms
[Type – DB_LINK]: 152 ms
[Type – SYNONYM]: 366 ms
[Type – INDEX]: 215898 ms
[Type – TRIGGER]: 325 ms
[Type – PACKAGE_BODY]: 71167 ms
[Type – JOB]: 952 ms
[Method loadCaptureTable]: 519561 ms
[Method processCaptureTable]: Exception in thread “main” java.lang.StackOverflowError
at oracle.dbtools.util.Array.indexOf(Array.java:120)
at oracle.dbtools.util.Array.indexOf(Array.java:122)
at oracle.dbtools.util.Array.indexOf(Array.java:122)
at oracle.dbtools.util.Array.indexOf(Array.java:122)
at oracle.dbtools.util.Array.indexOf(Array.java:85)
at oracle.dbtools.parser.Earley.tree(Earley.java:798)
at oracle.dbtools.parser.plsql.SqlEarley.tree(SqlEarley.java:1089)
at oracle.dbtools.parser.Earley.tree(Earley.java:814)
at oracle.dbtools.parser.plsql.SqlEarley.tree(SqlEarley.java:1089)
at oracle.dbtools.parser.Earley.tree(Earley.java:820)
…
at oracle.dbtools.parser.plsql.SqlEarley.tree(SqlEarley.java:1089)
at oracle.dbtools.parser.Earley.tree(Earley.java:814)
at oracle.dbtools.parser.plsql.SqlEarley.tree(SqlEarley.java:1089)
at oracle.dbtools.parser.Earley.tree(Earley.java:820)
at oracle.dbtools.parser.plsql.SqlEarley.tree(SqlEarley.java:1089)
at oracle.dbtools.parser.Earley.tree(Earley.java:814)
It’s a parser bug, we’re coming your plsql code looking for dependencies to properly sort the controller/changeLog.
We may need a data pump export of your schema so we can access your pl/sql code for a test case. Please open a SR with MOS so we can pursue troubleshooting.
When Windows decides to abruptly update my system in the middle of the night, I lose any open worksheets with SQL in them. Is there any way to persist those unsaved worksheets through a reboot?
My browser can auto-restore where I was, and Notepad++ can restore any open/unsaved text files, I figure SQL Developer could too. Am I missing something or is this a feature request?
Check your SQL History
And yes, we need a new platform for SQL Developer. We’re working on the replacement, now!
My choices to Environment preferences are not retained for the next session. I cleared/disabled Environment->persist file names and directory paths and checked/enabled Environment->Local History->Enable Local History. But when I restart sql developer, those preferences reverted back.
*Note: I have AddVMOption -DIndexedPreferencesCommand=false entry in my sqldeveloper.conf file as per this issue: https://community.oracle.com/tech/developers/discussion/4479330/sql-developer-20-2-indexpreferencestask-very-slow
Jeff,
Wondering how secure are saved passwords for connections? How are those passwords saved for each user’s connections? It seems our IT wants to disallow saved passwords. Currently using 21.4.
Thanks,
David
Enough for the use case. The most important thing is to lock down your machine in general.
SQLDeveloper 22.2.1 – Issue – can’t copy from worksheet & paste to Excel
When I attempt to copy a row from the query result and paste it into Excel, the fields are concatenated in Excel. Previous version (don’t know which one) separated the fields into individual cells. Is there a setting in SQL Developer that (I can’t find) which allows this?
Hmm, I just tried this and it’s working for me. Which OS and version of Excel?
Windows version 10.0.14393
Excel 2016
Yeah, same here. If you paste to like notepad, what do you see? Should be tab delimited text.
Also, just tested two previous SQLDeveloper versions 19.2.1 & 20.2.0.175.
These both copy from the query result fields to Excel cells correctly.
Since I opened the previous versions, 22.2.1 won’t open. It must have been the way it was installed?
I don’t have permissions to install it, so. It’s okay, thanks for the help.
I’ll just use one of the previous versions as I have no time to pursue this.
Again, appreciate your help.
Can I classify a column in logical model as confidential so that it is displayed in the data dictionary?
See the bits around sensitive and PII data properties for columns.
Similar to this.
Hi Jeff,
I love using the SQL Dev. Reports during my daily work.
It would be nice if longer running reports can be started in a unshared session (like a “SQL Unshared Worksheet”). Could be useful, what do you mean?
Right now the alternativ ist starting SQL Dev. in a second instance.
Regards,
David
Don’t have a great answer, it’s a hack but you could create duplicates of your connections, and save those for your reports.
Thanks, that will work adequately.
Hey Jeff Smith! Glad to see that you’re still responding to all these questions, and before everything I appreciate all you do for Oracle and their users.
My situation & question:
Attempting to upgrade to SQL Dev 22.2 , with Oracle Client 19c.
We are receiving this error when upgrading:
Connecting to Database Using SQL Developer v22.2 Fails With Error ” java.lang.NoClassDefFoundError: oracle/jdbc/datasource/impl/OracleDataSource” (Doc ID 2881979.1)
An error was encountered performing the requested operation:/
E:\Oracle\product\19.0.0\client_1\bin\ocijdbc12.dll: Can’t load IA 32-bit.dll on a AMD 64-bit platform.
Checked Java version, and the oracle home set in the TOOLS>PREFERENCES>DATABASE>ADVANCED seemed to be okay.
Any way to bypass this error so we can commit to upgrading towards SQL Dev 22.2 w/ Oracle 19c?
Thank you so much!
Errors upon attempting to log on:
java.lang.NoClassDefFoundError: oracle/jdbc/datasource/impl/OracleDataSource
at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.createConnection(RaptorConnectionCreator.java:424)
at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnectionImpl(RaptorConnectionCreator.java:400)
at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:120)
at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt$DefaultPrompter.promptForPassword(ConnectionPrompt.java:154)
at oracle.dbtools.connections.db.PasswordPrompter.promptForPassword(PasswordPrompter.java:15)
at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:202)
at oracle.dbtools.connections.db.DatabaseProvider.getConnection(DatabaseProvider.java:357)
at oracle.dbtools.connections.db.DatabaseProvider.getConnection(DatabaseProvider.java:340)
at oracle.jdevimpl.db.adapter.CAConnectionCreator.createConnectionImpl(CAConnectionCreator.java:52)
at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:838)
at oracle.javatools.db.DatabaseFactory.createDatabase(DatabaseFactory.java:282)
at oracle.jdeveloper.db.DatabaseConnections.getDatabase(DatabaseConnections.java:656)
at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:285)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1181)
at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1167)
at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:83)
at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:38)
at oracle.dbtools.raptor.navigator.db.BaseConnectionNode.getConnection(BaseConnectionNode.java:91)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode.getObjectFactory(DatabaseTreeNode.java:90)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:145)
at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:119)
at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.datasource.impl.OracleDataSource cannot be found by oracle.sqldeveloper.extras_22.2.0
at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501)
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421)
at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107)
at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
… 29 more
What kind of machine do you have? I see 32 bit and AMD…
Make sure you have both 64 bit Oracle Client homne’s and Oracle Java. Preferably a 21c client, but a 19c client should also work.
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 system@SALESPDB
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 system@SALESPDB
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.
Hello Jeff,
sorry but I still wasn’t able to fix the 404 error for this call
http://localhost:8080/ords//_/db-api/latest/database/security/users/
I created a user with DBA and PDB_DBA rights and I rest-enabled it
CREATE USER ORDS_PDB_ADMIN IDENTIFIED BY restadmin;
GRANT DBA, PDB_DBA, resource, connect TO ORDS_PDB_ADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => ‘ORDS_PDB_ADMIN’,
p_url_mapping_type => ‘BASE_PATH’,
p_auto_rest_auth => TRUE);
END;
I can access SQL Developer Web with the user but the URL
http://localhost:8080/ords/ORDS_PDB_ADMIN/_/db-api/latest/database/security/users/
is still returning 404…
What I’m doing wrong?
Thanks
ords_pdb_admin not ORDS_PDB_ADMIN in the URL
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,
thank you for the prompt reply!
I tried also the lowercase version
http://localhost:8080/ords/ords_pdb_admin/_/db-api/latest/database/security/users/
but I still get a 404.
The ORDS log indicates:
2022-10-05T13:40:09.836Z SEVERE Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
(CONNECTION_ID=ADt8J7sPSm6rJRGVBBIL3w==),|default|lo|/ORDS_PDB_ADMIN-2022-10-05T13-40-09.735977Z,jdbc:oracle:thin:@//:1521/ORDS_PDB_ADMIN
Is there a configuration issue?
pool.xml
Saved on Tue Oct 04 20:50:49 UTC 2022
C##DBAPI_CDB_ADMIN as SYSDBA
basic
testcustomer-testbrand-db1-oracle-db-oracle
1521
ORCLCDB
ORDS_PUBLIC_USER
true
true
settings.xml
Saved on Tue Oct 04 20:50:47 UTC 2022
true
Thanks
Yes, but i can’t debug your ords install via comments on a blog.
You should go to Support if you’re a paying customer of the database, or to our forums.
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