OraFAQ Forum: Client Tools » problem of utl file

Home » Other » Client Tools » problem of utl file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem of utl file [message #478594]

Mon, 11 October 2010 03:35

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

Report message to a moderator

Re: problem of utl file [message #478597 is a reply to message #478594]

Mon, 11 October 2010 04:15

Littlefoot
Messages: 21823
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.

Report message to a moderator

problem in utl. file [message #478601 is a reply to message #478594]

Mon, 11 October 2010 04:22

narang79
Messages: 137
Registered: June 2010

Senior Member

sir how i can cheack following utl file in database

UTL_FILE.FOPEN

Report message to a moderator

Re: problem in utl. file [message #478603 is a reply to message #478601]

Mon, 11 October 2010 04:45

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

Report message to a moderator

Re: problem in utl. file [message #478604 is a reply to message #478603]

Mon, 11 October 2010 04:54

narang79
Messages: 137
Registered: June 2010

Senior Member

i also dont know about file

Report message to a moderator

Re: problem in utl. file [message #478605 is a reply to message #478604]

Mon, 11 October 2010 05:05

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

Report message to a moderator

Re: problem of utl file [message #478610 is a reply to message #478597]

Mon, 11 October 2010 05:36

Littlefoot
Messages: 21823
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;

Report message to a moderator

Re: problem of utl file [message #478616 is a reply to message #478610]

Mon, 11 October 2010 05:45

narang79
Messages: 137
Registered: June 2010

Senior Member

sir grant given
but issue has not been resolved

same error occured

Report message to a moderator

Re: problem of utl file [message #478624 is a reply to message #478616]

Mon, 11 October 2010 05:56

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: problem of utl file [message #478625 is a reply to message #478624]

Mon, 11 October 2010 05:59

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.

Report message to a moderator

Re: problem of utl file [message #478628 is a reply to message #478625]

Mon, 11 October 2010 06:05

Littlefoot
Messages: 21823
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

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

Report message to a moderator

Re: problem of utl file [message #478641 is a reply to message #478631]

Mon, 11 October 2010 07:01

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: problem of utl file [message #478646 is a reply to message #478641]

Mon, 11 October 2010 07:22

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

Report message to a moderator

Re: problem of utl file [message #478649 is a reply to message #478646]

Mon, 11 October 2010 07:45

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

Report message to a moderator

Re: problem of utl file [message #478650 is a reply to message #478649]

Mon, 11 October 2010 07:52

Littlefoot
Messages: 21823
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).

Report message to a moderator

Previous Topic:	PK,PK-FK joins not changing
Next Topic:	Oracle 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 06:19:18 CST 2024