Manual for Tidycode Pl/Sql Formatter version 2.5.1

Description

This software allows you to tidy/beautify/format your Pl\Sql code in a batch file or Dos prompt.
This is a command line tool that gives you the freedom to build it in your programming environment. You can use it in your favorite editor easily.

How to use it

Goto to the installation directory and enter in dos:
tpsf -h
for the options and help,

How to use it in an editor

In a regular editor, there are user definable tools. Set a tool to execute tpsf.
Make sure that the working directory is the same as the installation directory. Otherwise, it won't work.

E.g. Ultraedit
Command line : tpsf -io %F
Working directory: d:\tpsf

About the use of switches

Incorrect switches are simply ignored. You are informed about that.
Incompatible switches are allowed. The switches that come later will override earlier ones.

There are 3 types of switches:
-with no value (-h,-v)
-with 2 possible values (-rs+,-rs-)
-with many possible values (-i,-o..)

Many switches are already "on" by default. Therefore the software may not work out as you expect.
Take these into account when using switches.

Here is the list:
-in+ -uk+ -c+ -rsaob+ -co+ -ncb+ -rs+ -iaew+ -iacw+ -iac+ -clb+ -iibe+ -itlwb+ -soe+ -sf+ -rl+ -sr+

There are switches that set a number of switches. They are categorized as styles.
See them here

List of switches

Switch/option

Meaning

-i[r] inpsrc input source can be:a filename,stdin,globbing [recursively]
-o outdest output destination can be:a filename,stdout
-io[r] filename same input and output file [recursively]
-oe ext extension of output if -(i)o is not specified
-od[r] dir output directory [recursive mode]
-ef[r] filename exclude filenames from formatting [recursively]
-sl lineno read input from this line (don't use lines before)
-el lineno don't use input after this line
-cs charset 8 bit ascii like character set of the input
-t s1[,s2..] tab is equal to s1,s2.. columns
-ut[+-] use only tabs for indenting
-uts[+-] use tabs and spaces if necessary for indenting
-is size(t) indent size in spaces or in tabs (generally)
-in[+-] indent lines yes/no
-il[Le] size(t) indent size in spaces or in tabs at (Le)vel(specific)
-sit[+-] set indent sizes to tab sizes
-ctis[+-] convert tabs into spaces
-uk[+-] uppercase keywords yes/no
-ck[+-] capital keywords yes/no
-lk[+-] lowercase keywords yes/no
-li[+-] lowercase identifiers
-ui[+-] uppercase identifiers
-ci[+-] capitalised identifiers
-si ident set identifiers to look like ident
-mcs size maximum allowed size of columns
-amso[+-] allow moving statements only
-asc[+-] allow shortening comments
-amc[+-] allow moving comments
-abusc[+-] allow broken up standalone comments
-abuc[+-] allow broken up comments
-acoslc[+-] allow conversion of single line comments
-aci[+-] allow changing indentation
-acs[+-] allow changing space
-c[+-] compactify, remove redundant spaces/keep
-co[+-] remove spaces around operations (+,- etcdo nothing/)
-sao[+-] add space around operations/do nothing
-rsaob[+-] remove spaces after opening brackets/keep
-rsbcb[+-] remove spaces before closing brackets/keep
-ncb[+-] don't remove spaces around brackets/do nothing
-rs[+-] keep the relative identation of an allowed sql/do nothing
-id[+-] extra indentation for declarations yes/no
-iaew[+-] extra indentation after exception when yes/no
-iacw[+-] extra indentation after case when yes/no
-iac[+-] extra indentation after cursor yes/no
-isc[+-] indent standalone comments
-isc2[+-] indent standalone comments in some special cases too
-iic[+-] indent inside comments/do nothing
-iibe[+-] indent inside begin/end block
-itlwb[+-] indent then/loop with block
-ni[+-] nice indents
-coi size(t) continuation indent size in spaces or in tabs
-iiie[+-] indent or do not indent if in else
-clb[+-] column like lists inside brackets
-actv[+-] align consecutive types in declarations vertically
-ps size padding size, number of spaces between variables and types
-acav[+-] align consecutive assignments (:=) vertically
-acev[+-] align consecutive equalities (=) vertically
-acarv[+-] align consecutive arrows (=>) vertically
-pcr[+-] put commas right
-as[+-] align selects
-au[+-] align updates
-aaw[+-] align after where clauses
-ac[+-] align conditions in IF/THEN
-aroa[+-] align right of assignments
-pius[+-] put 'into' under select
-plouwh[+-] put logical operations under where/having
-bus[+-] break up sql statements
-rask[+-] right align sql keywords
-actvct[+-] align consecutive types in create table-s vertically
-aav[+-] align aliases vertically
-bbj[+-] (line)break before joins
-isis size indentation size (in spaces) in Sql clauses
-ins[+-] extra indentation after selects
-iu[+-] extra indentation after updates
-iaw[+-] extra indentation after where clauses
-milipl no minimum list items per line
-mlipl no maximum list items per line
-buml[+-] break up multistatement lines
-rael[+-] remove all empty lines
-mlapcd[+-] maintain a linebreak after cursor declarations
-rlapcd[+-] remove linebreaks after cursor declarations
-mlai[+-] maintain a linebreak after each if
-rlai[+-] remove linebreaks after each if
-mlaf[+-] maintain a linebreak after each for loop
-rlaf[+-] remove linebreaks after each for loop
-mlaw[+-] maintain a linebreak after each while loop
-rlaw[+-] remove linebreaks after each while loop
-mlal[+-] maintain a linebreak after each 'loop' loop
-rlal[+-] remove linebreaks after each 'loop' loop
-mlal[+-] maintain a linebreak after each 'loop' loop
-mlafu[+-] maintain a linebreak after function names
-rlapr[+-] remove linebreaks after procedure
-mlapa[+-] maintain a linebreak after package
-rlapa[+-] remove linebreaks after package
-dbvn[+-] declaration based variable names
-kitt[+-] keep if/thens together
-kwlt[+-] keep while/loop together
-kflt[+-] keep for/loop together
-kpit[+-] keep procedure and is/as together
-kwtt[+-] keep when/then together
-sbi[+-] show block information after the end of blocks
-sbi.ml lines show it for blocks that have at least minimum lines
-sbi.m string marker string to use to denote end of block information
-sbi.max chars maximum characters to show from the start of the block
-rbi[+-] remove block information after the end of blocks
-cmk[+-] check for missing keywords [loop/then]
-x print the configuration and does not format
-v verbose , same as -x but it will tidy
-sf[+-] show filenames in block stack
-rl[+-] reverse listing of block stack
-sr[+-] show the result of formatting
-ss[+-] show the summary of multiple formatting
-b maxvers number of backup versions to keep
-bdir dir location of the backup directory
-ls filename load settings/switches from a file
-gsfif[+-] get switches from input file
-ae ext accept files with ext only
-sw[+-] show switches in effect
-sdw show default switches
-swc show switches compactly
-0 sets all switches to off
-pl[+-] prepend logo and formatting info to the output file
-plo[+-] prepend logo only
-mte[+-] multithreaded execution for multiple files
-w[+-] show warnings/potential problems in code
-al[+-] set all formatting switches on and left-aligned sql
-ar[+-] set all formatting switches on and right-aligned sql
-ai[+-] set all formatting switches on and indent in sql
-lsql[+-] left-aligned sql
-rsql[+-] right-aligned sql
-isql[+-] indent in sql
-alol[+-] at least one linebreak after blocks and elsewhere
-ol[+-] one linebreak after blocks and elsewhere
-html[+-] format of output is html
-ht filename filename of html template
-tag\\[type\\] tags use html tags for token type
-h this help

Switches and examples

-h

lists you all possible switches you can use.

-i[r] filename input file

This is the filename of scripts to tidy.
If the filename is stdin, the program uses the standard input as a source.
If the filename is clipboard, the clipboard is used as a source.
It is possible to use globbing instead of exact filenames.
This switch can be repeated multiple times. In that case, multiple files will be formatted.
-ir causes the formatter to look for files recursively in the subdirectories of filename that match the globbing.

E.g. tpsf -ir=*.sql

E.g. tpsf -i=h.sql
E.g tpsf -i stdin
tpsf -i *.sql -i exception.srt

-o filename output file (tidied up)

This is the resultant file of the formatting.

If the filename is stdout, the program uses the standard output as a destination.
If the filename is clipboard, the program puts the output in the clipboard.
E.g. tpsf -i=h.sql -o=h2.sql
E.g tpsf -i stdin -o stdout

-io[r] filename same input and output file

This is a shortcut for -i and -o.

-ior causes the formatter to look for files recursively in the subdirectories of filename as well.
E.g. tpsf -io=h.sql
tpsf -ior=*.bdy

--oe ext extension of output if -o is not specified

E.g. tpsf -i=h.sql -oe .bdy
It will produce a file h.bdy.

Using + appends the extension to the name of the generated file instead of replacing it.

E.g. tpsf -i=h.sql -oe +.bdy
It will produce a file h.sql.bdy

-od[r] output directory if -i is specified

By default, the output directory is the same is as the input directory given by -i switch.
-od sets the output directory.

If -ir is used, -od will not follow or create the corresponding subdirectories ,nor will it copy the files recursively. Instead, all files will go to the output directory. The directory structure will be "flattened". However, -odr will follow and create corresponding subdirectories that exist in the source. -odr is natural pair of -ir.

-od[r] can be used once effectively. If it is repeated in the command line, its previous instances will be ignored, only the last one will be used.

E.g. tpsf -i=c:\source\h.sql -od d:\temp
It will produce a file d:\temp\h.bdy. Temp directory needs to exist.

-ef[r] exclude filename

It will remove the list of files given by -i,-io switches matching filename.
This switch works only if all filenames have the same path format when using -i,-io or -ef.
The path format should be full path or no path or the same relative path format.
-efr causes the formatter to look for files recursively in the subdirectories of filename too.

E.g. tpsf -i=c:\source\*.sql -ef c:\source\d*.sql

It will format all files in the c:\source directory excluding those which match d*.sql

E.g. tpsf -i=*.sql -ef d*.sql -ef test.sql
tpsf -ir=*.sql -efr d*.sql

-sl lineno tidy lines from this lineno on

The resultant file will not contain lines before line lineno of the original file.

E.g. tpsf -i=h.sql -sl 4

-el lineno tidy lines up to that lineno

The resultant file will not contain lines after the line lineno of the original file.

E.g. tpsf -i=h.sql -sl 2000

-cs charset 8 bit ascii like character set of the input

This switch has an effect on how -uk,-ck,-lk,-ui,-ci,-li operates because what is an uppercase or lowercase
letter depends on the specified character set.
The allowed character sets are listed under Supported Character Sets.

-t s1[,s2..] tab is equal to s1,s2.. columns

Specify the column sizes of tab character.

E.g. tpsf -io=h.sql -t 4

-ut[+-] use only tabs for indenting

At the beginning of each line only tab character (9 in ascii) will be used to position code approximately as defined by -t.-is,-il switches.

-uts[+-] use tabs and spaces if necessary for indenting

At the beginning of each line tab character (9 in ascii) and spaces will be used to position code accurately as defined by -t.-is,-il switches.
Spaces are used only if necessary.
So any line can look like : <tab> anytimes (0..) <space> anytimes (0..)

-is size indent size in spaces (generally)

Probably it is one of the most important switches.

E.g. tpsf -io=h.sql -is 3

-in[+-] size indent size in spaces (generally)

It affects if the beginning of each line is indented according to block level.

E.g. tpsf -io=h.sql -is 3

-il[Level] size indent size at Level(specific)

Level refers to how many spaces of indentation precedes a given line of code. The code inside a loop is more indented than the code before the loop.
Statements that make the level of code higher/more indented :
packages, functions, procedures,loops, if , begin, when(optional), cursor (optional),
The size is in spaces. Use more of this switch, if you want to set more than 1 level.

E.g. tpsf -io=h.sql -is 3 -il1 2 -il2 4

-sit[+-] set indent sizes to tab sizes

-uk[+-] uppercase keywords yes/no

All keywords will be converted into uppercase letters. (BEGIN IF VARCHAR2)

E.g. tpsf -io=h.sql -uk+

-ck[+-] capital keywords yes/no

All keywords will be capitalized. (Begin If Varchar2)

E.g. tpsf -io=h.sql -ck+

-lk[+-] lowercase keywords yes/no

All keywords will be converted into uppercase letters (begin if varchar)

-ui[+-] uppercase identifiers yes/no

Default state:off.
All identifiers will be converted into uppercase letters. (TERRA5:=3;)

E.g. tpsf -io=h.sql -ui+

-ci[+-] capital identifiers yes/no

Default state:off.
All identifiers will be capitalized. (Terra5:=3;)

E.g. tpsf -io=h.sql -ci+

-li[+-] lowercase identifiers yes/no

Default state:off.
All keywords will be converted into lowercase letters. (terra5:=3;)

E.g. tpsf -io=h.sql -li+

-clb[+-] column like lists inside brackets

E.g. tpsf -io=h.sql -clb+

this:

a := (a + 1
 + 4
 + 5
 + 8);
will become that:
a := (a + 1
 + 4
 + 5
 + 8);

-c[+-] compactify, remove redundant spaces/keep

E.g. tpsf -io=h.sql -c+

Original

procedure h is

Tidied

procedure h is

-co[+-] remove spaces around operations (+,- etcdo /nothing)

E.g. tpsf -io=h.sql -co+

Original

d:= 5 + 7;

Tidied

d:=5+7;

Original tpsf -io=h.sql -ncb-	FOR rec IN(SELECT * FROM duality WHERE g =5)
Tidied tpsf -io=h.sql -ncb+	FOR rec IN (SELECT * FROM duality WHERE g = 5)

-rs[+-] keep the relative indentation of a sql/do nothing

If it is on, the indentation (relative to the start of sql) of each line of a select statement will be kept as much as possible.

Original
tpsf -io=h.sql -rs+

select *
from memory
where year>1786

Tidied
tpsf -io=h.sql -rs-

select *
from memory
where year>1786

-id[+-] indent after declarations yes/no

Original tpsf -io=h.sql -id-	PROCEDURE recurse IS b number:=5; d456 number:=456; cursor one is select list from todo;
Tidied tpsf -io=h.sql -id+	PROCEDURE recurse IS b number:=5; d456 number:=456; cursor one is select list from todo;

-iaew[+-] indent after when in exception yes/no

Original
tpsf -io=h.sql -iaew-

EXCEPTION
WHEN no_data THEN
hello;

Tidied
tpsf -io=h.sql -iaew+

EXCEPTION
WHEN no_data THEN
hello;

-iacw[+-] indent after when in case statement yes/no

Original
tpsf -io=h.sql -iacw-

CASE opinion
  WHEN '9' THEN
  dbms_output.put_line('Excellent');
  WHEN '5' THEN dbms_output.put_line('Poor');
  ELSE dbms_output.put_line('cannot decide');
END CASE;

Tidied
tpsf -io=h.sql -iacw+

CASE opinion
WHEN '9' THEN
dbms_output.put_line('Excellent');
WHEN '5' THEN dbms_output.put_line('Poor');
ELSE dbms_output.put_line('cannot decide');
END CASE;

-iac[+-] indent after cursor yes/no

Cursor statements (except their first line) will have an extra indent level/extra indentation.

Original
tpsf -io=h.sql -iac-

CURSOR b IS
SELECT * FROM dual;

Tidied
tpsf -io=h.sql -iac+

CURSOR b IS
SELECT * FROM dual;

-isc[+-] indent standalone comments

If it is on, single line comments that start a line are indented as everything else.
If it is off, they are not touched.

Original
tpsf -io=h.sql -isc-

begin
/*hello Sql*/
low:=4+4
-- end of little block
end;

Tidied
tpsf -io=h.sql -isc+

begin
/*hello Sql*/
low:=4+4
-- end of little block
end;

-isc2[+-] indent standalone comments in special cases too

These are the special cases: sql statements,lists,alignments and other.

Original
tpsf -io=h.sql -isc2-

begin
insert into l16 (C,L,L,L) values (
/*added by me*/cl);
end;

Tidied
tpsf -io=h.sql -isc2+

begin
insert into l16 (C,L,L,L) values (
/*added by me*/cl);
end;

-iic[+-] indent inside comments/do nothing

If it is on, it will indent text inside /*..*/

-iibe[+-] indent inside begin/end block

It is on by default. BEGIN/END blocks will have an extra indent level/extra indentation.

Original
tpsf -io=h.sql -iibe-

BEGIN
select star from sky
where star=(select star
from catalogue
where r>3
and brightness>5
);

END;

Tidied
tpsf -io=h.sql -iibe+

BEGIN
    select star from sky
    where star=(select star
    from catalogue
    where r>3
    and brightness>5
    );
END;

-itlwb[+-] indent then/loop with the next block

If this option is on, then/loop keyword are indented more than if the option is off.

Original
tpsf -io=h.sql -itlwb-

IF s=1 THEN
    IF (record_exists IS NOT NULL
        AND record_exists = yes_flag)
    THEN
      a:=3;
    END IF;
END IF;

Tidied
tpsf -io=h.sql -itlwb+

IF s=1 THEN
    IF (record_exists IS NOT NULL
        AND record_exists = yes_flag)
      THEN
      a:=3;
    END IF;
END IF;

-ni[+-] nice indents

Without this option, the first "end if" in the following example would be located more to the left side of the second "if" producing a less nice code. In this case, "end if" is located under "if".

Original
tpsf -io=h.sql -nl-

if s=1 then IF (record_exists IS NOT NULL
            AND record_exists = yes_flag)
            THEN
a:=3;
end if;
end if;

Tidied
tpsf -io=h.sql -nl+

IF s=1 THEN IF (record_exists IS NOT NULL
                  AND record_exists = yes_flag)
                THEN
                a:=3;
            END IF;
END IF;

-iiie[+-] indent if in else

It will indent if/end if block in else part of an if.

Original
tpsf -io=h.sql -iiie-

if a then
null;
else
if b then
null;
end if;
end if;

Tidied
tpsf -io=h.sql -iiie+

if a then
null;
else
if b then
null;
end if;
end if;

-actv[+-] align consecutive types in declarations vertically

In declarations, spaces are put in between a variable and its type so that types will form a column.

Original
tpsf -io=h.sql -actv-

d integer:=5;
ertr varchar2(4):='5';

Tidied
tpsf -io=h.sql -actv+

d integer:=5;
ertr varchar2(4):='5';

-ps size padding size, number of spaces between variables and types

It is meaningful only if -actv+ or -aav+ are used too.

Original
tpsf -io=h.sql -actv-

d integer:=5;
ertr varchar2(4):='5';

Tidied
tpsf -io=h.sql -actv+ -ps 2

d integer:=5;
ertr varchar2(4):='5';

-actvct[+-] align consecutive types in create table-s vertically

In CREATE TABLE statements, spaces are put in between a field and its type so that types will form a column.

Original
tpsf -io=h.sql -actvct-

create table listofjobs
(
name varchar2(70),
location varchar2(411)

);

Tidied
tpsf -io=h.sql -actvct+

create table listofjobs
(
name varchar2(70),
location varchar2(411)

);

-acav[+-] align consecutive assignments (:=) vertically

If assignments follow each other in each line, they will be aligned vertically.
It does not have any effect on lines containing more than one assignments.

Original
tpsf -io=h.sql -acav-

derival:=0;
d:=1;

Tidied
tpsf -io=h.sql -acav+

derival:=0;
d :=1;

-acev[+-] align consecutive equalities (=) vertically

If equality operators follow each other in each line, they will be aligned vertically.
It does not have any effect on lines containing more than one equality.

Original
tpsf -io=h.sql -acev-

where myownname = 'richard' and
tabname = 'nt';

Tidied
tpsf -io=h.sql -acev+

where myownname = 'richard' and
tabname = 'nt';

-acarv[+-] align consecutive arrows (=>) vertically

If arrows follow each other in each line, they will be aligned vertically.
It does not have any effect on lines containing more than one arrow.

Original
tpsf -io=h.sql -acarv-

call_now(myownname => USER,
tabname => 'nt');

Tidied
tpsf -io=h.sql -acarv+

call_now(myownname => USER,
tabname => 'nt');

-aav[+-] align aliases vertically

If aliases follow each other in consecutive lines, they will be aligned vertically.
Aliases may be detected after select and from keyword.
It does not have any effect on lines containing more than one alias.
In itself it may produce strange results. It shows its value if it is used with -as+ at least.

Original
tpsf -io=h.sql -aav-

select name dubbed,
emot emotion,
c "alias",
e2 "alias2",
ffff,
gg 4
from table122 a,
table2 b

Tidied
tpsf -io=h.sql -as+ -aav+

SELECT name dubbed,
       emot emotion,
         c    "alias",
       e2     "alias2",
         ffff,
     gg 4
FROM table122 a,
      table2 b

-pcr[+-] put commas right

It moves commas to the right side if they are at the beginning of a line.

Original
tpsf -io=h.sql -pcr-

PROCEDURE myerr(
   /* arguments*/
--msg ..
--msg2 ..
                ,msg VARCHAR2
                ,msg2 VARCHAR)

Tidied
tpsf -io=h.sql -pcr+

PROCEDURE myerr(
   /* arguments*/
--msg ..
--msg2 ..
                msg VARCHAR2,
                msg2 VARCHAR)

-as[+-] align selects

As variables are declared, so they will look elsewhere. This option is not compatible with
uppercase,lowercase,capitalized identifier switch. Those will override this option.
It also affects the naming of cursors. They are included as if they were variables.

Original
tpsf -io=h.sql

select e,j
from tablea,
tableb
where f1>f2
and f4=9;

Tidied
tpsf -io=h.sql -as+

select e,j
from tablea,
tableb
where f1>f2
and f4=9;

-au[+-] align updates

Within updates, non-keywords are left-aligned.

Original
tpsf -io=h.sql

update t
set g=56,
i=3;

Tidied
tpsf -io=h.sql -au+

update t
set g=56,
i=3;

-aaw[+-] align after where clauses

It also aligns after having clauses.

Original
tpsf -io=h.sql

where f1>f2
and f4=9;

Tidied
tpsf -io=h.sql -aaw+

where f1>f2
and f4=9;

-ac[+-] align conditions

It affects if/while/elsif/when conditions. Conditions will be left-aligned after a starting keyword.

Original
tpsf -io=h.sql

if u>2
and b!=4
then
f:=9;
end if;

Tidied
tpsf -io=h.sql -ac+

if u>2
and b!=4
then
f:=9;
end if;

-aroa[+-] align right of assignments

Everything after an assignment := will be right- aligned until the end of statement (;).

Original
tpsf -io=h.sql

a:=b
+c;

Tidied
tpsf -io=h.sql -aroa+

a:=b
+c;

-kitt[+-] keep if/then together

Redundant whitespace are removed before loop keyword in a while loop.
E.g. tpsf -io=h.sql -kitt+

Original

if f>3
then
e:=3;
end if;

Tidied

if f>3 then
e:=3;
end if;

-kwlt[+-] keep while/loop together

Redundant whitespace are removed before loop keyword in a while loop.
E.g. tpsf -io=h.sql -kwlt+

Original

while f>3
loop
f:=f-1;
end if;

Tidied

while f>3 loop

f:=f-1;
end if;

-kflt[+-] keep for/loop together

Redundant whitespace are removed before loop keyword in a for loop.
E.g.tpsf -io=h.sql -kflt+

Original

for t in 1..7
loop
e:=3;
end loop;

Tidied

for t in 1..7 loop
e:=3;
end loop;

-kpit[+-] keep (procedure/package)/(is/as) together

Redundant whitespace are removed between the last ) of a procedure header and is.
E.g. tpsf -io=h.sql -kpit+

Original

package x
IS
begin

Tidied

package x IS
begin

-kwtt[+-] keep when/then together

This affects when/then in exceptions,insert statements and in case statements.

E.g. tpsf -io=h.sql -kwtt+

Original

CASE
WHEN v IS NULL
THEN NULL

Tidied

CASE
WHEN FAC_ORDER.CORE_COMPLETED_DATE IS NULL THEN NULL

-sbi[+-] show block information after the end of blocks

-sbi.ml lines show it for blocks that have at least minimum lines

-sbi.m string marker string to use to denote end of block information

-sbi.max chars maximum characters to show from the start of the block

The 4 headings above have this one description.
sbi switch inserts a comment/description after each end of block statement that describes the block. The description identifies the block. It has 3 subswitches that help refine when the description appears and how long it can be. -sbi.ml sets the size of block in lines below which no description is generated. -sbl.m sets what marker to put before the description in the multiline comment. It is "of block:" by default. -sbi.max sets the maximum length of the description in characters.

E.g. tpsf -i h.sql -sbi+

Original

Original
tpsf -io=h.sql

declare
d number;
E number;
eReRer varchar2(22);

cursor Blag is ..;
begin
open blag;

e:=3424;
ererer:='one';

Tidied
tpsf -io=h.sql -dbvn+

declare
d number;
E number;
eReRervarchar2(22);

cursor Blag is ..;
begin
open Blag;
E:=3424;
eReRer:='one';

-cmk[+-] check for missing keywords (loop/then)

It performs additional syntax checking. If loop or then is required by a syntax construct, the formatter stops with a syntax error.

-x

It prints out what the formatting would do if it ran completely. It stops without doing anything.

-b maxvers number of backup versions to keep

Number of old backup versions to keep +1 (including the current backup version that is being created)

-bdir directory

Set backup directory

-v

The formatter displays/explains what it will do and runs.

-sf[+-] show filenames in block stack

When a syntax error occurs, it shows the filename of the current file in the block stack.

-rl[+-] reverse listing of block stack

When a syntax error occurs, the displayed block stack show last lines first.

-sr[+-] show the result of formatting

After the formatting is done, a message is printed which shows if the formatting is successfull or not.

-ss[+-] show the summary of multiple file formatting

It prints a message which is similar to this when all files are processed:
"Formatting summary: all 10, tried 2, done 1, failed 1."

The explanation of the labels are:
all: the number of files to be formatted.
tried: the number of files that the formatter tried to format.
done: the number of files that are formatted.
failed: the number of files that were not formatted because of syntax errors.

-ls filename load settings/switches from a file

It is possible to save the command line switches in a file and load those switches instead of giving them.
For example, use any text file. Save these switches as they are written here(without "): "-rs- -dbvn+" in a file named "sw.txt". Then use this: tpsf -ls sw.txt.

A settings file can contain comments (they must be separated by whitespace) like this :
"-- this is a line comment
-rs- -dbvn+
"

It is allowed to use other switches after -ls which then modify the loaded switches.

There is a default settings/configuration file that is loaded automatically if it exists.
Its name is tpsf.cfg and it should be placed in the installation directory.

An example of -ls : tpsf -ls sw.txt -mcs 100.

This will load the switches in sw.txt, then it will set the maximum colunm size, changing
the column size set in sw.txt, if it was defined there.

-ae ext accept files with ext only Only files with ext extension will be accepted for formatting.

If -ae is not used at all, any file with any extension is formatted.
If multiple -ae are used, then multiple extensions are accepted.

E.g tpsf -i h.sql -ae sql -ae ps
This will accept both h.sql and h.ps if used as input.

-sw[+-] show switches

It prints out the state of all switches. It does not run the program.

-sdw show default switches

It prints out the state of all default switches. That is , the state of switches before the command line arguments are processed. What is printed can depend on tpsf.cfg too if it exists. It does not run the program.

-swc show switches compactly

It prints out the state of all switches excluding the switches which values are the same as the default switches. What is printed can depend on tpsf.cfg too if it exists. It is possible that this does not print anything if no switches differ from the default switches.

-0 sets all switches to off

It affects all switches that precede it in command line.

-alll+ set all formatting switches on and left-aligned sql

It modifies the state of a set of switches. Those switches could be set individually but it may be faster to set them in this way. It is meant as a shortcut.
It turns sql formatting on, the sql statements will be left-aligned. The switches which require a number are not affected. E.g. wrapping and set number of list items. Also, the wrapping/html/syntax related switches are not modified.
Using minus sign after this switch have no effect.

-allr+ set all formatting switches on and right-aligned sql

It modifies the state of a set of switches. Those switches could be set individually but it may be faster to set them in this way. It is meant as a shortcut.
It turns sql formatting on, the sql statements will be right-aligned. The switches which require a number are not affected. E.g. wrapping and set number of list items. Also, the wrapping/html/syntax related switches are not modified.
Using minus sign after this switch have no effect.

-lsql[+-] left-aligned sql

-rsql[+-] left-aligned sql

-alol[+-] at least one linebreak after blocks and elsewhere

It modifies the state of a set of switches. Those switches could be set individually but it may be faster to set them in this way. It is meant as a shortcut.
There will be at least one linebreak after each block/cursor and function name.
Using minus sign after this switch have no effect.

-ol+-] one linebreak after blocks and elsewhere

It modifies the state of a set of switches. Those switches could be set individually but it may be faster to set them in this way. It is meant as a shortcut.
There will be one linebreak after each block/cursor and function name.
Using minus sign after this switch have no effect.

-html[+-] set the format of output to html

By default, the format of input Pl/Sql source is not html...
If this option is set, the output will be modified to contain html tags
so that Pl/Sql source can be viewed in a browser.
There are two suboptions that can be used to influence html formatting:
-ht and -tag.
-ht specifies an html file in which a generated converted formatted Pl/Sql source will be inserted.
-tag sets what tags to use for each token.

Original

CREATE PACKAGE BODY

Tidied
-html+

CREATE PACKAGE BODY

-ht html template filename

This is to be used with -html+.
-ht specifies an html file in which a generated converted formatted Pl/Sql source will be inserted.
The html file should contain the string $formatted.
The insertion point is $formatted. The string $formatted is replaced by the formatted source.
In this way, the generated output will be the html template file up to $formatted plus the formatter source
plus the html template file after $formatted.
A sample called htmltemplate.html is provided in the installation directory.

E.g. -ht htmltemplate.html

-tag\[token type\] tag sequence :sets what tags to use for each token.

This is to be used with -html+.
One can specify what html tags each token of the source should be surrounded by.
Multiple tags can be given for each token type.
If no tags are wanted for a token type then <> should be given.
By default, the tokentypes have tags defined.

These token types are accepted :	Examples
multilinecomment,	/****/
singlelinecomment	-- single line comment
other	.,;
whitespace
doublequotes	"a string"
singlequotes.	'a string'
qquotes	q':a string:'
identifier	Amount
keyword	create
number	77

E.g.
-tag[identifier] ""
Salary identifier will be formatted like this: Salary
-tag[number] ""
The number 1.2 will be formatted like that: 1.2

-pl[+-] prepend logo

It prepends logo and formatting related information in the formatted files.
The prepended line will look something like this:
/*Tidycode Pl/Sql Formatter 2.5.1 formatted on:1/7/2008 options: --is 2 -t 4 -mcs 0 -milipl 0 -mlipl 0 -cs US7ASCII -aaw- -abuc- -abusc- -ac- -acarv- -acav- -aci- acoslc- -acs- -actv- -amc- -amso- -aroa- -as- -asc- -au- -buml- -bus- -c+ -ci- -ck- -clb+ -cmk- -co+ -ctis- -dbvn- -html- -iac+ -iacw+ -iaew+-id- -iibe+ -iic- -in+ -isc- -isc2- -itlwb+ -kflt- -kitt- -kpit- -kwlt- -kwtt- -li- -lk- -mlacd- -mlaf- -mlafu- -mlai- ...*/

-plo[+-] prepend logo only

It prepends logo in the formatted files.
The prepended line will look something like this:
/*Tidycode Pl/Sql Formatter 2.5.1 formatted on:1/7/2008*/

-mte[+-] multithreaded execution for multiple files

Setting this switch will make formatting faster on multicore processors.
It results in a speed-up if there are at least 2 files specified in the command line.
The more cores there are available. the greater the speed-up will be. All available cores will be used automatically. The formatting order of the files -which comes from the command line order- may be out of order because of parallel execution compared to the situation if this switch is off. On the 4 core Q8200 processor 2x speed up was measured.

-gsfif[+-] get switches from input file

If it is on, the switches will be obtained from the input source file as well if they exist.
The switches should be placed in a multiline comment at the end of the file.
They should be the last multiline comment in the file , otherwise they are not recognised.
The following switches are ignored if they are defined in the input source file:
-tag,-bdir,-b,-ht,-ae
The input file should contain this line at the end :
/*tpsf options: <put here options>*/
To be exact, the options will come from three sources: from the default options file(tpsf.cfg),
the command line options and options embedded in the input file.
The options are evaluated in the order as they were listed in the previous sentence.
In short, it is possible to override all options specified in tpsf.cfg and in the command line in a input file.

E.g. a source file could contain this: /*tpsf options: -in+*/

-soe[+-] stop on errors

It is taken into account if multiple files are formatted.
If it is on, the formatting stops on at the first file in which there is an error.
That is , the remaining files are not formatted.
The program returns 0 return code when it is off, even there has been errors.

-w[+-] show warnings

It print one warning at the moment. If there is if/while/etc block starters are not in a block.

-rla.. remove linebreaks after ... type of switches

They can be used together with "maintain a linebreak" switches.
If both are used, first removal of linebreaks are performed, then the addition of linesbreaks.
In short, together they are useful to remove redundant linebreaks (more than 1 linebreaks).

Changing default options

When the formatter is run, it already turns on many options. See here which ones.
It is possible to change what is on by default.
The steps to do this:
1. Create a file tpsf.cfg in the installation directory.
2. Set the switches in tpsf.cfg you want to make default.

Optionally,use -0 switch to turn all options off.

Supported character sets

These are the supported 8 bit ASCII supersets/character sets :
US7ASCII EE8MACCES     NEE8ISO8859P4
EE8MACCROATIANS     RU8BESTA
AR8ADOS710     EE8MSWIN1250     RU8PC855
AR8ADOS710T     EE8PC852     RU8PC866
AR8ADOS720     EL8DEC     SE8ISO8859P3
AR8ADOS720T     EL8ISO8859P7     TH8MACTHAIS
AR8APTEC715     EL8MACGREEKS     TH8TISASCII
AR8APTEC715T     EL8MSWIN1253     TR8DEC
AR8ARABICMACS     EL8PC437S     TR8MACTURKISHS
AR8ASMO708PLUS     EL8PC851     TR8MSWIN1254
AR8ASMO8X     EL8PC869     TR8PC857
AR8HPARABIC8T     ET8MSWIN923     US8PC437
AR8ISO8859P6     HU8ABMOD
AR8MSWIN1256     HU8CWI2     VN8MSWIN1258
AR8MUSSAD768     IN8ISCII     VN8VN3
AR8MUSSAD768T     IS8PC861     WE8DEC
AR8NAFITHA711     IW8ISO8859P8     WE8DG
AR8NAFITHA711T     IW8MACHEBREWS     WE8ISO8859P1
AR8NAFITHA721     IW8MSWIN1255     WE8ISO8859P15
AR8NAFITHA721T     IW8PC1507     WE8ISO8859P9
AR8SAKHR706          WE8MACROMAN8S
AR8SAKHR707          WE8MSWIN1252
AR8SAKHR707T          WE8NCR4970
BG8MSWIN         WE8NEXTSTEP
BG8PC437S          WE8PC850
BLT8CP921     WE8PC858
BLT8ISO8859P13         WE8PC860
BLT8MSWIN1257         WE8ROMAN8
BLT8PC775
BN8BSCII     LA8ISO6937
CDN8PC863     LA8PASSPORT
CEL8ISO8859P14     LT8MSWIN921
CL8ISO8859P5     LT8PC772
CL8KOI8R     LT8PC774
CL8KOI8U     LV8PC1117
CL8ISOIR111     LV8PC8LR
CL8MACCYRILLICS     LV8RST104090
CL8MSWIN1251     N8PC865
EE8ISO8859P2     NE8ISO8859P10.
They can be used with -cs switch.

Line endings

The software accepts mac(charcode 13)/dos(charcodes 13,10) and unix(charcode 10) like line endings. It also detects the line ending used and the same type of end of line characters are inserted when a switch requires it.

Backing up

The original source is backed up. Even old backups are backed up.
The backup directory can be specified by -bdir switch. Otherwise, it is
the "tmp" directory.

Syntax checking

Some syntax checking is performed. If the check fails, the code is not tidied.
The followings are checked:
-the balance of brackets ()
-runaway/unclosed '
-runaway/unclosed q'
-runaway/unclosed "
-runaway/unclosed /*
-blocks are closed properly (missing end if,end loop, end package detection)

Return code of the software

If the executable runs without error, it returns error code 0.

Otherwise, it returns 2.

Safety measures

-Syntax checking. If it fails, the code is not formatted.
-The formatter checks itself. So it does not produce bad code easily. If self-check fails, it displays the message : Integrity error and the code is not touched. There is an exception. If -pcr switch is used, the check may not be performed as it could fail.
-It backs up the original source in the temp directory. (designated by tmp enviroment variable). By default, 40 versions are backed up. It can be changed by -b flag.
-Unless you specify -io, the output file is the original filename plus .tdy. So the original one is not overwritten.

License

Tidycode Pl\Sql Formatter
Idea/programmed by Marton Papp ((C)2007-2010 )
See licensetu.txt

Switch/option	Meaning
-i[r] inpsrc	input source can be:a filename,stdin,globbing [recursively]
-o outdest	output destination can be:a filename,stdout
-io[r] filename	same input and output file [recursively]
-oe ext	extension of output if -(i)o is not specified
-od[r] dir	output directory [recursive mode]
-ef[r] filename	exclude filenames from formatting [recursively]
-sl lineno	read input from this line (don't use lines before)
-el lineno	don't use input after this line
-cs charset	8 bit ascii like character set of the input
-t s1[,s2..]	tab is equal to s1,s2.. columns
-ut[+-]	use only tabs for indenting
-uts[+-]	use tabs and spaces if necessary for indenting
-is size(t)	indent size in spaces or in tabs (generally)
-in[+-]	indent lines yes/no
-il[Le] size(t)	indent size in spaces or in tabs at (Le)vel(specific)
-sit[+-]	set indent sizes to tab sizes
-ctis[+-]	convert tabs into spaces
-uk[+-]	uppercase keywords yes/no
-ck[+-]	capital keywords yes/no
-lk[+-]	lowercase keywords yes/no
-li[+-]	lowercase identifiers
-ui[+-]	uppercase identifiers
-ci[+-]	capitalised identifiers
-si ident	set identifiers to look like ident
-mcs size	maximum allowed size of columns
-amso[+-]	allow moving statements only
-asc[+-]	allow shortening comments
-amc[+-]	allow moving comments
-abusc[+-]	allow broken up standalone comments
-abuc[+-]	allow broken up comments
-acoslc[+-]	allow conversion of single line comments
-aci[+-]	allow changing indentation
-acs[+-]	allow changing space
-c[+-]	compactify, remove redundant spaces/keep
-co[+-]	remove spaces around operations (+,- etcdo nothing/)
-sao[+-]	add space around operations/do nothing
-rsaob[+-]	remove spaces after opening brackets/keep
-rsbcb[+-]	remove spaces before closing brackets/keep
-ncb[+-]	don't remove spaces around brackets/do nothing
-rs[+-]	keep the relative identation of an allowed sql/do nothing
-id[+-]	extra indentation for declarations yes/no
-iaew[+-]	extra indentation after exception when yes/no
-iacw[+-]	extra indentation after case when yes/no
-iac[+-]	extra indentation after cursor yes/no
-isc[+-]	indent standalone comments
-isc2[+-]	indent standalone comments in some special cases too
-iic[+-]	indent inside comments/do nothing
-iibe[+-]	indent inside begin/end block
-itlwb[+-]	indent then/loop with block
-ni[+-]	nice indents
-coi size(t)	continuation indent size in spaces or in tabs
-iiie[+-]	indent or do not indent if in else
-clb[+-]	column like lists inside brackets
-actv[+-]	align consecutive types in declarations vertically
-ps size	padding size, number of spaces between variables and types
-acav[+-]	align consecutive assignments (:=) vertically
-acev[+-]	align consecutive equalities (=) vertically
-acarv[+-]	align consecutive arrows (=>) vertically
-pcr[+-]	put commas right
-as[+-]	align selects
-au[+-]	align updates
-aaw[+-]	align after where clauses
-ac[+-]	align conditions in IF/THEN
-aroa[+-]	align right of assignments
-pius[+-]	put 'into' under select
-plouwh[+-]	put logical operations under where/having
-bus[+-]	break up sql statements
-rask[+-]	right align sql keywords
-actvct[+-]	align consecutive types in create table-s vertically
-aav[+-]	align aliases vertically
-bbj[+-]	(line)break before joins
-isis size	indentation size (in spaces) in Sql clauses
-ins[+-]	extra indentation after selects
-iu[+-]	extra indentation after updates
-iaw[+-]	extra indentation after where clauses
-milipl no	minimum list items per line
-mlipl no	maximum list items per line
-buml[+-]	break up multistatement lines
-rael[+-]	remove all empty lines
-mlapcd[+-]	maintain a linebreak after cursor declarations
-rlapcd[+-]	remove linebreaks after cursor declarations
-mlai[+-]	maintain a linebreak after each if
-rlai[+-]	remove linebreaks after each if
-mlaf[+-]	maintain a linebreak after each for loop
-rlaf[+-]	remove linebreaks after each for loop
-mlaw[+-]	maintain a linebreak after each while loop
-rlaw[+-]	remove linebreaks after each while loop
-mlal[+-]	maintain a linebreak after each 'loop' loop
-rlal[+-]	remove linebreaks after each 'loop' loop
-mlal[+-]	maintain a linebreak after each 'loop' loop
-mlafu[+-]	maintain a linebreak after function names
-rlapr[+-]	remove linebreaks after procedure
-mlapa[+-]	maintain a linebreak after package
-rlapa[+-]	remove linebreaks after package
-dbvn[+-]	declaration based variable names
-kitt[+-]	keep if/thens together
-kwlt[+-]	keep while/loop together
-kflt[+-]	keep for/loop together
-kpit[+-]	keep procedure and is/as together
-kwtt[+-]	keep when/then together
-sbi[+-]	show block information after the end of blocks
-sbi.ml lines	show it for blocks that have at least minimum lines
-sbi.m string	marker string to use to denote end of block information
-sbi.max chars	maximum characters to show from the start of the block
-rbi[+-]	remove block information after the end of blocks
-cmk[+-]	check for missing keywords [loop/then]
-x	print the configuration and does not format
-v	verbose , same as -x but it will tidy
-sf[+-]	show filenames in block stack
-rl[+-]	reverse listing of block stack
-sr[+-]	show the result of formatting
-ss[+-]	show the summary of multiple formatting
-b maxvers	number of backup versions to keep
-bdir dir	location of the backup directory
-ls filename	load settings/switches from a file
-gsfif[+-]	get switches from input file
-ae ext	accept files with ext only
-sw[+-]	show switches in effect
-sdw	show default switches
-swc	show switches compactly
-0	sets all switches to off
-pl[+-]	prepend logo and formatting info to the output file
-plo[+-]	prepend logo only
-mte[+-]	multithreaded execution for multiple files
-w[+-]	show warnings/potential problems in code
-al[+-]	set all formatting switches on and left-aligned sql
-ar[+-]	set all formatting switches on and right-aligned sql
-ai[+-]	set all formatting switches on and indent in sql
-lsql[+-]	left-aligned sql
-rsql[+-]	right-aligned sql
-isql[+-]	indent in sql
-alol[+-]	at least one linebreak after blocks and elsewhere
-ol[+-]	one linebreak after blocks and elsewhere
-html[+-]	format of output is html
-ht filename	filename of html template
-tag\\[type\\]	tags use html tags for token type
-h	this help

Manual for Tidycode Pl/Sql Formatter version 2.5.1

Contents

Description

How to use it

How to use it in an editor

About the use of switches

List of switches

Switches and examples

-h

-i[r] filename input file

-o filename output file (tidied up)

-io[r] filename same input and output file

--oe ext extension of output if -o is not specified

-od[r] output directory if -i is specified

-ef[r] exclude filename

-sl lineno tidy lines from this lineno on

-el lineno tidy lines up to that lineno

-cs charset 8 bit ascii like character set of the input

-t s1[,s2..] tab is equal to s1,s2.. columns

-ut[+-] use only tabs for indenting

-uts[+-] use tabs and spaces if necessary for indenting

-is size indent size in spaces (generally)

-in[+-] size indent size in spaces (generally)

-il[Level] size indent size at Level(specific)

-sit[+-] set indent sizes to tab sizes

-ctis[+-] convert tabs into spaces

-mcs mcssize maximum column width allowed

-amso[+-] allow moving statements only

-asc[+-] allow shortening comments

-amc[+-] allow moving comments

-abusc[+-] allow broken up standalone comments

-abuc[+-] allow broken up comments

-acoslc[+-] allow conversion of single line comments

-acs[+-] allow changing space

-aci[+-] allow changing indentation

-uk[+-] uppercase keywords yes/no

-ck[+-] capital keywords yes/no

-lk[+-] lowercase keywords yes/no

-ui[+-] uppercase identifiers yes/no

-ci[+-] capital identifiers yes/no

-li[+-] lowercase identifiers yes/no

-clb[+-] column like lists inside brackets

-c[+-] compactify, remove redundant spaces/keep

-co[+-] remove spaces around operations (+,- etcdo /nothing)

-sao[+-] add space around operations/do nothing

-rsaob[+-] remove spaces after opening brackets/keep

-rsbcb[+-] remove spaces after opening brackets/keep

-ncb[+-] don't remove spaces around brackets/do nothing

-rs[+-] keep the relative indentation of a sql/do nothing

-id[+-] indent after declarations yes/no

-iaew[+-] indent after when in exception yes/no

-iacw[+-] indent after when in case statement yes/no

-iac[+-] indent after cursor yes/no

-isc[+-] indent standalone comments

-isc2[+-] indent standalone comments in special cases too

-iic[+-] indent inside comments/do nothing

-iibe[+-] indent inside begin/end block

-itlwb[+-] indent then/loop with the next block

-ni[+-] nice indents

-iiie[+-] indent if in else

-actv[+-] align consecutive types in declarations vertically

-ps size padding size, number of spaces between variables and types

-actvct[+-] align consecutive types in create table-s vertically

-acav[+-] align consecutive assignments (:=) vertically

-acev[+-] align consecutive equalities (=) vertically

-acarv[+-] align consecutive arrows (=>) vertically

-aav[+-] align aliases vertically

-pcr[+-] put commas right

-as[+-] align selects

-au[+-] align updates

-aaw[+-] align after where clauses

-ac[+-] align conditions

-aroa[+-] align right of assignments

-kitt[+-] keep if/then together

-kwlt[+-] keep while/loop together

-kflt[+-] keep for/loop together

-kpit[+-] keep (procedure/package)/(is/as) together

-kwtt[+-] keep when/then together

-sbi[+-] show block information after the end of blocks

-sbi.ml lines show it for blocks that have at least minimum lines