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
Hey Jeff,
Since it doesn’t look like it currently exist, is there a unit testing type of feature that is on the vision for the VS Code Extension? Probably something that act similar to Java JUnit in a way https://junit.org/junit5/docs/current/user-guide/
We’re about to publish a SDK that developers can use to add features to/with our extension, and that would include things like the open source ut/PLSQL project, which already has an extension for SQL Developer ‘classic.’
SQL Developer for VSCode version 24.3.1
SELECT queries incur EXCEPTION when table contains a column of type NESTED TABLE.
I have recreated this issue on two different tables.
SQL Developer 23.1.1 works fine, does not incur exception.
SELECT * FROM MYTABLE;
/* An error occurred while running the statement */
Same error occurs in the Data (tab) when browsing the table
/* Error executing ‘SELECT ID, NESTED_COLUMN, ROWID AS “ora_rowid” FROM (“MYSCHEMA”.”MYTABLE”) ‘ */
DDL / DML to reproduce the issue follows.
CREATE TYPE MonthDayType AS TABLE OF VARCHAR2(20);
/
CREATE TABLE MyTable
( id NUMBER PRIMARY KEY,
nested_column MonthDayType
)
NESTED TABLE nested_column STORE AS nested_month_day_tbl;
/
INSERT INTO MyTable (id, nested_column) VALUES (1, MonthDayType(‘January’, ’01’));
/
INSERT INTO MyTable (id, nested_column) VALUES (2, MonthDayType(‘February’, ’14’));
/
INSERT INTO MyTable (id, nested_column) VALUES (3, MonthDayType(‘March’, ’21’));
/
COMMIT;
/
SELECT * FROM MyTable;
The grid doesn’t support that object type yet, but we’re working on it, for now use ‘execute as script’ for a workaround.
Hello Jeff. I have a problem with sqlcl/liquibase (v24.3). I want to execute a “runOracleScript” changeset with sourceType=“FILE”. Even with a simple spool and “select * from dual;” I get the error:
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset db_changesets/testfile.sql::raw::includeAll:
Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
https://docs.oracle.com/error-help/db/ora-00900/ [Failed SQL: (900) spool spooltest_file.log
select * from dual;
spool off]
ORA-00900: invalid SQL statement
With sourceType=“STRING” and the statements directly in the changeset it works!
Do you have a working example for sourceType=“FILE” ?
By the way, the documentation at https://docs.oracle.com/en/database/oracle/sql-developer-command-line/24.3/sqcug/examples-using-liquibase.html is very short about the parameters for runOracleScript. For example: What is “objectName” and “ownerName” for?
Thank You, Flo.
It’s supported and we use it internally all the time.
The ‘raw’ indicates you’re not using runOracleScript.
Can we see your actual XML changeSet?
It should look something like this
Hi Jeff. SQL Developer 21.2 on Windows 11 Pro. This behavior changed recently. I connect to a database in the Connections pane. Drilling down and clicking or opening any object, the new pane opens in the section with the Connections pane, rather than in the section with all the database worksheets/tabs/panes. I can drag the new object pane to where I want it. But I want it to start in a new tab next to the database worksheets/tabs/panes, like it used to.
Windows > Reset Desktop to Factory Settings
Thanks Jeff!
Hi Jeff. Having recently shifted away from good old EBS to Oracle Cloud Apps (Fusion), I’m REALLY missing SQL Developer. Is there any way to connect to the Fusion HCM/FIN/SCM databases using SQL Developer?
Your real question is do those SaaS offerings support access to the database?
In most cases, what they offer is the ability to copy your data on a routine basis to Autonomous Data Warehouse, and then you can query/connect with SQL Developer.
This isn’t a SQL Developer or ‘Jeff’ question, it’s more about what access the Applications are making available.
Hi Jeff,
https://www.thatjeffsmith.com/archive/2021/12/oracle-sqlcl-datapump/
I am attempting to utilize the data pump export/import feature from VSCode SQLcl. My objective is to export the schema directly into an AWS S3 bucket. However, I am struggling to formulate the appropriate command for this task. Could you please help me with a sample command that I can use to export/import the schema directly from S3?
Thanks in advance 🙂
We don’t support that, you’d export it to OCI object store.
I’m sure you could script it though…
Thanks for the confirmation.
In this case, I will check if I can directly export the schema to ATP storage. To optimize storage usage, I would like to implement the compression option for both data and metadata. Do you have any insights into potential issues or concerns related to the use of compression options in the export process?
Hi Jeff
I am trying to deploy REST API curl commands to query data and I am getting the OutOfMemoryError: Java heap space error occasionally. Do you have any advice on how to avoid this?
Thanks
Andy
I have a really, really trite reply to this, and I apologize in advance, but… give ORDS more memory?
To give you a better answer, I would need a better understanding of your runtime environment, load on ORDS, and types of requests/payloads you’re dealing with.
Hi Jeff, I’ve just installed, in my usual working machine, the new version of Sqldeveloper (24.3) and It’s came with some issues. I know this should be related to the Sqldeveloper preferences, because the previos version (23.1), I’m still got it and I can compare, doesn’t have it.
Examples:
1.- If I execute the query “select 1 numero, sysdate fecha, ‘HOLA’ caracter from dual;”, the number 1 is displayed like “oracle.sql.NUMBER.f18cf09”. The date and varchar data are ok.
2.- I cant’t select some item in the result grid and copy (Ctrl+c) to the clipboard, always remember the last characters copied in the worksheet…
I can’t figure out what kind of setting could be involved in this behavior
I would appreciate any tips
Thank you very much
Issue 1: Sounds like you have an oracle 21c or older client configured in sqldev – remove that, or replace with oracle 23ai client.
Issue 2: does using your mouse vs keyboard help? What OS are you on?
Thanks Jeff, both issues resolved….I’ve upgraded the intantclient to 23.6 and voilá…all is ok
kind regards
Mauricio
This is regarding ORDS and limiting downtime when using multiple database connections.
We run dual WebLogic servers, each running one ORDS servlet. Each ORDS has 7 DB pools connecting to 7 PDBs.
We are in the process of a rolling (PDB by PDB) upgrade of APEX. During the upgrade, we disable access to the PDB via web redirects, but do not shut down ORDS in order to retain access to the OTHER PDBs. After the upgrade, ORDS will no longer connect to the new APEX, and it seems that only a complete restart of the WebLogic managed server / ORDS fixes it. This causes a brief (about 1-2 minute) outage for ALL of our PDBs and the 30+ production, mission critical APEX applications we are running.
Question is: Is there some way around this full outage? Is there a way to tell ORDS to completely refresh a single connection pool without a full restart, so that it connects properly to an upgraded APEX? PS, this is also a problem whenever we have to change the ORDS_PUBLIC_USER password since ORDS does not seem to reread its wallet unless it is restarted.
Thanks!
That shouldn’t be happening, what version of ORDS?
Also, you’re not actually making any changes to ORDS config itself, it’s pools never get shutdown? Even if the PDB ‘goes down’, when you make a request, we should attempt to re-create the pool. If that fails, there’s a TIMEOUT before we try it again.
What about REST APIs on those pools or SQL Developer Web, are they reachable? Or is this only for APEX?
I am working on my home rig, not in the office, so unsure where to post bug reports.
Open & logon to legacy SQL Developer, and export my custom Type UTBAG. My backup copy.
Launch VS Code and update to version 24.3.1
I discovered the bug details below and have been waiting for this update. But the bug persists It is reproducible on my rig.
Logon to my Schema in VS Code and edit my UTBAG Type body and compile it. Note spec is not open in a worksheet.
Notice the output informs that the Body compiled ok.
Followed by PLS-00304 !
Type Body UTBAG compiled
LINE/COL ERROR
——— ————————————————————-
0/0 PL/SQL: Compilation unit analysis terminated
1/11 PLS-00201: identifier ‘UTBAG’ must be declared
1/11 PLS-00304: cannot compile body of ‘UTBAG’ without its specification
Errors: check compiler log
Next I open the UTBAG Type specification. It has been wiped out. All that remains is a single line.
create or replace
I’ve experienced this occasionally in Legacy SQL Developer previously. But, I am unable to consistently reproduce it. In VS Code it is easily reproduced. I suspect it a bug in Oracle DB.
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
However this bug occurs repeatedly using VS Code making this platform unusable for me.
Using my backup file, I paste the spec into the worksheet & recompile the Type specification, then close the worksheet.
Type NEO.UTBAG compiled
Next re-open the Type body and recompile. SAME ERROR is reproduced.
I need a test case.
Reproduce the bug with sample bugtype provided. Please test with identical version of
Oracle Database 21c Standard Edition 2 Release 21.0.0.0.0 – Production
Compile both the Type spec, and body using legacy SQL Developer.
Logoff
Launch VS Code
Open / Edit Type Body in worksheet
Click compile.
SCRIPT OUTPUT (tab)
Type Body BUGTYPE compiled
LINE/COL ERROR
——— ————————————————————-
0/0 PL/SQL: Compilation unit analysis terminated
1/11 PLS-00201: identifier ‘BUGTYPE’ must be declared
1/11 PLS-00304: cannot compile body of ‘BUGTYPE’ without its specification
Errors: check compiler log
Open Type spec in worksheet. Spec now has 1 line only.
create or replace
————————————————————————————————
CREATE OR REPLACE EDITIONABLE TYPE “MYSCHEMA”.”BUGTYPE”
authid current_user
as object
(
label varchar2(4000 char)
, constructor function bugtype(self in out nocopy bugtype
, v_label_in in varchar2) return self as result
)
/
CREATE OR REPLACE EDITIONABLE TYPE BODY “MYSCHEMA”.”BUGTYPE”
AS
constructor function bugtype(self in out nocopy bugtype
, v_label_in in varchar2) return self as result
is
begin
label := v_label_in;
return;
end;
end;
/
I’m not going to test with 21c, i’ll test with 23ai and 19c though.
Hello Jeff,
Could you please shed a light about the circumstances under which the thick/instant client is the preferred option to be used instead of the thin one?
Thank you very much
Panagiotis
With the latest driver, 23ai, there should be little to no reason to opt for THICK connections.
Hi Jeff,
Thanks for the new SqlDeveloper 24.3 version but, besides the Excel nasty import/export bug, I noticed another annoying behavior.. In the status bar at the bottom there is a persistent “Waiting for StatementRunner Task to Finish” with a swinging bar. I have nothing running AFAIK, so I suspect that this is a regression (the previous version did this also, but rarely).
Thanks
Folks on forums are also reporting this, and i’m trying to put together a reproducible test case. Thanks for sharing that information as well, it helps!
Hi there,
Is there a handy function hidden somewhere in oracle dbms, apex or ords_metadata packages to parse the url query parameter json to a sql where predicate?
We need to build a sdk to do this
Ah, ok, I’ll have a go at it myself in the meantime.
newbie sqlcl question, and I may be missing the obvious
from my Windows machine, I have sqlcl working fine
– but how do I connect to all my other remote databases/pluggable dbs (mostly on Linux but some cloud) using my tnsnames.ora aliases??
FYI all those aliases work from sqlplus/tnsping, so no networking problems
i.e. have heaps of aliases that all work from sqlplus
– surely I don’t have to enter the whole address for each connection instead of using my tns aliases
Thanks
John
can’t use oci client?
Not required for TNSNames but it is supported – now requires a 23ai client if you want to use a thick connection vs thin (jdbc)
What does this commamd return?
show tnsThanks –
put in 23ai client and it works fine now
First, thanks to you and the team for SQL Developer 24.3.
I bring up SQL Developer 24.3, import everything from the previous version, click on ‘Help’ and then click on ‘Check for Updates’. It brings up the ‘Select update source’ panel, which contains the following warning:
WARNING: You are running JDeveloper in non-Administrator mode. Hence, OPatch updates and other updates that depend on OPatch will not be available. To view OPatch updates, please run JDeveloper in an admin mode.
Please explain the warning and how to resolve it when running SQL Developer.
Check for Updates is just for extensions, you don’t need it for updating SQL Developer itself…so just ignore it.
Hi Jeff
I am using SQLcl on Windows – in PowerShell and a C# Form from where I call sqlcl.exe.
Works fine until I decided to migrate SQLcl to version 24.3 and JDK 17.
Now it says “Error Message = no ocijdbc23 in java.library.path” when I try to connect to a 19.24 DB.
Windows Server 2022 Standard with a Oracle 19 DB installed,
Using the following directory-structure:
sqlcl
jdk
sqlcl
bin
lib
That “jdk” directory is coming from the SQL Developer 24.3 installation, contains the Java 17.0.13 version.
What is missing ?
Last working setup: SQLcl 23.3 and Java 11
Regards, Martin
It’s trying to load the thick client – either because the oracle client is in your path, or you’re using a feature that requires it
in sqlcl now you can now use the ‘-thin’ flag to force the local jdbc driver to be used
sql -thin hr/oracle@localhost:1521/freepdb1Hey Jeff,
I have a quick question regarding the Sqlcl CLI tool. When saving connections using the connect command with -save flag, is there a way to export these connections to a file or something with a passphrase that can be used to import to another PC? I would like to export my connections from Sqlcl, including passwords like I can in SQL Developer.
Many thanks,
Jack
They’re already in files. Just copy up the connections directory and move them to your other PC.
Hi Jeff, I’m trying to process a simple html form with its action invoking an ords post service.
The html code is:
Nombre:
Apellidos:
Profesión:
Edad:
The handler is: BEGIN www_ords_pruebas.complex_post_v1(p_nombre => :nombre, p_apellidos => :apellidos, p_profesion => :profesion, p_edad => :edad, p_body => :body); END;
And I have defined all the explicit parameter like this:
ORDS.DEFINE_PARAMETER(
p_module_name => ‘prueba04’,
p_pattern => ‘con_param’,
p_method => ‘POST’,
p_name => ‘pf_nombre’,
p_bind_variable_name => ‘nombre’,
p_param_type => ‘STRING’,
p_source_type => ‘URI’,
p_access_method => ‘IN’,
p_comments => NULL
);
The procedure parameters’s values are all null, except :body. My output is like this:
{
“NOMBRE”:”Sin data”
,”APELLIDOS”:”Sin data”
,”PROFESION”:”Sin data”
,”EDAD”:”Sin data”
}
———————– BODY INI ——————————-
pf_nombre=Jimmy&pf_apellidos=Page&pf_edad=70&btn_submit=Enviar
———————– BODY FIN ——————————-
How can I get the values from the form in the handler??
Thank you very much
This the html code
”
Nombre:
Apellidos:
Profesión:
Edad:
“
I’m sorry, but I don’t know how to post the html code.
The input are like this: input type=”text” name=”pf_nombre” id=”nombre” value=”Jimmy”
and the method=POST and action=”http://192.168.0.150:8080/ords/pru19c/prueba04/con_param”
Turns out we built some cool tech into ORDS for FORM POST actions, including multi-file uploads. The docs and some blog samples are pending any day/hour now.
Are there any plans to add ORDS to https://endoflife.date?
I have a job that scrapes https://www.oracle.com/tools/ords/support.html to alert our app teams when their ords version is getting old. It works, but using an endoflife.date api would probably be more reliable.
Today is the day I learned this site even exists.
I see they have APEX listed, if they’re scraping our pages, then they should be able to see the ORDS info as well.
Also remember that our new support windows for apex, ords, etc is 18 mos vs 5 yrs.
Since endoflife.date isn’t being populated for ORDS, is there a better lookup source of version dates than the support page?
It currently doesn’t have 24.3 on it.
It came out just last week, it’s 18 months from the month it was release, so you have a year and a half.