Ask A Question

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.

  1. 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.
  2. 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.
  3. 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,513 Comments

  1. Bug, or feature?!

    SQL Developer 19.1.0.094

    right click a table, click Import, point to CSV file, see that the header row is there, so tell tool to skip 1 row, display updated to remove header row, click the continue button, select By Position, hmmm somewhere along the path choose INSERT, click the doit button – the header row is skipped, but so is the first row of data.

    Logged off, told user the data is loaded, only to have the user reply that one row wasn’t there.

    Went back and did the whole routine again, but said to skip 0 rows and this time just the header row is skipped.

    • “so tell tool to skip 1 row”

      but then you also unchecked the ‘header’ – so you told it to skip the first row of data…

  2. Dan Oviatt Reply

    I have installed SQL Developer on a RedHat server in order to use an ansible playbook to do an export of a database using the sdcli command. This works fine for root and for my user ( I am a privileged user) but when I use the command as ansible it starts the export but then says completed and the target file is empty. It also doesn’t show the row counts being exported as it does when I run it as myself. Why can a regular user not perform the export?

    • the ansible user will have it’s own set of connections/settings – it won’t just share the connections you have defined for your user or root.

  3. Jonas de Graaff Reply

    I try to implement a fixed (external defined) api-spec using plsq-based ORDS services. This api-spec uses a query-parameter ‘q’, just like ORDS query-based services do. However, even if the service is PLSQL based, I still cannot use the q parameter. In the documentation I read that this parameter is reserved, but I would expect ORDS only to use this parameter in query-based services. Now I get an errormessage. So my question is: Is there a way to use a parameter named q in a PLSQL-based service?

  4. Hi Jeff,

    I am wondering whether can we use the transformation scripting feature of the SQL modeler to read data from an xl/txt file and implement the changes.

    • ‘data’ & ‘implement the changes’

      How would these correlate to your model?

      Can you share an example?

  5. Steve Karetny Reply

    Jeff,

    SQL*Developer’s Table Icon is greyed out for the XDB.XDB$CONFIG table. Other tables under the XDB schema are regular bold icons. What does the greyed out table icon indicate? I could not find the documentation for the icon meaning.

    Steve

    • I honestly don’t know…the XDB schema and features are interesting, I’m sure it has some connotation.

  6. Ahsan Hussain Reply

    Hi Jeff,

    I am deploying ORDS in my organization, can you guide me which is the best way of deploying ORDS for performance

    1. Standalone mode
    2. On tomcat

  7. Nitin Kolte Reply

    Hi Jeff,

    I have downloaded the 20.4 version of sql developer. I am unable to open it on my machine.

    Could you please help me on this.

    Thanks,
    Nitin

  8. John Thomas Reply

    Jeff,

    I get a bit frustrated with hangs in SQL*Developer. Have got used to saving anything before I run it, but sometimes forget – and is that really something we should have to do in 2021 for fear of having to redo 30 minutes or so of effort?

    I’ve seen this a number of times and it’s something that makes me hold back from recommending to management that SQL*Developer is more widely used as a SQL IDE.

    So here’s what might be a simple test case. I wanted to generate a set of dates for an outer join to something. First time I tried the join, it hung. I then found running the CONNECT BY on its own would hang. Run it in SQL*Plus against the same database and it returns the result in a second. My SQL*Developer instances are just sat showing “Not responding”.

    WITH dates AS (
    SELECT
    trunc(sysdate – num) thisdate
    FROM
    (
    SELECT
    ROWNUM num
    FROM
    dual
    CONNECT BY
    level <= 60
    )
    )
    SELECT
    *
    FROM
    dates;

    No idea what causes the hang, but shouldn't SQL*Developer automatically save worksheets so at least we can start again when things go wrong?

    • John Thomas

      BTW, later on, the code above runs just fine. Same SQL Developer 20.4.0.379, haven’t even rebooted. (So the comment about auto-save still applies.)

    • No idea what causes the hang – neither do I…your scenario runs fine for me.

      If you can make this happen on demand, can you use the jstack utility in your JDK Home / bin directory to get a stack dump of the SQL Developer process, then we can see exactly where the deadlock is.

  9. When connected to a user with limited grants and then in the Connections browser opening a table or index and showing details, all works fine.
    Now, looking into monitored “Statements – Log”, I see only queries using DBA_views like dba_objects, although DBA_views are not granted to this user.
    E.g.
    SELECT /*OracleDictionaryQueries.ALL_TIMESTAMP_BY_ID_10g_QUERY*/
    AO.LAST_DDL_TIME FROM SYS.DBA_OBJECTS AO WHERE AO.OBJECT_ID = :1 AND NOT EXISTS ( SELECT 1 FROM RECYCLEBIN RB WHERE RB.PURGE_OBJECT = AO.OBJECT_ID)

    If I execute it in worksheet, I get “table or view doesn’t exist” as expected.
    So, how does SQL-Developer get these information and why is it shown this way in logs?

    • The first thing we do at connect time is to check if your user has access to the DBA_ views, if it does, we use them. If it doesn’t, we fall back to ALL_ views.

      What are you doing on screen when that SQL is being executed?

    • Thanks for pointing that out.
      I checked again and now realize, that I’ve used two different testenvs with obviously different privileges what actually should not be like this. Thanks 🙂

  10. Lee Lindley Reply

    I was excited to try sqlcl from articles I saw from you about simulating a “tail” command and also color prompt (though that one was just for fun).

    When I run “sql.exe” from DOS command prompt, all is well. The sqlprompt looks as expected and the color tricks work.

    When I start a cygwin bash login shell, whether inside a DOS command window or in a mintty, and run the “sql” bash script, there is a small issue with the sqlprompt. Again, not specific to the terminal because it happens both in dos comand prompt window after starting bash, and mintty, but it could be something in my ENV when I run bash.

    >←[mshow sqlprompt
    show sqlprompt
    sqlprompt “> ”
    >←[m

    >←[mset sqlprompt “@|red ME >|@”
    set sqlprompt “@|red ME >|@”
    ←[31mME >←[m←[m

    ←[31mME >←[m←[m

    If I start a bash shell in mintty like normal, then run sql.exe instead of the “sql” startup script made for the purpose, everything is fine with a normal text sqlprompt. So it is something happening in that “sql” bash script different from sql.exe and I can’t spot it. I’m not completely ignorant about TERM/TERMINFO and dos window escape sequence differences, but this is beyond my ken.

    I’ll just use sql.exe for now. Would like to understand it though.

    I’m running a fairly current cygwin bash.
    CYGWIN_NT-10.0 lee_acer 3.2.0(0.340/5/3) 2021-03-29 08:42 x86_64 Cygwin

    • I never use cygwin, just the straight-up EXE on Windows. I’ll ask ‘my guy’ to take a look though.

  11. valerie Amstutz Reply

    Hi Jeff starting with SQL Developer 19.2 dates are exported to Excel as dates instead of the text fields in prior versions. Is there anyway around this? I’m currently on SQL Dev 20.4 and need to get my dates in exported as 28-OCT-2020 12:00:00 AM. Instead they are coming over to Excel as 10/28/20 12:00:00 AM. It’s important to me to retain the upper case OCT as well. I am doing file compares from older files and need the date format consistent. Any help you can offer would be greatly appreciated.

    • david schleis

      Hello Jeff
      I am hoping that you can help me to determine which setting may I have accidentally changed between my install of SQL Developer 20.2 and 20.4 that causes our standard application install script to silently fail in 20.4.
      The script looks like what I have included below. (I added “pro #” lines to see where the failure is occurring) (its between 4 and 5 BTW)

      The release file is typically run using the @release_file.sql syntax, but I put the file into SQLDEV and I got the same result.

      I know that you are busy, and I appreciate any guidance that you can provide here.

      Thanks Jeff.
      ———– the following works in 20.2 fails silently in 20.4 with no known changes in preferences
      ———– both run-in on macOS 10.15.6
      PRO Installing Application

      set sqlblanklines on
      SET define ‘^’
      SET verify off
      SET feedback off
      SET timing off
      PRO 1
      define logname =” — Name of the log file
      PRO 2
      — Start The logging
      — =============================================
      –set termout off
      pro 3
      column my_logname new_val logname
      pro 4
      select ‘App_install_’||to_char(sysdate, ‘YYYY-MM-DD_HH24-MI-SS’)||’.log’ my_logname from dual;
      pro 5
      — good to clear column names when done with them
      column my_logname clear
      pro 6
      set termout on

      spool ^logname

      PRO ============================= Installation ===========================
      PRO

      PRO Log File = ^logname

      spool off
      PRO finished
      /

  12. Carlos Solano Reply

    I’m new to ORDS.
    Is it possible to construct a ORDS handler response using Pl/Sql but return the data in blocks of 25 records for example? In all the examples I have seen it uses records_per_page in zero, that is, all the response goes out all-in-one. Maybe using a table function?
    I need to construct the response data using Pl/Sql but I don’t want to give it all the records in a single response.
    Thanks for your suggestions.

    • You can, but you’d have to build the paging into your plsql.

      As in, you’ll need to code this yourself. I’ve seen a few folks do this and they’ve shared it online.

  13. Hi Jeff, sorry can’t reply to your answer…

    turned out to be a client issue, there is no deadlock by using instant client 11.2.0.4

    That raises the next question: is it possible to set up Connections with dedicated clients, or do i have to use different instances of sql developer for different connection-groups of databases?

    Thanks, Walter

  14. Gonzalo Luis Criado Reply

    Hello Jeff/community,

    First of all, I want to thank for all the awesome material that you and the community provide via their questions.
    I am in the phase of implementing security on ORDS and they told me that is based on object level (table,view,procedure,etc).
    Due to that I implemented a PL/SQL but when retrieves the values, takes out metadata like hasMore,limit and/or offset in the JSON reply.
    Thinking outside of the box, I tried later with a function and a cursor but I was not able to get it work.
    The type of handler is a GET and I am doing a select from a table. Could you provide an example of this use case?

    Thanks in advance for any clue/help that can be given!

    • You can have security and a sql based rest api, then you’ll get the paging and the metadata links you’re looking for.

      If you’re forced to go with plsql,you can still have those features of you’ll just have to code them in yourself.

  15. Jeff,
    Does SQL Developer cache connections even after we close the tool? If yes, how do we clear the cache?

    We have weird issue where some users are having TNS connection problem after we migrated the database from one cluster to another last weekend. SQL Developer uses shared tnsnames.ora for all users. In the TNS entry, we use hostname= so that, after the migration, we will just have to update our DNS so that the IP points to the new cluster. After migration, some users have started to report that they are having issue connecting to the database with error “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”. We have validated that database is fine because they can connect to the database through SQLPLUS using the same tnsnames.ora that the SQL Developer uses.

    We are using SQL Developer 18.3; Our database is in 19c.

    Thank you.

    • I think my post was not clear because the actual value in hostname= was removed. It could be because I have enclosed them with less-than and greater-than symbols.

      I have corrected the sentence here:
      .. In the TNS entry, we use hostname=cluster_dns_alias so that, after the migration, we will just have to update our DNS so that cluster_dns_alias IP points to the new cluster.

      I’m sorry for the confusion.

    • Hi Jeff,
      sqldeveloper 20.4 hangs while connecting to oracle 9i.
      Connection via sqlplus and testing connection within sqldeveloper is succesful, any help is greatly appreciated!
      maybe it is a firewall issue since the database(s) are within a secured subnet.

      C:\sqldeveloper_20_4\sqldeveloper\sqldeveloper\bin>sqldeveloper
      Java asserts are enabled!
      2021-05-10 12:12:33
      Full thread dump Java HotSpot(TM) 64-Bit Server VM (25.271-b09 mixed mode):

      “Open Connection” #144 prio=5 os_prio=0 tid=0x000000007bb94000 nid=0x1f00 waiting on condition [0x000000014c7ff000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.FutureTask.awaitDone(FutureTask.java:429)
      ^ at java.util.concurrent.FutureTask.get(FutureTask.java:191)
      C at oracle.dbtools.raptor.tasks.IdeTaskTracker.run(IdeTaskTracker.java:66)
      at java.lang.Thread.run(Thread.java:748)

      “IconOverlayTracker Timer: null-jdbcNodeInfoType” #138 prio=5 os_prio=0 tid=0x000000007bb95000 nid=0xbe4 in Object.wait() [0x0000000171ffe000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at java.util.TimerThread.mainLoop(Timer.java:526)

      – locked (a java.util.TaskQueue)
      at java.util.TimerThread.run(Timer.java:505)

      “InterruptTimer” #136 daemon prio=5 os_prio=0 C:\sqldeveloper_20_4\sqldeveloper\sqldeveloper\bin>tid=0x000000007bb9e000 nid=0x3944 in Object.wait() [0x00000001657ff000]
      java.lang.Thread.State: TIMED_WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.util.TimerThread.mainLoop(Timer.java:552)
      – locked (a java.util.TaskQueue)
      at java.util.TimerThread.run(Timer.java:505)

      “oracle.jdbc.driver.BlockSource.ThreadedCachingBlockSource.BlockReleaser” #135 daemon prio=4 os_prio=-1 tid=0x000000007bba2800 nid=0x3a1c waiting on condition [0x000000015f3ff000]
      java.lang.Thread.State: TIMED_WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
      at oracle.jdbc.internal.Monitor$WaitableMonitor.monitorWait(Monitor.java:259)
      at oracle.jdbc.internal.Monitor$WaitableMonitor.monitorWait(Monitor.java:241)
      at oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaser.run(BlockSource.java:346)

      “pool-6-thread-1” #129 prio=5 os_prio=0 tid=0x000000007bba0000 nid=0x22f8 waiting on condition [0x00000002419ce000]
      java.lang.Thread.State: TIMED_WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
      at java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
      at java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
      at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)

      “InnocuousThread-1” #127 daemon prio=6 os_prio=0 tid=0x000000007bb9b000 nid=0xaa0 runnable [0x00000001c6a3f000]
      java.lang.Thread.State: RUNNABLE
      at sun.nio.ch.Iocp.getQueuedCompletionStatus(Native Method)
      at sun.nio.ch.Iocp.access$300(Iocp.java:46)
      at sun.nio.ch.Iocp$EventHandlerTask.run(Iocp.java:333)
      at java.lang.Thread.run(Thread.java:748)
      at sun.misc.InnocuousThread.run(InnocuousThread.java:106)

      “sshd-SshClient[8c3f4f4]-timer-thread-1” #126 daemon prio=5 os_prio=0 tid=0x000000007bb95800 nid=0x3bd0 waiting on condition [0x00000001b3e3e000]
      java.lang.Thread.State: TIMED_WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
      at java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
      at java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
      at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)

      “Arbori Background Parser#1” #104 prio=5 os_prio=0 tid=0x000000007bb9f800 nid=0x3234 waiting on condition [0x00000002cec7f000]
      java.lang.Thread.State: TIMED_WAITING (sleeping)
      at java.lang.Thread.sleep(Native Method)
      at oracle.dbtools.raptor.plsql.structure.arbori.BackgroundParser$1.construct(BackgroundParser.java:147)
      at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
      at java.lang.Thread.run(Thread.java:748)

      “Background Parser#0” #103 prio=5 os_prio=0 tid=0x000000007bb98800 nid=0x2a84 waiting on condition [0x00000002c842f000]
      java.lang.Thread.State: TIMED_WAITING (sleeping)
      at java.lang.Thread.sleep(Native Method)
      at oracle.dbtools.raptor.plsql.BackgroundParser$1.construct(BackgroundParser.java:160)
      at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
      at java.lang.Thread.run(Thread.java:748)

      “Arbori Background Parser#0” #102 prio=5 os_prio=0 tid=0x000000007bb9a000 nid=0x37d0 waiting on condition [0x00000002c202e000]
      java.lang.Thread.State: TIMED_WAITING (sleeping)
      at java.lang.Thread.sleep(Native Method)
      at oracle.dbtools.raptor.plsql.structure.arbori.BackgroundParser$1.construct(BackgroundParser.java:147)
      at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
      at java.lang.Thread.run(Thread.java:748)

      “Thread-30” #82 daemon prio=5 os_prio=0 tid=0x0000000074e62000 nid=0x87c runnable [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “Disposer” #80 daemon prio=10 os_prio=2 tid=0x0000000074e5f800 nid=0x9f8 in Object.wait() [0x000000023ab4e000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:144)
      – locked (a java.lang.ref.ReferenceQueue$Lock)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:165)
      at com.sun.webkit.Disposer.run(Disposer.java:122)
      at java.lang.Thread.run(Thread.java:748)

      “Prism Font Disposer” #79 daemon prio=10 os_prio=2 tid=0x0000000074e5f000 nid=0x2df8 in Object.wait() [0x0000000233f4f000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:144)
      – locked (a java.lang.ref.ReferenceQueue$Lock)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:165)
      at com.sun.javafx.font.Disposer.run(Disposer.java:93)
      at java.lang.Thread.run(Thread.java:748)

      “Thread-29” #78 daemon prio=5 os_prio=0 tid=0x0000000074e62800 nid=0x22d0 runnable [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “JavaFX Application Thread” #77 daemon prio=5 os_prio=0 tid=0x0000000172071800 nid=0xd20 runnable [0x00000001ec23f000]
      java.lang.Thread.State: RUNNABLE
      at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
      at com.sun.glass.ui.win.WinApplication.lambda$null$4(WinApplication.java:187)
      at com.sun.glass.ui.win.WinApplication$$Lambda$95/1131537953.run(Unknown Source)
      at java.lang.Thread.run(Thread.java:748)

      “Thread-28” #76 daemon prio=5 os_prio=0 tid=0x0000000172072800 nid=0x2504 waiting on condition [0x00000001d963f000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
      at java.util.concurrent.LinkedBlockingDeque.takeFirst(LinkedBlockingDeque.java:492)
      at com.sun.glass.ui.InvokeLaterDispatcher.run(InvokeLaterDispatcher.java:108)

      “QuantumRenderer-0” #74 daemon prio=5 os_prio=0 tid=0x0000000172077000 nid=0x824 waiting on condition [0x00000001d323f000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
      at java.util.concurrent.LinkedBlockingQueue.take(LinkedBlockingQueue.java:442)
      at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at com.sun.javafx.tk.quantum.QuantumRenderer$PipelineRunnable.run(QuantumRenderer.java:125)
      at java.lang.Thread.run(Thread.java:748)

      “Swing-Shell” #69 daemon prio=5 os_prio=0 tid=0x0000000172074800 nid=0x3004 waiting on condition [0x00000001dfa3e000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
      at java.util.concurrent.LinkedBlockingQueue.take(LinkedBlockingQueue.java:442)
      at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at sun.awt.shell.Win32ShellFolderManager2$ComInvoker$3.run(Win32ShellFolderManager2.java:585)
      at java.lang.Thread.run(Thread.java:748)

      “WaitCursor-Timer” #67 prio=6 os_prio=0 tid=0x0000000172075800 nid=0x291c in Object.wait() [0x00000001e5e3f000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at java.util.TimerThread.mainLoop(Timer.java:526)
      – locked (a java.util.TaskQueue)
      at java.util.TimerThread.run(Timer.java:505)

      “RaptorTaskThread01-Open Connection” #53 prio=5 os_prio=0 tid=0x000000017206b800 nid=0x2e94 runnable [0x00000001c063d000]
      java.lang.Thread.State: RUNNABLE
      at sun.nio.ch.SocketDispatcher.read0(Native Method)
      at sun.nio.ch.SocketDispatcher.read(SocketDispatcher.java:43)
      at sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:223)
      at sun.nio.ch.IOUtil.read(IOUtil.java:197)
      at sun.nio.ch.SocketChannelImpl.read(SocketChannelImpl.java:378)
      – locked (a java.lang.Object)
      at oracle.net.nt.TimeoutSocketChannel.read(TimeoutSocketChannel.java:416)
      at oracle.net.ns.NSProtocolNIO.doSocketRead(NSProtocolNIO.java:1119)
      at oracle.net.ns.NIOPacket.readHeader(NIOPacket.java:267)
      at oracle.net.ns.NIOPacket.readPacketFromSocketChannel(NIOPacket.java:199)
      at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:141)
      at oracle.net.ns.NIOPacket.readFromSocketChannel(NIOPacket.java:114)
      at oracle.net.ns.NIONSDataChannel.readDataFromSocketChannel(NIONSDataChannel.java:98)
      at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:834)
      at oracle.jdbc.driver.T4CMAREngineNIO.getNBytes(T4CMAREngineNIO.java:636)
      at oracle.jdbc.driver.T4C8TTILob.readRPA(T4C8TTILob.java:957)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:595)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
      at oracle.jdbc.driver.T4C8TTIClob.createTemporaryLob(T4C8TTIClob.java:648)
      at oracle.jdbc.driver.T4CConnection.createTemporaryClob(T4CConnection.java:5656)
      at oracle.sql.CLOB.createTemporary(CLOB.java:824)
      at oracle.jdbc.driver.OraclePreparedStatement.setStringForClobCritical(OraclePreparedStatement.java:5250)
      at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5086)
      at oracle.jdbc.driver.OracleCallableStatement.setString(OracleCallableStatement.java:4713)
      at oracle.jdbc.driver.OraclePreparedStatement.setStringAtName(OraclePreparedStatement.java:12666)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.setStringAtName(OraclePreparedStatementWrapper.java:644)
      at oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCStatement$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.setStringAtName(Unknown Source)
      at oracle.dbtools.db.OracleUtil.checkAccessImpl(OracleUtil.java:448)
      at oracle.dbtools.db.DBUtil.checkAccess(DBUtil.java:1815)
      at oracle.dbtools.db.AccessCache.checkAccess(AccessCache.java:64)
      at oracle.dbtools.db.AccessCache.hasAccess(AccessCache.java:55)
      at oracle.dbtools.db.AccessCache.hasAccess(AccessCache.java:24)
      at oracle.dbtools.raptor.query.QueryUtils.checkNonOracleAccess(QueryUtils.java:611)
      at oracle.dbtools.raptor.query.QueryUtils.getQuery(QueryUtils.java:411)
      at oracle.dbtools.raptor.query.QueryUtils.getQuery(QueryUtils.java:271)
      at oracle.dbtools.raptor.query.ObjectQueries.getQuery(ObjectQueries.java:42)
      at oracle.dbtools.raptor.utils.DatabaseFeatureRegistry$QueryFeature.isFeatureEnabled(DatabaseFeatureRegistry.java:69)
      at oracle.dbtools.raptor.utils.DatabaseFeatureRegistry.initializeFeatures(DatabaseFeatureRegistry.java:141)
      at oracle.dbtools.raptor.features.DatabaseFeatureRegistryImpl.access$200(DatabaseFeatureRegistryImpl.java:42)
      at oracle.dbtools.raptor.features.DatabaseFeatureRegistryImpl$2.initializeConnection(DatabaseFeatureRegistryImpl.java:236)
      at oracle.dbtools.raptor.utils.Connections.initializeConnection(Connections.java:1974)
      at oracle.dbtools.raptor.utils.Connections.access$100(Connections.java:65)
      at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:335)
      – locked (a java.lang.Object)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1180)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1166)
      at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:83)
      at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:38)
      at oracle.dbtools.raptor.navigator.db.BaseConnectionNode.getConnection(BaseConnectionNode.java:91)
      – locked (a java.lang.Object)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode.getObjectFactory(DatabaseTreeNode.java:90)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:145)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:119)
      at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at java.lang.Thread.run(Thread.java:748)

      “Thread-14” #49 daemon prio=5 os_prio=0 tid=0x0000000172071000 nid=0x1950 in Object.wait() [0x00000001a123f000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at java.util.prefs.AbstractPreferences$EventDispatchThread.run(AbstractPreferences.java:1482)
      – locked (a java.util.LinkedList)

      “Scheduler” #43 daemon prio=5 os_prio=0 tid=0x000000017206a000 nid=0x2cb8 in Object.wait() [0x0000000186fae000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at oracle.dbtools.raptor.backgroundTask.TaskLinkedList.takeNextTask(TaskLinkedList.java:47)
      – locked (a oracle.dbtools.raptor.backgroundTask.TaskLinkedList)
      at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$SchedulerThread.run(RaptorTaskManager.java:548)

      “Log Poller” #42 prio=1 os_prio=-2 tid=0x000000017206e000 nid=0x39a0 waiting on condition [0x0000000180bae000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
      at java.util.concurrent.LinkedBlockingQueue.take(LinkedBlockingQueue.java:442)
      at oracle.ide.log.QueuedLoggingHandler.take(QueuedLoggingHandler.java:60)
      at oracle.ideimpl.log.TabbedLogManager$4.run(TabbedLogManager.java:333)
      at java.lang.Thread.run(Thread.java:748)

      “TimedCache-Timer” #34 daemon prio=5 os_prio=0 tid=0x000000007c3b4800 nid=0xd94 in Object.wait() [0x000000016bbff000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at java.util.TimerThread.mainLoop(Timer.java:526)
      – locked (a java.util.TaskQueue)
      at java.util.TimerThread.run(Timer.java:505)

      “Framework Event Dispatcher” #26 daemon prio=5 os_prio=0 tid=0x000000007c3b8000 nid=0x3ac4 in Object.wait() [0x0000000152bff000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.getNextEvent(EventManager.java:400)
      – locked (a org.eclipse.osgi.framework.eventmgr.EventManager$EventThread)
      at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:336)

      “Framework Active Thread” #23 prio=5 os_prio=0 tid=0x000000007c3b5000 nid=0x4a0 in Object.wait() [0x00000001463ff000]
      java.lang.Thread.State: TIMED_WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at org.eclipse.osgi.framework.internal.core.Framework.run(Framework.java:1870)
      – locked (a org.eclipse.osgi.framework.internal.core.Framework)
      at java.lang.Thread.run(Thread.java:748)

      “Active Reference Queue Daemon” #19 daemon prio=1 os_prio=-2 tid=0x000000003f4c9800 nid=0x3918 in Object.wait() [0x0000000066b7f000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:144)
      – locked (a java.lang.ref.ReferenceQueue$Lock)
      at org.openide.util.lookup.implspi.ActiveQueue$Impl.removeSuper(ActiveQueue.java:93)
      at org.openide.util.lookup.implspi.ActiveQueue$Daemon.run(ActiveQueue.java:131)

      “TimerQueue” #17 daemon prio=5 os_prio=0 tid=0x000000003f209800 nid=0x3b04 runnable [0x000000005fc7e000]
      java.lang.Thread.State: TIMED_WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
      at java.util.concurrent.DelayQueue.take(DelayQueue.java:223)
      at javax.swing.TimerQueue.run(TimerQueue.java:174)
      at java.lang.Thread.run(Thread.java:748)

      “AWT-EventQueue-0” #16 prio=6 os_prio=0 tid=0x000000003f1a3000 nid=0x1388 waiting on condition [0x0000000058e8f000]
      java.lang.Thread.State: WAITING (parking)
      at sun.misc.Unsafe.park(Native Method)
      – parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
      at java.awt.EventQueue.getNextEvent(EventQueue.java:554)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:187)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

      “AWT-Windows” #14 daemon prio=6 os_prio=0 tid=0x000000003f194000 nid=0x29a4 runnable [0x00000000528ce000]
      java.lang.Thread.State: RUNNABLE
      at sun.awt.windows.WToolkit.eventLoop(Native Method)
      at sun.awt.windows.WToolkit.run(WToolkit.java:315)
      at java.lang.Thread.run(Thread.java:748)

      “AWT-Shutdown” #13 prio=5 os_prio=0 tid=0x000000003f191000 nid=0x37c4 in Object.wait() [0x000000004c4cf000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at sun.awt.AWTAutoShutdown.run(AWTAutoShutdown.java:295)
      – locked (a java.lang.Object)
      at java.lang.Thread.run(Thread.java:748)

      “Java2D Disposer” #12 daemon prio=10 os_prio=2 tid=0x000000003f190000 nid=0xbb0 in Object.wait() [0x00000000460cf000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:144)
      – locked (a java.lang.ref.ReferenceQueue$Lock)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:165)
      at sun.java2d.Disposer.run(Disposer.java:148)
      at java.lang.Thread.run(Thread.java:748)

      “RMI TCP Accept-0” #11 daemon prio=5 os_prio=0 tid=0x0000000038c34000 nid=0x3744 runnable [0x000000003f05f000]
      java.lang.Thread.State: RUNNABLE
      at java.net.DualStackPlainSocketImpl.accept0(Native Method)
      at java.net.DualStackPlainSocketImpl.socketAccept(DualStackPlainSocketImpl.java:127)
      at java.net.AbstractPlainSocketImpl.accept(AbstractPlainSocketImpl.java:535)
      at java.net.PlainSocketImpl.accept(PlainSocketImpl.java:189)
      – locked (a java.net.SocksSocketImpl)
      at java.net.ServerSocket.implAccept(ServerSocket.java:545)
      at java.net.ServerSocket.accept(ServerSocket.java:513)
      at sun.management.jmxremote.LocalRMIServerSocketFactory$1.accept(LocalRMIServerSocketFactory.java:52)
      at sun.rmi.transport.tcp.TCPTransport$AcceptLoop.executeAcceptLoop(TCPTransport.java:405)
      at sun.rmi.transport.tcp.TCPTransport$AcceptLoop.run(TCPTransport.java:377)
      at java.lang.Thread.run(Thread.java:748)

      “Service Thread” #9 daemon prio=9 os_prio=0 tid=0x0000000018508800 nid=0x2b94 runnable [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “C1 CompilerThread2” #8 daemon prio=9 os_prio=2 tid=0x00000000184c9800 nid=0x1054 waiting on condition [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “C2 CompilerThread1” #7 daemon prio=9 os_prio=2 tid=0x000000001848a800 nid=0xfb8 waiting on condition [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “C2 CompilerThread0” #6 daemon prio=9 os_prio=2 tid=0x0000000018481800 nid=0x3414 waiting on condition [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “Attach Listener” #5 daemon prio=5 os_prio=2 tid=0x0000000018480000 nid=0x3bdc runnable [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “Signal Dispatcher” #4 daemon prio=9 os_prio=2 tid=0x000000001847f000 nid=0x3a30 waiting on condition [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “Finalizer” #3 daemon prio=8 os_prio=1 tid=0x0000000018461800 nid=0x2588 in Object.wait() [0x000000002582e000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:144)
      – locked (a java.lang.ref.ReferenceQueue$Lock)
      at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:165)
      at java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:216)

      “Reference Handler” #2 daemon prio=10 os_prio=2 tid=0x0000000018458800 nid=0x2720 in Object.wait() [0x000000001f42f000]
      java.lang.Thread.State: WAITING (on object monitor)
      at java.lang.Object.wait(Native Method)
      at java.lang.Object.wait(Object.java:502)
      at java.lang.ref.Reference.tryHandlePending(Reference.java:191)
      – locked (a java.lang.ref.Reference$Lock)
      at java.lang.ref.Reference$ReferenceHandler.run(Reference.java:153)

      “main” #1 prio=5 os_prio=0 tid=0x0000000000ec0000 nid=0x363c waiting on condition [0x0000000000000000]
      java.lang.Thread.State: RUNNABLE

      “VM Thread” os_prio=2 tid=0x0000000018431800 nid=0x3030 runnable

      “GC task thread#0 (ParallelGC)” os_prio=0 tid=0x0000000000ed8800 nid=0xa58 runnable

      “GC task thread#1 (ParallelGC)” os_prio=0 tid=0x0000000000eda800 nid=0x39cc runnable

      “GC task thread#2 (ParallelGC)” os_prio=0 tid=0x0000000000edc000 nid=0x2410 runnable

      “GC task thread#3 (ParallelGC)” os_prio=0 tid=0x0000000000edd800 nid=0x2608 runnable

      “VM Periodic Task Thread” os_prio=2 tid=0x0000000038af7800 nid=0x16c waiting on condition

      JNI global references: 3920

      Heap
      PSYoungGen total 560128K, used 86850K [0x00000000d5580000, 0x0000000100000000, 0x0000000100000000)
      eden space 482816K, 13% used [0x00000000d5580000,0x00000000d9759b68,0x00000000f2d00000)
      from space 77312K, 25% used [0x00000000fb480000,0x00000000fc776d80,0x0000000100000000)
      to space 108032K, 0% used [0x00000000f2d00000,0x00000000f2d00000,0x00000000f9680000)
      ParOldGen total 455680K, used 252644K [0x0000000080000000, 0x000000009bd00000, 0x00000000d5580000)
      object space 455680K, 55% used [0x0000000080000000,0x000000008f6b92d0,0x000000009bd00000)
      Metaspace used 117814K, capacity 127118K, committed 127232K, reserved 1159168K
      class space used 15582K, capacity 18360K, committed 18432K, reserved 1048576K

    • 9i is so old I have zero percent confidence the 19c or 21c jdbc drivers we’re using were ever tested on that platform.

      Export that database, build a new 19c database, import it, and use that for your queries.

  16. Jeff,
    I wanted to know what grants are needed to view the SQL of the views in SQL Developer.
    According to my DBA, we in development do not have access to the “select_catalog_role” role.

    Would there be another way to gain access?

    [Sorry for the English of Google Translate]
    Thanks.

    • I’ve not been able to get a list from R&D. I have an approved ER for the database such that a user having SELECT/READ/EXECUTE privs on an object should be able to get the DDL for that object.

      In the meantime, if your DBA won’t give you basic privs in your development environment, you’ll need to ask them to provide these DDL scripts for you.

      This is one of many reasons I recommend each developer have their own oracle db running on your machine where they can do anything they want.

  17. Good Morning,

    I am looking to create two URLS using one DB. The setup is, an off site Oracle server already exists. One Admin server to run WebLogic and manage other servers. It is up and running. I have a managed server connecting to the ORDS DB by having run ords.war, and configured it to connect to the off site Oracle DB, and it all runs fine. This server is setup for public users to access and enter data.

    The goal now is to have a second server that connects to the same DB on the same Oracle server, but will be used solely for internal use.

    My plan is to simply add the second server to the Admin server, then run the ords.war file. However I am unsure exactly what to do in regard’s to running ords.war. Can I use the same DB accounts, can I use ords.war or do I need to call is maybe ords2.war, I rally am not sure how to proceed on that part?

    Feel free to ask me questions for clarification and I will do my best. Thanks

    • Right, so you can have two ords running on the same machine even, connected to the same database. They would need to be listening on separate ports…

      You can have two ords on different machines, in even different networks, connected to the same database.

      You just need them to share a repo – so use the same version in both places, and you’ll need to know the password for ORDS_PUBLIC_USER so they can be the same for both installs. You can create a separate user for the pool if necessary…but that’s not necessary based on what you’ve described above.

      Reading your email again…you have a single WLS install, and you want to deploy 2 separate ords.war files for it? Or you have 2 different WLS deployments?

    • If this is a duplicate post my apologies, but I dont see my response posted, so I am not sure if it is posting.

      I have an Adminserver

      Then I have a managed server wls_server1 that has the ords.war file and i.war file deployed
      url example: http://server1:7004/ords (this server is up and running fine)

      I am building a new WLS managed server, so say we will call it:
      wls_server2, deploy the ords.war and i.war file but name them ords2 in the Deployment of weblogic
      a url of say: http://server2:7005/ords

      This should work?

      I recently installed wls_server1 so I have all the accounts passwords etc for the existing accounts.

      We have several envoroments wherby we have an AdminServer and four or five managed servers, so i am familiar with deploying etc.

      Ex:
      AdminServer
      wls_server1 ords.war amd i.war
      wls_server2 ords2.war and i2.war
      wlse_server3 ords3 ….

      However in those cases each ords install and server connects to a different DB.

      This instance I am trying to two separate urls and connecting to the same DB, one the port:7004 is a public facing app, and the port 7005 will be internal.

      Again I apologize is this is a duplicate post, and thank you

  18. John Chase Reply

    SQLDeveloper 19.2.1.247 in an enclave network (no Internet access) with Windows 10 VDI clients where I have zero control (at least, not without a lot of red tape) on the environment. In trying to add a new connection, I click on the down arrow next to the big green plus and choose “New Database Connection” . . . nothing happens. Hm. So I exported my connections, used that JSON to gen up my new connection, and imported it in. Now, I need to set the password, so I right-click on the connection and choose “Properties” . . . nothing happens. I tried it on one of my existing connections that works (both “Connect” and “Open SQL Worksheet”) . . . nothing happens. I also noticed the “Reset Password” menu option is gone. Not grayed-out, just not there at all. On a whim I thought about thin vs. thick client, and played around with that (Preferences/Database/Advanced), but it made no difference. I have dumped out of the VDI and back in a couple times. Still no change. Is it trying to hit the Internet and can’t get there? Is it something Java (or Path) related? If I could use ProcMon, I might be able to at least guess at what’s going on in the background, but my hands are tied.

    • John Chase

      Got it. I had disabled a bunch of extensions to make loading faster. Guess something behind the connections dialog requires the oracle.sqldeveloper.ssh extension. Once I took that out of the exclude list, everything is back to normal.

    • Yes, I have one WLS Admin server, that currently one ords.war file deployed to one server.

      AdminServer

      managed servers:
      wls_server_1 where I have ords.war, and i.war file deployed
      url might be https://server1:7004/ords

      The additional managed server would be:
      wls_server_2: deployed files: ords2.war i2.war I have to rename the war files as they all sit on the same Admin server
      url might be https://server2:7005/ords

      The problem, or question is, to re-use the same DB and accounts as you mentioned.

      As it is a second server, I could also stick with /ords and there is only one install on the server

      My initial thought was that I could use one managed server say, wls_server_1 and keep the primary ords install, but then add a second ords2 to the same server. But that would not allow for separate url’s or ports I believe.

    • Not sure my last comment made it through

      I have:
      Adminserver

      a Managed server:
      wls-server_1 with ords.war and i.war file deployed
      url something like: http://server1:7004/ords

      Adding a second server
      wls_server_2 deploying ords2.war amd i2.war as the Admin server needs to seperate the war files with a name
      url might be: http://server2:7005/ords

      I have build server with multiple managed servers, but all those connect to different DB’s. The trouble I am wrapping my head around here and that both managed servers will use the same DB.

      Thank you

  19. Jeff,
    We can go straight to 20.2? Our Apex is at 5.1.4, database is at 18c. The main concern is the compatibility amongst all these components. Now ORDS is upgraded, we are planning to upgrade Apex to 19.1, the database to 19c. Are we safe to go to 20.2 for both Apex and ORDS in 19c database? I got mixed messages in researching for the best version to go to (together with input from colleagues), I’d prefer to go to the latest versions on everything, but don’t want to get burnt if there are issues not discovered yet.

    Thanks a lot!

    • Hi Jeff,

      So I only keep the version ORDS 18.2 running, apex at 5.1.4 in the database, when we try to access apex applications, we got 403 Forbidden error. Could it be a compatibility issue? Will upgrade ORDS to a newer version resolve the issue? Thanks a lot!

  20. Update: I was able to install the new version ORDS 18.2 and deployed on our tomcat server. We have two instances running 2.0.10 and 18.2. Will clean out the old one when the developers are ready. Thanks all the same.

  21. Hi Jeff,
    Can I configure two ORDS instances in one database? We are planning to upgrade our current ORDS from 2.x to 18.2, our developers use Apex heavily for applications development, so when I do the install I must choose the option of use Application Express for additional configurations? I can have two urls one pointing to apex (it is working well now), the other to ords (the 18.2 version). Is that achievable? Our apex version is 5.1.4. Planning to upgrade to 19.1 in a few weeks.

    Thanks a lot!

    • Why move to 18.2? That’s already 3+ years old…

      Also, I’m not seeing why you would need 2 different ORDS?

  22. Hello Jeff

    I have been having trouble setting up debugger in SQL developers.
    I keep getting this error:
    Connecting to the database CUDEV_PPRD.
    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘134.117.xxx.xx’, ‘4000’ )
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68

    So I ran this
    DECLARE
    id Varchar2(30);
    BEGIN
    id := DBMS_DEBUG.initialize(‘134.117.107.98.10607030547406’,0);
    DBMS_DEBUG.debug_on(TRUE);
    END;

    This causes my SQL developer to freeze.
    Please advise on how to set up.
    HDean
    ORA-06512: at line 1
    Process exited.
    Disconnecting from the database CUDEV_PPRD.

    • Did you read my debugger posts? If you have version 20.2 or 20.4, set debugger to dbms_debug in preferences.

  23. Chukwudi Awaibe Reply

    Hi Jeff,

    Please I want to create a batch file out of this code that I can schedule with Task Scheduler

    sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.

    • Chukwudi Awaibe

      Yes, the connection in SQLdev have the password included

    • Chukwudi Awaibe

      I need to schedule it to run monthly or at specified times. Thats why I am using SDcli cart

    • Chukwudi Awaibe

      Its prompting for DB password when I run sdcli cart export -cart on cmd. Under a windows user. The SQLDev UI connection is a DB user

    • Chukwudi Awaibe

      How can I create this script –

      sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.

      on a batch file. Maybe I can specify a username and password in the batch file and schedule it with a task scheduler

    • Chukwudi Awaibe

      The windows user is the same windows user I am running sqldev (GUI) with? but the db connection users is different. I am using a DB user to connect to the database.

    • Chukwudi Awaibe

      Please How do I place the code on a batch file. What code can I use. START does not work

      C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin>sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.csv

      I get the error when I just place the code on a batch file
      ‘C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin’ is not
      recognized as an internal or external command,
      operable program or batch file.

    • Chukwudi Awaibe

      The sdcli cart code exports the data to a CSV file successfully when run from CMD. though with db password prompt. But I need to schedule it to run monthly using a bat file

    • Chukwudi Awaibe

      Hi Jeff,

      Thanks for the support. I was able to eliminate the password prompt from CMD by clicking “save password” in the db connection. I used the code below in a batch file to schedule it on windows scheduler

      cd C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin
      sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.csv

      The exported data comes with the dbname.”Viewname” as the worksheet name. Is there a way to change this default worksheet name in the cart configuration to a friendly name.

  24. Is there a way of directing output from a query in SQLDeveloper to a dynamically named file on disk?

    I am required monthly to produce these CSV files, each named after their month; and I have not been able to programmatically set the name of the file for the SPOOL command.

    On the same topic:

    Is there a way of saving the output of a script to disk? Currently I am using dbms_output.put() and then manually saving the output to a file, but it is messy and error-prone.

  25. Hi Jeff – Your BLob article tells about loading and looking at imagery with blobs in SQLDeveloper but How do I remove a blob from a row. I don’t want to remove the entire column, just the one blob from a row.
    the row contains things like UUID, filename, description, image data as a blob, and a few other things. for my testing, I put in an image and now I want to remove just that one image from my table. I’ve googled it but have not found the answer. Seems like this should be easy and straight forward but it is stumping me.
    Thanks!

    • I show how to do that, or one way how to do that, here.

      Good or bad, I have way more than 1 ORDS+BLOB posts…

    • Marie Alm

      Hi again – I did an update on that row and set the blob cell to null. It seemed to work. I guess I didn’t need to write you but I didn’t find that way to do it until after I had written you my comment. If I need to do something else to, say, reclaim space, please do let me know. — Thanks.

Reply To hdean Cancel Reply