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,730 Comments
Hey Jeff,
I am running the following connected to my 23ai database as sys with sysdba role:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => ‘*’,
ace => xs$ace_type(privilege_list => xs$name_list(‘connect’),
principal_name => APEX_APPLICATION.g_flow_schema_owner,
principal_type => xs_acl.ptype_db));
END;
/
I get the following error. I remember being able to run this before on 19c and 23ai. i dont know why i am getting this error.
Error starting at line : 1 in command –
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => ‘*’,
ace => xs$ace_type(privilege_list => xs$name_list(‘connect’),
principal_name => APEX_APPLICATION.g_flow_schema_owner,
principal_type => xs_acl.ptype_db));
END;
Error report –
ORA-06550: line 5, column 46:
PLS-00201: identifier ‘APEX_APPLICATION.G_FLOW_SCHEMA_OWNER’ must be declared
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:
I was connecting to the container db instead of the pdb 🙂
Stay out of the DB as SYS…bad things can happen, esp if you think you’re in the PDB but you’re actually in the CDB 🙂
Hi Jeff,
Have you ever tested SQL Developer 23.1 version to connect to Azure AD for DB authentication and work smoothly? I have been struggling to have it working and SR has not been lucky for last 4 months. I constantly run into ora-1017 Invalid User Password.
Thank you,
Jaruwan N.
I have not but our security team has and they have some blog posts on it.
Can you email me the SR# so I can take a look at what’s going on with your case?
[email protected]
Hi, Jeff! How ARE you? I have a SQL Developer Cart question.
I have created several carts, and CMD files to run the exports therein.
I have debugged these, I log on to the server from where I want to run them, these run fine and the extracts are created as desired (one per view, for about 56 views of data with select criteria).
What I want to do is use the Task-Scheduler on that server to run those CMD files (logged in as me, with my password, so as to get “my” settings in SQL Dev), so it can be done on a schedule w/o needing manual running. I have set up many things over the years, to run stuff via Task-Scheduler on Windows, all with great success. But not this time.
The task initiates, stalls, does nothing for its whole time (4hrs), exits, with no error I can find.
I cannot figure out how to get an error message as to why it will not run (and, as I said, the CMD file called by Task-Scheduler … runs just fine when I click on it / run it directly). There must-be-something … that the SDCLI program might want … when called from Task-Scheduler?? I have searched, but cannot find any answers (maybe I am using wrong terms). Is there some setting for SDCLI that I am missing?
Here is the contents of my “one cart” CMD file … I call this multiple times for, like, 10 different carts.
And, as I said, it works when the master CMD file is invoked (which then calls this, which actually calls SDCLI).
Finally … the last bit of the SDCLI line … that writes to the “outer log” … indeed captures how many records were exported for each invoke of this command file. There are _no_errors_ in the -log [filename] for each export, so, again, I figure my basic settings are OK. But Task-Scheduler is somehow unhappy.
Your advice is appreciated!!!!
Suzanne Michelle, NYC Transit
====================================================
@Echo Off
REM Both SDCART and XML -type files are plain text / XML format.
:
SETLOCAL EnableDelayedExpansion
:
set SQLD_LOC=C:\SQLDeveloper\sqldeveloper\sqldeveloper\bin
set CART_LOC=C:\SQLDeveloper\Cart_Files
set LOG_LOC=D:\Logs
set THIS_EXP=%1
set THIS_LOG=%2
set THIS_FOLDER=%3
:
if /%2/==// GOTO THIS_ERR
:
set EXPT_LOC=D:\Exports
if NOT /%3/==/Exports/ set EXPT_LOC=D:\Exports\!THIS_FOLDER!
REM Else it stays as D:\Exports (other options: D:\Exports\Newer or D:\Exports\Older)
:
if exist !LOG_LOC!\!THIS_LOG!_outer.log Del /Q !LOG_LOC!\!THIS_LOG!_outer.log
:
echo The SQL Developer Location is … !SQLD_LOC! >!LOG_LOC!\!THIS_LOG!_outer.log
echo The Cart-File Location is … !CART_LOC! >>!LOG_LOC!\!THIS_LOG!_outer.log
echo The Export Results will be in … !EXPT_LOC! >>!LOG_LOC!\!THIS_LOG!_outer.log
echo The Export Error Logs are in … !LOG_LOC! >>!LOG_LOC!\!THIS_LOG!_outer.log
:
c:
cd !SQLD_LOC!
:
sdcli cart export -cart !CART_LOC!\!THIS_EXP!.sdcart -config !CART_LOC!\Export_Tool.xml -target !EXPT_LOC! -log !LOG_LOC!\!THIS_LOG!.log >>!LOG_LOC!\!THIS_LOG!_outer.log
if not /%4/==// PAUSE
:
GOTO VERY_END
:
:THIS_ERR
:
Echo.
Echo Please invoke with [name-of-cart-export-file] and [name-of-log-file], both -WITHOUT- extensions
Echo and add … where the export should write out: Exports, Older, Newer (in -exactly- this case/text)
Echo …
Echo Example: %0 Cart_DRs_wLocations DRs_Export Exports
Echo.
Echo or: %0 GO_Master_Older GOs_Old_Export Older
Echo …
Echo Try again, after you . . .
PAUSE
:
:VERY_END
EXIT
====================================================
What are you doing in your Carts? It might be MUCH faster to implement the tasks using the SPOOL, UNLOAD, & native LOAD commands in SQLcl.
My best guess is some sort of environmental setting for the task scheduler perhaps isnt’ able to find java?
For debugging i woud create a very simple, singular tasks, and make sure that works, if not it’s not the Cart’s issue, it’s probably between the OS and Java/SQLDev.
Never mind. I answered my own question – it has to do with Task Scheduler settings. One must be sure to set the “start in” option _where_ the batch file / CMD file is located. THAT did the trick. Sometimes, I hate computers.
Good luck to you and yours! I recommend your sites / SQL Developer all the time!
SM
I also sometimes hate computers!
PS – I _did_ test it all with one cart file. I needed to export as Excel files (because of impossible-to-remove carriage-returns in the data – works fine with Excel … my task was to export data so it could be ingested into MS Dynamics 365 DB / program, written by people who do not know ETL functionality in any language (“of course” I could remove the CRs, but then the data would not have been correct on import (and those programmers couldn’t put the CRs back in))).
The Cart feature works brilliantly!!
Jeff – a final postscript – I did get it all to work as I wanted, but I learned one more thing.
It’s important when saving a card, to be sure to check the “include” box for the exports.
61 of my 62 exports had it checked (which it must do by default?).
One did not / did not work … and no info on the SDCLI close (e.g., “Nothing was included, so nothing exported.”).
Perhaps … a hint when saving “You’ve not chosen to include anything. Is that what you want?” … could help others.
Hey Jeff,
I am running oracle database 23ai and AEPX 24.1 in a docker container locally. I would like to send emails from the application i am running locally(http://localhost:8181/ords/). In OCI -> Developer Servics -> Email Delivery -> Configuration -> SMTP Sending Info => Security label tooltip says “Email Delivery requires a secure TLS connection (v1.2) to submit email to the public endpoint.” I wonder if there is a mismatch between the 23ai default tls version(1.3) and OCIS’s expected 1.2 version. Do you know if it is possible to do what i am trying to do? Sending emails from a local application with OCI’s smtp credentials.
I think I configured everything correctly in my apex instance settings. When I run this
BEGIN
APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG;
END;
I get the following error.
ORA-29019: The protocol version is incorrect.
ORA-06512: at “SYS.UTL_TCP”, line 63
ORA-06512: at “SYS.UTL_TCP”, line 331
ORA-06512: at “SYS.UTL_SMTP”, line 199
ORA-06512: at “SYS.UTL_SMTP”, line 223
ORA-06512: at “APEX_240100.WWV_FLOW_MAIL”, line 1302
ORA-06512: at “APEX_240100.WWV_FLOW_MAIL”, line 2774
ORA-06512: at “APEX_240100.WWV_FLOW_INSTANCE_ADMIN”, line 2151
ORA-06512: at line 2
Mmmmmm, the DB itself can send emails and you can have APEX call that fairly easily. You’re asking how to have APEX ask the database to call the OCI service to do the emails, and I’m not sure on that. I would start with the APEX team, and this doesn’t involve ORDS as I understand it.
I am also getting the same error in oracle db 19.22.0.0.0. I ran the following script to fix the previous ACL error
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => ‘*’,
ace => xs$ace_type(privilege_list => xs$name_list(‘connect’),
principal_name => APEX_APPLICATION.g_flow_schema_owner,
principal_type => xs_acl.ptype_db));
END;
/
Me and colleague successfully imported connections from SQL Developer to VS Code following your guide in June:
https://www.thatjeffsmith.com/archive/2024/01/how-to-import-connections-from-sql-developer-to-vs-code/.
Now when yet another colleague tries to do the same thing it is not working due to it is impossible for us to get SQLci to start from the terminal, it just opens the default terminal.
I have read your answer to a person in this thread:
https://forums.oracle.com/ords/apexds/post/trouble-opening-the-built-in-sqlcl-in-sql-dev-for-vscode-23-1943
How do we open SQLci without any connection?
Wonders,
Jan
Sorry, what do you mean by this?
Now when yet another colleague tries to do the same thing it is not working due to it is impossible for us to get SQLci to start from the terminal, it just opens the default terminal.
Also
How do we open SQLci without any connection?
From a terminal, find the sqlcl/bin folder, and run the sql program there, for example
sql /nologHi Jeff,
We are facing a new problem with ORDS. Randomly, we have this message inserted in the response:
{“error”; “inline_json_parse_error”, “error_property_name”:”property”, “error_message”:”expected one of: <> but got: <>”}}}}], “hasMore” …..
What do you think about that ?
regards
I think I need more information/details
What do you need Jeff ?
We are calling a function in Oracle 19 database that receive parameters throw a POST and binded variable (payload).
The consumer says that ORDS cut the communication but as you can see in the message, this is during the parsing of the PL/SQL result that we have the probleme.
Our ORDS version is 23.1 and we can’t deploy newer version (a bug with oracle 19 and lastest ORDS version)
What bug? We don’t have any ORDS blockers with 19c.
The error reads like your plsql doesn’t the ORDS response pagination attributes.
https://support.oracle.com/knowledge/Middleware/2972860_1.html
That database bug and invalid ORDS_METADATA object is safe to ignore, you can SERVE the ORDS post install, you just won’t be able to use the database user ‘self service’ sign-up feature.
Hi Jeff,
Does SQLcl recognize private temporary tables for loading CSV files?
SQLCLi> load ORA$PTT_SOURCE source.csv
Table .ORA$PTT_SOURCE does not exists and cannot be loaded
FYI, it worked fine for a GTT.
Oracle 18c / (SQLcl) version: 24.2.0.0 build: 24.2.0.180.1721
Any ideas?
Thanks.
I seriously doubt it. At least I’ve never tested that scenario, nor asked the developer to support it. What’s your use case?
Also, you know that 18c of the database is a dead-end, right? You need to upgrade to 19c if you want continued support for that database.
Hi Jeff,
QQ… on SQL Developer you can right click on a user (under Other Users) and there is an “Edit User…” option there. I don’t see that on the VS Code extension. Is that functionality going to be added at some point or is it already there in some other place?
Thanks in advance,
Alberto
Great question!
It’s on the list, for later. Along with much of the other management features that DBAs rely on!
We’re attempting to use the MongoDB API with ORDS to point our application code as seamlessly as possible (read: ideally, no code changes) away from working against MongoDB and toward an Oracle back-end. When the ORDS MongoDB API presents its intended connection URL, it includes “&loadBalancer=true” as a parameter. Our current code is apparently using older MongoDB drivers that don’t understand that parameter. Is there any way to “disable” ORDS requiring that specific parameter, resulting in it not being required? Is there some ORDS config setting, or the like?
I’m hopeful that there is, as upon some research, the developers have come back with it would require a fairly significant code change to accommodate newer drivers.
What mongo driver or programming interface are you using?
Also what version of Oracle, ORDS, and mongo are you using?
The version of the MongoDB driver that our application is using is 3.8.
The version of ORDS is 24.1 and the backend Oracle database is 19c.
What driver are you using and did you check to see if it has a legacy mode?
To be more specific about the driver, it is the “MongoDB Java driver version 3.8”. I’m not sure what the legacy mode would be, in relation to that driver. My question, really, was whether from the ORDS/MongoDB API end, is there any way to make it not require that parameter? Because, this 3.8 version of the driver does not understand that “loadBalanced” parameter. It’s apparently a MongoDB v4.0 type of thing. The reason I’m asking this is that upgrading the drivers is proving to be a lot more work for our application development team, in terms of breaking other things, than it would be if somehow we could just tell ORDS to not care about that parameter.
Hello Jeff,
Should we use SODA collections in 23ai? We are currently developing on 19c. We have 2 SODA collections. One to hold JSON data and another SODA collection to store a blob(small image of a signature or initials for a contract). We installed our application on a 23ai database (23.4.0.24.05 to be exact) and 23ai complains about our first SODA collection(the one that holds JSON data) because the View that we created for this SODA collection reference the default columns of the soda collection in 19c, but 23ai default columns for the soda collection are different. As you know the new default columns for a soda collection in 23ai are DATA, ETAG, RESID.
This is how we created our first collection:
DECLARE
collection SODA_Collection_T;
BEGIN
— Create the collection (if it doesn’t already exist)
collection := DBMS_SODA.create_collection(‘PROFIT_MATRIX’);
END;
/
Should is a fun word 🙂
Can you, of course! However the default properties of a SODA collection have changed from 19 to 23, so if you want to keep using them as you had been, when you create the collection you’ll need to provide a few more properties in the call to get a v19 SODA collection in your 23 database.
in 19c run this –
DECLARE
collection SODA_COLLECTION_T;
metadata JSON_OBJECT_T;
BEGIN
collection := DBMS_SODA.open_collection('mycoll');
metadata := json_object_t(collection.get_metadata());
metadata.remove('tableName');
metadata.remove('schemaName');
DBMS_OUTPUT.put_line('Collection metadata: ' || metadata.to_string);
END;
/
Then use the output from that to help you create your collections in 23, like so
DBMS_SODA.create_collection('mycoll', 'metadata string goes here');
In 23ai you have the option to instead use a JSON Collection Table vs a SODA collection…and SODA collections use these ‘JSON Collection tables’ under the covers, so either way, you’re still using SODA, in a sense 🙂
Hi Jeff,
I’m trying to integrate OCI notifications with APEX running on ADB to get notifications from DB management Alert logs i created an ORDS module/handler but for some resaon i don’t get how to capture the confirmation URL needed after creating subscription with custom URL i tried as i’m not an APEX developer and trying to follow some similar examples is it possible to provide some guidance on this ?
The Autonomous database service itself has metrics available for notifications.
Have you investigated the native features already?
Hi Jeff,
But the target databases we are looking to monitor is either DBCS or EXACS we already enabled enabled DB management for them and we need to build an apex application that get notfications from different Target Databases in case of any errors and display it .
ADB is used only for APEX
Database monitoring is available and encouraged for DBCS and EXACS, but that being said…
What do you mean, ‘integrate OCI notifications with APEX’
You have an ORDS REST API that allows you to read the database alert logs?
‘Confirmation URL after creating subscription’
What mechanism are you using that you would have a subscription workflow that results in a confirmations URL, something you wrote yourself, or something that OCI provides? It’s not clear to me exactly what you’re trying to do.
ORU-10027: buffer overflow, limit of 20000 bytes.
How to remedy this ANNOYING error? I’m uunable to “see” all utPLSQL verbose messaging results.
“Oracle SQL Developer” VERSION 23.1.1.345.2114
Run[>] Stored Procedure; “Running/tab output” Exception: ORU-10027: buffer overflow, limit of 20000 bytes.
Applied “Post” recommendations.
Database preferences: Filename for connection strtup script: Login.sql has statement: SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED;
Preferences Worksheet: Max lines in script output: 99999. Max rows to print in a script: 999999
BOTH Stored Procedure AND Run [>] script block have: DBMS_OUTPUT.ENABLE(999999);
DBMS_OUTPUT panel: Buffer size: 999999 (is not automatically enabled by login script as described in older post).
Just how much text are you trying to get? This bug sounds familiar, I can take a look but why not log errors to a file or use the utplsql extension?
/* Simple block run inside a worksheet to reproduce issue
Prerequisite: installation of utPLSQL. */
SET SERVEROUTPUT ON SIZE UNL FORMAT WORD_WRAPPED
CLEAR SCREEN;
DECLARE
b_bool BOOLEAN := TRUE;
procedure P_TEST_PROCEDURE is
begin
for i in 1..9999
loop
ut.expect(b_bool,’Use case: BOOLEAN’).TO_BE_FALSE();
end loop;
end;
BEGIN
SYS.DBMS_OUTPUT.ENABLE(999999); — <—<< Increase default buffer limit.
P_TEST_PROCEDURE();
END;
Please answer my questions.
The root cause for this issue was a 2nd statement buried elswhere in the code: SYS.DBMS_OUTPUT.ENABLE(20000);
Ah cool, thanks for following up.
Are there any plans to implement a ‘utPLSQL unit testing feature’ in the new Oracle SQL Developer for VS Code, and if so what timeframe?
The plan is to document our extension APIs and then the utplsql maintainers can build an extension on top of ours.
Hi Jeff, there is a way in SQL Developer 23.1.1 to export query data grid (or directly a table) with quoted strings ?? For example, exporting to clipboard an export generate somethink like this:
insert into mi_tabla (col1, col2, col3) values (1, 2, q’^Esta es una muestra de string con ……^’);
I supose the user define quote character
Thanks in advance
kind regards
Mauricio
for inserts, no – we’d have to make a code change
Unable to Get the Meta Data Catalog. Getting error 404.
Auto Rest is disabled.
ORDS version is 404.
Any Suggestions?
Maybe your URL is wrong, or the schema isn’t rest enabled?
http://localhost:8080/ords/hr/metadata-catalog/
http://localhost:8080/ords/hr/metadata-catalog/countries/
if you leave off the trailing / we should redirect you to it, maybe that’s not happening?
I have another odd issue with curl to ORDS select call.
I have notice that ORDS will not return columns if the value is nothing, if it has null it will return the column.
Example i have a table that has 2 rows.
acct , name, last, address
11111, fname, lname, null
22222, fname, lname,
select acct , name, last, address from persontable;
it returns in json
acct: 11111, name: fname, last: lname, address:null
acct: 22222, name: fname, last: lname
row 2 returns, but missing a column.
When i view this table on Web Sql Developer and click to edit(pencil) column for 11111 it contains (null) in light grey
On the same page and view row 22222, for address there is nothing.
Null vs Nothing, how do i get sql to return that column no matter what it contains.
I did do a rawtohex on that column with nothing , just to make sure it has nothing but it returns this…
C001351800000000003D3497B47F0000988EDE97B47F0000C06D3B97B47F000000CA9A3BA00F0000A00F00000100040090A66F14000000000000000000000000
The column with null returns null with rawtohex.
As a follow up(null vs nothing). I had an export similar to cloud db, i then imported to my local db.
And the field says (XMLTYPE) on regular sql developer, but in web sql developer it is blank.
But truly not blank, cause the rawtohex show lots of info.
So guess, curl does not pull back column if column is XMLTYPE and value of (XMLTYPE)? weird
your example above had no xml…i could see where xmltype influences the output like you’re seeing
do you have an actual example i can use to debug/play around with ?
Correct it is really not blank, it is just that Web SQL Developer shows as blank.
In regular sql developer it shows as XMLTYPE on field.
But, if you click edit the XMLTYPE cell on regular sql developer, the row as true example has this inside.
FIXED-PRICE-END
0
N
Y
CONFIXEF
C1PR
C2M-FPEF
C2MX
give me a table with your xml data…otherwise i’ll consider this as ‘mystery solved’
Yup, you can mark it as google solved it.
Curl command would return empty or null for XMLTYPE columns. Need to convert to test to pull it down.
Curl command to ORDS should have the query like below when trying to pull string/text from XMLTYPE column.
select A.BO_XML_DATA_AREA.getStringVal() from acct_table A;
Hi Jeff-
Do you know if the Oracle SQL Developer extension for vscode will support LDAP connections in the future?
Thanks in advance,
-Bill
It should work now, you just need to configure a jdbc connection URL.
Formal, UI support for LDAP is on the list, yes!