Skip to Main Content
  • Questions
  • SQL Developer drops connection to Oracle Database 19c when trying to edit PL/SQL code

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Yury.

Asked: November 03, 2020 - 1:14 pm UTC

Last updated: November 23, 2020 - 6:19 am UTC

Version: 17.2.0.188, 19.1.0.094

Viewed 1000+ times

You Asked

Dear Colleagues,

I failed to find solution of my problem in the Internet and kindly ask for your assistance.

Production database was migrated from Oracle 11 to Oracle 19. Middleware and the entire production system work fine. Allround Automations' PL/SQL Developer works as usual. However, Oracle SQL Developer drops connection any time I try to open a PL/SQL object (function, procedure, trigger, package) for viewing/editing. Select from user_source works fine. I have v.17 and v.19 SQL Developers in two PCs, the first one informs that the connection was reset, the second one pops up login pannel. In fact they both drop connection. Both can select from dba_source view, but the actual SQL query sent to the database closes the connection:

 WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE 
 WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
 SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)  
 WHERE origin_con_id = max_orig 
 ORDER BY LINE
;


Parts of the above query work fine, not the entire query.

Please advise.

Sincerely,
Yury

PS: Tried to create a test procedure from script. Createds successfully, same behaviour when try to view/edit.

and Connor said...

Couple of things I would recommend

1) Use the most recent version of SQL Developer. The SQL Dev team constantly improve the product

2) See if the problem can be reproduced when using the Oracle client. It may be an issue in the jdbc driver.

(Tools => Preferences => Database => Advanced => Use OCI)

If you still have the problem, jump onto the SQL Dev forum and the dev team can guide you through getting a stack trace to be used to investigate more deeply.

https://community.oracle.com/tech/developers/categories/sql_developer

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yury Kirchin, November 20, 2020 - 12:20 am UTC

Dear Connor,

Thank you very much for your recommendations and sorry for delay with my reply: i was waiting for some free time to try these. However, when our administrator came and I tried to show him all the mess - everything started working fine!! He firmly says nothing was done to the server, I firmly say nothing was done to the clients - it did not work when I wrote you, and it works perfectly now. Sorry for disturbing, however there is something "under the carpet".
Connor McDonald
November 20, 2020 - 4:03 am UTC

hee hee.... I know that feeling.

Thanks for getting back to us.

Yury Kirchin, November 21, 2020 - 3:14 am UTC

Hi Connor,

Thank you very much, you helped me not once. Please ping me over private e-mail to my Yury.Kirchin@gmail.com - I have a great Russian song clip for you and for all your team. On Oracle. And many, many thanks!
Connor McDonald
November 23, 2020 - 6:19 am UTC

Emails can be sent to asktom_us@oracle.com

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library