I can't still compile package bodies from source file

Trevis

Hi all,

This extension looks great. Being able to connect using proxy users was pretty cool and I am using that.

However, the inability to compile my source files directly from VSCode makes the extension not that useful as it could. I am used to code within VSCode and I have a "VSCode Task" that runs SQL*Plus and compile the file I am working on. But I always wanted to use some more native extension to achieve this, so I thought I could use this extension but I can't.

When I open a source file that contains my package specification I can "right-click" and choose "Execute all" and it kinda works. It opens a new tab with my entire code in there and if I scroll to the very bottom I can see the result. The downside is that there is an error it won't highlight the line... But when I try the same steps with source files having the package body it simply can't compile. Looks like it handles the spaces and CRLF in a different way because in the new tab it open I can see my code but with no spaces between keywords and CRLF are somehow different than what I have in my original code.

Anyways, I see a big potential for this extension when we're finally able to code and compile without having to leave VSCode to compile in SQL Developer or SQL*Plus.

Stay safe anyone.

Christian.Shay -Oracle

Hi,

I'm pretty sure you and I debugged this issue over email. If that was not you, please let me know.

You were hitting a bug in our parser that is fixed in the upcoming 19.3.3 release. Sorry about that.

Thanks,

Christian

Trevis

Yup, it was me.

Great thanks.

Christian.Shay -Oracle

Hi, this issue is fixed in the new 19.3.3 version. Please try it out and let me know what you think.

Ray007

How can I open a package file and get the control-d option instead of needing to use control-r to execute all? control-r works differently it seems in that it has define set to & while control-d does not. I currently have to right click on the object in DB explorer and choose edit; then copy/paste the code in the .sql file over to that window to compile....

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16780770#Comment_16780770

Hi Ray,

If you choose Edit from Oracle Explorer, we:

1) Download the plsql package to a temp file and open it

2) Add "Save To Database" menu item which compiles the package back to the database, with any errors showing up in the Problems tab.

You can use File-Save in the VS Code menu to save this temp file to a more permanent one. When you open that file, you will need to Execute All like you do with ordinary scripts.

You can change shortcuts however you want, by doing the following:

To create keyboard shortcuts (keybindings) or to modify existing ones, go to the File menu in VS Code and select Preferences->Keyboard Shortcuts

In the search text box, enter a portion of the name of the extension whose shortcut you would like to modify, for example "oracle".

A list of commands and their shortcuts will appear. Click the pencil icon on the left side of a row to edit a shortcut, or + (plus sign) to create a new one.

When prompted, enter the key combination and if it is not being used by another extension, press enter. If it is being used by another extension, press escape and try again.

To delete a shortcut, right click on the shortcut and select Remove Keybinding

Ray007

Thanks but I'm not caring so much about a specific shortcut. I'm wanting to open a file and get the "save to database" feature which seems a bit different from the "execute all" feature:

• errors show up in the problems tab
• substitution variables are disabled so code like htp.print("This & that") doesn't ask me for a value for amp. :/

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16783513#Comment_16783513

Well, the good news is we will be supporting the Problems tab with ordinary scripts in the next (19.3.4) realease.

The substitution variable issue is interesting. Have you ever successfully done that from SQL*Plus or SQL Developer/SQL Cl? I have to look into whether you can escape it.

Ray007

That sounds awesome. Thanks Christian. In other environments like SQL*Plus you need to issue "set define off" before compiling/running the script.

In other environments it seems to be smart depending on window type. For example All Around Automations PL/SQL Developer will automatically not have substitution variables on when working in a programming (package/trigger/function/etc) window. But will behave like SQL*Plus in a command window. I believe Oracle SQLDeveloper does the same.

In VSCode with this plugin, you seem to be clever as well. If I edit DB source, substitution variables are turned off when compiling (control-d). But if I open a sql file, I need to manually issue a "set define off" or the same code will ask for every ampersand in my code.

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16784958#Comment_16784958

Thanks for the extra info, Ray. I will add this to our list of future features for evaluation.

Christian.Shay -Oracle

https://community.oracle.com/tech/developers/discussion/comment/16783513#Comment_16783513

Hi Ray,

We just released version 19.3.4 which adds support for the Problems Panel for scripts. Could you please try it out and let me know what you think?

Ray007

I absolutely love the support for Problems Panel for compile errors. That's like 80% there. :)

But substitution variables are still on by default. :( To reproduce:

1. connect to a database
2. right-click on a package in the "Oracle Exporer" view
3. select "Open Package Body"
4. note that at this point, you are working on some temporary .plsql file. The only option if you right-click on the editor background is "Save To Database".
5. make a change to the package. For example add a comment with "-- This   better be good."
6. save this to the database with "Save to Database" and all is good still. 😊
7. save the file to your filesystem somewhere else (probably your git repo)
8. now right-click on the background of the editor. You can now "Execute SQL" and "Execute All". But "Save to Database" is gone!!! 🤨
9. let's just compile this to the database again without making any changes. Choose "Execute All"
10. A dialog pops up asking you for your Input Parameters "Enter value of nbsp:". 😫

Ray007

The workaround is a little ugly. If you have & in your code.

• open your code file
• type "set define off" into it anywhere.
• highlight "set define off" and run "Execute SQL"
• delete "set define off"
• make your changes to your code file. Then choose "Execute All" to compile into DB.
• enjoy the fact that your bugs are now displayed in the "Problems" tab. 🤗

Christian.Shay -Oracle

Thanks Ray. We will definitely keep this one in mind as we plan the next release. Hopefully we can do something about it.