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,793 Comments
Hi Jeff,
I found the issue. The .DMD file of the new data model design I am working on had following –
C:\ABC_DDL
I think this could have happened because I created the data model design by first importing from a DDL file. Anyway, once I removed above section from the .DMD file, I am able to use MyUserDefinedProperties.udposdm in both data model designs.
Thanks,
DR
Hi Jeff,
Thank you so much for the great material you have been posting on your website for more than a decade.
I have been wealth of information you have posted to learn how to effectively utilize Oracle SQL Developer Data Modeler features. For a data model design, I added user defined properties and saved them in MyUserDefinedProperties.udposdm in Default System Types Directory. Now, I want to use this file in another data model design, but not able to figure out how to do that. Is there a way to use existing .udposdm file?
I am using Oracle SQL Developer version 19.1.0.094, build 094.2042.
Thank you,
DR
You’ll get a quicker and more accurate answer if you can ask that question here.
Hi Jeff,
Row comparison.
Useful to quickly find which column of 2 almost the same rows makes them different.
With such feature is one-click to go. Without this it is some annoying manual search which usually end up with some additional trailing spaces…
Illustration: https://www.allroundautomations.com/wp/inhoud/uploads/pls140-sw-compareresults.png
I know PL/SQL Developer has this feature (ilustration above is from that program) and I miss it in SQL Developer.
Or maybe it it already present (fingers crossed), but I didn’t found it yet? 🙂
Thank you,
Adam
Nope, not a feature in the product today.
Thank you.
If you have a list of features to think of to be developed in a future or maybe you plan to organize some community voting on which of them are the most expected, please add “Row comparison” to this list. 🙂
Best regards,
Adam
It’s not on the list today…but I do appreciate the input. It’s this kind of stuff that helps put things on or higher on the list.
Is there any new feature for row comparison or any plans for enhancement like this?
No importa lo que haga no me imprime ni siquiera este mensaje en la pantalla de dbms, Favor ayuda.
begin dbms_output.put_line(‘hola’); end;
No importa lo que hiciste … ¿qué hiciste exactamente?
¿Con qué versión de Oracle está trabajando?
Hi Jeff,
I’m using SQL-DEV 19.4
How can I use SSO to connect to the database?
In the previous version (eg 19.1) this was working.
Now when I try to make a connection I always get a pop-up asking me for the username/password.
Kind regards
Peggy
Do you still have 19.1 on your desktop, and does it still work?
Yess indeed on a 19.1 it is still working.
Please open a SR with MOS and file a bug report. The main thing that’s changed from 19.1 to 19.4 is the JDBC driver which is now 19c. Are you using a OCI/Thick connection? Might be a driver conflict with your $ORACLE_HOME.
Hi,
I have a rather generic question.
We are migrating from Oracle 11 to Oracle 12 Container Database.
Our application uses utl_http to call external web services, but with the target solution we will not be able to use ports for http, https, smtp, etc. However we are switching from Webtier with mod_plsql to ORDS (standalone).
So I was thinking, perhaps there is an easy solution, already thought of by Oracle, how to use ORDS for outgoing HTTP requests (and get the responses).
We were looking at APEX_WEB_SERVICE, but it is not clear, if this uses 443 directly or goes through ORDS.
What would you recommend?
ORDS is exclusively for receiving HTTP requests. If you want to go out, then you would look at the HTP package. And no, that doesn’t use ORDS.
Hi Jeff —
I’m trying to create a transformation script in data modeler that not only allows me to automatically create a view from a table (I new there is a wizard for this) but I also want to create the view with a couple new columns in which I programmatically provide the expression. I’m able to change the examples to iterate across all views, but don’t see how to manipulate and add/remove/change columns of a view: Just looking for a quick snippet to help me out. Truly Appreciate.
var tables = model.getTableViewSet().toArray();
//tables = model.getAllViews();
for (var t = 0; t<tables.length;t++){
table = tables[t];
name = name + table.getName() + ",\n";
name = name + "\n==============================================\n\n SQL: \n" + table.getUserDefinedSQL() + "\n ============================================\n\n";
//table.setName(name);
columns = table.getElements();
//columns = table.getColumnViewSet();
size = table.getElementsCollection().size();
//columns.createViewElement();
//columns.addItem();
for (var i = 0; i < size; i++) {
column = columns[i];
cname = column.getName().toLowerCase();
name = name + " " + cname + ",\n";
//column.setName(cname);
}
//table.setDirty(true);
}
See the journaling table script…it adds columns
Thanks Jeff —
I should have asked if the views would show up in Data Modeler. I wanted to change the Views that are in data modeler by adding the additional columns and seeing them in the GUI as well without having to reverse engineer after the fact.
Is what I’m asking make sense? Truly appreciate the input… and thanks in advance.
So it would have been helpful.. If I just read the documentation ;-). CreateTableView and CreateViewElement gives me what I need.
Thanks for your help.. made me take another look at what I was doing.
Best Regards,
Robert
Awesome!
In SQL developer strings containing european characters do not display correctly.
e.g
SELECT ‘Ä Ê Í,’FROM DUAL;
Results in
‘ÄÊÍ,’
——
� � �,
i.e column name displays characters but not the result row
Database settings are
1* SELECT * FROM V$NLS_PARAMETERS
SQL> /
PARAMETER – VALUE
NLS_LANGUAGE – AMERICAN
NLS_DATE_LANGUAGE – AMERICAN
NLS_CHARACTERSET – US7ASCII
NLS_SORT – BINARY
NLS_NCHAR_CHARACTERSET – AL16UTF16
NLS_COMP – BINARY
I have tried various values for encoding and fonts in SQL developer.
I cannot change database parameters.
Similar queries work in SQLplus from Unix.
Thanks
Jens
You’re storing them as NVARCHAR2 data?
if so, works for me
Just make sure your Editor FONT in preferences is a font that supports display of those characters
Hi Jeff,
Why are the below queries not giving consistent results?
select to_date(’13-MAR-20′,’DD-MON-RR’) from dual; -> Gives Correct date
select to_date(’13-MAR-20′,’DD-MON-YY’) from dual; -> Gives Correct date
select to_date(’13-MAR-20′,’DD-MON-YYYY’) from dual; -> Adds +2 to the date
Thanks & Regards,
Amit Parsan
Good question.
It’s not happening for me, but I’m not in your database. A better question for our SQL forum or even AskTom.
Hi Jeff,
we are currently using liquibase in one of my projects. I try to use the sqlcl liquibase implementation but I experience several differences to the vanilla liquibase. One difference occurs with connected Changelog files.
In the following example controlfile db.changelog-3.25.0.xml we are pointing to db.changelog-3.24.5.xml.
If we run lb status db.changelog-3.25.0.xml we see a difference even if this changesets are already applied.
9 change sets have not been applied to DZD06@jdbc:oracle:thin:@//dfvvesdzd01db.dzbank.vrnet:1521/ESDZD01
dzdigital360-3.25.0/1_Sprint25_1ddl.sql::liquibase-3.25.0-1::herzhd
select id,author,filename from databasechangelog;
584017293450 liquibase liquibase-internal
liquibase-2.24.5-1 herzhd dzdigital360-2.24.5/createdb.sql
liquibase-2.24.5-2 herzhd dzdigital360-2.24.5/createdb.sql
liquibase-2.24.5-3 herzhd dzdigital360-2.24.5/createdb.sql
liquibase-2.24.5-4 herzhd dzdigital360-2.24.5/createdb.sql
liquibase-3.25.0-1 herzhd dzdigital360-3.25.0/1_Sprint25_1ddl.sql
liquibase-3.25.0-2 herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
liquibase-2.25.0-2a herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
liquibase-2.25.0-2b herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
liquibase-2.25.0-2c herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
liquibase-2.25.0-2d herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
liquibase-2.25.0-2e herzhd dzdigital360-3.25.0/2_Sprint25_1dml.sql
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-3.25.0-2::herzhd
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-2.25.0-2a::herzhd
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-2.25.0-2b::herzhd
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-2.25.0-2c::herzhd
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-2.25.0-2d::herzhd
dzdigital360-3.25.0/2_Sprint25_1dml.sql::liquibase-2.25.0-2e::herzhd
dzdigital360-3.25.0/3_Sprint25_enable.sql::liquibase-3.25.0-3::herzhd
dzdigital360-3.25.0/4_Sprint25_delete.sql::liquibase-3.25.0-4::herzhd
It looks like, the sqlcl is looking only for the first changelog file. Is this this the expected behavior?
Hi Jeff, I have a question about relative links in ORDS.
According to your blog https://www.thatjeffsmith.com/archive/2017/05/ords-restful-services-and-uri/ we generated a hyperlink with $. So far so good.
We’d like to provide available links on the resource in the response, like HAL.
But $ always returns the complete URI including host. We are a SaaS provider and In our deployment we use an API Gateway between the end-user application clients and ORDS, where the original request is redirected to the ORDS server. So the generated hyperlink with the ORDS endpoint in it, is not the endpoint the client will use.
Is there a way to have a relative link in the generated URI?
So “http://ords-server/ords/employees” will be
“/ords/employees” from the ords root path.
Thanks for your answer!
Hi,
I have a question about the SQL Developer. I do not have much knowledge using SQL Developer .. My Question is i tried to upload BO(Business Objects) SQL Query into oracle SQL Developer software. When i run the query i got 5 errors in results that states that count value is multiplying the double i do not know why . how can i rectify this issue.
If you do not get my point clearly then it is fine.
Many Thanks,
Gowthami.
I’m not familiar with that error message.
Can you share your SQL and errors so I don’t have to guess what might be happening?
Jeff,
Should I be able to Import Connections with passwords into Data Modeler, from a JSON file Exported by SQL Developer with passwords?
When I try the Data Modeler Connections Import, I get the error: “The specified decryption key does not match the key used to encrypt the passwords in the file.”
To ensure I’m entering the same password, I’ve copied it to my clipboard, then pasted it for the SQL Developer Export and the Data Modeler Import.
I’m running on Windows 10 Enterprise version 1809. The SQL Developer version is 19.2.1.247; the Data Modeler version is 19.2.0.182. I believe these are the latest versions.
Thanks!
Good question. Theoretically, I would say ‘yes’
Yes, that’s what I thought, but it isn’t working for me. I wonder if anyone has been able to do this successfully.
HI Jeff,
In Version 19.4 the feedback off supresses Error Messages even with WHENEVER SQLERROR EXIT. In SQLPLUS this works fine.
Any ideas why sqlcl reacts this way?
SET FEEDBACK OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
select * fom table_does_not exist;
IN SQLPLUS
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 9 06:37:11 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Mon Mar 09 2020 06:36:41 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> set feedback off
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SQL> select * from abc;
select * from abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
IN SQLCL
SQLcl: Release 19.4 Production auf Mo Mär 09 06:40:12 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved. Alle Rechte vorbehalten.
Last Successful login time: Mo Mrz 09 2020 06:40:14 -04:00
Verbunden mit:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> set feedback off
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SQL> select * from abc;
Abgemeldet von Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Fixed for version 20.1, and I think this was a Windows-only bug.
Hi Jeff,
Thank your for the quick fix. The bug happend on linux box.
Linux xxx.xxx.xxx 3.10.0-1062.12.1.el7.x86_64 #1 SMP Thu Dec 12 06:44:49 EST 2019 x86_64 x86_64 x86_64 GNU/Linux
Hi Jeff
a question: I recently wrote a very useful tool that allows to extract a DB record plus all entries that are either referencED by that record as well as – optionally – also all records that are referencING said record. This allows to extract what I call a “subgraph” from a DB, i.e. a consistent subset of data with all valid cross-references allowing to e.g. extract such a data set from a productive DB and insert it into a test DB in order to reproduce or replay certain situations. Extremely useful for debugging and analysing!
Now, the issue is: colleagues have started to use this tool and like it and they also started to extract pretty large subsets. Doing so we are encountering, that the output of the tool is frequently truncated or rather: we only get the last 3000 or so lines. The begin is chopped off. This of course limits the usefulness severely. 🙁
I am running my script as an extension plugin and the output is sent to the “Messages – Log” window which obviously has only a limited buffer size. Can one somehow adjust and enlarge that buffer size?
I already fiddled with Tools -> Preferences -> Database -> Worksheet: Max rows and Max lines in script output but these settings don’t seem to influence the Message output window.
Can one modify the output buffer length?
Preferences->Environment/Log has save logs to directory and maximum log lines (which is 3000 by default). -Brian.
POST command using Oracle ORDS version 18.2.0.r1831332. inserting 2 records in error.
I am doing a single POST operation that should insert only 1 record at the time in a table where a text file is inserted into a blob_document column. ( see table DDL here below. )
But instead it is creating 2 records.
One record will have the blob column populated correctly with the text file and a second record is created where the blob_document column is empty.
I have here below the DDL of the sequence and table (with trigger) and the POST command sql statment.
—
CREATE TABLE “AL”.”DOCS_JSON”
( “SEQ_ID” NUMBER(10,0) NOT NULL ENABLE,
“LOADING_DATE” DATE DEFAULT sysdate,
“BLOB_DOCUMENT” BLOB
) ;
—
CREATE UNIQUE INDEX “AL”.”DJ_PK” ON “PAL”.”DOCS_JSON” (“SEQ_ID”);
—
CREATE OR REPLACE TRIGGER “AL”.”BIUD_DJ_TRG”
before insert or update or delete on docs_json
for each row
begin
if deleting then
null;
elsif inserting then
select dj_seq_id.nextval into :new.seq_id from sys.dual;
elsif updating then
:new.seq_id := :old.seq_id;
end if;
end;
—
—
— POST —
declare
l_blob blob := :body;
l_seqId number := 0;
begin
— nextval —
insert into DOCS_JSON( LOADING_DATE, BLOB_DOCUMENT ) values ( sysdate, l_blob ) returning seq_id into l_seqId;
—
:response := l_seqId;
:status := 200;
—
exception when others then
:response := ‘Status NOK.’;
:status := 400;
end;
Could this be a problem with the ORDS version?
The Oracle ORDS version is: 18.2.0.r1831332.
Any help or hint will be much appreciated.
Regards,
Anibal
If you add a COMMIT to your anon block for your restful service?
If you disable the trigger? Also, have you considered just using an IDENTITY clause for your PK? Then you don’t have to worry about triggers and sequences anymore.
in sql developer when i do search or “search and replace” in a sql worksheet or package edit it works fine the first time. But when I want to do a new search or “search and replace” in the same worksheet/package, and placing the cursor back to the start. it does not work. I have to close the search/search and replace little block in the top and start a new search and then it works.
These actions you do a lot in an editor. Is this a known bug or is there something to solve this or workaround?
Do you have wraparound selected? Icon to the right of Replace.
No i did not have wraparound selected. Should i try search with wraparound selected and if not found wraparound not selected? What does search with wraparound selected do?
wraparound means when you get to the end of the buffer/file, do you want to go back and search from the beginning?
if you have it off, it will only look ‘ahead’ from where it started last, which explains pretty much what you’re experiencing
toggle the option and try your search again
Yes, when i keep wraparound button selected/active the search and replace works nice for me. Thanks.
This is what I want to see (below) in this format (more or less) that can be inserted into a script that will generate a SQL profile in Oracle . This exists in OtherXML in plans that are stored in AWR and would be useful if it could be easily exported from what shows the {hint} section when you do an Explain Plan in SQL Developer for a plan that might not be in AWR. In the scenario I described the plan was not in AWR and when I executed, it ran fast enough it didn’t get captured in the cursor cache or AWR in order to get it from there.
[BEGIN_OUTLINE_DATA]’,
[IGNORE_OPTIM_EMBEDDED_HINTS]’,
[OPTIMIZER_FEATURES_ENABLE(‘12.2.0.1′)]’,
[DB_VERSION(‘12.2.0.1′)]’,
[OPT_PARAM(‘_unnest_subquery’ ‘false’)]’,
[OPT_PARAM(‘_gby_hash_aggregation_enabled’ ‘false’)]’,
[OPT_PARAM(‘_fix_control’ ‘5099019:1′)]’,
[ALL_ROWS]’,
[OUTLINE_LEAF(@”SEL$3FF8579E”)]’,
[UNNEST(@”SEL$1″)]’,
[OUTLINE(@”UPD$1″)]’,
[OUTLINE(@”SEL$1″)]’,
[INDEX(@”SEL$3FF8579E” “I”@”SEL$1” (“PS_VDL_VCH_TAO11″.”PROCESS_INSTANCE” “PS_VDL_VCH_TAO11″.”BUSINESS_UNIT” “PS_VDL_VCH_TAO11″.”VOUCHER_ID”))]’,
[INDEX(@”SEL$3FF8579E” “PS_VOUCHER”@”UPD$1″)]’,
[LEADING(@”SEL$3FF8579E” “I”@”SEL$1” “PS_VOUCHER”@”UPD$1″(“PS_VOUCHER”.”VOUCHER_ID_RELATED” “PS_VOUCHER”.”BUSINESS_UNIT”))]’,
[USE_NL(@”SEL$3FF8579E” “PS_VOUCHER”@”UPD$1”)]’,
[END_OUTLINE_DATA]’);
Use FORMAT=’OUTLINE’ in your dbms_xplan call …
SQL_ID 28jqctcapt05v, child number 0 ------------------------------------- SELECT employees.first_name, employees.last_name, employees.salary, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id order by salary desc fetch first 10 rows only Plan hash value: 936479370 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | VIEW | | 10 | 820 | 7 (29)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK | | 106 | 4240 | 7 (29)| 00:00:01 | | 3 | MERGE JOIN | | 106 | 4240 | 6 (17)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 | | 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 | |* 6 | SORT JOIN | | 107 | 2568 | 4 (25)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2568 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$58A6D7F6") MERGE(@"SEL$1" >"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$3" "from$_subquery$_004"@"SEL$3") INDEX(@"SEL$58A6D7F6" "DEPARTMENTS"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID")) FULL(@"SEL$58A6D7F6" "EMPLOYEES"@"SEL$1") LEADING(@"SEL$58A6D7F6" "DEPARTMENTS"@"SEL$1" "EMPLOYEES"@"SEL$1") USE_MERGE(@"SEL$58A6D7F6" "EMPLOYEES"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("EMPLOYEES"."SALARY") DESC )<=10) 6 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")What should do when Tnsnames directory is empty?
You don’t need a TNSNames file to connect to Oracle. You can use a Basic type connection, same principle as using the EZConnect syntax in SQL*Plus.
Is there a way to export the results of an Explain Plan from SQL Developer 19 to some text format?
The use case I have is trying to tune a problematic SQL. If I do an Explain Plan in SQL Developer I can get the right plan and it shows the associated hints for the plan that is showing. I have a SQLPlus script where I can get a runtime plan / hints from a SQL that is running, but I can’t get the right plan from history. I want to be able to cut and paste the hints from the SQL Developer plan into the script, hoping to create a profile that will get the right run-time plan.
I have eye-balled the plan in SQL Developer and manually updated the hints in the script but that is incredibly excruciating ! I haven’t been able to figure out how to get the hints like this via SQLPlus or any other way.
Why not just use dbms_xplan?
Hello Jeff,
I’m using Sql Developer v19.2.1.247. I was trying to change the font (under Tools–>Preferences–>Fonts), but when I click on the drop down menu (Font Name), I do not see font I’m looking for (HE Terminal / TT0596M.TTF). I know it’s installed, and other apps (i.e. Putty) are able to use it.
Any ideas?
Thanks in advance!
-John
We ask the OS for a list of fonts, what we’re showing, is what we get. And by ‘we’, I mean Java and not SQL Developer.
I downloaded a Prince font the other day, and it worked.
Hi Jeff,
First I want to say how impressed I am by SQL Developer as a tool. I am new to Oracle, but have been using a major competitor’s product extensively for a couple of decades. I must say that whenever I go now to the other product I hugely miss many of features from SQL Developer, like the Describe popup Shift-F4 for example…
Now down to my question. I know that dragging fields from the Shift-F4 popup surrounds them with single quotes and that using CTRL-copy/paste writes the fields without quotes in a row-by-row list, but also without commas.
Neither of these are suitable for quickly building a Select statement with a lot of fields in it…
Is there a way to drag (or copy-paste) some fields from the Describe popup as a comma-separated list of unquoted names?
Thank you
Have ORDS 19.4 installed on Tomcat. I seem to be a missing a step in the configuration. Any idea what it is?
Debug Trace
[TE] GET /ords/xx_apex/sign-in?username=XX_APEX&r=_sdw%2F start: 2020-02-28T15:27:17.162Z duration: 137ms
Stack Trace
URLMappingNotFoundException [statusCode=404, reasons=[The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured]]
at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:135)
Forgot to mention that I’m just trying to check out sql developer web. I have Apex up and running fine, but sql-developer web is complaining. It’s probably related to me not understanding what this actually does:
begin
ords.enable_schema(
p_enabled => TRUE,
p_schema => ‘XX_APEX’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘XX_APEX’,
p_auto_rest_auth => FALSE
);
Did you enable SDW?
This URL is probably easier
/ords/sql-developer
What is XX_APEX
That’s not the actual APEX schema is it?
If it is, you cannot do that.
You need to REST enable a database user account, and then try to login as that user.
It was a Apex Schema… Created a separate database account. Got same result…
The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured
Debug Trace
[TE] GET /ords/darenjanes/sign-in?username=DARENJANES&r=_sdw%2F start: 2020-02-28T18:53:59.472Z duration: 60ms
ords.enable_schema(
p_enabled => TRUE,
p_schema => ‘DARENJANES’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘DARENJANES’,
p_auto_rest_auth => FALSE
);
change
p_url_mapping_pattern => ‘DARENJANES’,
to
p_url_mapping_pattern => ‘darenjanes’,
Beware of smartquotes if you copy/paste this.
That was it. I would never have found that…..
Much appreciated!
Hi Jeff,
I’ve been using SQL-D for some time now and my team relies on it on a daily basis. I’d like to know how it calculates the average response time in the CLIENTS section.
When I query “Response Time Per Txn” from SYS.V_$SYSMETRIC_SUMMARY, the value it records/reports is typically quite different, i.e. >20 ms. In fact, I don’t see any metrics from the SYSMETRIC_SUMMARY that line up neatly with this value.
I’ve searched online with no really good results.
Thanks for the great product! Any help is appreciated.
Erin
My quick search of docs found this nugget, but note that this is calculated by the database, not SQL Developer.
I get the error after it prompts for username using …/ORDS/sql-developer . Yes, I did enable.
true
true
Hi,
When calling sqlplus -s in shell script using heredocs to assign the output to a shell variable I get the value I expect. When using sql -s I get an extra leading character.
Is that expected behaviour? We have a lot of scripts that use this and I was hoping for an easy move from sqlplus to sqlcl but this incompatibility would mean that all the logic that uses the returned values would fail.
Sqlcl v19.4 on Oracle Linux in OCI Sydney (IAAS).
Hope that’s the info you need.
Cheers
Nigel
Step One: googles ‘heredocs’
I’ll take a look.
But can you give me an example? Even if just a select from dual?
Sorry Jeff – I typed up the question on my phone on the ferry so couldn’t give a working example.
I have done it twice to show it is consistently adding a space. I have reused the same variable to show it is not related to the variable in any way.
Results from the echo statements are
1 response=X
2 response= X
3 response=X
4 response= X
This would be the content of such a script. It is ever so slightly different to mine as proxyuser and DBAp006 are defined in other scripts but hopefully you’ll be able to reproduce it.
#!sh
PROXYUSER=”/”
DBAp006=”db of your choice”
response=$(sqlplus -s “$PROXYUSER”@”$DBAp006″<<-endsql
whenever sqlerror exit sql.sqlcode
set pages 0 verify off feedback off head off
select dummy from dual;
endsql
)
echo "1 response="$response""
response=$(sql -s "$PROXYUSER"@"$DBAp006"<<-endsql
whenever sqlerror exit sql.sqlcode
set pages 0 verify off feedback off head off
select dummy from dual;
endsql
)
echo "2 response="$response""
response=$(sqlplus -s "$PROXYUSER"@"$DBAp006"<<-endsql
whenever sqlerror exit sql.sqlcode
set pages 0 verify off feedback off head off
select dummy from dual;
endsql
)
echo "3 response="$response""
response=$(sql -s "$PROXYUSER"@"$DBAp006"<<-endsql
whenever sqlerror exit sql.sqlcode
set pages 0 verify off feedback off head off
select dummy from dual;
endsql
)
echo "4 response="$response""
Hi Jeff,
Are you waiting for anymore info from me so you can reproduce, fix or decline this difference between SQLcl and SQPPlus?
Cheers
Nigel
Just lack of time to give it a go…instead of waiting on me you can go file an Service Request with My Oracle Support right away.
Thank you for all your work building out a great product.
We are working to rollout TCPS connections and creating a standard configuration for all our users of SQL Developer (19.4 64-bit). We have been able to make connections by having a separate client install, but we have not found documentation if this can be accomplished without including a separate client install. Let me know if I missed a document that lists the setup steps for setting up TCPS connections in SQL Developer.
Thank You,
Brian