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,907 Comments
VSCode Extension 25Q1 release – I read a comment on visual studio marketplace that the release will be in only a few days/weeks that was posted on 3/18… Has the date been finalized yet?
Yes
Hello Jeff:
I posted this question several years ago, but cant find that post — at any rate, the answer may have changed in the meantime.
I”m using version 21.4.1.349
One of the biggest annoyances I face is that I need many connections for my job and when I open a script and choose the required connection to run it in, the drop-down list gives me my entire list of connections, which can make it very time-consuming to pick the right one. I would like the drop-down to display only the connections that I have already logged in to. Is that possible? If not, any other suggestions for solving this? Thanks.
The active connections are already sorted to the top, so it’s easier to pick a connection that’s already in use.
Hey Jeff,
is it possible to configure ORDS to serve two different /i folders for different APEX version? We’re running ORDS 24.1.1 with two database pools which both use APEX 23. Now we’d like to add another pool that’s already on APEX 24. I tried moving the standalone.static.path into the respective pool files (with different paths for 23 images and 24 images) and defining the standalone.static.context.path in each pool file (/i for APEX 24 and /ii for APEX 23) but that doesn’t seem to work. Am I missing something or is that just not possible?
Thanks!
Option 1 – use the CDN instead
begin
apex_instance_admin.set_parameter(
p_parameter => 'IMAGE_PREFIX',
p_value => 'https://static.oracle.com/cdn/apex/22.2.0/' );
commit;
end;
Or put images to something like
/opt/apex/241 and /opt/apex/242
Set ORDS static images location to /opt/apex/
Change APEX installations static images location:
If ORDS standalone.static.context.path=/i,
then APEX 24.1 set static image location /i/241/ and to APEX 24.2 /i/242/.
Use the reset_image_prefix.sql script in each of your APEX installs.
Hi Jeff,
We are migrating the oracle 12c database with Apex/Ords configured from on premises to Oracle autonomous database. We have successfully loaded schemas and apex workspace from on premises to C@C database but not getting how to test the ords/apex login. in OCI for the database under tools section Application express we have an URL for apex but it is not working, We have opened port 443 as well getting connection is not private error.
Is there any steps we can get to migrate the apex/ords from on premises to C@C ?
We have ORDS 19.2 running in dedicate application servers in on premises, can we point the c@C ATP database to already running ORDS service in on premises database ? Kindly help
So you’re running Oracle Autonomous via Cloud & Customer?
If you click the ‘Database Actions’ button in your console, what happens?
19.2 ORDS is ancient, it needs to be retired!
Hi Jeff,
Yes it is oracle Autonomous cloud at customer.
Even for Database actions also when i open the link in browser getting the same “Your connection is not secure” error only.
I’m on vacation this week.
You should open a SR with MOS.
Hi Jeff,
In SQL Developer, oracle table types were not supported to be displayed while executing procedures/functions/packages with these types existing as output parameters.
Is this supported in SQL dev extension for VS Code? either these types exist as input or output. If not supported now, is it intended/planned?
here is a sample from oracle documentation:
—————————–
CREATE TYPE cust_address_typ2 AS OBJECT
( street_address VARCHAR2(40)
, postal_code VARCHAR2(10)
, city VARCHAR2(30)
, state_province VARCHAR2(10)
, country_id CHAR(2)
, phone phone_list_typ_demo
);
CREATE TYPE cust_nt_address_typ
AS TABLE OF cust_address_typ2;
—————————–
Please note that there might be a nested table as well.
Thank you.
displaying while executing, do you mean debugging?
that is indeed supported in both tools, assuming those types are compiled for debug
i have examples, somewhere, on my blog here
Hi Jeff,
Firstly many thanks for all your blog posts and time. Point me to support for this one if necessary, but I thought I’d try posting directly to you.
ORDS 23.3.2
APEX 23.1
Tomcat 9.0.62
Oracle 19c
I have an APEX app. that is currently not used overnight. When I run the APEX App Builder or the application login page first thing in the morning, it takes 30-45 seconds for the page to respond.
Looking at v$session, initially there are no ORDS_PUBLIC_USER nor APEX_PUBLIC_USER records (this was happening also when records were appearing in v$session though). When hitting the login page URL, after 20-30 seconds an ORDS_PUBLIC_USER record appears, then a few seconds after that, an APEX_PUBLIC_USER appears, then the APEX page is shown.
After that the application works fine throughout the day.
After trying a lot of different settings in settings.xml I ended up with this file:
Saved on Tue Mar 05 09:55:47 UTC 2024
true
3
300
0
1000
600
25
25
3
RECYCLE
900
This did not fix the issue, so I cleared out settings.xml apart from:
Saved on Tue Mar 05 09:55:47 UTC 2024
true
and my ORDS settings are now the defaults:
Configuration:
/appl/ords/conf/
Database pool: default
Setting Value Source
—————————————– ————————————- ———–
apex.security.administrator.roles SQL Developer, OAuth Client Developer Default
apex.security.developer.roles SQL Developer, OAuth Client Developer Default
apex.security.user.roles Default
cache.metadata.enabled false Default
cache.metadata.timeout 11m Default
database.api.enabled true Global
database.api.management.services.disabled false Default
db.adminUser Default
db.adminUser.password Default
db.cdb.adminUser Default
db.cdb.adminUser.password Default
db.connectionType basic Pool
db.credentialsSource POOL Default
db.customURL Default
db.hostname abcde12345 Pool
db.idlePoolTimeout Default
db.invalidPoolTimeout 15m Default
db.password ****** Pool Wallet
db.poolDestroyTimeout 5m Default
db.port 1521 Pool
db.serviceNameSuffix Default
db.servicename ABCDE Pool
db.sid Default
db.tnsAliasName Default
db.tnsDirectory Default
db.username ORDS_PUBLIC_USER Pool
db.wallet.cache /tmp/ords-wallet-cache Default
db.wallet.zip Default
db.wallet.zip.path Default
db.wallet.zip.service Default
debug.printDebugToScreen false Default
debug.trackResources false Default
error.externalPath Default
error.responseFormat AUTO Default
feature.grahpql.max.nesting.depth 5 Default
feature.openservicebroker.exclude false Default
feature.sdw true Pool
feature.sdw.selfServiceSchema false Default
feature.serviceconsole false Default
http.cookie.filter Default
http.cookie.filter.byValue Default
icap.port Default
icap.secure.port Default
icap.server Default
jdbc.ConnectionWaitTimeout 3 Default
jdbc.DriverType thin Default
jdbc.InactivityTimeout 1800 Default
jdbc.InitialLimit 3 Default
jdbc.MaxConnectionReuseCount 1000 Default
jdbc.MaxLimit 20 Default
jdbc.MaxStatementsLimit 10 Default
jdbc.MinLimit 2 Default
jdbc.auth.admin.role Default
jdbc.auth.enabled false Default
jdbc.cleanup.mode RECYCLE Default
jdbc.driverName Default
jdbc.statementTimeout 900s Default
jdbc.ucp.enableJMX true Default
json.sdo.geometry.output.geojson false Default
misc.defaultPage apex Default
misc.pagination.maxRows 10000 Default
mongo.access.log Default
mongo.enabled false Default
mongo.host 0.0.0.0 Default
mongo.idle.timeout 30m Default
mongo.op.timeout 10m Default
mongo.port 27017 Default
ocid.dbtools.connection Default
owa.docTable Default
owa.trace.sql false Default
plsql.gateway.mode proxied Pool
procedure.postProcess Default
procedure.preProcess Default
procedure.rest.preHook Default
public.properties.url Default
request.traceHeaderName Default
resource.templates.enabled false Default
restEnabledSql.active true Pool
security.credentials.attempts -1 Default
security.credentials.file Default
security.credentials.lock.time 10m Default
security.disableDefaultExclusionList false Default
security.exclusionList Default
security.httpsHeaderCheck Default
security.inclusionList Default
security.jwks.connection.timeout 5s Default
security.jwks.read.timeout 5s Default
security.jwks.refresh.interval 10s Default
security.jwks.size 100000 Default
security.jwt.allowed.age -1s Default
security.jwt.allowed.skew 0s Default
security.jwt.authenticators DATABASE Default
security.jwt.profile.enabled true Default
security.maxEntries 2000 Default
security.requestAuthenticationFunction Default
security.requestValidationFunction ords_util.authorize_plsql_gateway Pool
security.validationFunctionType plsql Default
security.verifySSL true Default
standalone.access.log Default
standalone.binds 0.0.0.0 Default
standalone.context.path /ords Default
standalone.doc.root ${config.url}/global/doc_root Default
standalone.http.port 8080 Default
standalone.https.cert Default
standalone.https.cert.key Default
standalone.https.host Default
standalone.https.port 8443 Default
standalone.static.context.path /i Default
standalone.static.path Default
standalone.stop.timeout 10s Default
I have a development environment with the same versions of APEX, ORDS, Tomcat and the DB, but never experience this wait time in the morning.
Should the jdbc.MinLimit of 2 mean that there are always two ORDS_PUBLIC_USER records in v$session? What about APEX_PUBLIC_USER threads? Are those just spawned automatically? Is there a setting I am missing to ensure that there are always threads available to serve APEX pages?
The XML did not paste properly. My settings.xml previously was:
entry key=”database.api.enabled” true
entry key=”jdbc.ConnectionWaitTimeout” 3
entry key=”jdbc.InactivityTimeout” 300
entry key=”jdbc.InitialLimit” 0
entry key=”jdbc.MaxConnectionReuseCount” 1000
entry key=”jdbc.MaxConnectionReuseTime” 600
entry key=”jdbc.MaxLimit” 25
entry key=”jdbc.MaxStatementsLimit” 25
entry key=”jdbc.MinLimit” 3
entry key=”jdbc.cleanup.mode” RECYCLE
entry key=”jdbc.statementTimeout” 900
Then I cleared it and just left the database.api.enabled setting in there.
It sounds like it just takes a long time to create a connection to that database. The pools will size down to whatever the min value is. So your pools should go down to just 2 connections, which i think is the default.
Unless something in the db is killing idle connections, which is very likely in a prod environment as connections/processes are costly to maintain, esp if they aren’t doing anything.
Hi Jeff,
Many thanks for the quick reply. I think that is it – that prod. is killing unused connections, since this does not happen in dev.
regards,
Malcolm.
I found a work-around but i think there’s a bug somewhere .
Exporting an ORDS API from SQL Developer (both 23 & 24 versions) i get a section for the parameter used for output (RESULTSET type) comething like this:
ORDS.DEFINE_PARAMETER(
p_module_name => ‘my_module’,
p_pattern => ‘my_pattern’,
p_method => ‘POST’,
p_name => ‘items’,
p_bind_variable_name => ‘items’,
p_source_type => ‘URI’,
p_param_type => ‘RESULTSET’,
p_access_method => ‘OUT’,
p_comments => NULL);
Trying to import it in ORDS 23 (just using a SQL Sheet in SQL Developer) there’s no problem, it works in the correct way.
But trying the same process on ORDS 24 i get this error:
ORA-20048: Invalid combination of access_method and source_type.
The problem seems related to the “source_type” parameter ==> ‘URI’.
I manually replaced all the ‘URI’ in ‘RESPONSE’ that was the one i was seeing in the GUI and it worked.
Hope this help someone.
What version of ORDS were you exporting from, and what version of ORDS were you importing to? Exact versions please, so 23.3 vs just 23.
Hi Jeff,
I would appreciate if you could share timelines for the upcoming release and a specific date/week in each quarter we should be looking forward to.
Thanks.
Gk
Since 2017, it’s been quarterly.
25.1 of ORDS is due end of March – assuming you mean ORDS.
Excuse me for the delay.
Here are the exact versions.
Exporting Version: ORDS 19.2.0.r1991647
Importing Version: ORDS 24.4.0.r3451601
Bye
Gigi
That’s a 5 yr difference, I’m guessing our PL/SQL interfaces may have changed in that time frame.
I suppose that, but you’re a reference for a lot of people, so i post the question and the “workaroud” for this issue on your blog.
So if someone look for the error “ORA-20048: Invalid combination of access_method and source_type.” can find the solution (that’s what i did, before write, but didn’t find anything).
Your blog is higly indexed, so now the solution is available for all.
Thanks
Gigi
p_param_type => ‘RESULTSET’ would never map to URI for a parameter on a template/hander….that could only be a RESPONSE. If 19.2 exported like that, it sounds like a 19.2 bug that got fixed at some point.
Hi Jeff
I don’t know if just it a bug on SQLcl 24.3.2.0. I’m using the LOAD feature to upload a CSV file to a table. I’m getting that the table doesn’t exist on target database, but it does:
SQL> conn -name DWHMED_X8
Connected.
SQL> info streaming.ods_cta_consumos_prestacion_live
TABLE: ODS_CTA_CONSUMOS_PRESTACION_LIVE
LAST ANALYZED:2025-03-01 00:30:57.0
ROWS :0
SAMPLE SIZE :0
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
COD_EMPRESA NUMBER(2,0) No
ID_CONSUMO NUMBER(10,0) No
…. cleaned for clarity
SQL> load STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LIVE /home/oracle/streaming/output/CTA_CONSUMOS_PRESTACION_DAV.csv
Load data into table STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LIVE
csv
column_names on
delimiter ,
enclosures “”
double
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
2025-03-05 14:44:37.437 SEVERE oracle.dbtools.data.loadservice.LoadService start Table STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LI does not exist or is not available from connection.
2025-03-05 14:44:37.447 SEVERE oracle.dbtools.data.loadservice.LoadAPI Unable to start load service;
Unable to load object Unable to start load service;
#ERROR Table STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LI does not exist or is not available from connection.
SEVERE: Load terminated
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 24.3.2.0 build: 24.3.2.330.1718
Does it seems like the name of the table is too long? Look how the error message doesn’t display the whole name.
Thanks in advanced
It’s not the length of the table name but something else. I take it you’re not logged in as user, STREAMING?
SQL> select * from ODS_CTA_CONSUMOS_PRESTACION_LI;
0 rows selected.
SQL> load ODS_CTA_CONSUMOS_PRESTACION_LI data.csv
csv
column_names on
delimiter ,
enclosures ""
double
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
Load data into table HR.ODS_CTA_CONSUMOS_PRESTACION_LI
#INFO Number of rows processed: 6
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 6
SUCCESS: Processed without errors
I was able to login as a different user (with INSERT privs on that table), and load it as well, using schema prefix (HR)
That’s right, I’m not connected as user STREAMING.
Please notice that the name of the table is STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LIVE and not STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LI (it’s missing the last two letters) and this is what makes me think that there is some issue with the lenght of the name, the error message shows STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LI instead of STREAMING.ODS_CTA_CONSUMOS_PRESTACION_LIVE, and I can see that the table does exist.
I will ask the owner of the DB if they can share the password of the owner of the table and see if it works with that.
Hi, Jeff. What is your recommendation for saving connections in SQL Dev for VS Code? I have many databases with multiple schemas in each that I need to connect as. In the old SQL Developer, if I don’t save the password, I can replace both username and password during connection. That allows me to save one connection per instance. In the new extension, only the password entry is available. Do I need to save a connection for each possible username in the DB?
Anxiously awaiting the release with connection folder support!
One connection defined per user, for the moment, yes. I have put in a request to loosen the requirements such that you can get prompted for both user and password on a connection definition.
Connection folder support is almost here!
I used VS Code with the oracle extension in my database course last fall. One day we had a denial of service attack and discovered that our Oracle server was set up so that idle connections were not terminated. On that day neither of us who teach the course nor our students could access Orcale. The DoS attack went away, the idle connections were eliminated and since then the this paramter has been changed so that not only are idle cnnections removed after six hours but a srcipt is run every day on the server to remove idle connections.
Because of this one day, my colleagure refsues to consider any other tool than the original SQL Developer and is basically forcing me to use SQL Developer and not VS Code (which I do like). How can I convince him that the future is VS Code and these things that happened last fall are a one-up and most lilely will not happne again?
What is the association between this DoS attack and your tools?
Sqldev and our VS Code extension have effectively the same logic when it comes to database connections.
I’m happy to talk to your colleague, feel free to share my contact info:
[email protected]
Hi
I am using SQL DM v23.1. Is it possible to define the preferences for DDL to get an output for a Table as below.
CREATE TABLE ATTRIBUTE_PARTY (
ATTRIBUTE_SK INTEGER DEFAULT ON NULL CRM_S.NEXTVAL NOT NULL
,PARTY_SK INTEGER NOT NULL
,ATTRIBUTE_TYPE_SK INTEGER NOT NULL
,FLAG_ACTIVE INTEGER DEFAULT ON NULL 1 NOT NULL
,DATE_START DATE DEFAULT ON NULL SYSDATE
,DATE_END DATE
,ATTRIBUTE_BLOB BLOB
)
TABLESPACE D1 NO INMEMORY
LOB ( ATTRIBUTE_BLOB ) STORE AS SECUREFILE (
CHUNK 8192
RETENTION
ENABLE STORAGE IN ROW
NOCACHE
);
CREATE UNIQUE INDEX P604_U ON
ATTRIBUTE_PARTY (
ATTRIBUTE_SK
ASC )
TABLESPACE D1;
CREATE INDEX P604_I1 ON
ATTRIBUTE_PARTY (
PARTY_SK
ASC )
TABLESPACE D1;
ALTER TABLE ATTRIBUTE_PARTY
ADD CONSTRAINT P604_PK PRIMARY KEY ( ATTRIBUTE_SK )
USING INDEX P604_U;
Can you be more specific? What are you not getting, that you’re expecting? And do you have a specific DDL generation preference you need help with?
There was no assocaiation between SQL Developer and the DoS attacks–my collegaue believes there was.
What do you mean exctaly by Sqldev and your VS Code extension haveoing effectively the same logic when it comes to database connections?
While the extension has a web front end, the back end is Java..and the database connections are managed by that Java code and use our jdbc driver…just like sql developer, which is all java.
Hi Jeff!
I create a rest template API with GET method using SQL Developer to export a table to a csv file. But I can’t get a header in the resulting file, how can this be done? Im am using the latest version of SQL Developer (24) on-premise.
Regards
/Ulf
The CSV feature is technically deprecated, but I doubt we’ll ever remove it.
However we won’t be enhancing it.
You’ll need to construct the csv payload yourself with a plsql block.
The Java version is still much better than the VS Code plugin. I hope it stays afloat for a long time.
It has a good amount of time left. We have many more features to port over. BTW, both are ‘the java version’ – java is the back end for everything we do in VS Code extension.
Ubuntu 22.04 sqldeveloper checkbox save password cannot be checked.
try SQL Developer 24.3.1, 23.1.1, 22.2.1 on OS:Ubuntu 22.04 and java version “17.0.6” 2023-01-17 LTS.
All have this problem.
For sql developer 24.3.1, in Messages – Log window, sometimes will show Unexpected runtime exception while delivering HashStructureHookEvent’, sometimes no this error message, but in both case the save password still cannot be checked, so I think this error is not related.
There is some warnings message in command prompt windows when start sqldeveloper 24.3.1, as follow:
xxx@xxx-virtual-machine:/opt/sqldeveloper-24.3.1$ ./sqldeveloper.sh
Oracle SQL Developer
Copyright (c) 2005, 2024, Oracle and/or its affiliates. All rights reserved.
Java HotSpot(TM) 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release.
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper-24.3.1/netbeans/platform/lib/boot.jar)
WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager
WARNING: System::setSecurityManager will be removed in a future release
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper-24.3.1/ide/extensions/oracle.ide.jar)
WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore
WARNING: System::setSecurityManager will be removed in a future release
java version:
xxx@xxx-virtual-machine:/opt/sqldeveloper-24.3.1$ java -version
java version “17.0.6” 2023-01-17 LTS
Java(TM) SE Runtime Environment (build 17.0.6+9-LTS-190)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.6+9-LTS-190, mixed mode, sharing)
I already asked on your stackoverflow question, what messages do you see in the log panel?
No any error in Messages – Log windows in sql developer.
Jeff,
Can we activate multi-line selection in the datagrid within VS Code? If that’s not currently an option, are there plans to implement this feature in the future?
It’s there now, you have to ctrl+cmd CLICK each row…but we’re going to add shift+mouse and ctrl+A support for selection and for doing range selection of cells and columns as well.
How about windows?
I tried Windows + Ctrl + Click, Ctrl + Alt + Click, Ctrl + Shift + Click but nothing seems working.
NVM, I got it. Control + Click at row number selects the line.
I am testing First Party Basic Authentication on ORDS. I want to
1- Add a new user (hradmin) to the ORDS standalone web server (Listener)
2- Assign a predefined role to that that user
I can add a user using this command –> ords config user add hradmin “HR Administrator”
But I can’t assign a role that I have created prev. (called emp_role) to hradmin user. I’ve been trying the following command and none of them work.
C:\ords.24.3.1\bin\ords user hradmin emp_role
or
java -jar ords.war user hradmin emp_role
in both cases ords giving me the messages asking me to use other command line options
My question: What is the command to add a user and asign a role to it in ORDS? I know there is a way to do so in Tomcat.
ords version is 24.3.1
Thanks
Houman .
If you look at that credentials file, you can see the user listed along with the roles
Hi Jeff,
I started using SQLcl and I really like it.
I have a question regarding the ” set sqlformat ansiconsole -config=hightlight.json”
Where should we create this file ?
On a windows pc and on Linux Server
Because I tried in the SQLcl directory and to other directories but I always get the error :
Invalid File : highlight.json
Take note I’ve got the info from your site
here : https://www.thatjeffsmith.com/archive/2021/10/oracle-sqlcl-all-the-pretty-colors-for-your-console/
And in Kris : https://krisrice.io/2019-04-18-SQLcl-SQLFormat/
And yes, I notice the error in the help about the “endWith” in fact we nee to use “endsWith”
Thanks
here the highligh.json
{“highlights”:[
{“type”:”startWith”,”test”:”P”,”color”:”INTENSITY_BOLD,CYAN”},
{“type”:”endsWith”,”test”:”TT”,”color”:”BLUE”},
{“type”:”contains”,”test”:”RCAL”,”color”:”YELLOW”},
{“type”:”exact”,”test”:”FORD”,”color”:”GREEN”},
{“type”:”regex”,”test”:”[0-9]{2}”,”color”:”MAGENTA”}
]
}
you can put the file anywhere you want, if the CWD isn’t where it’s at, you’ll need to either cd to the directory or provide the full file path
Here’s my example
Your JSON as shared is invalid, it has ‘smart quotes’ .. but not sure if that’s because of the way it was copied/pasted (from Word?)
Hi Jeff,
I really like the SQL Developer extension for VS Code, it has improved my workflow and reduced the context switching.
One thing I wanted to check is reg. the Query Result pane/grid.
In the old SQL Developer, we are able to select and run multiple queries and see the results in separate Query Result tabs… Is this functionality available in VS Code extension? If not, is this something your team is looking to implement in future?
Thanks.
YES!
What you can do right now is open multiple sql worksheets and run a query in each, and each will see their query results.
But having ability to see them side-by-side, is something we need AND plan to build this year – including for execution plans.
Hi Jeff,
I am new to ORDS and want to log each API calls automatically (I’m still trying to get my DBA to help setup prehook).
In the meantime, I have defined my POST handler as below
ORDS.DEFINE_HANDLER(
p_module_name => ‘rest’,
p_pattern => ‘item’,
p_method => ‘POST’,
p_source_type => ‘plsql/block’,
p_items_per_page => 0,
p_mimes_allowed => ‘application/json’,
p_comments => NULL,
p_source =>
‘DECLARE
BEGIN
insert into table1(:ITEM_ID, :ITEM_TYPE, :ITEM_QUANTITY);
ins_api_log (:current_user, :content_type, :body_text, v_response);
commit;
END;’
);
I found if ins_api_log in the block, :ITEM_ID is null where as if I comment out ins_api_log line, :ITEM_ID is the bind data from json.
The above plsql/block works without issues with GET, DELETE. Is there a limitation to POST?
Note I checked ins_api_log is working as expected and inserting into the target table with required data.
I would need to see the json payload you’re including on that POST.
Your webserver logs will have what you want, by the way, you’ll see all the incoming HTTP/HTTPS requests, including the ones for ORDS API calls. No need to use a prehook for this, unless you ALSO want to log this in the database instead of in your OS or logging/metrics stack.
I installed the VS Code extension for SQL Developer yesterday and completed importing my connections. But I’m having a weird thing happen when trying to connect to certain databases. We have 2 domains. I can connect to databases in the domain I’m currently logged in to with no issue. In order to connect to a database in the other domain, I have to open VPN to that domain. But here’s the strange thing – I don’t have to do that in SQL Developer, both domains work without VPN. Furthermore, I can open SQLcl in VS Code and connect to any database. It is only when testing a connection or trying to open SQL Worksheet that I get this error:
A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly.
Any ideas, I’m at a loss.
Thanks,
Steve
Not sure what you mean about domains, can you go into more details?
Also, when you’re testing SQLcl, can you confirm you’re testing the SQLcl that ships with the VS Code extension? Right-click on connection, and select ‘Open SQLcl’ – that works?
Yes, I am using SQLcl that ships with the VS Code extension and that does work.
By domains I mean network domains. We have 2 that segregate servers geographically. While in the office I can connect to any database using native SQL Developer and SQLcl that is built into the extension. No VPN connection required. But for some reason, connections to the other network domain in the extension fail, both while testing the connection in VSCode or trying to open SQL Worksheet. The only way I can connect to databases in the other domain is if I first connect to the other network domain’s VPN.
So, there must be some sort of difference in how network connections work between native SQL Developer (and SQLcl in the extension) and the VS Code extension.
Hope that makes more sense.
Thanks,
Steve
On the surface this doesn’t sound possible, yet here you are 🙂
In VS Code, if you bump the extension logging level up to TRACE (in extension settings), and try to make the connection, can you share the output panel’s contents?
I set the trace level, but where are the trace files located?
it’s VS Code extension logging, it goes directly to the ‘Output’ panel in VS Code
Unfortuanately, nothing shows up in the output window when trying to login to the database in the other network domain. Just a popup window in the bottom right-hand corner showing:
A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly
The settings for trace level includes a selection for enabling logging to a file. Any idea what file it may be creating?
I found the output; it is rather large. Can I email it to you?
Thanks,
Steve
This is probably the part of the trace you are interested in:
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleConnectPacketResponse
INFO: Got Resend, SessionTraceId = 2DB155F5
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleAcceptPacket
INFO: Connection established. Cleared conn strategy stack
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO sendMarker
INFO: Sending break marker, SessionTraceId = 2DB155F5
Jan 17, 2025 9:58:13 AM oracle.jdbc.driver.PhysicalConnection connect
INFO: throwing
java.sql.SQLRecoverableException: ORA-17002: I/O error: Connection reset, Authentication lapse 0 ms.
Can you email me the entire log plus a dump of your runtime env (os, java, clients, …)
[email protected]
Problem solved! I had a discussion with our network guy, and it turns out that the firewall was blocking the connection because it was not only checking the port but was also checking for an Oracle signature from the software. This was removed and now I connect.
I’ll leave it to you to decide whether this is something that can be updated in the VS Code extension.
Thanks,
Steve
Can you share how this check was being enforced? You have my email…
Hi Jeff,
I am trying to setup ORDS api logging using prehook functionality. From all the pages I have come across, it is referring to https://www.jmjcloud.com/blog/using-the-ords-prehook-function which is returning 404 (even on the homepage).
From what I gathered, prehook call a procedure with no arguments. In my case, I am trying to include the :current_user when inserting into my logging table. When I compile the procedure, I am getting
PLS-00049: bad bind variable ‘CURRENT_USER’
My sample proc is
create or replace procedure pre_hook_ins_api_log as
begin
insert into api_log
( current_user
, request_method
, ords_path, query_string, headers
, remote_host
)
values (
:current_user
, owa_util.get_cgi_env(‘REQUEST_METHOD’)
, owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
, owa_util.get_cgi_env(‘X-MY-HEADER’)
, owa_util.get_cgi_env(‘REMOTE_ADDR’)
);
return true;
exception
when others then
return false;
end;
Can you provide some guidance how to setup my proc?
You won’t pass anything to prehook, it will run in the same session as your request, and you can use :current_user in your function.
Docs here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/24.4/orddg/developing-REST-applications.html#GUID-FE6D2A44-D02D-4969-B05C-E4A5BC5D9F27
It has to be a function that returns a boolean.
Hi Jeff,
Thanks for the quick reply.
I changed it to a function but it is still getting PLS-00049: bad bind variable ‘CURRENT_USER’
create or replace function pre_hook_ins_api_log
return bool
as
begin
insert into api_log
( current_user
, request_method
, ords_path, query_string, headers
, remote_host
)
values (
:current_user
, owa_util.get_cgi_env(‘REQUEST_METHOD’)
, owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
, owa_util.get_cgi_env(‘X-MY-HEADER’)
, owa_util.get_cgi_env(‘REMOTE_ADDR’)
);
return true;
exception
when others then
return false;
end;
Hi Jeff
I work for a finance company and we use SQL Developer. We are wondering if Oracle will continue to develop this tool in the future or if you will only push the VSCode plugin. Is there a binding date for the end-of-life of SQL Developer?
Thank you very much for your feedback.
Salvi
The Oracle SQL Developer tool will continue…but in the form of a VS Code extension. The delivery vehicle is changing from Java Swing to VS Code. The licensing remains the same. It just gets better.
The current desktop tool will stick around until we have sufficiently moved over all the features. There is no binding EOL date. Each release gets an official 18mos support window – that’s binding.