Code Formatting not working for Conditional Compilation $IF

[dgs!]

I recently discovered the PLSQL_CCFLAGs and the abiliy to add conditional compilation to a package.
We have a system that is meant to run VIA a batch tool. The programs used by the batch tool are all over our procedures. This means that we are unable to run the procedures through the TEST scrips in PL/SQL Developer. I would like to encapsulate these batch objects around a conditional $IF $ELSE $END statement. If I set a flag (TESTMODE=TRUE) then I can use the TEST scripts. If it is compiled without the flag it will run through the batch scripts.
The code formatter is not working for $IF $ELSE $END.

 

[Marco Kalter]

Can you provide an example?

 

[dgs!]

--------------------------------
-- Code Formatter does not
-- format this correctly
--------------------------------
BEGIN
  $IF ($$TESTMODE) $THEN
  DBMS_OUTPUT.PUT_LINE('RUNNING IN TEST MODE');
  $ELSE
  DBMS_OUTPUT.PUT_LINE('RUNNING IN NORMAL MODE');
  $END
END;

---------------------------------------
-- This is what I would like
-- to see.  Treat this
-- $IF  $THEN  $ELSE $END just
-- like a normal IF THEN ELSE END.
---------------------------------------
BEGIN
  $IF ($$TESTMODE) $THEN
    DBMS_OUTPUT.PUT_LINE('RUNNING IN TEST MODE');
  $ELSE
    DBMS_OUTPUT.PUT_LINE('RUNNING IN NORMAL MODE');
  $END
END;

 

[T-Gergely]

You can use [ code] ... [ /code] UBBCode (without spaces between the brackets) to preserve formatting.

$IF ($$TESTMODE) $THEN
  DBMS_OUTPUT.PUT_LINE('RUNNING IN TEST MODE');
$ELSE
  DBMS_OUTPUT.PUT_LINE('RUNNING IN NORMAL MODE');
$END

 

[dgs!]

Excellent, I didn't know that.
Thanks.
...
but the PL/SQL code formatter still doesn't format the top one correctly.

I meant to take out the bottom example.

 

[dgs!]

Did I fall off the radar?

 

[dgs!]

Anyone else able to recreate this issue?
Feeling a little forgotten here...
Hello-hello is this thing working??

 

[Worker]

If you're talking about the PL/SQL Beautifier, then I can reproduce this problem. Code with $IF .. $END is not indented as one might expect.

 

[dgs!]

I am talking about the PL/SQL Beautifier, thanks for confirming.
Speaking of the beautifier, a very few of the people I work with use this tool because they say that they do not like the way if formats things.
You need to spend a little time setting it up, but once you invest the time, (I think) it does a nice consistent job.
They'd rather space, space, space, upper case, lower case everything manually. I say work smart and not hard and use the beautifier.
Would it be possible to get more options for formatting? Maybe if the tool had more flexibility, more people would use it.

 

[dgs!]

Here's an interesting tid-bit about $IF $THEN $ELSE

Normal IF THEN ELSE:
This code will not run at all because the formatted DBMS_OUTPUT statement is missing a closing ")"

DECLARE
  n_number NUMBER := 16000;
BEGIN
  IF $$TESTMODE THEN
  dbms_output.put_line(to_char(n_number, '99,999');
  ELSE
  dbms_output.put_line('something else');
  END IF;
END;

$IF $THEN $ELSE:
However, providing the code is NOT running in TESTMODE, this code will fall into the ELSE statement without causing an error:

DECLARE
  n_number NUMBER := 16000;
BEGIN
  $IF $$TESTMODE $THEN
  dbms_output.put_line(to_char(n_number, '99,999');
  $ELSE
  dbms_output.put_line('something else');
  $END
END;

I don't think I like the $IF....

PS, The beautifier still doesn't format $IF correctly. Yet another reason NOT to like $IF.

 

[William Robertson]

I think a lot of us have been asking for some work to be done on the beautifier for the best part of a decade but Marco's elves haven't got around to it yet.

btw if you could try to talk your colleagues out of uppercasing code, that would be great. I honestly believe the way so many people make their PL/SQL look like poorly written Cobol damages its reputation as a language, and our profession as a result.

 

[dgs!]

Thanks for the reply. I've noticed that if you ask 3 developers how to format the code, you'll get 5 different answers.
Most of the people I know refuse to use the code formatter. They prefer to do it manually, which in my opinion is working hard and not smart.
space, space, space... that's idiotic and it's never consistent.

If you take the time to set up the many, many options in the formatter, you can usually get it to do about 95% of the formatting for you with the press of a button. For those hard to format places, simply do it manually, then encapsulate that portion in
-- NoFormat Start
-- NoFormat End

I like the keywords in uppercase, everything else in lower case. The examples I included were typed quickly simply to show the problem with the $IF statement formatting.

space, space, space...

 

[William Robertson]

I wouldn't say there were 'many, many' formatter options, compared to SQL Developer or TOAD, but yes I agree it is well worth working through them all and I wish more developers had your enthusiasm. Even so I find reformatting is only worth doing on poorly-formatted code (not mine, naturally ) and then it's a 2-step process:

1. Apply the Beautifier.
2. Clean up after the Beautifier.

Each to his own of course, but I also uppercased my keywords for my first 20 years working with the Oracle toolset. Then around 5 years ago (following some discussions on OTN about treating PL/SQL as a modern programming language, and also looking at code by Tom Kyte, Bryn Llewellyn and Jonathan Lewis) I realized that it wasn't helping. Perhaps it made some sense way back before there was PL/SQL or colour terminals, and the habit just never went away.

These days every project I work on seems to be besieged by OOP specialists who consider databases in general and PL/SQL in particular unfashionable and due for replacement by Java, C# or (more recently) some pie-in-the-sky Big Data technology. And just look at the PL/SQL code we are writing - it looks like COBOL from 1974! If we want to keep our careers as Oracle specialists I really think we all need to do everything we can to show PL/SQL as a capable, modern language.

Have at this presentation, especially slides 5 and 6:
PL/SQL: The Good Parts by Morten Braten

 

[T-Gergely]

[off-topic]
Nice presentation. Not particularly #5-#6, though.
As for #11, alas package global constants initialize package state, so you might want to use pragma serially_resuable.
For #36, extract and some other useful functions are now deprecated.