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+ -soe+ -sf+ -rl+ -sr+
There are switches that set a number of switches. They are
categorized as styles.
See them here
|
Switch/option |
Meaning |
| general: | |
| -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 |
| wrapping: | |
| -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 |
| spaces: | |
| -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 |
| indentation: | |
| -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 |
| -iiie[+-] | indent or do not indent if in else |
| alignments: | |
| -clb[+-] | column like lists inside brackets |
| -actv[+-] | align consecutive types in declarations vertically |
| -ps size | padding size, number of spaces between variables and types |
| -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 |
| sql: | |
| -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 verticall |
| -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 |
| linebreaks: | |
| -milipl no | minimum list items per line |
| -mlipl no | maximum list items per line |
| -buml[+-] | break up multistatement lines |
| -rael[+-] | remove all empty lines |
| -mlacd[+-] | maintain a linebreak after cursor declarations |
| -rlacd[+-] | 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 |
| -mlal[+-] | maintain a linebreak after each 'loop' loop |
| -rlal[+-] | remove linebreaks after each 'loop' loop |
| -mlafu[+-] | maintain a linebreak after function names |
| -rlafu[+-] | remove linebreaks after function names |
| -mlapr[+-] | maintain a linebreak after each procedure |
| -rlapr[+-] | remove linebreaks after procedure |
| -mlapa[+-] | maintain a linebreak after package |
| -rlapa[+-] | remove linebreaks after package |
| other: | |
| -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 |
| -sic[+-] | show/add insert comment/field information in INSERT |
| -sic.m string | marker string to use to denote an insert comment/field infos |
| -ric[+-] | remove insert comments/field connections |
| -ifs start | if start of statement is start.. |
| -ei | end if |
| -ifsib[+-] | ignore forward slashes in brackets |
| -ifsl[+-] | ignore forward slashes everywhere |
| -no | no output for given input |
| -np | no processing |
| -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 |
| -swx[+-] | show switches in effect and explain |
| -sdw | show default switches |
| -sdwx | show default switches and explain |
| -swc | show switches compactly |
| -swcx | show switches compactly and explain |
| -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 |
| -soe[+-] | stop on errors if multiple files are processed |
| -w[+-] | show warnings/potential problems in code |
| styles: | |
| -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 sq |
| -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: | |
| -html[+-] | format of output is html |
| -ht filename | filename of html template |
| -tag\[type\] tags | use html tags for token type |
| Pl/Sql preprocessor: | |
| -pp[+-] | preprocess(yes/no) |
| -pp.I path | add include path |
| -pp.D macro=value | define macros |
| -pp.pf filename | preload this file |
| -pp.clp | clear include path |
| -pp.cpfl | clear preloaded file list |
| -pp.cmd | clear macro definitions |
| -pp.ld[+-] | insert line directives |
| -pp.ild[+-] | ignore line directives in source |
| -pp.kld[+-] | keep line directives in source |
| -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.
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 -i=h.sql
E.g tpsf -i stdin
tpsf -i *.sql -i exception.srt
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.
If the filename is null, there will not no output. Or you use the
switch -no.
E.g. tpsf -i=h.sql -o=h2.sql
E.g tpsf -i stdin -o stdout
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
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
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.
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
The resultant file 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
It converts non-leading tabs into spaces with the exception of multiline comments.
For them, leading tabs may be converted too if they span multiple lines.
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 |
if a then null; else if b then null; end if; end if; |
Tidied |
if a then null; else if b then null; end if; end if; |
|
Original |
d integer:=5; |
Tidied |
d integer:=5; |
|
Original |
d integer:=5; |
Tidied |
d integer:=5; |
|
Original |
create table listofjobs ); |
Tidied |
create table listofjobs |
|
Original |
derival:=0; |
Tidied |
derival:=0; |
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 |
where myownname = 'richard' and |
Tidied |
where myownname = 'richard' and |
|
Original |
call_now(myownname => USER, |
Tidied |
call_now(myownname => USER, |
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 |
select name dubbed, |
Tidied |
SELECT name dubbed, emot emotion, c "alias", e2 "alias2", ffff, gg 4 FROM table122 a, table2 b |
|
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 |
for i:=1 to 100 loop null; end loop; |
Tidied |
for i:=1 to 100 loop null; end loop; /*of block:for i:=1 to 100 loop*/ |
| Original |
INSERT INTO invoices (buyer, amount, Date) VALUES ('David Rod', 244, 'Jan-3-2004'); |
Tidied |
INSERT INTO invoices (buyer, amount, DATE) VALUES (/*buyer<-*/'David Rod', /*amount<-*/244, /*Date<-*/'Jan-3-2004'); |
| 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 name, title||' '||job FROM names natural JOIN employees; |
Tidied |
select name, title || ' ' || job from names natural JOIN employees; |
| Original |
SELECT name, title||' '||job FROM names ; |
Tidied |
select name, title || ' ' || job from names ; |
| 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 |
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 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 |
Here is the table that shows similarities and differences between the Pl/Sql preprocessor implemented and a C preprocessor.
| Operation | In a C preprocessor | In this Pl/Sql preprocessor | Syntax | Pl/Sql preprocessor example |
| Changing lineno or filename | #line lineno filename | #line lineno filename | same syntax | #line 23 "delete.sql" |
| Defining a macro | #define macroname definition | #define macroname definition - at the end of line is continuation indicator |
similar syntax - is used instead of / |
#define do dbms_output #define dd dmbs_output- ('line is executed'); #define get_all(table) select * from table |
| Operators in macros | # - stringify ## concetanate |
# - stringify double quotes ### stringify single quotes ## concetanate (single quoted strings too and q operator) |
### is new | #define tostring(arg) ###arg tostring(done) -> 'done' #define row(no) row##no E.g. row(7) -> row7 |
| Print an error message and stop | #error "message" | #error "message" | same | #error "defines are incorrect" |
| Print a warning message | #warning "message" | #warning "message" | same | #warning "run this as SYSTEM user" |
| Including a file | #include "filename" | #include "filename" | same | #include "defaultoptions.sql" |
| Branching | #ifdef, #ifndef, #if, #endif, #else and #elif | #ifdef, #ifndef, #if, #endif, #else and #elif | same | #ifdef clear_tables truncate table projects; #endif |
| Conditions in #if and like directives |
operands +,-,*/,..etx | ? : is not supported, there is OR instead of || there is AND instead of && there is NOT instead of ! there is MOD instread of % the rest of operators are supported: +,-\,*,&,|,^,>>,<<, defined |
similar | #if defined tables and defined ignore #warning "tables are ignored" #endif |
| Case handling | Macro names, identifiers and directives are case sensitive. |
Macro names, identifiers and directives are case insensitive. |
differs | #DEFINE f from F -- will match F |
| Integers in conditions | can be unsigned 64 bit integer and signed | can be signed 64bit | differs | #if -5 |
| __LINE__ | Current line number single quoted | Current line number double quoted | similar | dbms_output.put_line(__line__); -> dbms_output.put_line('76'); |
| __FILE__ | Filename single quoted | Filename single quoted | similar | bms_output.put_line(__file__); -> dbms_output.put_line('c:\project\run.sql'); |
It turns on the Pl/Sql preprocessor.
If formatting and block level syntax checking is not needed use
-np too.
The preprocessor has limitations, it does not handle doc Sqlplus
command. It will still work but if doc comment contains a macro or
a directive , it may be processed while it should not.
Also, if prompt command contains " or ' or q operator, it may not
work properly
E.g. tpsf -i h.sql -pp
It sets the search path where the file are looked for when using #include.
E.g. tpsf -i h.sql -pp -pp.I c:\sql\includes
It presets the value of a macro.
E.g. tpsf -i h.sql -pp -pp.D clear_tables=1
This file will be preprocessed before the input files. It can be used to define macros.
This switch can be used multiple times to preload more that one
file.
It is useful to define macros that are to be used everywhere.
E.g. tpsf -i h.sql -pp -pp.pf shortcuts.sql
It clears the search path. It is useful if something is inherited from tpsf.cfg for example.
E.g. tpsf -i h.sql -pp -pp.clp -pp.I c:\sql\includes
It clears the list of files that will be preprocessed before processing the input files.
It is useful if something is inherited from tpsf.cfg for example.
E.g. tpsf -i h.sql -pp -pp.cmd -pp.cpfl -pp.pf shortcuts.sql
It removes all existing macros.
It is useful if something is inherited from tpsf.cfg for example.
E.g. tpsf -i h.sql -pp -pp.cmd -pp.D clear_tables=1
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-2011 )
See licensetu.txt