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, my question is related to ORDS.
Is there any way to instruct ORDS to make JSON Reference as relative URIs?
Example:
From: “$ref”: “http://localhost/ords/agreements/
To: “$ref”: “/ords/agreements/
I’ve read JSON reference draft as APEX docs mention that JSON references are based on it, and this draft contemplates relative URIs in section: 4. Resolution.
References:
https:// http://www.thatjeffsmith.com/archive/2017/05/ords-restful-services-and-uri/
https:// http://www.oracle.com/tools/technologies/rest-data-services.html#about_uris
https:// tools.ietf.org/html/draft-pbryan-zyp-json-ref-03#section-4
We have a large database that was developed using Oracle Designer and are looking to start using Data Modeler for future development work. In Designer you could define an entity short name, and when generating out to a table there was an option to use this as a column prefix as well as for keys – eg to generate a column called PROD_DESCRIPTION instead of just DESCRIPTION. We have used this approach to give us uniquely named columns across all our tables . I can’t find a way of doing this in Data Modeler, unless we actually assign the prefix at attribute level (but can’t do that for domain based attributes). Is there a way of assigning a prefix to a column, preferably when engineering from logical to relational model, or otherwise at the point of generating DDL ?
Yeah you can define default naming patterns in the design preferences. I’m on vacation, but someone should be able to help you on the forums.
I know about Templates in Design Preferences which gives you control over the naming of various types of keys / constraints/indexes. But I can’t find anything that allows you to define rules for actual attributes when engineering to columns.
We have just started to use sql developer, having previously used TOAD. We connected to a database and then did file open to bring in an sql file. We did a shift-F4 to see the describe on a table but it returned saying connection required. So several questions come to mind.
1) How do we know if the tab we are viewing is “connected” to a database?
2) after opening a file, how do we connect to the database without having to run it.
3) Are there any settings in preferences that allow the session to stay connected?
Regards
Jeff
After you open the file, upper right hand corner, use the connection selector to choose the connection you want to execute the file against.
Hi, Jeff,
I can’t create NEW connections; previously existing connections work fine.
AppData folder and subfolders for my user have full admin perms.
I get this error: “Could not load connection type:Oracle
null.” Recently upgraded to Oracle SQL Developer 19.1.0.094
Same issue with 18.3 (which worked fine for months until recently).
Any help is much appreciated.
See if it will let you export your connections.
If so, delete the connections.json file and then do an import.
If that doesn’t work, rename your system19.1 directory, start the tool and say yes to import your settings from 18.3 again.
Hi Jeff,
is there a way to tell SQL Developer to also treat // as comments? I am copying SQL back and forth between SQL Developer and an old ETL tool hat only supports // to mark inline comments, not –. Doing a search&replace twice every time is tedious, unfortunatly.
Thanks & regards
Chris
Sorry, no.
And that’s a driver/DB thing, not us.
And if we did, you’d share your code with someone, and then it’s break bc they were using something other than SQLDev, etc
How can I tell if my SQL Developer is 32 bit or 64 bit?
It’s not SQL Developer, it’s Java. Whatever JDK you’re using determines if it’s 32 or 64 bit.
Help! Why is SQL DEVELOPER just showing me the file name where the query exists instead of the actual SQL run? I am changing the SQL in that file and running the SQL as needed but it is not in SQL History.
What exactly are you executing in a SQL Worksheet? That is what goes into the History.
DROPS and CREATE TABLE
In a file, that you’re executing a la
@script.sql — F5 ?
in a file but I am executing each query individually using CTRL-Enter
That would be a bug then, version?
Under SQL
file:C:\folder\folder\blah.sql
Under Type it says Script regardless if I run a single sql or multiple. If there is a place I can send a screenshot let me know.
Version 19.1.0.094
Build 094.2042
Just as an FYI…If I copy the full script out and into the Worksheet created when Connecting…All the SQL shows up in SQL History even if I run it as a script.
I have had and love sqlcl 4.2 for about 2 years. Now on the same OEL 11gR2 database servers. I’m trying to get sqlcl 19.1 running, but keep getting this error “Could not initialize class oracle.jdbc.OracleDriver”. When run from my Ubuntu 18.04 client, 19.1 works fine, I *think* because no ORACLE_HOME is set. I found a couple of old posts on this on TechNet, but those suggestions don’t work for me.
In the 19.1 “sql” shell script, this function definition forces JAVA_HOME to be the one in the ORACLE_HOME tree, but that’s the 1.7 JDK and not the 1.8.0 version in /opt/java I’ve been using for sqlcl 4.2. That forcing a JAVA_HOME isn’t in the 4.2 sql shell script.
function checkADE {
#
# Resolve java path for development builds
#
if [ “m$ORACLE_HOME” != “m” ]; then
if [ -d “$ORACLE_HOME/jdk/jre” ]; then
JAVA_HOME=$ORACLE_HOME/jdk/jre
PATH=$JAVA_HOME/bin:$PATH
fi
export SQLPLUS_CLASSIC=true
fi
}
So, what do I do?
…
Further testing results on the DB server, trying to connect to a local DB.
I realized that I may need to install the exact same JDK that I installed on my Ubuntu machine for SQLDev and sqlcl 19.1, so I installed it on the DB server. No luck, same error.
If I run the sqlcl/bin/ sql script, as provided, I get this:
Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file
Which is to be expected, since the ORACLE_HOME JDK is 1.7.
If I force JAVA_HOME to the correct one for sqlcl by doing this in the checkADE function in the sql script
# JAVA_HOME=$ORACLE_HOME/jdk/jre
JAVA_HOME=/usr/java/jdk1.8.0_211-amd64
I still get this:
ERROR: Could not initialize class oracle.jdbc.OracleDriver
Now, if I force JAVA_HOME to be the correct one, and unset ORACLE_HOME in the script, I can connect to a remote DB. Also, I can connect to a local DB if I pretend it’s remote by using “username@localDB”. It works, but will require us to modify all our scripts that use sqlcl for the special reasons it’s superior to sqlplus.
So, being a DBA and not a Java-head or super-scripter, I don’t know what to do next to allow “normal” connection to a local DB. Advice welcome.
Also, in the “run” function in the script I tack on this to run glogin to set the command prompt automatically.
@”$ORACLE_BASE/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql”
I was using $ORACLE_HOME/sqlplus/admin, but can’t now because I have to unset ORACLE_HOME for sqlcl to even work. I’ve looked at the sqlcl docs and can’t see any sqlcl equivalent to glogin.sql. Is there?
Thanks.
Jack
Jeff,
[oracle@john admin]$ sqlcl -version
/usr/java/jdk1.8.0_211-amd64
SQLcl: Release 19.1.0.0 Production
[oracle@john admin]$
So, I’ll work around it ’til it’s fixed.
Thanks.
Jack
Hallo Jeff,
I am using Oracle based RESTfull service within the Oracle Apex 18.2 environment.
I am using Oracle 18.2 and Oracle ORDS 18.1 and Oracle APex 18.2
I have a table with 3 fields.
When using the apex_web_service.make_rest_request from the database a record is inserted without any problem.
When i try this within Oracel Apex 18.2 using the ORDS Websources like below :
apex_exec.execute_web_source(
p_module_static_id => ‘AutoREST_service_ins_case’,
p_operation => ‘POST’,
p_parameters => l_parameters
);
Then i am getting HTTO 400.
I create the modules and web sources from within Oracle Apex 18.2.
Have you had done a post using apex_exec.execute_web_source?
Regards,
Anibal
Jeff,
Is there any reason we couldn’t use the ORDS_PUBLIC_USER proxy account to authenticate via LDAP?
If, in the db, I create an account for myself, such as:
CREATE USER joe IDENTIFIED GLOBALLY AS ‘CN=Couture\, Joe,OU=co-users,OU=CO,OU=ROC,DC=som,DC=state,DC=ms,DC=us’;
ALTER USER joe GRANT CONNECT THROUGH ords_public_user AUTHENTICATED USING DISTINGUISHED NAME;
I have yet to discover whether it would be possible to achieve this in the Standalone ORDS environment which ships with the SQL Developer IDE. I issued the db commands and tried put in my AD credentials in the Basic Authentication dialog rendered by the browser, unsuccessful.
Hi Jeff,
I have created a web source which will need to post (insert) a record in a table.
Executing the url in the Resting tool in Firefox a record is being inserted correctly.
When trying this from within Apex 18.1 using ORDS ( Version 18.1.1.95.1251 ) web source by submitting the page which activate a process i am getting the error:
ORA-20999: Web Source returned an HTTP error: HTTP 500: Internal Server Error.
Here i am using HTTP.
The Apex instance ‘REQUIRE_HTTPS’ is set to NO.
Any help or hint will be much appreciated.
Regards,
Anibal
Check the ORDS server logs when the 500 occurs, you’ll see the insert and the underlying ORA message.
Also, compare how the POST is constructed in Firefox vs APEX
I have a query that returns CLOBs from dbms_metadata.get_ddl. In SQL Developer, I save this query output as a CSV file. It shows up fine in the output window, but when I save it, most of the time the CLOB values are saved as oracle.sql.CLOB@xxxxxx. Lately, I’ve been getting this result more than the actual CLOB values.
I’ve tried spooling and using /*csv*/, but that option tends to break the code in strange places and not keep it all in the same column of output when importing into Excel.
Any ideas? I’m using version 18.4.0.376.
Why would you want hundred line DDL exported as CSV? As soon as it hits a comma or line break, it’ll break everything.
I use it for doing schema compares across platforms and environments. I export as CSV, then have a PowerShell script that breaks it into files and folders by object type and object. Then I can use a tool to compare. The builtin SQL Developer export tool leaves out some things that I want to compare (jobs, for example), so I wrote my own. It works perfectly when the output actually exports the CLOBs instead of the oracle.sql.CLOB@xxxxx.
Share your query, and I’ll take a look.
— Specify schema to extract in object_owner function a few lines down
WITH
— Converts DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type
— Java object names may need to be converted with DBMS_JAVA.LONGNAME – that code is not included
— ========================================================================================================================
— Owner to search database for, NOT case sensitive
FUNCTION object_owner RETURN VARCHAR2 IS
BEGIN
RETURN UPPER (‘AFDSUSR’);
END object_owner;
— Trims leading and trailing spaces, and leading line feed characters
FUNCTION trim_string (p_clob IN CLOB) RETURN CLOB IS
BEGIN
RETURN TRIM (LEADING CHR (10) FROM TRIM (TRIM (LEADING CHR (10) FROM p_clob)));
END trim_string;
— ========================================================================================================================
— System generated types have to be handled differently
system_gen_types AS (
SELECT ‘TYPE’ object_type, name object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, text).EXTRACT (‘//text()’)
ORDER BY line).GetClobVal(), 1) text
FROM dba_source
WHERE owner = object_owner
AND type = ‘TYPE’
AND name LIKE ‘SYS_PLSQL_%’
GROUP BY ‘TYPE’, name),
— Used to aggregate user granted roles in alphabetic order
user_roles AS (
SELECT ‘USER’ object_type, ‘ROLES’ object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, ‘GRANT “‘ || granted_role || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
ORDER BY granted_role).GetClobVal(), 1) text
FROM dba_role_privs
WHERE grantee = object_owner),
— Used to aggregate system grants in alphabetic order
system_grants AS (
SELECT ‘USER’ object_type, ‘SYSTEM GRANTS’ object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, ‘GRANT “‘ || privilege || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
ORDER BY privilege).GetClobVal(), 1) text
FROM dba_sys_privs
WHERE grantee = object_owner),
— Used to aggregate object grants in alphabetic order
object_grants AS (
SELECT ‘USER’ object_type, ‘OBJECT GRANTS’ object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, ‘GRANT ‘ || privilege || ‘ ON “‘ || owner || ‘”.”‘ || table_name || ‘” TO “‘ || grantee || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
ORDER BY owner, table_name, privilege).GetClobVal(), 1) text
FROM dba_tab_privs
WHERE grantee = object_owner
OR owner = object_owner
OR grantor = object_owner),
— Referential integrity constraints
ref_constraints AS (
SELECT ‘REF CONSTRAINT’ object_type, table_name object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, trim_string (dbms_metadata.get_ddl (‘REF_CONSTRAINT’, constraint_name, owner)) || ‘;’ || CHR (10)).EXTRACT (‘//text()’)
ORDER BY constraint_name).GetClobVal(), 1) text
FROM dba_constraints
WHERE owner = object_owner
AND constraint_type in (‘R’) — Referential integrity constraints
GROUP BY ‘REF CONSTRAINT’, table_name),
— Constraints
constraints AS (
SELECT ‘CONSTRAINT’ object_type, table_name object_name,
DBMS_XMLGEN.CONVERT
(XMLAGG
(XMLELEMENT (e, trim_string (dbms_metadata.get_ddl (‘CONSTRAINT’, constraint_name, owner)) || ‘”;’ || CHR (10)).EXTRACT (‘//text()’)
ORDER BY constraint_name).GetClobVal(), 1) text
FROM dba_constraints
WHERE owner = object_owner
AND constraint_type in (‘U’, ‘P’, ‘C’) — Unique, Primary, Check Constraints
GROUP BY ‘CONSTRAINT’, table_name),
— ========================================================================================================================
— General database objects
objects AS (
SELECT dbao.owner,
CASE WHEN dbao.object_name LIKE ‘MLOG$_%’ AND dbao.object_type = ‘TABLE’ THEN
(SELECT master
FROM dba_mview_logs
WHERE log_owner = dbao.owner
AND log_table = dbao.object_name)
ELSE
dbao.object_name
END object_name,
CASE WHEN dbao.object_name LIKE ‘MLOG$_%’ AND dbao.object_type = ‘TABLE’ THEN
‘MATERIALIZED VIEW LOG’
ELSE
dbao.object_type
END object_type,
dbao.object_name metadata_object_name,
CASE
WHEN dbao.object_type IN (‘JOB’, ‘RULE SET’, ‘RULE’, ‘EVALUATION CONTEXT’, ‘CREDENTIAL’,
‘CHAIN’, ‘PROGRAM’) THEN ‘PROCOBJ’
WHEN dbao.object_type = ‘DATABASE LINK’ THEN ‘DB_LINK’
WHEN dbao.object_type = ‘PACKAGE’ THEN ‘PACKAGE_SPEC’
WHEN dbao.object_type = ‘PACKAGE BODY’ THEN ‘PACKAGE_BODY’
WHEN dbao.object_type = ‘TYPE’ THEN ‘TYPE_SPEC’
WHEN dbao.object_type = ‘TYPE BODY’ THEN ‘TYPE_BODY’
WHEN dbao.object_type = ‘MATERIALIZED VIEW’ THEN ‘MATERIALIZED_VIEW’
WHEN dbao.object_type = ‘MATERIALIZED VIEW LOG’ OR (object_type = ‘TABLE’ AND object_name LIKE ‘MLOG$_%’)
THEN ‘MATERIALIZED_VIEW_LOG’
WHEN dbao.object_type = ‘QUEUE’ THEN ‘AQ_QUEUE’
WHEN dbao.object_type = ‘JAVA CLASS’ THEN ‘JAVA_CLASS’
WHEN dbao.object_type = ‘JAVA TYPE’ THEN ‘JAVA_TYPE’
WHEN dbao.object_type = ‘JAVA SOURCE’ THEN ‘JAVA_SOURCE’
WHEN dbao.object_type = ‘JAVA RESOURCE’ THEN ‘JAVA_RESOURCE’
WHEN dbao.object_type = ‘XML SCHEMA’ THEN ‘XML_SCHEMA’
ELSE dbao.object_type
END metadata_object_type
FROM dba_objects dbao
WHERE dbao.owner = object_owner
— These objects are included with other object types
AND object_type NOT IN (‘INDEX PARTITION’, ‘INDEX SUBPARTITION’, ‘LOB’,
‘LOB PARTITION’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
— Ignore system-generated types that support collection processing
AND NOT (object_type = ‘TYPE’ AND object_name LIKE ‘SYS_PLSQL_%’)
— Ignore objects associated with materialized views
AND NOT (object_type = ‘INDEX’ AND object_name LIKE ‘I_MLOG$_%’)
AND NOT (object_type = ‘TABLE’ AND object_name LIKE ‘RUPD$_%’)
— Exclude nested tables, their DDL is part of their parent table
AND (owner, object_name) NOT IN (SELECT owner, table_name FROM dba_nested_tables)
— Exclude overflow segments, their DDL is part of their parent table
AND (owner, object_name) NOT IN (SELECT owner, table_name FROM dba_tables WHERE iot_type = ‘IOT_OVERFLOW’)),
— ========================================================================================================================
— Combining all objects together for final SELECT
schema_code AS (
— General objects
SELECT object_type, object_name,
trim_string (dbms_metadata.get_ddl (metadata_object_type, metadata_object_name, owner)) object_ddl
FROM objects
UNION ALL
— System generated types
SELECT object_type, object_name, text
FROM system_gen_types
UNION ALL
— Referential integrity constraints
SELECT object_type, object_name, text
FROM ref_constraints
UNION ALL
— Other constraints
SELECT object_type, object_name, text
FROM constraints
UNION ALL
— User
SELECT ‘USER’, ‘CREATION’,
trim_string (dbms_metadata.get_ddl (‘USER’, username)) object_ddl
FROM dba_users
WHERE username = object_owner
UNION ALL
— Tablespace quota
SELECT ‘USER’, ‘TABLESPACE QUOTA’,
trim_string (dbms_metadata.get_granted_ddl (‘TABLESPACE_QUOTA’, username)) object_ddl
FROM dba_ts_quotas
WHERE username = object_owner
AND ROWNUM = 1
UNION ALL
— Roles
SELECT object_type, object_name, text
FROM user_roles
UNION ALL
— System grants
SELECT object_type, object_name, text
FROM system_grants
UNION ALL
— Object grants
SELECT object_type, object_name, text
FROM object_grants
UNION ALL
— Default role
SELECT ‘USER’, ‘DEFAULT ROLE’,
trim_string (dbms_metadata.get_granted_ddl (‘DEFAULT_ROLE’, grantee)) object_ddl
FROM dba_role_privs
WHERE grantee = object_owner
AND default_role = ‘YES’
AND ROWNUM = 1
UNION ALL
— Non-default profile creation script
SELECT ‘USER’, ‘NON-DEFAULT PROFILE’,
trim_string (dbms_metadata.get_ddl (‘PROFILE’, profile)) object_ddl
FROM dba_users
WHERE username = object_owner
AND profile ‘DEFAULT’)
— ==========================================================================================================================
SELECT object_type, object_name, object_ddl
FROM schema_code
ORDER BY object_type, object_name;
Just wanted to check back and see if you’d had a chance to look at the query you requested for the issue I was having. Thanks!
When writing multiple lines of code it is often difficult to determine if row 60 lines up with row 20 or row 10. Is there a visual hint, such as a line, that will appear when you put your cursor at the first character of row 60 so that you can scroll up to see which rows begin in the same column? It is especially difficult when reviewing someone else’s code who indents only two spaces for each section. After a number of rows they just blend into each other.
Have you tried the formatter?
Hi Jeff,
I’m a former Oracle Designer user and I decided to use SQL Dev Data Modeler (DM) in order to keep applications’ ER model in maintenance and new application. I was wondering how DM manages object sharing among designs. Let’s suppose that we have two designs (D1 and D2) and I’d like to share the Entity PERSON, belonging to Design D1, to Design D2. How can I do that with DM? If it is possible, will DM reflect the changes made on Entity PERSON in Design D2?
Thank you in advance for your attention and support.
Kind regards,
Anderson Bestteti.
Try this Linked Objects
Hi Jeff,
I’ve been using the SQL Developer Real Time SQL Monitoring for a while now, but for some odd reason, on the same databases as I was working with previously, when I am in the monitoring window, it is not refreshing. Even if I manually click the Refresh button, nothing happens.
As I look at the screen now, it is 16:06pm. Yet the SQL at the top, is 13:54pm. Auto refresh is set to 10s. The red pin is vertical, which I believe means not frozen. Anyway, even if I click it, and wait, still nothing changes!
Even if I run something in the SQL worksheet, nothing changes in Real Time SQL Monitor.
When I click the refresh button, it gets framed in blue but nothing changes.
Any ideas??? Its driving me nuts.
Hi Jeff, I work in a lot of places and companies, and I need to keep track of the pl/sql code (versioning control and further comparisons) developed in each one of this places, I’m having some difficult to “save” this codes, especially the pl objects, I also tried to use the git integration, but I see that it is more efficient to deal with scripts, any sugesttion? I’am a great fan of your work, thanks in advance!
we’re building a liquibase solution where we handle upgrades for you, should make versioning your schemas MUCH easier – will be in SQLcl v19.2
Hi Jeff
We have two servers with ORDS. Both have exact the same names and definitions of schema, modules, templates and handlers.
If I try to reach the URL defined as “GET”-method on the first server, I get JSON and everything is OK. But if I try to reach similar URL (only name of host differs), I get 404.
in APEX-interface I see all the modules and templates, and all results I get selecting from ords_metadata are the same. Just like at the first server. But at the first server It works, at the second – does not. I also tried to create new module, template and handler at the second server in the APEX-interface. I created them but request still gives 404.
What we also tried: re-start ORDS, validate ords.war. Nothing helped.
I also asked my ora-admin switch on the screen-debug. It says “dispatcher not found”. What does it mean? What should we do?
get the standard output from ords running on the 2nd server, will show same you would see when printing debug to screen
Excuse me, Jeff, screen debug works really well, but I can’t understand what does it mean “dispatcher not found” in this log. I can also see that it maps to the correct database by the URI-part /myschema/ so it is hard for me to understand what is wrong? Even by ssh from this server I cant perform GET-method to itself. Maybe the reason is lack of some grants? But as I can see in PROXY_USERS and DBA_SYS_PRIVS – everything is the same as on server, on which ORDS works
I also switched on the debug-screen on the server, where ORDS works well.
I got such message:
mapped request using: /myschema/* to: -MYSCHEMA|apex|rt|,
On the second server I have this:
mapped request using: /myschema/* to: -APEX_PUBLIC_USER|apex|rt|,
WHY? I have already done ENABLE_SCHEMA, what I did wrong?
Hello Jeff – We are using standalone ORDS 19.1 and wondering if there is any way to display progress bar in browser while ORDS is running plsql code or dbms_scheduler jobs in background? Few of our APIs runs for 2-3 minutes and looking at empty white browser for 3 minutes is quite depressing!! and wondering if there is any way to get a progress bar to track the %age completion?
Thanks,
Vivek
For long running requests better that your response be a 200 with a link to follow for status updates
Hello Jeff,
Is it possible to view the contents of the collection in the debug window in PL/SQL Developer?
PL/SQL Developer, version 10.0.5.1710
https://stackoverflow.com/questions/56743758/
It’s possible in Oracle SQL Developer, yes.
Hi Jeff
I’m using SQL Developer Data Model 4.1.5 (907) and I’m facing some issues when generating the Relationald Model and Physical DDL . I’ve tried several sites and documentation but I could’n find yet an answer. I hpoe you can help me .
The issues are :
1) On Logical model I have two relationship for the same table, in a master-detail relationship. When generating te Relational model it is created two fields in the child table as the FK for the master table., as expected. However the detail table fields names are mastercode and mastercode1 , for instance. Then I change the names to and . The next time I generate the Relational model again those tw filds are renamed to the previous name mastercode and mastercode1.
==> How to avoid the relational model generation changes these names ?
2) I use Sequence and table trigger on each table to generante my number primary keys values. In Relational Model on each table, in PK field properties “automatic increment” , I have to set the trigger style as ALWAYS because the default is WHEN NULL.
==> Is there a way to change the default trigger style to ALWAYS , instead of WHEN NULL ?
==> Is it possible to set the automatic increment property for the PK fields in Logical Model ? If so, how to do that ?
Thank you so much for your instructions and directions !
In my company, the SQL developer is widely used. Due to the standardization of our work, we want to use the same code templates for each developer. Is there a way to export and import the templates or outsource them to a shared file?
Hi,
It’s possible to exclude virtual hidden in Database diff tools from SQLdeveloper 19.1.0.094 ?
I think that Virtual hidden it’s a mechanism internal oracle when i use stats.
I need to compare two schema in db but the result it’s alway different because virtual hidden appears.
the database packages we use for the ddl gen and DIFFs don’t offer anything to help with virtual columns, so you’ll need to remember to uncheck them in your reports to the alter scripts aren’t generated for those items
Thank for you feedback.
In my case we have almost 100 tables, because virtual hidden and the others case because differents colums, types, etc.
You advise me in the report to scroll each diff table and uncheck to be made by hand only vritual hidden ?
You think that case can be add in next released, select option for ignore Virtual Hidden like storage, high values, etc ?
Best regards
I would look for a different way to do your compares, that, and you need to raise an ER to the database team.
Hi Jeff
Is there a way in the formatter to force a line break after a line comment? i see an option to add a line break before a comment, but couldn’t figure out how add a line break after a comment
WITH
/*This is a line comment which describes what this next portion of code does.*/ example AS(
SELECT
MIN(post_date)AS min_post_date
FROM
trend_table
)
SELECT
*
FROM
example;
Sorry Michel. I didn’t mean to post under your question
Question: Is there a way to force a line break before semicolon (or statement delimiter) in SQL developer formatter?
Background: I usually write my query without too much formatting and then press Ctrl+F7 to format it in a easy-to-read way, however I noticed that the auto-formatting usually delete the my line break before semicolon , so the semicolon will be at the end of last line in my statement after formatting. I found this a little inconvenient because when I comment out the last line, the semicolon would be commented out all together, result in execution error when I hit Ctrl+Enter to run the statement.
I am using Version 18.1.0.095, looked through all the option in ‘Code Editor > Format’, but didn’t found a way to force line break before semicolon. Can you please help me with this? Much appreciated.
Hi Jeff,
Just a reminder to see if you were able to check the question in the original post?
Appreciate any help!
No I do not see a way to make this happen with the current formatter GUI for options, so you would need to remember to add the ‘;’ or select the query you want executed before hitting ctrl-enter…
Hi Jeff,
Is there any new features for ORDS in SQL Developer 19.1 compare to SQL Developer version 18.4?
And general are there new features in SQL Developer 19.1 that are not in SQLDeveloper version 18.4?
Thanks in advance for your comments.
Regards,
Anibal
there are new features in sql developer 19.1
there are new features in ords 19.1
I don’t know of any new features in SQL Developer for ORDS in 19.1 though other than SQL Developer 19.1 now includes ORDS 19.1 vs ORDS 18.4
Hi Jeff,
For one particular use case I am considering using the autorest functionality.
When doing an insert via POST I could not find a way to utilize an identity column and if I leave any columns out of the POST to try let them default or auto-populate in the case of the identity column I get an error as per below.
Works fine when I define all columns values in the payload, wondering if this is a limitation of autorest
Header:Error-Reason Cannot contain CRLF Charcters
How do you have your IDENTITY column defined, exactly?
IDENTITY Column is defined as below
myid NUMBER generated by default as identity (nocache),
This is with ords.3.0.9.348.07.16
If it were BY DEFAULT ON NULL then you could send the body with
{“IDENTITY_COL”:null,…}
And it will work.
If that’s not possible, you’ll need to write a custom POST endpoint to do the INSERT for you, using your own SQL.
Overlooked that option thanks for the advice that will work.