search

Trivadis / plsql-formatter-settings

PL/SQL & SQL formatter settings based on the Trivadis PL/SQL & SQL Coding Guidelines
Apache License 2.0
131 stars 46 forks source link

Patching SQL Developer breaks Script Output tab feedback of direct SQL statement execution #268

Closed DavidotNet closed 6 months ago

DavidotNet commented 6 months ago

Patching causes the reversible issue described below.

After completing the patching instructions on README page, within a worksheet, executing any Insert, Update, or Delete statement directly; not inside of a begin end; script block; causes the following to occur.

  1. At top of the IDE window, click 'View' then 'Task progress'
  2. Paste or type any Insert, Update, or Delete statement into the worksheet; 3 Press F9 to execute the statement
  3. A red exclamation point appears on the '(!) Script output' tab.
  4. No feedback e.g. 1 row inserted; 1 row updated; 1 row deleted (statements do work successfully in SQL*Plus command prompt)
  5. In the Task Progress window, you see 'ScriptRunner Task (failed)

Restore functionality as before patching. Close the IDE. Reverse the copy / rename steps of dbtools-common.jar
Restart the IDE Repeat the steps 1,2,3 and you'll see #5 emits the feedback 1 row inserted, etc. No more #4 or #6 'ScriptRunner Task (failed)

A complete rollback requires restoring the IDE settings changes as described on the README page.

PhilippSalvisberg commented 6 months ago

Thank you for reporting this issue. I know that using an incompatible version of dbtools-common.jar may cause issues. I'm aware of some minor effects in the grid result for CLOB columns, but besides that, it works without issues on my side. I use SQL Developer 23.1.1.245 on macOS with a dbtools-common.jar from SQLcl 23.3.0 and I cannot reproduce your findings. Here's a screenshot:

image

Can you please provide more information about the versions you are using, maybe the OS platform and the exact version of the JAR file you use to patch SQLDev? It would also help if you could provide a screenshot similar to to one above. TIA.

DavidotNet commented 6 months ago

Reproducible. Do you know whether there is a diagnostic log file and the path/filename?

Original file after installation. No issues. Directory of C:\SQLDeveloper\sqldeveloper-23.1.1.345.2114-x64\sqldeveloper\sqldeveloper\lib

01/07/2024 10:06 AM 8,470,098 dbtools-common.jar

Extracted from SQLCLI Zip download. Directory of C:\Oracle\SQLcl23.3\sqlcl-23.3.0.270.1251\sqlcl\lib

01/14/2024 01:24 PM 9,874,466 dbtools-common.jar

Showing file size as compared to SQLDeveloper original & SQLCLI Directory of C:\SQLDeveloper\sqldeveloper-23.1.1.345.2114-x64\sqldeveloper\sqldeveloper\lib

01/14/2024 01:24 PM 9,874,466 dbtools-common.jar 01/07/2024 10:06 AM 8,470,098 dbtools-common.original.jar 01/14/2024 01:24 PM 9,874,466 dbtools-common.SQLCLI.jar

[cid:dc8f5119-421b-48dc-bec0-f34eaaff0abc]

[cid:25aa928c-7496-429c-a97b-b3d01b120b13]

From: Philipp Salvisberg @.> Sent: Wednesday, January 17, 2024 2:26 AM To: Trivadis/plsql-formatter-settings @.> Cc: DavidotNet @.>; Author @.> Subject: Re: [Trivadis/plsql-formatter-settings] Patching SQL Developer breaks Script Output tab feedback of direct SQL statement execution (Issue #268)

Thank you for reporting this issue. I know that using an incompatible version of dbtools-common.jar may cause issues. I'm aware of some minor effects in the grid result for CLOB columns, but besides that, it works without issues on my side. I use SQL Developer 23.1.1.245 on macOS with a dbtools-common.jar from SQLcl 23.3.0 and I cannot reproduce your findings. Here's a screenshot:

image.png (view on web)https://github.com/Trivadis/plsql-formatter-settings/assets/3883171/35baa84d-fc31-4796-bae2-0851598519bd

Can you please provide more information about the versions you are using, maybe the OS platform and the exact version of the JAR file you use to patch SQLDev? It would also help if you could provide a screenshot similar to to one above. TIA.

— Reply to this email directly, view it on GitHubhttps://github.com/Trivadis/plsql-formatter-settings/issues/268#issuecomment-1895228091, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQ7HBJ2HN2PB4WJDY57FHBLYO54LFAVCNFSM6AAAAABB5YJPNOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJVGIZDQMBZGE. You are receiving this because you authored the thread.Message ID: @.***>

PhilippSalvisberg commented 6 months ago

@DavidotNet I cannot reproduce the problem with the information you've provided. And I'm using the dbtools-common.jar with a file size of 9'874'466 bytes. Most probably we are missing some information to reproduce that problem. It's caused by the JAR switch. So much is clear. And you seem to use the right versions (SQLDev, SQLcl, JAR files on Windows x64).

Can you please provide the content you are trying to execute in the worksheet? - The easier the example, the better. The goal is to reproduce it on my side.

Can you please provide a screenshot showing the statements you were executing and the relevant windows with the red exclamation mark and maybe its mouse-over text if any?

DavidotNet commented 6 months ago

I just discovered that by double clicking on the 'ScriptRunner Task Failed' 'item' in the 'Task Progress' pane, the error details are provided.

[cid:0b75b13f-9972-43ee-9f79-2794171ec501]

From: Philipp Salvisberg @.> Sent: Thursday, January 18, 2024 12:02 AM To: Trivadis/plsql-formatter-settings @.> Cc: DavidotNet @.>; Mention @.> Subject: Re: [Trivadis/plsql-formatter-settings] Patching SQL Developer breaks Script Output tab feedback of direct SQL statement execution (Issue #268)

@DavidotNethttps://github.com/DavidotNet I cannot reproduce the problem with the information you've provided. And I'm using the dbtools-common.jar with a file size of 9'874'466 bytes. Most probably we are missing some information to reproduce that problem. It's caused by the JAR switch. So much is clear. And you seem to use the right versions (SQLDev, SQLcl, JAR files on Windows x64).

Can you please provide the content you are trying to execute in the worksheet? - The easier the example, the better. The goal is to reproduce it on my side.

Can you please provide a screenshot showing the statements you were executing and the relevant windows with the red exclamation mark and maybe its mouse-over text if any?

— Reply to this email directly, view it on GitHubhttps://github.com/Trivadis/plsql-formatter-settings/issues/268#issuecomment-1897804763, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQ7HBJ7JWNKW3L2MA4YQHH3YPCUIBAVCNFSM6AAAAABB5YJPNOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJXHAYDINZWGM. You are receiving this because you were mentioned.Message ID: @.***>

PhilippSalvisberg commented 6 months ago

[cid:0b75b13f-9972-43ee-9f79-2794171ec501]

No idea what this is.

Again, without information to help me reproduce the issue I cannot help. I asked several times for a screenshot and the script that is causing this issue. Please add a comment via web UI and not as e-mail answer to do that. Thanks.

DavidotNet commented 6 months ago

Outlook-bydopzwk

Attached two images showing statements to reproduce the exception. One image includes a StackTrace showing the root cause NoSuchMethodError. So the failure occurs for any 'statement'. Seemingly the named method is not present in the Windows version of SQLcl dtools-common.jar Both Images contain the statement I used to reproduce the issue.

My operating system. image Outlook-bydopzwk Windows specifications Edition : Windows 11 Pro Version : 23H2 OS build : 22631.3007 Experience : Windows Feature Experience Pack 1000.22681.1000.0

PhilippSalvisberg commented 6 months ago

Seemingly the named method is not present in the Windows version of SQLcl dtools-common.jar

There is no Windows version of SQLcl. The dbtools-common.jar is platform independent. I cannot reproduce the problem on my Windows 11 machine (clean install). Here's the screenshot of a test using the JAR file from SQLcl 23.3.0.:

image

I checked the content of dbtools-common.jar regarding the method bestPracticeCheck in the class oracle.dbtools.raptor.newscriptrunner.PLSQL

IMO the error stack produced in your environment is only possible if classes from both JAR versions are available on the class path. Therefore I suggest deleting all cached files and directories in the following directory after closing SQL Developer:

%APPDATA%\SQL Developer\system23.1.1.345.2114\system_cache
DavidotNet commented 6 months ago

I followed your suggestion and removed the directory as follows and opened SQLDeveloper. I executed a few statements and can no longer reproduce the failures. Closing this issue. Thank you.

cd %APPDATA%\SQL Developer\system23.1.1.345.2114 rd /s /q system_cache