Program Window applies keyword/reserved word formatting and syntaxt coloring to text in REM comments

[Tim Scott]

Our application has many scripts TO run IN SQL*Plus during installation / upgrade / patching. We retain a MOD history IN these scripts WITH rem comments at THE top.

In the Command Window, these are recognised OK as comments.

IN THE Program Window, these are treated AS code lines AND thus syntax colouring etc IS applied. THE most annoying one has TO be WHEN keywords are upper-cased due TO our beautifying rules OR WHEN an apostrophe IN a COMMENT IS treated AS THE START OF a STRING literal, eg:

REM JRA-123 10-DEC-14 Tim Fixed problem WHERE we couldn't find data
REM JRA-456 12-dec-14 Tim Isn't it annoying WHEN this isn't a literal ?

Often, if there's an odd NUMBER OF apostrophes IN THE mod. history comments, THE "literal" ONLY ends WHERE THE FIRST literal IN THE code starts.

WITH apologies TO fellow Brits FOR THE American spellings above: I reached THE CHARACTER LIMIT FOR THE forum post's 'Subject' !

NB: Upper case words appear above as I typed this in a Program Window to demonstrate the effect

Could we have a preference setting to "Treat REM lines as comments up to the first 'CREATE [OR REPLACE] ' line?"

[ with apologies if this is fixed in v10 .. we're awaiting v11 with bated breath ]

 

[Hilarion]

In Tools/Preferences/Window Types/Program Window theres "Ignore unrecognized PL/SQL" option. It may help (not sure).

In general the program window is for PL/SQL objects (stored procedures, etc.), not SQL*Plus scripts, and REM (and similar commands) are SQL*Plus application commands, not Oracle DB PL/SQL.

 

[Tim Scott]

That option is already checked (to ignore unrecognised PL/SQL).

I appreciate what you're saying about PL/SQL Objects vs SQL*Plus scripts, but most of our PL/SQL Objects are created/updated when deploying / maintaining the application by running a script with SQL*Plus...

There's also a set of scripts that just do inserts/create tables, indexes/ ... they - and their mod histories - work well in a Command Window. There are *occasional* gotchas which we endeavour to avoid.

In practical terms, we could amend the scripts that only create PL/SQL objects to use a different style of mod history, but then we'd have to a) change oodles of scripts and b) remember that these mod histories should be slightly different to other mod histories.

I've been using PL/SQL Developer for a while and mainly raised the comment to see if others might also be interested in the tweak : just because I've been thinking "it's a niggle - get over it" ... I had no idea whether or not hundreds of other users had also thought the same - although I couldn't see anything on the forum before I posted.

 

[Hilarion]

What we've been using for DB modification scripts were separate scripts for individual DB objects (that way you can edit PL/SQL object scripts in PL/SQL Developer "Program Window" without problems, as that object script contains only that object definition and no SQL*Plus commands) and SQL*Plus script file/files that call those (and can contain any other SQL*Plus commands and/or DML).
That's a scenario in which PL/SQL Developer can be used without problems, allows flexibility and can still be used with simple SQL*Plus application.

I can see benefits of having an omnipotent PL/SQL Developer Window (than can handle SQL*Plus, PL/SQL programs development and testing - all in one), but I do not think that the current solution is such a problem, so this feature can wait.

 

[Gustavo]

Maybe you can try this:

-- NoFormat Start

-- enter all you non-pl text here

-- NoFormat End
Create Or Replace ...
...
...

Regards,
Gustavo

 

[Tim Scott]

Good idea, Gustavo. It didn't work for me (suspecting there's a preference somewhere to facilitate it), but the suggestion led me to try enclosing the comment block with /* ... */ and that worked nicely.

I'll run the idea by the rest of the development team to see what they say.

Thanks,

 

[William Robertson]

I don't have PL/Dev in front of me right now, but is the problem simply the REM keyword? Just wondering whether replacing with "--" would do it.

btw, why not maintain change history in the source control system? (I know ClearCase likes to keep a history comment block within the code itself, though, so perhaps that's what you are using.)

 

[Tim Scott]

Thanks for the suggestions, Will, but we have several 100 scripts and I don't want to tweak them all just for this niggle, nor do I want to introduce a change to them on every mod history line as it will obscure investigations into old changes when we use 'Blame', etc. These standards were put together before PL/SQL developer was conceived in 1989(?).

We do keep change histories in SVN, too, but we also deploy the scripts on customer sites and numerous internal systems. Being able to see the mod histories in the files (without access to SVN) is often invaluable to the technical staff supporting the product. There's some copy/pasting of comments into SVN commits and I know we can use tools to extract those comments into deployed files ... we just haven't got around to working out how that would best work out for us as we've had more pressing things to address.