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.
Goto to the installation directory and enter in dos:
tpsf -h
for the options and help,
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
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+
|
Switch/option |
Meaning |
| -i inpsrc | input source can be:a filename,stdin and clipboard |
| -o outdest | output destination can be:a filename,stdout and clipboard |
| -io filename | same input and output file |
| -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 |
| -clb[+-] | column like lists inside brackets |
| -actv[+-] | align consecutive types in declarations vertically |
| -acav[+-] | align consecutive assignments (:=) 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 |
| -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 |
| -cmk[+-] | check for missing keywords [loop/then] |
| -x | print the configuration and does not format |
| -v | verbose , same as -x but it will tidy |
| -b maxvers | number of backup versions to keep |
| -bdir dir | location of the backup directory |
| -ls filename | load settings/switches from a file |
| -ae ext | accept files with ext only |
| -sw[+-] | show switches in effect |
| -0 | sets all switches to off |
| -html[+-] | format of output is html |
| -ht filename | filename of html template |
| -tag\\[type\\] | tags use html tags for token type |
| -h | this help |
lists you all possible switches you can use.
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.
E.g. tpsf -i=h.sql
E.g tpsf -i stdin
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
This is a shortcut for -i and -o.
E.g. tpsf -io=h.sql
The resultant fiile will not contain lines before line lineno
of the original file.
E.g. tpsf -i=h.sql -sl 4
The resultant file will not contain lines after the line
lineno of
the original file.
E.g. tpsf -i=h.sql -sl 2000
Probably it is one of the most important switches.
E.g. tpsf -io=h.sql -is 3
It affects if the beginning of each line is indented
according to block level.
E.g. tpsf -io=h.sql -is 3
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
Indent size and tab column size don't need to be the same but it is often desirable if they are the same.
This switch sets indent size (which they
would be specified
by -is, il) to tab column sizes.
E.g. tpsf -io=h.sql -t 3 -sit
If a line is longer than mcssize columns, it will be shortened using different methods.
The methods:
-splitting the line by tokens(-ams-) or statements (-ams+),
so it becomes shorter
-removing redundant * or - in case of /*****/ and --------- line if
-asc is on.
-comments can be moved into next line if -amc is on.
-standalone comments are broken up on words if -abusc is on
-comments are broken up on words if -abuc is on
E.g. tpsf -io=h.sql -mcs 80
| Original |
/*********/ /**unit start***/ ------------ /*one two*/ begin/***/ a:=5+2; end; |
Tidied -mcs 5 |
/***/ /**unit start*/ ----- /*one two*/ begin /***/ a:=5+ 2; end; |
| Original |
a:=4; b:=5; |
Tidied -mcs 7 -amso+ |
a:=4; b:=5; |
| Original |
a:=4; /******/ |
Tidied -mcs 9 -asc+ |
a:=4;/**/ |
| Original |
a:=4;/******/ |
Tidied -mcs 9 -amc+ |
a:=4; /******/ |
| Original |
/* my long comment*/ |
Tidied -mcs 5 -abusc+ |
/* my long comment*/ |
| Original |
e:=e*4;/* my long comment*/ |
Tidied -mcs 13 -abux+ |
e:=e*4; /* my long comment*/ |
| Original |
--my long comment
too long |
Tidied -mcs 5 -acoslc+ -abusc+ |
/* my long comment too long*/ |
| Original |
begin f:=5; end; |
Tidied -mcs 5 -acs+ |
begin f:=5 end; |
| Original |
begin f:=5; end; |
Tidied -mcs 5 -aco+ |
begin f:=5 end; |
All keywords will be converted into uppercase letters. (BEGIN
IF VARCHAR2)
E.g. tpsf -io=h.sql -uk+
All keywords will be capitalized. (Begin If Varchar2)
E.g. tpsf -io=h.sql -ck+
All keywords will be converted into uppercase letters (begin if varchar)
E.g. tpsf -io=h.sql -ui+
Default state:off.
All identifiers will be capitalized. (Terra5:=3;)
E.g. tpsf -io=h.sql -ci+
E.g. tpsf -io=h.sql -li+
E.g. tpsf -io=h.sql -clb+
this:
a := (a + 1
+ 4
+ 5
+ 8);
will become that:
a := (a + 1
+ 4
+ 5
+ 8);
E.g. tpsf -io=h.sql -c+
|
Original |
procedure h is |
Tidied |
procedure h is |
E.g. tpsf -io=h.sql -co+
|
Original |
d:= 5 + 7; |
Tidied |
d:=5+7; |
E.g. tpsf -io=h.sql -co- -sao+
|
Original |
d:=5+7; |
Tidied |
d := 5 + 7; |
|
Original tpsf -io=h.sql -rsaob- |
FOR rec IN (
SELECT g,quality FROM duality |
|
Tidied tpsf -io=h.sql -rsaob+ |
FOR rec IN
(SELECT g,quality
FROM duality |
|
Original tpsf -io=h.sql -rsbcb-l |
FOR rec IN (
SELECT g,quality FROM duality |
|
Tidied tpsf -io=h.sql -rsbcb+ |
FOR rec IN
(SELECT g,quality
FROM duality |
| Original tpsf -io=h.sql -ncb- |
FOR rec IN(SELECT
* FROM duality |
| Tidied tpsf -io=h.sql -ncb+ | FOR rec IN
(SELECT * FROM duality WHERE g = 5) |
|
Original |
select * |
|
Tidied |
select * |
|
Original |
PROCEDURE recurse IS |
|
Tidied |
PROCEDURE recurse IS b number:=5; d456 number:=456; cursor one is select list from todo; |
|
Original |
EXCEPTION |
|
Tidied |
EXCEPTION |
|
Original |
CASE opinion |
|
Tidied |
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; |
|
Original |
CURSOR b IS |
Tidied |
CURSOR b IS |
| 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; |
| 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; |
If it is on, it will indent text inside /*..*/
|
Original |
BEGIN 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; |
If this option is on, then/loop keyword are indented more than if the option is off.
|
Original |
IF
s=1 THEN IF (record_exists IS NOT NULL AND record_exists = yes_flag) THEN a:=3; END IF; END IF; |
Tidied |
IF
s=1 THEN IF (record_exists IS NOT NULL AND record_exists = yes_flag) THEN a:=3; END IF; END IF;
|
|
Original |
if
s=1 then IF (record_exists IS NOT NULL AND record_exists = yes_flag) THEN a:=3; end if; end if; |
Tidied |
IF
s=1 THEN IF (record_exists IS NOT NULL AND record_exists = yes_flag) THEN a:=3; END IF; END IF; |
|
Original |
d integer:=5; |
Tidied |
d
integer:=5; |
|
Original |
derival:=0; |
Tidied |
derival:=0; |
|
Original |
call_now(myownname
=> USER, |
Tidied |
call_now(myownname
=> USER, |
|
Original |
PROCEDURE myerr( |
Tidied |
PROCEDURE myerr( |
|
Original |
select
e,j from tablea, tableb where f1>f2 and f4=9; |
Tidied |
select
e,j from tablea, tableb where f1>f2 and f4=9; |
|
Original |
update
t set g=56, i=3; |
Tidied |
update
t set g=56, i=3; |
|
Original |
where
f1>f2 and f4=9; |
Tidied |
where
f1>f2 and f4=9; |
|
Original |
if
u>2 and b!=4 then f:=9; end if; |
Tidied |
if
u>2 and b!=4 then f:=9; end if; |
|
Original |
a:=b +c; |
Tidied |
a:=b +c; |
| Original |
if
f>3 then e:=3; end if; |
Tidied |
if
f>3 then e:=3; end if; |
| Original |
while
f>3 loop f:=f-1; end if; |
Tidied |
while
f>3 loop f:=f-1; end if; |
| Original |
for t in
1..7 loop e:=3; end loop; |
Tidied |
for t in
1..7 loop e:=3; end loop; |
| Original |
package
x IS begin |
Tidied |
package x
IS begin |
| Original |
CASE WHEN v IS NULL THEN NULL |
Tidied |
CASE WHEN FAC_ORDER.CORE_COMPLETED_DATE IS NULL THEN NULL |
| Original |
select amount into amountv from
budget where id=4 |
Tidied |
select amount into amountv from budget where id=4 |
| Original |
select amount into amountv from
budget where id=4 |
Tidied |
select amount into amountv from budget where id=4 |
| Original |
select amount into amountv from
budget where id=4 |
Tidied |
select amount into amountv from budget where id=4 |
| Original |
declare u integer; b integer; begin a:= 3 ;b:=4; select * from master_table; t:=4; if 4 then r:=3; else r:=4;end if; loop g:=g+1; end loop; end; |
Tidied |
DECLARE u INTEGER; b INTEGER; BEGIN a:= 3 ; b:=4; SELECT * FROM master_table; t:=4; IF 4 THEN r:=3; ELSE r:=4; END IF; LOOP g:=g+1; END LOOP; END; |
| Original |
select * from budget where money<100 and deadline>'1.1.2007' |
Tidied |
select * from budget where money<100 and deadline>'1.1.2007' |
| Original |
dbms_output.put_line('end of program'); |
Tidied |
Dbms_Output.put_line('end of program' |
| Original |
select 1, 2,3,4,5, 6,7,8,9 from dual |
Tidied |
select 1,2,3,4,5, 6,7,8, 9 from dual |
| Original |
select 1,2,3,4,5, 6,7,8,9 from dual |
Tidied |
select 1,2,3, 4,5, 6,7,8, 9 from dual |
| Original |
select 1,2,3,4,5, 6,7,8,9 from dual |
Tidied |
select 1,2,3,4,5, 6,7,8,9 from dual |
| Original |
declare cursor simple is select * from dual; b integer; begin |
Tidied |
DECLARE CURSOR simple IS SELECT * FROM dual; BEGIN |
| Original |
iif
g>8 then f:=d+3; end if; --next |
Tidied |
if
g>8 then f:=d+3; end if; --next |
| Original |
for d in
1..8 loop f:=d+3; end loop; --next |
Tidied |
for d in
1..8 loop f:=d+3; end loop; --next |
| Original |
while
f<200 loop f:=d+3; end loop; --next |
Tidied |
while
f<200 loop f:=d+3; end if; --next |
| Original |
loop f:=d+3; end loop; --next |
Tidied |
loop f:=d+3; end loop; --next |
| Original |
procedure next(b integer) is begin |
Tidied |
procedure next (b integer) is begin |
| Original |
end; --next |
Tidied |
end; --next |
| Original |
end
mypackage; --next |
Tidied |
end
mypackage; --next |
| Original |
declare cursor simple is select * from dual; b integer; begin |
Tidied |
DECLARE CURSOR simple IS SELECT * FROM dual; BEGIN |
| Original |
iif
g>8 then f:=d+3; end if; --next |
Tidied |
if
g>8 then f:=d+3; end if; --next |
| Original |
for d in
1..8 loop f:=d+3; end loop; --next |
Tidied |
for d in
1..8 loop f:=d+3; end loop; --next |
| Original |
while
f<200 loop f:=d+3; end loop; --next |
Tidied |
while
f<200 loop f:=d+3; end if; --next |
| Original |
loop f:=d+3; end loop; --next |
Tidied |
loop f:=d+3; end loop; --next |
| Original |
end; --next |
Tidied |
end; --next |
| Original |
end
mypackage; --next |
Tidied |
end
mypackage; --next |
|
Original |
declare cursor Blag is ..; |
Tidied |
declare cursor Blag is ..; |
Number of old backup versions to keep +1 (including the
current backup version that is being created)
| Original |
CREATE PACKAGE BODY |
Tidied -html+ |
<font color=blue>CREATE</font><font color=blue> </font><font color=blue>PACKAGE</font><font color=blue> </font><font color=blue>BODY</font> |
| 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 |
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.
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)
If the executable runs without error, it returns error code 0.
Otherwise, it returns 2.-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.
Tidycode Pl\Sql Formatter
Idea/programmed by Marton Papp ((C)2007-2008 )
See licensetu.txt