Toad Formatter guide

Toad for Oracle & DevOps Toolkit
1

Where can I find a guide or manual about the various Toad Formatter options?

I have several questions that don't have intuitive answers in the Oracle Formatter Options menu. For example:

  1. How do I define a replacement "default header", that a) is multi-line, and b) doesn't include the default "Formatted on mm/dd/yyyy hh::mi:ss (QP5 v5.374)" first line?

  2. How to get SELECT statements aligned (since SELECT is not DML, and there is no separate SELECT statement alignment category)?

  3. How to get these formatter options to be applied to auto-generated scripts (e.g., the "Script" tab on a table or view) without having to load the script into an editor and hit CTRL_SHIFT_F?

The Toad help has little-to-no useful information about the formatter (other than how to apply formatting to code in an editor or to files), and I cannot find any Toad formatter manual online.

Thank you.

2

Answering Q2:
Go to View -> Toad Options -> Formatter -> right tree -> Alignments - DML Statements

About Q1 (header): Entering multiple lines apparently requires explicit newlines ( \n ) to be coded to function, such as Hello\nWorld. Apparently no way to get rid of the "Formatted on..." message.

Andre

3

Disable the tagline option (Header node) if you don't want any descriptive comment after formatting.

image
image903×481 42.6 KB

4

Ah, I was speed-reading regarding your point about the formatting comment tag SwingCorey, sorry.
You want to replace the default tagline with your own version... I think Andre is correct... don't see a way to replace it totally with your own comment tag.

5

Thinking a little more about this, I created a "replace macro" that gets rid of the default tag line that Toad affixes to formatted code. I used a regular expression to find the QP5 formatter comment/s starting from the begin/comment chars.

So if you augment the tagline with your own header comments (via the Header node of the Formatter options tree) then after you format your code, you can use the regex-type replace per below. Hope this helps in the meantime. Wondering now if there's a way to shortcut it on the keyboard!??!

image

6

\(QP5 v5\.\d\d\d\) perhaps ?

7

Not entirely related but regarding the formatter.
I cannot find how to set it up to leave my "with" clause in one line. It seems to always change it to something like:
with
x
as
(...

What option will make it stay as with x as (...
and then let the rest of the SQL format as set up to?

**with x  as**
    (select sysdate                        as today
          , sysdate + 1                    as tomorrow
          , to_char (sysdate, 'Day')       todays_name
          , to_char (sysdate + 1, 'Day')   tomorrows_name
       from dual)
select *
  from x;

I simply cannot find the option...

8

Hello Abraham,

Checking the option 'Spacing -> Newlines -> Position IS/AS on the same line' is already a first step.

For the remainder I'll look into it and see if I can do anything useful.

Thanks,
Andre

9

I modified formatter to output variations of the WITH clause as follows (with option IS/AS checked):

WITH x AS
       (SELECT SYSDATE                        AS today,
               SYSDATE + 1                    AS tomorrow,
               TO_CHAR (SYSDATE, 'Day')       todays_name,
               TO_CHAR (SYSDATE + 1, 'Day')   tomorrows_name
          FROM DUAL)
SELECT *
  FROM x;

--

WITH
   x AS
      (SELECT SYSDATE                         AS today,
              SYSDATE + 1                     AS tomorrow,
              TO_CHAR (SYSDATE, 'Day')        todays_name,
              TO_CHAR (SYSDATE + 1, 'Day')    tomorrows_name
         FROM DUAL),
   y AS
      (SELECT SYSDATE                         AS today,
              SYSDATE + 1                     AS tomorrow,
              TO_CHAR (SYSDATE, 'Day')        todays_name,
              TO_CHAR (SYSDATE + 1, 'Day')    tomorrows_name
         FROM DUAL)
SELECT *
  FROM x;

--

WITH
   PROCEDURE p IS
   BEGIN
      NULL;
   END;

   PROCEDURE q IS
   BEGIN
      NULL;
   END;

   x AS
      (SELECT SYSDATE                         AS today,
              SYSDATE + 1                     AS tomorrow,
              TO_CHAR (SYSDATE, 'Day')        todays_name,
              TO_CHAR (SYSDATE + 1, 'Day')    tomorrows_name
         FROM DUAL)
SELECT *
  FROM x;

--

WITH
   PROCEDURE p IS
   BEGIN
      NULL;
   END;

   PROCEDURE q IS
   BEGIN
      NULL;
   END;

   x AS
      (SELECT SYSDATE                         AS today,
              SYSDATE + 1                     AS tomorrow,
              TO_CHAR (SYSDATE, 'Day')        todays_name,
              TO_CHAR (SYSDATE + 1, 'Day')    tomorrows_name
         FROM DUAL),
   y AS
      (SELECT SYSDATE                         AS today,
              SYSDATE + 1                     AS tomorrow,
              TO_CHAR (SYSDATE, 'Day')        todays_name,
              TO_CHAR (SYSDATE + 1, 'Day')    tomorrows_name
         FROM DUAL)
SELECT *
  FROM x;

--

WITH
   PROCEDURE p IS
   BEGIN
      NULL;
   END;

   PROCEDURE q IS
   BEGIN
      NULL;
   END;

SELECT *
  FROM x;

(QP-4300, will appear in component QP5 version 5.419)

Thanks Abraham for your hint!
Andre

1 Like