Home » SQL & PL/SQL » SQL & PL/SQL » Error while using UTL_FILE in Oracle10g
Error while using UTL_FILE in Oracle10g [message #222911] Tue, 06 March 2007 23:23 Go to next message
girishta
Messages: 30
Registered: December 2006
Member
Hi,
This script attached here is being used in Oracle9i is working fine. But the same script when i use in oracle10g i get the follwing error. Please find the script used and the log.

For this i have created a Directory (REP) and also i have given UTL_FILE_DIR=<path> | *

This script is being run in PAS user. And also when i queried the DBA_OBJECTS view as pas user i am getting UTL_FILE as INVALID and when i tried recompile, its not possible to compile.
Also i ran the utlfile.sql as well.( executed successfully)

Please reply......


Script:
=======


/* =================================================
Program : biodat.sql
For : BIO-DATA Report
Author : Latha
Date : Jan-98
PARAMETERS : &1 - Print File
&2 - Location
==================================================== */
connect pas/pas;

@/sys1/pas/source/plsqlset.sql

spool /sys1/pas/process/biodat.log

declare

/* ------------- Temp Variables -------------------- */
slctr number(5,0);
pgctr number(5,0);
lnctr number(2,0);
locn char(9);
formth char(8);
firrec char(1);
tmp1 number(12,2);
tmp2 number(12,2);
tmp3 number(12,2);
tmp4 number(12,2);
tmp5 number(12,2);
tmp6 number(12,2);
tmp7 number(12,2);
tmp8 number(12,2);
tmpdate char(6);
company char(32);
v_filehandle UTL_FILE.FILE_TYPE ;


v_ErrorNumber NUMBER;
v_ErrorText varchar2(200);

/* --------------- CURSOR ------------------------- */
cursor cur1 is
select * from paymas2
where loc = '&2'
and deact <> 'D'
order by staffno ;

/* ---------------- BEGIN ------------------------ */
begin
slctr := 0 ;
pgctr := 0 ;
lnctr := 0 ;
firrec := 'Y';
tmp1 := 0;
tmp2 := 0;
tmp3 := 0;
tmp4 := 0;
tmp5 := 0;
tmp6 := 0;
tmp7 := 0;
tmp8 := 0;
tmpdate := ' ';

select decode('&2','1','BANGALORE','2','KUDREMUKH',
'3','MANGALORE','4','NEW DELHI','5','PEL.PLANT','6','CHENNAI',
' '),
decode('&2','7','KUDREMUKH IRON AND STEEL CO LTD',
'8','KUDREMUKH IRON AND STEEL CO LTD',
'KUDREMUKH IRON ORE CO LTD') into locn, company from dual;

formth := to_char(sysdate,'MON YYYY');

/* ---------------- Open Cursor ------------------- */

v_filehandle := utl_file.fopen('REP','&1','w');

/* ----------------- LOOP ------------------------- */

for cur1_rec in cur1
loop
if firrec = 'Y' or lnctr > 60 then
firrec := 'N';
goto hdrprint;
end if;

<<starting>>

tmpdate := nvl(cur1_rec.doa,' ');

utl_file.putf(v_filehandle,'\n%s%s%s%s%s',
cur1_rec.deact||rpad(' ',5)||cur1_rec.loc||' '||cur1_rec.staffno||' ',
cur1_rec.scc||' '||rpad(cur1_rec.name,20)||' ',
rpad(cur1_rec.desig,12)||' '||substr(tmpdate,5,2),
substr(tmpdate,3,2)||substr(tmpdate,1,2)||' '||cur1_rec.dep,
' '||cur1_rec.tenure||' '||cur1_rec.grcd||' ');

utl_file.putf(v_filehandle,'%s%s%s%s%s',
cur1_rec.incmth||' '||cur1_rec.increl||' '||cur1_rec.pffpf,
' '||cur1_rec.trnsub||' '||cur1_rec.qrty||' ',
cur1_rec.qrno||' '||to_char(cur1_rec.shar,'b9')||' ',
cur1_rec.benfun||' '||cur1_rec.club||' ',
cur1_rec.bank||' '||cur1_rec.acty||' ');

utl_file.putf(v_filehandle,'%s',
to_char(cur1_rec.acno,'b9999999999999999'));

lnctr := lnctr + 1;

tmp1 := nvl(cur1_rec.basicpay,0)/100;
tmp2 := nvl(cur1_rec.deppay,0)/100;
tmp3 := nvl(cur1_rec.perspay,0)/100;
tmp4 := nvl(cur1_rec.splpay,0)/100;
tmp5 := nvl(cur1_rec.vdada,0)/100;
tmp6 := nvl(cur1_rec.actrenthra,0)/100;
tmp7 := nvl(cur1_rec.splall,0)/100;

tmpdate := nvl(cur1_rec.laspromdt,' ');

utl_file.putf(v_filehandle,'\n%s%s%s%s%s',
to_char(tmp1,'b99999.99')||to_char(tmp2,'b99999.99'),
to_char(tmp3,'b99999.99')||to_char(tmp4,'b99999.99'),
to_char(tmp5,'b99999.99')||' '||substr(tmpdate,5,2),
substr(tmpdate,3,2)||substr(tmpdate,1,2)||' ',
to_char(tmp6,'b99999.99')||to_char(tmp7,'b99999.99'));


tmp1 := nvl(cur1_rec.washall,0)/100;
tmp2 := nvl(cur1_rec.minall,0)/100;
tmp3 := nvl(cur1_rec.inctax,0)/100;
tmp4 := nvl(cur1_rec.vpf,0)/100;
tmp5 := nvl(cur1_rec.hrr,0)/100;
tmp6 := nvl(cur1_rec.schbus,0)/100;
tmp7 := nvl(cur1_rec.penper,0);

utl_file.putf(v_filehandle,'%s%s%s%s',
to_char(tmp1,'b99999.99')||to_char(tmp2,'b99999.99'),
to_char(tmp3,'b99999.99')||to_char(tmp4,'b99999.99'),
to_char(tmp5,'b99999.99')||to_char(tmp6,'b99999.99'),
to_char(tmp7,'b99'));

utl_file.putf(v_filehandle,'\n%s',rpad('-',132,'-'));

lnctr := lnctr + 2;

goto nohdrprint;

<<hdrprint>>
utl_file.putf(v_filehandle,'\n%s',chr(12));

pgctr := pgctr + 1;

utl_file.putf(v_filehandle,'\n\n\n\n%s%s',
'BIODAT'||rpad(' ',44)||company,
rpad(' ',23)||'PAGE:'||to_char(pgctr,'b9999'));

utl_file.putf(v_filehandle,'\n\n%s%s%s',
locn||rpad(' ',31)||'BIO-DATA AND STANDARD E/D DETAILS FOR ',
formth||rpad(' ',21)||'DATE:'||to_char(sysdate,'fmMon ddth yyyy'));

utl_file.putf(v_filehandle,'\n%s',rpad('=',132,'='));

utl_file.putf(v_filehandle,'\n%s%s%s',
'DEACT L STFNO SCC NAME DESIGNATION APP.DT ' ,
'DEP TENURE GR IMC IRC PFC TSC QTRTY QTR# SCH BFC CLUB BANK ',
'A/CTY A/C-#');

utl_file.putf(v_filehandle,'\n%s%s%s',
'BASICPAY DEPTNPAY PERS-PAY SPL-PAY VDA / DA DT-BIRTH REALRENT ',
'SPL-ALLW WASH-ALL MIN-ALLW INCM-TAX VPF/ GPF RENT-REC BUS-CHRG ',
'RPS');

utl_file.putf(v_filehandle,'\n%s',rpad('=',132,'='));

lnctr := 10 ;

goto starting;

<<nohdrprint>>
null ;
end loop ;
/* ----------------- FILE CLOSE ------------------- */

utl_file.fclose(v_filehandle);

exception
when utl_file.invalid_operation then
utl_file.fclose(v_filehandle);
raise_application_error(-20061,' Invalid Operation');

when utl_file.invalid_filehandle then
utl_file.fclose(v_filehandle);
raise_application_error(-20062,' Invalid File Handle');

when utl_file.write_error then
utl_file.fclose(v_filehandle);
raise_application_error(-20063,' Write Error');

when others then
utl_file.fclose(v_filehandle);
raise;
end;
/
select * from errlist;
spool off;
@/sys1/pas/source/plsqlend.sql
commit;


LOG

SQL> declare
2
3 /* ------------- Temp Variables -------------------- */
4 slctr number(5,0);
5 pgctr number(5,0);
6 lnctr number(2,0);
7 locn char(9);
8 formth char(8);
9 firrec char(1);
10 tmp1 number(12,2);
11 tmp2 number(12,2);
12 tmp3 number(12,2);
13 tmp4 number(12,2);
14 tmp5 number(12,2);
15 tmp6 number(12,2);
16 tmp7 number(12,2);
17 tmp8 number(12,2);
18 tmpdate char(6);
19 company char(32);
20 v_filehandle UTL_FILE.FILE_TYPE ;
21
22 v_ErrorNumber NUMBER;
23 v_ErrorText varchar2(200);
24
25 /* --------------- CURSOR ------------------------- */
26 cursor cur1 is
27 select * from paymas2
28 where loc = '&2'
29 and deact <> 'D'
30 order by staffno ;
31
32 /* ---------------- BEGIN ------------------------ */
33 begin
34 slctr := 0 ;
35 pgctr := 0 ;
36 lnctr := 0 ;
37 firrec := 'Y';
38 tmp1 := 0;
39 tmp2 := 0;
40 tmp3 := 0;
41 tmp4 := 0;
42 tmp5 := 0;
43 tmp6 := 0;
44 tmp7 := 0;
45 tmp8 := 0;
46 tmpdate := ' ';
47
48 select decode('&2','1','BANGALORE','2','KUDREMUKH',
49 '3','MANGALORE','4','NEW DELHI','5','PEL.PLANT','6','CHENNAI',
50 ' '),
51 decode('&2','7','KUDREMUKH IRON AND STEEL CO LTD',
52 '8','KUDREMUKH IRON AND STEEL CO LTD',
53 'KUDREMUKH IRON ORE CO LTD') into locn, company from dual;
54
55 formth := to_char(sysdate,'MON YYYY');
56
57 /* ---------------- Open Cursor ------------------- */
58
59 v_filehandle := utl_file.fopen('REP','&1','w');
60
61 /* ----------------- LOOP ------------------------- */
62
63 for cur1_rec in cur1
64 loop
65 if firrec = 'Y' or lnctr > 60 then
66 firrec := 'N';
67 goto hdrprint;
68 end if;
69
70 <<starting>>
71
72 tmpdate := nvl(cur1_rec.doa,' ');
73
74 utl_file.putf(v_filehandle,'\n%s%s%s%s%s',
75 cur1_rec.deact||rpad(' ',5)||cur1_rec.loc||' '||cur1_rec.staffno||' ',
76 cur1_rec.scc||' '||rpad(cur1_rec.name,20)||' ',
77 rpad(cur1_rec.desig,12)||' '||substr(tmpdate,5,2),
78 substr(tmpdate,3,2)||substr(tmpdate,1,2)||' '||cur1_rec.dep,
79 ' '||cur1_rec.tenure||' '||cur1_rec.grcd||' ');
80
81 utl_file.putf(v_filehandle,'%s%s%s%s%s',
82 cur1_rec.incmth||' '||cur1_rec.increl||' '||cur1_rec.pffpf,
83 ' '||cur1_rec.trnsub||' '||cur1_rec.qrty||' ',
84 cur1_rec.qrno||' '||to_char(cur1_rec.shar,'b9')||' ',
85 cur1_rec.benfun||' '||cur1_rec.club||' ',
86 cur1_rec.bank||' '||cur1_rec.acty||' ');
87
88 utl_file.putf(v_filehandle,'%s',
89 to_char(cur1_rec.acno,'b9999999999999999'));
90
91 lnctr := lnctr + 1;
92
93 tmp1 := nvl(cur1_rec.basicpay,0)/100;
94 tmp2 := nvl(cur1_rec.deppay,0)/100;
95 tmp3 := nvl(cur1_rec.perspay,0)/100;
96 tmp4 := nvl(cur1_rec.splpay,0)/100;
97 tmp5 := nvl(cur1_rec.vdada,0)/100;
98 tmp6 := nvl(cur1_rec.actrenthra,0)/100;
99 tmp7 := nvl(cur1_rec.splall,0)/100;
100
101 tmpdate := nvl(cur1_rec.laspromdt,' ');
102
103 utl_file.putf(v_filehandle,'\n%s%s%s%s%s',
104 to_char(tmp1,'b99999.99')||to_char(tmp2,'b99999.99'),
105 to_char(tmp3,'b99999.99')||to_char(tmp4,'b99999.99'),
106 to_char(tmp5,'b99999.99')||' '||substr(tmpdate,5,2),
107 substr(tmpdate,3,2)||substr(tmpdate,1,2)||' ',
108 to_char(tmp6,'b99999.99')||to_char(tmp7,'b99999.99'));
109
110
111 tmp1 := nvl(cur1_rec.washall,0)/100;
112 tmp2 := nvl(cur1_rec.minall,0)/100;
113 tmp3 := nvl(cur1_rec.inctax,0)/100;
114 tmp4 := nvl(cur1_rec.vpf,0)/100;
115 tmp5 := nvl(cur1_rec.hrr,0)/100;
116 tmp6 := nvl(cur1_rec.schbus,0)/100;
117 tmp7 := nvl(cur1_rec.penper,0);
118
119 utl_file.putf(v_filehandle,'%s%s%s%s',
120 to_char(tmp1,'b99999.99')||to_char(tmp2,'b99999.99'),
121 to_char(tmp3,'b99999.99')||to_char(tmp4,'b99999.99'),
122 to_char(tmp5,'b99999.99')||to_char(tmp6,'b99999.99'),
123 to_char(tmp7,'b99'));
124
125 utl_file.putf(v_filehandle,'\n%s',rpad('-',132,'-'));
126
127 lnctr := lnctr + 2;
128
129 goto nohdrprint;
130
131 <<hdrprint>>
132 utl_file.putf(v_filehandle,'\n%s',chr(12));
133
134 pgctr := pgctr + 1;
135
136 utl_file.putf(v_filehandle,'\n\n\n\n%s%s',
137 'BIODAT'||rpad(' ',44)||company,
138 rpad(' ',23)||'PAGE:'||to_char(pgctr,'b9999'));
139
140 utl_file.putf(v_filehandle,'\n\n%s%s%s',
141 locn||rpad(' ',31)||'BIO-DATA AND STANDARD E/D DETAILS FOR ',
142 formth||rpad(' ',21)||'DATE:'||to_char(sysdate,'fmMon ddth yyyy'));
143
144 utl_file.putf(v_filehandle,'\n%s',rpad('=',132,'='));
145
146 utl_file.putf(v_filehandle,'\n%s%s%s',
147 'DEACT L STFNO SCC NAME DESIGNATION APP.DT ' ,
148 'DEP TENURE GR IMC IRC PFC TSC QTRTY QTR# SCH BFC CLUB BANK ',
149 'A/CTY A/C-#');
150
151 utl_file.putf(v_filehandle,'\n%s%s%s',
152 'BASICPAY DEPTNPAY PERS-PAY SPL-PAY VDA / DA DT-BIRTH REALRENT ',
153 'SPL-ALLW WASH-ALL MIN-ALLW INCM-TAX VPF/ GPF RENT-REC BUS-CHRG ',
154 'RPS');
155
156 utl_file.putf(v_filehandle,'\n%s',rpad('=',132,'='));
157
158 lnctr := 10 ;
159
160 goto starting;
161
162 <<nohdrprint>>
163 null ;
164 end loop ;
165 /* ----------------- FILE CLOSE ------------------- */
166
167 utl_file.fclose(v_filehandle);
168
169 exception
170 when utl_file.invalid_operation then
171 utl_file.fclose(v_filehandle);
172 raise_application_error(-20061,' Invalid Operation');
173
174 when utl_file.invalid_filehandle then
175 utl_file.fclose(v_filehandle);
176 raise_application_error(-20062,' Invalid File Handle');
177
178 when utl_file.write_error then
179 utl_file.fclose(v_filehandle);
180 raise_application_error(-20063,' Write Error');
181
182 when others then
183 utl_file.fclose(v_filehandle);
184 raise;
185 end;
186 /
old 28: where loc = '&2'
new 28: where loc = '1'
old 48: select decode('&2','1','BANGALORE','2','KUDREMUKH',
new 48: select decode('1','1','BANGALORE','2','KUDREMUKH',
old 51: decode('&2','7','KUDREMUKH IRON AND STEEL CO LTD',
new 51: decode('1','7','KUDREMUKH IRON AND STEEL CO LTD',
old 59: v_filehandle := utl_file.fopen('rep','&1','w');
new 59: v_filehandle := utl_file.fopen('rep','b1.prt','w');
v_filehandle UTL_FILE.FILE_TYPE ;
*
ERROR at line 20:
ORA-06550: line 20, column 15:
PLS-00905: object PAS.UTL_FILE is invalid
ORA-06550: line 20, column 15:
PL/SQL: Item ignored
ORA-06550: line 59, column 2:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 59, column 2:
PL/SQL: Statement ignored
ORA-06550: line 74, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 74, column 2:
PL/SQL: Statement ignored
ORA-06550: line 81, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 81, column 2:
PL/SQL: Statement ignored
ORA-06550: line 88, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 88, column 2:
PL/SQL: Statement ignored
ORA-06550: line 103, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 103, column 2:
PL/SQL: Statement ignored
ORA-06550: line 119, column 16:

Re: Error while using UTL_FILE in Oracle10g [message #222938 is a reply to message #222911] Wed, 07 March 2007 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe you should find out why utl_file is invalid?
Re: Error while using UTL_FILE in Oracle10g [message #222948 is a reply to message #222938] Wed, 07 March 2007 00:57 Go to previous messageGo to next message
girishta
Messages: 30
Registered: December 2006
Member
Hi frank,
When i queried the dba_objects .. am getting....


SELECT OWNER, OBJECT_NAME , OBJECT_TYPE , STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME = 'UTL_FILE';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------------- --------------- -------
SYS UTL_FILE PACKAGE VALID
SYS UTL_FILE PACKAGE BODY VALID
PUBLIC UTL_FILE SYNONYM VALID
PAS UTL_FILE PACKAGE INVALID

My question is
1. Will the program uses SYS object or Should we have to create the same in PAS user..

2. Shall i drop this Invalid object and try .. what am thinking is .. this program using PAS object as am running this as PAS user.

Please reply ... am in hurry ...

Thanks & Regards

Giri
Re: Error while using UTL_FILE in Oracle10g [message #222950 is a reply to message #222911] Wed, 07 March 2007 00:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please reply ... am in hurry ...
The lack of planning on your part does not constitute an emergency on my part.
With free advice, you get what you pay for it.
Re: Error while using UTL_FILE in Oracle10g [message #222954 is a reply to message #222950] Wed, 07 March 2007 01:07 Go to previous messageGo to next message
girishta
Messages: 30
Registered: December 2006
Member
Thanks for the advice
Re: Error while using UTL_FILE in Oracle10g [message #222963 is a reply to message #222948] Wed, 07 March 2007 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
girishta wrote on Wed, 07 March 2007 07:57
My question is
1. Will the program uses SYS object or Should we have to create the same in PAS user..

2. Shall i drop this Invalid object and try .. what am thinking is .. this program using PAS object as am running this as PAS user.


I think you actually know very well what to do...
Re: Error while using UTL_FILE in Oracle10g [message #222965 is a reply to message #222954] Wed, 07 March 2007 01:27 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
PLS-00905: object PAS.UTL_FILE is invalid

As it is giving error on PAS, check out why it is giving error.
BTW where is the package body in PAS?

try
alter package UTL_FILE compile;

If I say drop the object in PAS, will you blindly take my words?
Cross check first, why that has been created.

By
Vamsi
Re: Error while using UTL_FILE in Oracle10g [message #223011 is a reply to message #222963] Wed, 07 March 2007 03:15 Go to previous messageGo to next message
girishta
Messages: 30
Registered: December 2006
Member
Dear Frank,
What i have enclosed here was just a guess please tell me the right way please ...


And

Hi Vamshi,
I tried to recompile it but its giving a warning saying that Package altered with compilation errors..
I am not able to trace those errors....

Re: Error while using UTL_FILE in Oracle10g [message #223014 is a reply to message #223011] Wed, 07 March 2007 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What I meant was that I think you know you should not have created the utl_file package under the subordinate user.
As Vamsi said, why was this done? Was there some special reason for it or did you just run the script?
Re: Error while using UTL_FILE in Oracle10g [message #223026 is a reply to message #223014] Wed, 07 March 2007 04:28 Go to previous message
girishta
Messages: 30
Registered: December 2006
Member
Dear Frank & Vamsi,
Actually am a Consultant in my company. I had been giving support this client. That object was created by them. Now I dropped that Pas object. NOW ITS WORKING FINE .....

THANKS A LOT FOR YOUR Valuable SUPPORT.

Giri



Previous Topic: Truncate Command
Next Topic: problem with "like" predicate
Goto Forum:
  


Current Time: Sun Dec 11 03:54:22 CST 2016

Total time taken to generate the page: 0.21881 seconds