Rule #0: $_ is default everything.
if you call a function and dont pass a parameter it uses $_.
if you execute a function and dont put the result anywhere,
it goes to $_.
if you read a file, and dont specify an into variable,
it goes to $_.
...
Rule #1: All variables begin with $ and are numeric or alpha strings.
Arrays begin with @ and are 1 dimensional
You can do a lot with a perl program of this structure:
#!/usr/local/bin/perl -w # execute perl, activate warnings
# # comment
open(OLD,"filename"); # open a file
while (<OLD>) { # read a record into $_
chomp; # remove CR/LF character from $_
if (/Z/) {next} # if this is true (does $_ match Z?), read next record
if (...) {last} # if this is true, end loop
@words=split(' '); # seperate record($_) into fields
$w0=$words[0]; # $w0 = field 1
$w1=$words[1]; # $w1 = field 2
$w2=$words[2]; # $w2 = field 3
print "bla bla $w0 yada-yada $w1 and $w3\n";
# output something, notice \n
} # end read loop
close(OLD); # close file
Example: Analyze all tables in a database:
From server manager run @analyzet0.sql:
spool analyzet0
select owner, table_name
from dba_tables
where not (owner='SYSTEM' or owner='SYS')
order by 1,2;
spool off
analyzet0.log:
OWNER TABLE_NAME
------------------------------ ------------------------------
ANDYA AGA_HOLD
ANDYA AGA_HOLD2
ANDYA AGA_HOLD3
ANDYA AGA_HOLD4
CFIS ANNREPTS
CFIS ANN_AGENCY
...
760 rows selected.
Now run: (analyzet0.pl > analyzet1.sql)
#!/usr/local/bin/perl -w
$k=0;
open (OLD,'analyzet0.log');
print "spool analyzet1\n";
while (<OLD>) {
if ($k++<2) {next;}
if (/rows/) {next;}
chomp;
@words=split(' ');
$owner=$words[0];
$tn=$words[1];
print "select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), '$owner', '$tn' from dual;\n";
print "analyze table $owner",".","$tn compute statistics;\n";
}
print "select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') from dual;\n";
print "spool off\n";
close(OLD);
analyzet1.sql:
spool analyzet1
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'ANDYA', 'AGA_HOLD' from dual;
analyze table ANDYA.AGA_HOLD compute statistics;
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'ANDYA', 'AGA_HOLD2' from dual;
analyze table ANDYA.AGA_HOLD2 compute statistics;
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'ANDYA', 'AGA_HOLD3' from dual;
analyze table ANDYA.AGA_HOLD3 compute statistics;
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'ANDYA', 'AGA_HOLD4' from dual;
analyze table ANDYA.AGA_HOLD4 compute statistics;
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'CFIS', 'ANNREPTS' from dual;
analyze table CFIS.ANNREPTS compute statistics;
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), 'CFIS', 'ANN_AGENCY' from dual;
analyze table CFIS.ANN_AGENCY compute statistics;
...
spool off
Run server mamager @analyzet1 to produce:
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'ANDY 'AGA_HOL
--------------------------------------------------------------------------- ----- --------
1998/03/27 16:15:47 ANDYA AGA_HOLD
1 row selected.
Statement processed.
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'ANDY 'AGA_HOLD
--------------------------------------------------------------------------- ----- ---------
1998/03/27 16:15:48 ANDYA AGA_HOLD2
1 row selected.
Statement processed.
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'ANDY 'AGA_HOLD
--------------------------------------------------------------------------- ----- ---------
1998/03/27 16:15:48 ANDYA AGA_HOLD3
1 row selected.
Statement processed.
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'ANDY 'AGA_HOLD
--------------------------------------------------------------------------- ----- ---------
1998/03/27 16:15:48 ANDYA AGA_HOLD4
1 row selected.
Statement processed.
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'CFI 'ANNREPT
--------------------------------------------------------------------------- ---- --------
1998/03/27 16:15:48 CFIS ANNREPTS
1 row selected.
Statement processed.
TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS') 'CFI 'ANN_AGENC
--------------------------------------------------------------------------- ---- ----------
1998/03/27 16:15:48 CFIS ANN_AGENCY
1 row selected.
Statement processed.
Now you can see how long each analyze took, and if any of them fail,
you be able to get their names. This would be much more complicated
using sql to generate sql. Also, you can imagine the implications of
using perl to calculate file sizes, extent sizes, and the like in
generating sql that would be impossible in using sql to generate sql.