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,180 Comments
Hey jeff,
How can I record the screen time of user in oracle apex application and store it to the database.
A great question, for the APEX team. Try asking in their forums.
Posting this question again since someone replied to my question and raised his own question. My question in the thread seems to have missed.
HI Jeff,
I am using SQL Developer 23.1.
I am trying to extract the ddl for an object (e.g. a table) using the quick ddl option. I am getting all the dependent objects e.g. table ddl, index, constraint, grants etc. but unable to get the ddl of the synonyms pointing to the table in the extracted ddl. I dont see an option to including synonyms when exporting table ddl. Is there a way we can extract the synonyms in the same extracted ddl file for the table?
Thanks
I spent 5 minutes on this yesterday and came up blank. I think the answer is yes, there’s a call to dbms_metadata.get_dependent_ddl that can be called for synonyms on a table, I THINK, that would do what you want, but I wasn’t able to get far with it.
Short answer: file an ER with My Oracle Support, and we’ll take a look.
Thank You Jeff for the reply.
I have raised SR 3-33245222811 with the SQL Developer product type. I hope raising a SR is the correct way to file an ER.
Thank You.
Biraja
It is!
Hi Jeff,
Just installed SQL D 23.1; explain plan button works fine; but when using ‘autotrace traceonly’, the trace works, but it should also provide a plan. Instead, get this message in the output:
”
PLAN_TABLE_OUTPUT
———————————————
SQL_ID: null, child number: 0 cannot be found
”
Can you help? Thanks.
We’re computing the SQLID for the query, and then getting the plan for that SQL. That’s not working for your SQL, that’s where the ‘null’ is coming from.
Here’s the code we’re using to print the plan
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'05dnu8cqtmgc4',format=>'ALLSTATS LAST'))Here’s how it is working for me –
set autotrace traceselect * from hr.locations
Autotrace TraceOnly
Exhibits the performance statistics with silent query output
23 rows selected.
SQL_ID 05dnu8cqtmgc4, child number 0
-------------------------------------
select * from hr.locations
Plan hash value: 2989070059
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| LOCATIONS | 23 |
------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Statistics
-----------------------------------------------------------
2 CCursor + sql area evicted
3 DB time
To continue helping you I’ll need more information from your side. Like, does this happen for any/all queries? Is your account ‘privileged,’ and what version of the database are you connected to?
While exporting rest API I am getting this error ORA-20850: Parameter p_module_id must not be null.
Weird. Well, there are like 6 ways you could be exporting your REST APIs.
What versions of which tools are you using, and how exactly are you exporting your REST API?
HI Jeff,
I am using SQL Developer 23.1.
I am trying to extract the ddl for an object (e.g. a table) using the quick ddl option. I am getting all the dependent objects e.g. table ddl, index, constraint, grants etc. but unable to get the ddl of the synonyms pointing to the table in the extracted ddl. I dont see an option to including synonyms when exporting table ddl. Is there a way we can extract the synonyms in the same extracted ddl file for the table?
Thanks
Hi Jeff
When editing PL/SQL, at the bottom of the screen there’s a super helpful one liner of how deeply nested you are in the code, and clicking on any element takes you to the start of that section (say a FOR loop, or an IF statement)
Is it possible to click on the same element, but in a way that takes you to the END of that section (say END LOOP or END IF statement) ???
I know I can collapse/expand, but a CTRL + click (or similar) on the elements in the one liner at the bottom would be very useful if it doesn’t already exist
Thanks for your time
Simon R
No, that that I know of, sorry.
Hi Jeff,
Any comments on my previous question please?
Thanks
Can you offer any guidance on resolving why I cant get apex export to work from sqlcl. I am looking to abandon using APEXExport.class. I’m seeing the following errors:
workspace and instance exports fail…. but individual application does not
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.7.0 build: 21.4.7.333.1841
SQL> apex export -workspaceid 2150425194735257 -dir /backup/apex
APEX: Unable to process request. Verify specified options:
ORA-00942: table or view does not exist
SQL> apex export -instance -dir /backup/apex
APEX: Unable to process request. Verify specified options:
ORA-00942: table or view does not exist
SQL> SELECT * FROM apex_release;
VERSION_NO API_COMPATIBILITY PATCH_APPLIED
_____________ ____________________ ________________
22.1.0 2022.04.12 APPLIED
SQL> connect app_owner
Password? (**********?) *******
Connected.
SQL> apex export -applicationid 500 -dir /backup/apex
Exporting Application 500
Grab sqlcl 23.1 and call me in the morning.
I’m trying to launch SQLcl through Azure Devops using a self-hosted agent. SQLcl launches fine using CMD when I login directly to the server, but when it comes through the pipeline agent it bombs with this error. I haven’t been able to find any reference to this specific error. Any thoughts? Thanks!
Exception in thread “main” java.lang.ExceptionInInitializerError
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.instance(ConnectionStorage.java:60)
at oracle.dbtools.plusplus.connections.db.NamedConnections.loadConnections(NamedConnections.java:64)
at oracle.dbtools.plusplus.connections.db.NamedConnections.(NamedConnections.java:60)
at oracle.dbtools.plusplus.connections.db.NamedConnections$Holder.(NamedConnections.java:42)
at oracle.dbtools.plusplus.connections.db.NamedConnections.instance(NamedConnections.java:45)
at oracle.dbtools.plusplus.connections.db.SqlclConnectionSupport$ResolverHolder.(SqlclConnectionSupport.java:18)
at oracle.dbtools.plusplus.connections.db.SqlclConnectionSupport.connectionResolver(SqlclConnectionSupport.java:30)
at oracle.dbtools.db.ConnectionSupport.install(ConnectionSupport.java:30)
at oracle.dbtools.db.ConnectionSupport.setConnectionSupportImplementation(ConnectionSupport.java:19)
at oracle.dbtools.raptor.scriptrunner.cmdline.SQLCliHelper.getCliScriptRunnerContext(SQLCliHelper.java:115)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processOptions(SqlCli.java:1140)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.(SqlCli.java:174)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:341)
Caused by: java.lang.RuntimeException: oracle.dbtools.plusplus.connections.db.storage.StoreException: TODO: Error creating connections dir
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage$Holder.(ConnectionStorage.java:50)
… 13 more
Caused by: oracle.dbtools.plusplus.connections.db.storage.StoreException: TODO: Error creating connections dir
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.verifyAndCreateDirectory(ConnectionStorage.java:222)
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.getConnectionStorageDirectory(ConnectionStorage.java:212)
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage$Holder.(ConnectionStorage.java:48)
… 13 more
Caused by: java.nio.file.attribute.UserPrincipalNotFoundException
at java.base/sun.nio.fs.WindowsUserPrincipals.lookup(WindowsUserPrincipals.java:147)
at java.base/sun.nio.fs.WindowsFileSystem$LookupService$1.lookupPrincipalByName(WindowsFileSystem.java:244)
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.createDirectoriesWithAcl(ConnectionStorage.java:297)
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.createDirectories(ConnectionStorage.java:276)
at oracle.dbtools.plusplus.connections.db.storage.ConnectionStorage.verifyAndCreateDirectory(ConnectionStorage.java:220)
… 15 more
##[error]Cmd.exe exited with code ‘1’.
Looks like it’s trying to create a .sqlcl directory in the $HOME and it’s failing.
Hi Jeff
Appreciate the help you provide to the community.
How to use the SQLCL connection strings saved using “NET =:/;” for opening database connections. I dont seem to be able to find documentation on how and where the connection strings, saved using the NET command, could be used.
please share the link for the documents if any.
Regards
Vijay
Sorry Jeff.
I meant connection strings saved using NET name=dbhost:port/service_name; The comments aren’t showing if I enclose the name in “” .
once you have saved a NET name, you can simply use it in a connection request
SQL> net cdb=localhost:1521/free;SQL> net list
cdb
free
SQL> connect [email protected] as sysdba
Password? (**********?) ******
Connected.
SQL> show connection
COMMAND_PROPERTIES:
type: ORACLE
user: sys
url: cdb
role: sysdba
CONNECTION:
[email protected]:oracle:thin:@localhost:1521/free AS SYSDBA
CONNECTION_IDENTIFIER:
cdb
CONNECTION_DB_VERSION:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
NOLOG:
false
PRELIMAUTH:
false
SQL>
— SELECT SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
— SDO_ORDINATE_ARRAY(a, b)) AS cust_location, cust_name
— FROM (
— SELECT * FROM (
— SELECT 77.376855 AS a, 28.617492 AS b, ‘plot2’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376372 AS a, 28.617529 AS b, ‘plot2’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376388 AS a, 28.617715 AS b, ‘plot2’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376870 AS a, 28.617691 AS b, ‘plot2’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376830 AS a, 28.617253 AS b, ‘plot1’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376369 AS a, 28.617285 AS b, ‘plot1’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376384 AS a, 28.617463 AS b, ‘plot1’ AS cust_name FROM dual
— UNION ALL
— SELECT 77.376845 AS a, 28.617431 AS b, ‘plot1’ AS cust_name FROM dual
— )
— )
I have this code to visualize two polygons on oracle apex maps, but i don’t get any geometry in the front end
I’m not the best person to help with APEX apps, but someone here will know what to do.
Is there a current How To document detailing the steps to configure SQL Developer to connect to a SQL Server database using Window Authentication? I found some older posts that referenced using a third party driver, (jTDS), and the following file placements within SQL Developer:
– Copy the jtds-1.3.1.jar into: {$sqldevhome}\jlib Directory.
– Copy the the ntlmauth.dll dll in the \x64\SSO subdir into: {$sqldevhome}\jdk\jre\bin\ntlmauth.dll
Any help would be greatly appreciated.
That’s pretty much it, I think.
Ok. I’ll keep messing with it. I appreciate the reply.
Hi Jeff,
I have a procedure with table type as an out parameter (a table of object).
When I try to execute it using SQL developer, it returns an error If I try to show the table out parameter in the output.
Normal scalar parameters are successfully shown (if the table type is not shown).
Is there some way to display such parameter type in the output upon execution?
Here is an example:
–In HR Schema, I created the following objects:
CREATE TYPE EMP_OBJ AS OBJECT (
F_EMP_ID NUMBER(12),
F_FIRST_NAME VARCHAR2(50),
F_LAST_NAME VARCHAR2(50),
F_JOB_ID VARCHAR2(50),
F_SALARY NUMBER(15,3)
);
/
CREATE TYPE EMP_ARR AS TABLE OF EMP_OBJ;
/
Then I created the following procedure:
CREATE OR REPLACE PROCEDURE GET_EMPS_BY_DEPT_ID (
I_DEPT_ID IN EMPLOYEES.DEPARTMENT_ID%TYPE,
O_EMP_ARR OUT EMP_ARR
)
AS
BEGIN
SELECT EMP_OBJ(
E.EMPLOYEE_ID,
E.FIRST_NAME,
E.LAST_NAME,
E.JOB_ID,
E.SALARY
)
BULK COLLECT INTO O_EMP_ARR
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = I_DEPT_ID;
END;
/
Now I want to execute the procedure in SQL developer using “Run” option and show the output of the procedure of parameter “O_EMP_ARR”.
Even in the auto-generated code, the line to show the output array is paused.
Here is the auto-generated code:
DECLARE
I_DEPT_ID NUMBER;
O_EMP_ARR HR.EMP_ARR;
BEGIN
I_DEPT_ID := 50;
GET_EMPS_BY_DEPT_ID(
I_DEPT_ID => I_DEPT_ID,
O_EMP_ARR => O_EMP_ARR
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE(‘O_EMP_ARR = ‘ || O_EMP_ARR);
*/
–:O_EMP_ARR := O_EMP_ARR;
–rollback;
END;
/
My question is how to display the output of such types in SQL developer.
Something rather than “DBMS_OUTPUT” of course.
Thank you.
I don’t think that’s supported, you’re going to have to write some code.
Hello Jeff,
We use SQL Developer Data Modeler to model and document our Oracle databases. We have also used it to document some on-premise Microsoft SQL databases. We are however running into a problem connecting to the SQL databases we have in the Azure cloud.
We get this error message:
Status : Failure -Reason: Login failed due to client TLS version being less than minimal TLS version allowed by the server.
I suspect that the issue lies with JDTS 1.3.1 not supporting TLS 1.2. Are you able to recommend another driver or a workaround to solve this connectivity issue?
Thank you,
Carsten
If you export your Azure SQL database to a sql script, we can use that to create a model.
Hey Jeff,
i have ords 20.4 that running on tomcat 9
im tring to see my instance metrics , so i add to the defaults.xml in my config folder this line:
true
and restart.
do i need to do anything else?
and how can i see the metrics?
thanks.
Kris shows how here.
thank you ,
i did it
and i keep getting http 401…
[user@]$ curl -v –user : http://:8080/ords/_/instance-api/stable/status
* About to connect() to port 8080 (#0)
* Trying …
* Connected to () port 8080 (#0)
* Server auth using Basic with user ”
> GET /ords/_/instance-api/stable/status HTTP/1.1
> Authorization: Basic #####################
> User-Agent: curl/7.29.0
> Host: :8080
> Accept: */*
>
< HTTP/1.1 401
< Content-Type: application/problem+json
< Content-Length: 183
< Date: Mon, 22 May 2023 15:54:38 GMT
<
{
"code": "Unauthorized",
"message": "Unauthorized",
"type": "tag:oracle.com,2020:error/Unauthorized",
"instance": "tag:oracle.com,2020:ecid/##########################"
* Connection #0 to host left intact
}
what do i need to do?
Create an ords user with the appropriate role, did you do that? It’s NOT a database user.
Are we able to send the queried report directly to email recipients by scheduling a job
Via DBMS_SCHEDULER, yes – but that’s a database feature/function, not a SQL Developer one.
So you would have the database via job startup sqlcl, run your script, to get your report, and then have another item email that report.
SQL Developer, great tool, snippets, great feature, i would love to be able to specify the location for the user snippets file (we are using a vm that doesn’t let us over write what was installed, so snippets we add disappear between sessions
Back in late 2022 I believe you wrote and indicated in early 2023 that SQL Developer would be moving away from many of the Microsoft modules that it was using to open source modules. Since SQL Developer 23.1 documentation doesn’t reference such I wonder if there is an update on that change?
Microsoft modules? No idea what that means, we’re a 100% java stack at the moment.
We’re building a new platform based on VS Code, is that what you mean?
Yes, VS Code. Apologies. Wheb might it be available?
Later this year.