Enable configurable usage of delimited identifiers in package (body) editor

On Oracle currently the code in the package (body) editor always adds the configured delimiters in the CREATE OR REPLACE ... line, even if their usage is not activated in the database properties. Maybe add a setting to enable/disable their usage in the package code. And when already there, maybe also add a setting to add a configurable text to the package. This would come in handy when exporting (copy/pasting) code from the editor to files which will then later be processed by sqlplus or else and require a trailing "/"-line on each code/command.

Hi, Can you please clarify a bit? > On Oracle currently the code in the package (body) editor always adds the configured delimiters in the > CREATE OR REPLACE ... > line, even if their usage is not activated in the database properties. Maybe add a setting to enable/disable their usage in the package code. Are you saying you get the anonymous block identifiers ("--/" and "/" by default) inserted into the code in the editor shown in the Object View when you select a package body? If so, please post (or send us) as screenshot since that is not supposed to happen and I cannot reproduce it. Or are do you mean that the anonymous block identifiers are inserted in the script generated by Export Database when choosing the SQL format? If so, that is something we will look into and make more configurable (e.g. select between the anonymous block identifiers, the MySQL DELIMITER statement or the DbVisualizer @delimiter command to deal with delimiters within a code body). With "not activated in the database properties", do you mean that use of delimiters is not enabled in the DbVisualizer Tool Properties? If so, I'm not sure which property you refer to. A screenshot would be a great help. If it's an Oracle database property you mean, please specify which one, ideally with a URL to a description of the property. > And when already there, maybe also add a setting to add a configurable text to the package. This would come in handy when exporting (copy/pasting) code from the editor to files which will then later be processed by sqlplus or else and require a trailing "/"-line on each code/command. Can you please give an example of what this would look like? And again, I'm unclear on if you're talking about the package body editor or the SQL generated by Export. Best Regards, Hans

Ok, maybe I expressed myself a bit unclear in the first posting. In my Oracle database properties there is a section "Delimited Identifiers" where the "Begin Identifier" and "End Identifier" are both set to double quotations ("). Below are the two unchecked options for the use of delimiters, "Scripting" and "Auto Completion/Query Builder". Yes, I'm talking about the Object View/Package Editor when I select a Package or Package Body in the left-hand tree. But what I'm meaning is, that although the code was generated in the database (with other tools) like that: ... CREATE OR REPLACE PACKAGE BLAH.BLUB ... END BLUB; / it gets displayed in DbVis like that: ... CREATE OR REPLACE PACKAGE "BLAH"."BLUB" ... END BLUB; Note the added "" and the missing /. What I meant is, that I could not find an option to turn off the adding of "". As for the /, I know that it's interpreted as a command delimiter in oracle and therefore is logically not part of the code. That's why I've been asking for an additional option where I can enable or give a string which is added to the display of the package in the package editor. (And of course, removed before execution/compilation via DbVis). The reason behind these is, that when different developers share the same codebase (SVN, etc) but use different tools (TOAD, SQLDev, DbVis, ...) they have to pay extra attention to those little pitfalls. Supporting a more SQLDeveloper-like presention of the code would help with the usage of DbVis in such shared environments. I'm sorry, that I didn't provide screenshots yet, I've never done them with Linux yet, still have to figure that out. Anyway, did above description make it a bit clearer or would you still prefer screenshots?

Hi, Okay, then I believe I get it. No screenshots needed. Regarding the delimited identifiers, this is an area that is giving us all kinds of grief no matter what we do. If we always use them in generated SQL, they cause errors when the use of delimited identifiers can be turned off at the database level (possible for some databases, like DB2). If we never use them, there are obviously problems with identifiers containing special characters. And if we make it configurable, many users report one of the two problems above depending on how they have configured DbVisualizer. The compromise that has turned out to be the lesser of two evils is to make it configurable for the features where SQL is generated and then seen/used by the end user (Scripting, Auto Completion and Query Builder) but always (with some exceptions for databases where its use can be disabled) use them for SQL that is used "behind the scene" (executed for various Object View tabs, etc). The code editor falls somewhere in between, because we need to send the code as-is to the database (parsing can be really tricky here because there may be syntax errors in the code that are better handled by the database engine). We have decided to always use delimiters to avoid problems with special characters in identifiers. The delimiters, after all, are just that: BLAH.BLUB is equivalent to "BLAH"."BLUB", except that the latter works even if the identifier use mixed case and/or special characters. Regarding the "/" statement delimiter, it needs to be paired with a "/--" start delimiter if you want to run the CREATE statement in SQL*Plus, so if we added "/" at the end, we would also need to add "/--" at the beginning (or whatever character sequences you have configured for the anonymous SQL block identifiers). While we could add a property setting for this, I wonder if the scenario you describe isn't better served by the Export Schema feature, or new Export features? Export Schema (using the SQL format) already puts the CREATE PACKAGE BODY statement within "/---" and "/" delimiters. We added an Export Table action in 7.0. It would be quite easy to add an Export Package Body (and similar for other code objects) in a future release. What do you think? Sorry for the long rant, but I hope that when you understand the background, we can together come up with the best solution for your scenario. Best Regards, Hans

Ok, I already expected it to be complicated but seems, I underestimated it quite a bit. For me it would be perfectly ok if it's easier or more consistent for you to realize this feature through an Export Schema or something else :) My intention was just to smooth out the code-differences generated by various tools to minimize re-working the code before committing them to a shared repository. And since I doubt, that the developers of TOAD or SQLDev would even consider reading my request past the subject I posted here ;) If it's too complicated or expensive to integrate thi I can understand that. For me personally this is not one of the killer-features but rather a nice-to have, so no worries. Some more stuff I recognized during playing with this stuff: - is it intentional, that the identifiers in the CREATE-line are ""-ed but at the END-line are not? - the EXPORT SCHEMA functionality currently does not add the schema-name itself to the exported packages. Could an option "add schema name" in the export-dialog make sense here?

Hi, > If it's too complicated or expensive to integrate thi I can understand that. For me personally this is not one of the killer-features but rather a nice-to have, so no worries. Great, thanks for understanding. I will add as an RFE to add Export actions for individual objects in addition to the Export Schema action. This should help a bit with your scenario. > - is it intentional, that the identifiers in the CREATE-line are ""-ed but at the END-line are not? The END-line may optionally contain a "label", but it is not interpreted by the database as an object identifier (even though the label text may be the object name, by convention). Hence, DbVisualizer leaves it as-is. > - the EXPORT SCHEMA functionality currently does not add the schema-name itself to the exported packages. Could an option "add schema name" in the export-dialog make sense here? There is an option for this already, in Tool Properties, in the Qualifiers category for the database. In 7.0, enabling qualifiers for Scripting also enables it for Export. In the next feature release, there will be a separate setting for Export. Best Regards, Hans