Ask A Question

Shoaib 7 years ago Reply

Morning Jeff,

I have two questions:

1. How can I set something in setting or preference in SQL Developer so I don’t have to put escape or set define off for ‘&’ symbol? I mean if I don’t to put in my every script or statement.

2. How can I extract DDL for a user or role with all the privileges, like if I click on a user then click on SQL then it shows only user creation, but not the roles and privileges are granted, same for any Role. I mean in Toad it shows by default.

Thanks a lot for your time in advance.

thatjeffsmith Post Author 7 years ago

1. This
2. This
Shoaib 7 years ago

Thanks Jeff for the answers.

I got the solution for Q#2, but Q#1 still not resolved, I mean it is still asking for value (for &). How can avoid and escape globally (from SQL Developer menu).

Thanks again

Tres 7 years ago Reply

Months_Between: I need to replicate this function in R. do you know where the source code is contained? The db is 11g; I’m really hoping I can find the implementation somewhere locally

thatjeffsmith Post Author 7 years ago

I can’t help you with that. If the function is wrapped, then we don’t provide the source.

Vadi 7 years ago Reply

Hi Jeff

Sub: Include date and time in the export dump file name.

Thanks for the great site.

I want to know how to put date and time in the export dump file name when I am exporting a dump file using the data pump wizard in Oracle SQL Developer.

Thanks in advance
Vadi

thatjeffsmith Post Author 7 years ago

Check the box

Run the job, here’s my file that was produced.
Vadi 7 years ago

Hi Jeff

Thanks a lot.

How did I miss something so obvious? 🙂

Regards
Vadi
thatjeffsmith Post Author 7 years ago

No worries, the world moves fast and it’s easy to miss things 🙂

Basha A 7 years ago Reply

Hi Jeff,
I recently downloaded sqldeveloper-17.3.0.271.2323 and when started using it I came across two issues so far.

When generating DDL esp. for objects like Materialized Views I get a message like below in the beginning and the code it generated after that is incomplete.

— Unable to render MATERIALIZED VIEW DDL for object APPS.AMS_ACT_METRICS_MONTHLY_MV with DBMS_METADATA attempting internal generator.

Secondly when I tried to run AWR reports and when I have multiple Database ID’s and tried to pick the current ID from the LOV and when tried to pick the snapshot Start id it throws errors like below.
The error pops out immediately when i clicked the ‘Browse’ Lov button.

!Error Encountered!
An error was encountered performing the requested operation:
Missing IN or OUT parameter at index:: 3
Vendor Code 17041

I tried with the latest bundle sqldeveloper-17.3.1.279.0537 and still having these two issues stated above.

I was not having those issues in the previous version 17.2
Not sure whether this has to do with any preferences setting in the new version or there is a bug in this release.
Appreciate if you could review and update with your findings.

Also I happened to delete the old version from my computer without keeping a backup copy.
So is there a way I can download 17.2 version until these issues are addressed?
I looked around and could not find any links to download archives.

Thanks

thatjeffsmith Post Author 7 years ago

Just scroll down to ‘previous versions’ – here’s the 17.2 link for you

Once you get it back, can you confirm 17.2 is working for both scenarios vs 17.3? Also, are you connected to a RAC?
Basha A 7 years ago

Thanks for your quick update Jeff.
To clarify, I was working with non-RAC databases and after I reverted to 17.2 the awr reports and DDL generation for Mat.Views are working fine.
I know these worked when I had 17.2 version earlier.
Do you know of when the latest version will get these issues addressed?
Thanks,
thatjeffsmith Post Author 7 years ago

Need to figure out the problem first. What version of the database are you connected to?
thatjeffsmith Post Author 7 years ago

i’m on a non-rac 12.2 db and it’s working fine for me. what version of oracle are you connected to?
Basha A 7 years ago

Hi Jeff,
I was connected to 11.2.0.4 when experiencing issues.
The issue happens in 10g (10204),11g R2 (11203,11204) and 12c (12.1.0.2)
I do not have a 12cR2 database yet.

Thanks
Basha A 7 years ago

Hello Jeff,
Do you know when this issue will be fixed in the newer versions? I have tried the latest version 17.4 and still facing the issue trying to generate DDL for Materialized Views.
Appreciate your feedback.

Thanks

thatjeffsmith Post Author 7 years ago

There’s no way to fix what we do not know what is broken.

This is one of the 30 queries we run when you ask for the DDL of an MV

 SELECT
    dbms_metadata.get_ddl(
        'MATERIALIZED_VIEW',
        :name,
        :owner
    )
FROM
    dual
UNION ALL
SELECT
    dbms_metadata.get_dependent_ddl(
        'INDEX',
        TABLE_NAME,
        table_owner,
        :version
    )
FROM
    (
        SELECT
            TABLE_NAME,
            table_owner
        FROM
            dba_indexes
        WHERE
            table_owner =:owner
            AND   TABLE_NAME =:name
            AND   index_name NOT IN (
                SELECT
                    constraint_name
                FROM
                    sys.dba_constraints
                WHERE
                    TABLE_NAME = TABLE_NAME
                    AND   constraint_type = 'P'
            )
            AND   ROWNUM = 1
    )
UNION ALL
SELECT
    dbms_metadata.get_dependent_ddl(
        'COMMENT',
        :name,
        :owner,
        :version
    )
FROM
    dual

Ladon Ghalili 7 years ago Reply

Hi Jeff,

I have started using sql Developer (Version 17.2.0.188) a couple of weeks ago, and everything is working fine except that the PDF report creation. It seems to create the PSF report, but I can’t find it anywhere.

Also, is there any tool to create a user interface menu? (either within or third party?)

Sorry if my questions are obvious, but I have been away from oracle for quite a few years and a lot has happened!

Thanks,
Ladon

Albert 7 years ago Reply

I am trying a simple SELECT of an SDO_GEOMETRY column, and that fails:

$ sql scott/tiger
SQLcl: Release 12.2.0.1.0 RC on Fri Oct 20 17:50:47 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> desc us_cities
Name Null? Type
———- ——– ——————
ID NOT NULL NUMBER
CITY VARCHAR2(42 CHAR)
STATE_ABRV VARCHAR2(2 CHAR)
POP90 NUMBER
RANK90 NUMBER
LOCATION MDSYS.SDO_GEOMETRY

SQL> select * from us_cities;
Oct 20, 2017 5:51:06 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:7797)
java.lang.NullPointerException
at oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:7797)
at oracle.dbtools.db.SQLPLUSCmdFormatter.processTypeAttributesForStruct(SQLPLUSCmdFormatter.java:7594)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1182)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:262)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:236)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:137)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:65)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:578)
at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:494)
at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:62)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:364)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:218)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:331)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:221)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:336)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:343)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:982)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:399)

thatjeffsmith Post Author 7 years ago

SQLcl: Release 12.2.0.1.0 RC
Can you go get version 17.3 from OTN, and try again?
Albert 7 years ago

I get the same error:

$ /opt/sqlcl/bin/sql scott/tiger

SQLcl: Release 17.3.0 Production on Sun Oct 22 11:14:53 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select * from us_cities;
Oct 22, 2017 11:15:10 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
java.lang.NullPointerException
at oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
at oracle.dbtools.db.SQLPLUSCmdFormatter.processTypeAttributesForStruct(SQLPLUSCmdFormatter.java:8221)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1264)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:270)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:244)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:142)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:67)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:856)
at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:767)
at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:80)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:390)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:216)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:337)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:390)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:401)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1225)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:477)
Albert 7 years ago

A simpler test:

SQL> create table t1 (g sdo_geometry);
Table T1 created.
Elapsed: 00:00:00.537

SQL> insert into t1 values (sdo_geometry(‘POINT(0 0)’));
1 row inserted.
Elapsed: 00:00:06.619

SQL> commit;
Commit complete.
Elapsed: 00:00:00.005

SQL> select * from t1;

Oct 22, 2017 11:18:45 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: oracle.dbtools.db.SQLPLUSCmdFormatter.addQuotesToTextualAttributes(SQLPLUSCmdFormatter.java:8440)
java.lang.NullPointerException
…

J. Henkelman 7 years ago Reply

Is it possible to disable the ‘Other Users’ node in sql developer 4.1.1? I am working in an educational setting and users should not be able to see each other’s work.

thatjeffsmith Post Author 7 years ago

No, but you can create users that don’t have privs to see other schema objects, their details, and obviously their data.

If you rely on the UI to ‘hide’ stuff, you’re gonna be screwed when the students figure out how to just write a SQL statement to see whatever they want.
J. Henkelman 7 years ago

Thanks.

San 7 years ago Reply

HI Jeff is there a way in SQL developer if my ABC_1 table has one col name MEMO_TEXT , It shows Text information with RTF tags like this below

“{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Times New Roman;}}
\viewkind4\uc1\pard\f0\fs20 ‘ This is my text abacedefgh’

How can I change setting or something else so that my table ABC_1 – col MEMO_TEXT shows only
This is my text abacedefgh

thatjeffsmith Post Author 7 years ago

No, but there’s a way in SQL.

Create a view that queries your table and uses something like regex_exp() to strip out the unwanted text.

janp 7 years ago Reply

Hi Jeff,

I would like to report some problems I have with the latest SqlDeveloper 17.3

1) Put a multi-line string inside an editor panel (i.e. a string inside single quotes) then try to edit it.
For me it does not work. The string blinks or disappears either partially or entirely. I have to remove
the initial quote to be able to edit the dynamic sql strings enclosed into some pl/sql procedures.
2) weird lock ups.. It happens that, when the database is slow to respond, e.g. upon package compilation, the entire interface locks up until the compilation has finished. This is quite bad.
3) I normally suspend my PC to RAM. After reviving the system, then Sqldeveloper interface is normally screwed, i.e. the various components appear like they haven’t be refreshed. Sometimes it is possible to have the application back to normal by resizing it, sometimes it is not. It has to be restarted.
4) Upon moving my PC from a LAN to another one, if I forget some connections active and I try to execute a command using these connections then the corresponding editor freezes for a long while so that normally I have to kill and restart the app..

Thank you

thatjeffsmith Post Author 7 years ago

1 – fixed last night, go get version 17.3.1.
2 – the connection is busy…you can’t do anything on that connection until it’s available again.
3 – me too. Never have issues. I have Windows 7, 16GB of RAM, and am using Oracle Look and Feel in the preferences. I never ever have these problems on my Mac either.
4 – Yup, that’s gonna suck. The JDBC driver doesn’t like it when a connection goes away. The timeouts are what they are. Just try a reconnect first, or disconnect before you unplug your machine and move it.
janp 7 years ago

2 – ok, THAT connection is busy but this should not prevent me to click on other editor tabs, both associated to that connection and to others. What I mean is that the interface, while compiling a package, is completely locked up as if it is single threaded. I am working on a remote slow development server so this lock may last a few seconds.

5 – and one more thing, :-). the default NLS settings for the dates is DD-MON-RR. If I try to export “inserts” from the data rows obtained from a query that contain date columns with the value, e.g., 9999-12-31, I get something like

Insert into EXPORT_TABLE values (to_date(’31-DEC-99′,’DD-MON-RR’));

This insert however inserts 1999-12-31 in the table, not the original value.
Technically this is not a bug, but the “spirit” of the export command should be, IMHO, to restore the original value, regardless to what the NLS setting is.

Thank you very much
thatjeffsmith Post Author 7 years ago
the default NLS settings for the dates is DD-MON-RR
Dates are stored as dates. We see strings, but they’re formatted that way via NLS_DATE_FORMAT. If your setting is indeed DD-MON-RR, your date would be returned as ’31-DEC-99′.

Also, running this:

INSERT INTO EXPORT_TABLE VALUES (to_date(’31-DEC-99′,’DD-MON-RR’));

Will insert a date of December 31st, 1999 – the date format mask ensures that. That’s the same date as ‘1999-12-31’ – it’s just that the format mask is different. If you always want it to look the same, embed the date formats in your queries.
janp 7 years ago

Okay, maybe I was not clear, but my question was different. The problem is that when the table contains 9999-12-31, export uses NLS_DATE_FORMAT to create the insert statement and therefore loses information from the century, so 9999 is transformed in 1999.
thatjeffsmith Post Author 7 years ago

Set your date format to RRRR then.

So you’re using 9999 as some fake placeholder?
janp 7 years ago

Yep 9999-12-31 is like infinity for the application I am working on.. when I try to export I must care about the NLS else I risk to upload the wrong data.. It happened :-). I changed the NLS on my machine but there are many here, and in general, if the purpose of export is to export the data in a reliable way maybe it should be better to not rely on NLS
thatjeffsmith Post Author 7 years ago

I don’t want to argue with you, yes this could happen. Knowing your date formats and how your apps store data is important. That’s why I advise always including the date format explicitly in your code and queries.
janp 7 years ago

I cannot agree more and in fact what I normally do in my code is to do an explicit formatting. In this case however it is the sqldeveloper export tool that generates the insert statement based on the NLS_DATE_FORMAT setting that happens to produce wrong effects in the exposed case. The usefulness of the export tool is thus limited by this behavior.
thatjeffsmith Post Author 7 years ago

You just need to change the preferences to meet your requirements.

Wadson Ramon 7 years ago Reply

Solve my problema with
set encoding UTF-8.
I just to not understand why.

thatjeffsmith Post Author 7 years ago

send me your file and i’ll try to answer that question for you
wadson Ramon 7 years ago

Code:

Clear Columns Computes Breaks
set lines 180 pages 30 arrays 30
col log_time format a19
col sid format 9999
col username format a19
col osuser format a20
col serial# format 99999
col spid format a9
col lockwait format 9999999
col St format a2
col CX format a2
col machine format a28
define var1=1
define var2=1
select * from (Select a.sid, a.serial#,
substr(to_char(a.logon_time,’ dd-mm-yy hh24:mi:ss’),1,20) log_time,
a.process, a.osuser, a.username, b.spid,
substr(a.status,1,1) St, a.lockwait,
decode(a.server,’NONE’,”,substr(a.server,1,2)) CX, a.machine
from v$session a, v$process b
where a.paddr = b.addr and
a.username is not null
)
where &&var1 like &&var2
order by 8,6,3
/
thatjeffsmith Post Author 7 years ago

the problem is with the file itself though, please email me it as is to [email protected]

Wadson Ramon 7 years ago Reply

Solve my problema with
set encoding UTF-8.
I just to not understand why.

Marin 7 years ago Reply

Hi Jeff,
My question is about Data Modeler.
Is there a way to automate for example “schema compare” , “schema extract” and/or other activities.
I guess if there is a option to start it via command line with parameters, or using SqlCli.
Do I miss something in documentation ?
Thanks

thatjeffsmith Post Author 7 years ago

Sorry, the data modeler doesn’t offer a command line interface.

Pedro Cubero 7 years ago Reply

I’m using the last version from SQL Developer, and sometimes I see that the environment is stucked, I try to do one thing, and until past one minute, the environment is active again. Is there any issue with the tool?

I have installed SQL Developer with Java 8. How can I improve the tool, faster for example?

Thanks in advance.

thatjeffsmith Post Author 7 years ago

Can you describe what is happening ‘sometimes?’ Is it possible the connection is just busy running a query? If you have a slow network connection to your database, there’s not much we can do to improve things.
Wadson Ramon 7 years ago

Any script that I try to run from the below error:

SQL> @sess
Oct 15, 2017 9:33:27 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor
SEVERE: Could not process url:file:/home/oracle/DBA/sess.sql
Oct 15, 2017 9:33:27 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: java.io.Reader.(Reader.java:78)
java.lang.NullPointerException
at java.io.Reader.(Reader.java:78)
at java.io.BufferedReader.(BufferedReader.java:101)
at java.io.BufferedReader.(BufferedReader.java:116)
at oracle.dbtools.raptor.newscriptrunner.ScriptParser.scriptParserInit(ScriptParser.java:87)
at oracle.dbtools.raptor.newscriptrunner.ScriptParser.(ScriptParser.java:83)
at oracle.dbtools.raptor.newscriptrunner.ScriptParser.(ScriptParser.java:75)
at oracle.dbtools.raptor.newscriptrunner.FallbackParserProvider.(FallbackParserProvider.java:22)
at oracle.dbtools.raptor.newscriptrunner.SqlParserProvider.getScriptParserIterator(SqlParserProvider.java:25)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:169)
at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.runExecuteFile(SQLPLUS.java:3870)
at oracle.dbtools.raptor.newscriptrunner.SQLPLUS.run(SQLPLUS.java:210)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQLPLUS(ScriptRunner.java:406)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:243)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:337)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:390)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:401)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1112)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:477)
thatjeffsmith Post Author 7 years ago

can you read the file from inside sqlcl? what happens if you try to !cat it?

Thomas Whelan 7 years ago Reply

Formatting in 17.2. I used the preference ‘Number of Commas per Line’, but now you tell me that that has been deprecated. I often deal with big select statements. If I use the default formatting of SQL Developer, then they get expanded to over 400 lines. It’s hard to work with that. Here’s what it looks like with 8 commas per line.
https://postimg.org/image/85hbeabip7/

However, if I try to get the same in 17.2, then it splits the Select in a strange manner(I assume it is just respecting the max characters per line property)
https://postimg.org/image/3132tdxkij/

Why was this preference removed? I liked it a lot.

thatjeffsmith Post Author 7 years ago

The 8 commas per line looks horrible to me, and I’d not want to support that code. Of course, this is a subjective matter…you obviously feel differently.

We had multiple months of beta giving folks an opportunity to provide feedback, and no one cried foul on this one going away.

We could always add it back. Please add a request to sqldeveloper.oracle.com, encourage your friends to vote for it, and we can take a look at adding it back.

Or, you can keep a copy of sqldev v4.1 around just to be used for formatting.

Juan Luis Soto V. 7 years ago Reply

old dbms_job (before scheduler).
Hi Jeff,
I have some old fashion jobs and i cannot see the information about last execution, because of ORA-942. My sqldeveloper is 4.2.0. I can create jobs and i can see the information from other software.

If you can tell me what is the grant i need i will apreciate.
Connections, scheduler –> DBMS jobs.

thanks in advance.

thatjeffsmith Post Author 7 years ago

View > Log > Statements..this will let you see the SQL we run for each screen/click/button press

Pedro Cubero 7 years ago Reply

I have installed SQL Developer 17, it’s not easy to define a SQL formatter. Do you have a xml file that I can import?

thatjeffsmith Post Author 7 years ago

It already has a formatter. If you don’t like the default options, you can change them.

Bryce 7 years ago Reply

Hi Jeff,

I’m having a confusing time right now between versions of SQL Dev with spooling the current date to a filename (in other words, it worked previously and now it doesn’t).

In version 3.2.20.09 I can run the below script successfully and it creates a file with the desired name invoice_20171011.csv (for today).
In version 4.1.5.21 it just spits out a file with the name SPOOL.LST.

The script:
COLUMN FileName NEW_VALUE SetDate
SELECT ‘invoice_’||TO_CHAR(SYSDATE,’yyyymmdd’)||’.csv’ FILENAME FROM DUAL;
SPOOL &&SetDate
SPOOL off

I’m relatively new to SQL but have been trawling the net for days trying to work this out as I figured it was something I just didn’t know but I’ve finally decided to ask instead of wasting more time.

The only difference I can see between the two is that in 3.2, SPOOL is coloured like an identifier but in 4.1 it is coloured as a keyword.

Any ideas?

Thank you in advance.

thatjeffsmith Post Author 7 years ago

tested – works in SQL*Plus
– fails in SQLcl

This would be a bug.
Bryce 7 years ago

Thanks Jeff, I decided to try 17.3 this morning (AU) and can confirm that it works in the current version.

Thanks for the reassurance I wasn’t hallucinating 🙂

Considering this works in 17.3 I won’t bother raising a bug fix request.
thatjeffsmith Post Author 7 years ago

Cool. We do have a nasty editor bug in 17.3, that we aim to fix in the next few days. So look for another update – yeah, I know 🙁
Bryce 7 years ago

Yep, experienced it, read about it, extended deadlines and waited.

Downloaded the update today and all good, thank you! 🙂

PS – option to import previous installation setup is Gold.

TR Rudkin 7 years ago Reply

Jeff,

Isn’t it is funny how the simplest of things annoy us the most? Installed 17.3.0.271 build 271.2323. In Preference … Code Editor->PL/SQL Syntax Colors I set the color for ‘PLSQL Brace’ to my preference. ( If you must know Twilight Bold-Italic Yellow ). As I type a brace it appears as selected, but in under a second it reverts to the ‘PLSQL Opperator’ color. Is there a setting I am missing that would keep my preferred colors?

thatjeffsmith Post Author 7 years ago

Sounds like a parser bug. Is this JUST happening in 17.3?
TR Rudkin 7 years ago

Yes was OK in 17.2 . The issue exists on both Linux and Windows. On windows have tried both look&feel options Oracle and windows

TR
TR Rudkin 7 years ago

Was 17.3.1 going to address this issue? Or should I stay on 17.2?
thatjeffsmith Post Author 7 years ago

17.3 had a bunch of bug fixes over 17.2, this wasn’t one of them. This hasn’t been logged as a bug even. 17.3.1 has 1 bug fix only, read today’s post for details.

Nadav 7 years ago Reply

I noticed in a screen shot in one of Jeff’s posts that there are attributes within a single entity that appear in different colors. I have not been able to find how to format the display of individual attributes (e.g., color, bold). Any guidance? Thanks. Nadav

Shoaib 7 years ago Reply

Hi Jeff,

How to trace a session in SQL Developer, like we can do in Toad in session browser.

Thanks.

Shoaib 7 years ago

Never mind, found it in Tools > Monitor Sessions section.
Thanks.

Bernard 7 years ago Reply

Dear Jeff,

I have installed the latest version of the SQL Developer. Upon setting up the preferences I ran into a problem with the syntax highlighting of comments. If I choose to set a color to highlight the background of any comment then every blank space (and tab space) is highlighted in the same color (even if it is not in a comment).
Is this a normal behaviour or some bug?

Thanks.

Hannah 7 years ago Reply

This is probably a really silly question, but is there a way to set up SQL developer so that when I open up a new worksheet or a saved file it opens up next to my current tab? It did this when I was on an older version but I was upgraded to 17.2.0.188 and now newly opened items always open at the end.

Amin Adatia 7 years ago Reply

V17.2
Tools => Monitor Sessions

I go to my DBMS_SCHEDULER job line and look at the Active SQL. The resulting output is from some other sid/serial on a different instance. My command is showing INSERT while the Active SQL is for a User query.

thatjeffsmith Post Author 7 years ago

We’re just showing what’s in the V$ views.

Are you on a RAC instance by any chance?

Tanya Finlay 7 years ago Reply

I’m new to SQL Developer and am looking for some basic assistance. I’ve installed version 4.3.1 (that’s what our department has) and have configured it to talk to DB2. I’m able to do a successful test. But then when I click on “Connect”, I get three error boxes that pop up

1. An error was encountered performing the requested operation:
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SYSCAT.SCHEMATA, DRIVER=4.13.127
Vendor code -204

2. An error was encountered performing the requested operation:
DB2 SQL Error: SQLCODE=-516, SQLSTATE=26501, SQLERRMC=null, DRIVER=4.13.127
Vendor code -516

3. An error was encountered performing the requested operation:
DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=4.13.127
Vendor code -514

But when I click OK, I still have an actual connection to the dB and am able to do my usual queries in the query builder.

I’m wondering what these error messages mean so that I can make them go away, and what functionality am I missing out on because something didn’t load or connect properly.

I’m an application developer who uses this tool to retrieve data for reporting purposes.

thatjeffsmith Post Author 7 years ago

If you’re doing regular DB2 work, I’d ask for the native DB2 tools. SQL Developer provides DB2 functionality/connectivity for the sole purpose of migrating your database to Oracle. So our query support is basic, and most of the other features in SQLDev are not available for DB2.

No idea what those messages mean. Probably OK to ignore them if your queries are running OK.
Tanya Finlay 7 years ago

Thanks Jeff. I was thinking of just leaving them alone as they don’t “appear” to be causing any issues. That said, would you have any idea how I might be able to suppress them?

Thanks again!

Stacy Gaddy 7 years ago Reply

Any idea why TNSPING command from sqlcl is throwing this error for any db I try?

SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 17.2.0.184.0917

SQL> tnsping mydb
Sep 22, 2017 11:29:50 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
java.lang.SecurityException: sealing violation: package oracle.net.ns is sealed
at java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
at java.net.URLClassLoader.definePackageInternal(URLClassLoader.java:654)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:689)
at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
at java.security.AccessController.doPrivileged(AccessController.java:686)
at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
at java.lang.ClassLoader.defineClassImpl(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:346)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:154)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:729)
at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
at java.security.AccessController.doPrivileged(AccessController.java:686)
at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
at java.lang.Class.forNameImpl(Native Method)
at java.lang.Class.forName(Class.java:278)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.createProtocolInstance(PingCmd.java:144)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.ping(PingCmd.java:98)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.handleEvent(PingCmd.java:57)
at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:446)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:214)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:336)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:405)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:416)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1128)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)

thatjeffsmith Post Author 7 years ago

Do you have a ojdbc7 AND ojdbc8 in the lib directory?
Stacy Gaddy 7 years ago

Nope.
export CLASSPATH=~/sqlcl/lib

Nothing else in the classpath. Only ojdbc8.jar in the distro. Would the system path be in play as well?
thatjeffsmith Post Author 7 years ago

yeah maybe…you could nuke the jar from the sqlcl/lib and see if lets you connect anyway
STACY GADDY 7 years ago

That definitely did the trick. I’m running sqlcl from a user who also has dba rights and access to $ORACLE_HOME… and $ORACLE_HOME in the PATH…. but again, not CLASSPATH. I’m sure there are possibly several ojdbc variants as part of the standard db install. In my case, I’m using latest PSU of 11.2.0.4.
thatjeffsmith Post Author 7 years ago

Great…it’d be better for it to go with our jar as the driver is MUCH newer. Or if you’re OK for now, I’ll leave you be.

Ask A Question

7,791 Comments

Write A Comment Cancel Reply