SQL & PL/SQL

THANK'S JOHN SPENCER , THAT PROBLEM (PLS-00201:
identifier 'TRIM' must be declared) IS SOLVED !!!!

BUT NOW THERE IS A NEW PROBLEM, I THINK LINKED TO ONE O
MORE "EXCEPTION" NOT DEFINED FOR THE FUNCTION "FOPEN" AT LINE 24
OF THE SCRIPT (THERE ARE "EXCEPTION" IN TO THE BEGIN END, BUT
FOR OTHERS "FOPEN"..)
CAN HELP ME FOR THE JUST SINTAX ???I NOT HAVE MORE ESPERIENCE IN
THIS ...HOWEVER TANKS AT ALL !!
BYE ...ANGELO

THIS THE ERRORS:
*****************************************************************

SQL*Plus: Release 3.3.4.0.0 - Production on Fri Jan 4 13:35:15
2002

Copyright (c) Oracle Corporation 1979, 1996. All rights
reserved.


Connected to:
Oracle7 Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production

SQL> @prova.sql
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 82
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at line 23


Disconnected from Oracle7 Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production
*****************************************************************


THIS IS THE SCRIPT...

*****************************************************************

+1 declare
+2 hw_id
tivoli.computer_system.hardware_system_id%type;
+3 tme_id
tivoli.computer_system.tme_object_id%type;
+4 pag integer;
+5 crlf varchar2(3);
+6 tab char(1);
+7 mtab char(4);
+8 formfeed char(1);
+9 n integer;
+10 nmachine integer; --
contatore macchine; ne vanno 3/pag
+11 tabs varchar2(20); --
tabulatore variabile (fino a 20)
+12 rpt utl_file.file_type; -- file
contenente il report del sw
+13 tmprpt utl_file.file_type; -- report
temporaneo per ultime modifiche sw
+14 sw_name varchar2(1024); -- sw nome
+ vers.
+15 begin
+16 pag := 0;
+17 nmachine := 0;
+18 crlf := chr(10);
+19 tab := chr(09); -- tabulatore
+20 mtab := tab||tab||tab||tab;
+21 formfeed := chr(12);
+22
+23 -- apertura file
+24 rpt := utl_file.fopen
('/oracle/tuning','rpt_sw.txt','w');
+25
+26 -- prima pagina
+27 utl_file.new_line(rpt,25);
+28 utl_file.put_line(rpt,'
=================================================================

===================');
+29 utl_file.new_line(rpt,7);
+30 utl_file.put_line(rpt,' I
N V E N T A R I O S O F T W A R E');
+31 utl_file.put_line(rpt,' - -
- - - - - - - - - - - - - - - -');
+32 utl_file.new_line(rpt,6);
+33 utl_file.put_line(rpt,'
Stampa del ' || to_char(sysdate, 'DD.MM.YYYY') ||'
alle '||to_char(sysdate
,'hh24:mi:ss') );
+34 utl_file.new_line(rpt);
+35 utl_file.put_line(rpt,'
=================================================================

===================');
+36
+37 -- loop sul sistema hw (tabella
TIVOLI.COMPUTER_SYSTEM)
+38 for s in (select * from tivoli.computer_system order
by tme_object_label) loop
+39 -- for s in (select * from tivoli.computer_system
where tme_object_label='rmcc007') loop
+40 hw_id := s.hardware_system_id;
+41 -- trova l'ID Tivoli fino al carattere '+'
(escluso)
+42 tme_id := substr(s.tme_object_id, 1, instr
(s.tme_object_id, '+') - 1);
+43
+44 if nmachine = 0 then
+45 --- scrive intestazione pagina
+46 utl_file.put_line(rpt,formfeed);
+47 pag := pag + 1;
+48 utl_file.put_line(rpt,crlf);
+49 utl_file.put_line(rpt,'------------------------
-----------------------------------------------------------------

--------
-');
+50 utl_file.put_line(rpt,tab || tab ||tab|| ' I N
V E N T A R I O S O F T W A R E ' ||tab||tab||tab||' Pag. '||
ltrim(rt
rim(to_char(pag,'B999,999') )) );
+51 utl_file.put_line(rpt,'------------------------
-----------------------------------------------------------------

--------
-');
+52 end if;
+53 utl_file.put_line(rpt,crlf||crlf);
+54 utl_file.put_line(rpt,s.tme_object_label || '
(ID Tivoli: ' || tme_id || ') scan time: ' ||
s.computer_scantime);
+55
+56
+57 -- /////////////////////
+58 -- S O F T W A R E
+59 -- /////////////////////
+60 utl_file.new_line(rpt,3);
+61 utl_file.put_line(rpt,tab || 'Situazione attuale
software: ');
+62 utl_file.put_line(rpt,tab || '--------------------
-------- ');
+63 utl_file.new_line(rpt,1);
+64 select count(*)
+65 into n
+66 from tivoli.installed_unknown_file
+67 where hardware_system_id=hw_id
+68 and CONFIG_CHANGE_TYPE != 'DELETE';
+69 if n = 0 then
+70 utl_file.put_line(rpt,tab||'NON RISULTA
INSTALLATO ALCUN SOFTWARE');
+71 end if;
+72 n := 0;
+73
+74 -- apre il report temporaneo per le ultime
modifiche
+75 tmprpt := utl_file.fopen
('/oracle/tuning','rpt_sw.tmp','w');
+76 utl_file.new_line(tmprpt,3);
+77 utl_file.put_line(tmprpt,tab || 'Ultime
modifiche:');
+78 utl_file.put_line(tmprpt,tab || '-----------------
');
+79 utl_file.new_line(tmprpt,1);
+80
+81 sw_name := ' ';
+82 for c in (SELECT ltrim(rtrim
(a.SOFTWARE_COMPONENT_NAME)) || ' ' || ltrim(rtrim
(a.SOFTWARE_COMPONENT_VERSION)) SOFTWARE,
+83
b.installed_file_name,b.installed_file_size,b.config_change_type
,b.config_change_time
+84 FROM software_signature_file
a,INSTALLED_UNKNOWN_FILE B
+85 WHERE b.hardware_system_id=hw_id
+86 AND
a.SIGNATURE_FILE_NAME=b.installed_file_name
+87 AND
a.SIGNATURE_FILE_SIZE=b.installed_file_size
+88 ORDER BY
b.installed_file_name,b.installed_file_size,
b.config_change_time desc,b.config_change_type ) loop
+89
+90 -- dbms_output.put_line('sw_name: ' || sw_name ||',
c.SOFTWARE: '||c.SOFTWARE);
+91
+92 -- trova le info sull'i-esimo sw
+93 if c.SOFTWARE != sw_name then
+94 -- il sw in analisi non compare ancora nel
report
+95 n := 1;
+96 sw_name := c.SOFTWARE;
+97 -- dbms_output.put_line('sw_name NEW: ' || sw_name);
+98 end if;
+99
+100 --dbms_output.put_line(to_char(n,'99')|| ' ' ||
c.config_change_type || ' ' || c.config_change_time || ' ' ||
c.SOFTWARE);
+101
+102 if n = 2 then
+103 -- e' l'ultima modifica del sw
+104 utl_file.put_line
(tmprpt,tab||c.config_change_type || ' ' || c.config_change_time
|| ' ' || sw_name);
+105 n := 0;
+106 end if;
+107
+108 if n = 1 then
+109 -- sw nuovo..: se non e' DELETE va nella
situazione attuale altrimenti va in modifica
+110 if c.CONFIG_CHANGE_TYPE != 'DELETE' then
+111 utl_file.put_line
(rpt,tab||c.config_change_type || ' ' || c.config_change_time
|| ' ' || sw_name);
+112 n := 2;
+113 else
+114 utl_file.put_line
(tmprpt,tab||c.config_change_type || ' ' || c.config_change_time
|| ' ' || sw_name);
+115 -- il sw non va piu' ripetuto; e' stato
eliminato
+116 n := 0;
+117 end if;
+118 end if;
+119
+120 end loop;
+121
+122 -- chiude rpt temporaneo
+123 utl_file.fclose(tmprpt);
+124
+125 -- riversa il report temporaneo nel report finale
+126 begin
+127 tmprpt := utl_file.fopen
('/oracle/tuning','rpt_sw.tmp','r');
+128 while true loop
+129 utl_file.get_line(tmprpt,sw_name);
+130 utl_file.put_line(rpt,sw_name);
+131 end loop;
+132 utl_file.fclose(tmprpt);
+133 exception
+134 when no_data_found then
+135 utl_file.fclose(tmprpt);
+136 end;
+137
+138 nmachine := nmachine + 1;
+139 if nmachine = 3 then
+140 nmachine := 0;
+141 end if;
+142 end loop;
+143
+144 utl_file.fflush(rpt);
+145 -- chiude il report
+146 utl_file.fclose(rpt);
+147 end;
+148 /
+149 exit;
*****************************************************************