Home » Other » Client Tools » problem of utl file
problem of utl file [message #478594] Mon, 11 October 2010 03:35 Go to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
sir
following text is used in procedure
but it will create some problem

UTL_FILE.FOPEN('TDS','form26q.txt','w')

how i can check through toad what is the ptroblem
Re: problem of utl file [message #478597 is a reply to message #478594] Mon, 11 October 2010 04:15 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is kind of difficult to say anything with information you provided.

How do you know that it will make problems? Or did you mean to say that it did cause problems already? How did you use UTL_FILE.FOPEN? I guess it was a PL/SQL procedure. How did you run it? Is there any error message? If so, which one?

If you don't know how to use TOAD, perhaps you should turn to SQL*Plus. It doesn't look that nice, but might help you diagnose the problem.
problem in utl. file [message #478601 is a reply to message #478594] Mon, 11 October 2010 04:22 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
sir how i can cheack following utl file in database

UTL_FILE.FOPEN
Re: problem in utl. file [message #478603 is a reply to message #478601] Mon, 11 October 2010 04:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I don't see any file to check.
Database does not store any OS file within it.

or you can see here.

regards,
Delna
Re: problem in utl. file [message #478604 is a reply to message #478603] Mon, 11 October 2010 04:54 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
i also dont know about file
Re: problem in utl. file [message #478605 is a reply to message #478604] Mon, 11 October 2010 05:05 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
ERROR at line 1:
ORA-04067: not executed, package body "ALLENGERSDB.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"ALLENGERSDB.UTL_FILE"
ORA-06512: at "ALLENGERSDB.CREATE_FILEF26Q", line 40
ORA-06512: at line 1
Re: problem of utl file [message #478610 is a reply to message #478597] Mon, 11 October 2010 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that user ALLENGERSDB doesn't "see" UTL_FILE package. Did you - as a privileged user (such as SYS) -
GRANT EXECUTE ON UTL_FILE TO ALLENGERSDB;
Re: problem of utl file [message #478616 is a reply to message #478610] Mon, 11 October 2010 05:45 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
sir grant given
but issue has not been resolved

same error occured
Re: problem of utl file [message #478624 is a reply to message #478616] Mon, 11 October 2010 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you copy/paste the whole SQL*Plus session so that we would see what you did and how? Because, it kind of works for me ...
SQL> declare
  2    handle utl_file.file_type;
  3  begin
  4    handle := utl_file.fopen('TDS','form26q.txt','W');
  5    utl_file.fclose(handle);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
Re: problem of utl file [message #478625 is a reply to message #478624] Mon, 11 October 2010 05:59 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
declare
handle utl_file.file_type;
begin
handle := utl_file.fopen('TDS','form26q.txt','W');
utl_file.fclose(handle);
end;
Error at line 1
ORA-04067: not executed, package body "ALLENGERSDB.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "ALLENGERSDB.UTL_FILE"
ORA-06512: at line 4

Script Terminated on line 1.
Re: problem of utl file [message #478628 is a reply to message #478625] Mon, 11 October 2010 06:05 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, you are connected as ALLENGERSDB. Right? Where did you grant EXECUTE on UTL_FILE to it? Connect as a privileged user, run the following statement and copy/paste the output:
select * from all_synonyms where synonym_name = 'UTL_FILE';

[Updated on: Mon, 11 October 2010 06:05]

Report message to a moderator

Re: problem of utl file [message #478631 is a reply to message #478628] Mon, 11 October 2010 06:10 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
yes i have connected in allengersdb

rights given in sys/dba

message when script run is

OWNER SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
PUBLIC UTL_FILE
SYS UTL_FILE
Re: problem of utl file [message #478641 is a reply to message #478631] Mon, 11 October 2010 07:01 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What Oracle database version do you use?

It may be possible that there's only UTL_FILE specification there, and no UTL_FILE body. If that's the case, recreate UTL_FILE package using UTLFILE.SQL script (connect as SYS).
Re: problem of utl file [message #478646 is a reply to message #478641] Mon, 11 October 2010 07:22 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
we are using oracle 10.2.0.4

yes if i seen in allengers db then i found spec there not body

but in sys i seen spec & body both

but if i run UTLFILE.SQL script (connect as SYS). but after that found only spec in allengersdb not found body
Re: problem of utl file [message #478649 is a reply to message #478646] Mon, 11 October 2010 07:45 Go to previous messageGo to next message
narang79
Messages: 137
Registered: June 2010
Senior Member
but i manually created pack body from sysdba to allengersdb then it shows


SQL> declare
2 handle utl_file.file_type;
3 begin
4 handle := utl_file.fopen('TDS','form26q.txt','W');
5 utl_file.fclose(handle);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "ALLENGERSDB.UTL_FILE", line 29
ORA-06512: at "ALLENGERSDB.UTL_FILE", line 448
ORA-06512: at line 4
Re: problem of utl file [message #478650 is a reply to message #478649] Mon, 11 October 2010 07:52 Go to previous message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think that you should create UTL_FILE in your own schema; it is supposed to exist in SYS schema, and SYS should grant EXECUTE on UTL_FILE to other users. A public synonym exists so that everyone could use it (without specifying owner's name (that would be SYS)) ("everyone" here means "everyone who owns privileges to use it).
Previous Topic: PK,PK-FK joins not changing
Next Topic: Oracle 9i
Goto Forum:
  


Current Time: Mon May 27 23:04:17 CDT 2024