Home » Other » Training & Certification » UTL_FILE
UTL_FILE [message #275770] Mon, 22 October 2007 11:11 Go to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

Hi All,

This is my first post. I will be glad if you can help me.

I am getting the error:

ORA-06550: line 2, column 12:
PLS-00201: identifier 'UTL_FILE' must be declared
ORA-06550: line 2, column 12:
PL/SQL: Item ignored
ORA-06550: line 5, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

-------
here are what i have done:

(1) OS: mkdir C:\ORALOAD
(2) CREATE DIRECTORY oraload AS 'C:\ORALOAD\';
(3) and lastly the PL/SQL

DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/
Re: UTL_FILE [message #275771 is a reply to message #275770] Mon, 22 October 2007 11:13 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
A couple of things:

You need to specify in which directory the file resides.

Remove the when others exception block.

[Updated on: Mon, 22 October 2007 11:15]

Report message to a moderator

Re: UTL_FILE [message #275772 is a reply to message #275770] Mon, 22 October 2007 11:16 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

Thanks, thats a good observation. But how can I pass thru the UTL_FILE error? It will still no work just the same right? please help.
Re: UTL_FILE [message #275773 is a reply to message #275770] Mon, 22 October 2007 11:20 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

i tried putting the 'C:\ORALOAD' on the pl/sql block and i am still getting the same error.
Re: UTL_FILE [message #275783 is a reply to message #275770] Mon, 22 October 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

PLS-00201: identifier 'UTL_FILE' must be declared

Do you have the privilege to execute this package?
Does the synonym exist?

Regards
Michel
Re: UTL_FILE [message #275854 is a reply to message #275783] Tue, 23 October 2007 01:14 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

no ia hve tried creating synonym for this. could you please give me the syntax? Thanks for replying
Re: UTL_FILE [message #275856 is a reply to message #275854] Tue, 23 October 2007 01:21 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

it needs to be granted by a person having a DBA rights.


regards,
Re: UTL_FILE [message #275860 is a reply to message #275854] Tue, 23 October 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before doing anything, I would investigate why this (publc) synonym does not exist.
It should as its creation is called by catproc.sql which should be executed at database creation time.
Maybe your DBA thought you should not have the privileges.
Investigate.

Regards
Michel
Re: UTL_FILE [message #275977 is a reply to message #275770] Tue, 23 October 2007 09:45 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

hello all, thanks for your replies but I am just working locally,
this is our assignment and I am getting trouble working it on
Re: UTL_FILE [message #275996 is a reply to message #275770] Tue, 23 October 2007 10:42 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

I have already created a procedure, and tried to run the script above, but I am still getting the same error

 

CREATE OR REPLACE procedure UTL_FUNCTION (test VARCHAR2) IS
 vInHandle  utl_file.file_type;
 vNewLine VARCHAR2(200);
BEGIN
  vInHandle := utl_file.fopen('C:\ORALOAD\', 'test.txt','r');

  IF utl_file.is_open(vInHandle ) THEN
    LOOP
      BEGIN
        utl_file.get_line(vInHandle , vNewLine);

        IF vNewLine IS NULL THEN
          EXIT;
        END IF;

      END;
    END LOOP;
END;

.
Re: UTL_FILE [message #276000 is a reply to message #275996] Tue, 23 October 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Connect as sys and run catproc.sql

Regards
Michel
Re: UTL_FILE [message #276010 is a reply to message #275770] Tue, 23 October 2007 11:57 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

 FORGIVE ME with my ignorance, I dont know what's catproc.sql, what it does with Oracle.

Thanks for sharing your valuable time. I really appreciate it
.
Re: UTL_FILE [message #276030 is a reply to message #276010] Tue, 23 October 2007 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who created the database?

Regards
Michel
Re: UTL_FILE [message #276097 is a reply to message #276010] Tue, 23 October 2007 23:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sbenosa wrote on Tue, 23 October 2007 18:57

 FORGIVE ME with my ignorance, I dont know what's catproc.sql, what it does with Oracle.

Thanks for sharing your valuable time. I really appreciate it
.


20.500 hits for catproc.sql on Google. Or don't you know what that is either?
Re: UTL_FILE [message #276178 is a reply to message #275770] Wed, 24 October 2007 05:00 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

I am working locally so Its my local DB.
Re: UTL_FILE [message #276179 is a reply to message #276178] Wed, 24 October 2007 05:07 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

so do what was told to you.you will find this script file in your ORACLE\Home....

@Michel Cadot
Quote:


Connect as sys and run catproc.sql





regards,

[Updated on: Wed, 24 October 2007 05:10]

Report message to a moderator

Re: UTL_FILE [message #276201 is a reply to message #276178] Wed, 24 October 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I am working locally so Its my local DB.

This does not answer my question "Who created the database?".
If this is you, you should know that all standard packages are in $ORACLE_HOME/rdbms/admin directory.
If this is not you, take care, connecting as SYS you can corrupt your database.

Regards
Michel
Re: UTL_FILE [message #276286 is a reply to message #275770] Wed, 24 October 2007 12:07 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

Hi All,

I was able to run the catproc.sql sctipt.

@$ORACLE_HOME/rdbms/admin/catproc

However, I am getting another error, but i will try to solve it my own first. I will not hesitate though to post it should I run into trouble.

I really appreciate your help. One information will lead to another information to research. It makes my oracle training more interesting. Thanks and Godbless
Re: UTL_FILE [message #276594 is a reply to message #275770] Thu, 25 October 2007 10:23 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

Hi All, this is the latest error I am getting. I tried to manipulate the data declarations but i am still getting the same. Thanks


ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 5

Re: UTL_FILE [message #276608 is a reply to message #276594] Thu, 25 October 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you got that where?

Regards
Michel
Re: UTL_FILE [message #276612 is a reply to message #275770] Thu, 25 October 2007 11:03 Go to previous messageGo to next message
sbenosa
Messages: 12
Registered: September 2007
Location: Philippines
Junior Member

When I run my initial query


DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
--EXCEPTION
--WHEN OTHERS THEN
--EXIT;
END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/ 

Re: UTL_FILE [message #276692 is a reply to message #276612] Fri, 26 October 2007 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have no stop condition in your loop, so it is bound to exit only when an exception occurs.
I don't know what error utl_file raises when you try to read beyond the end of the file, but this could very well be the one.
Re: UTL_FILE [message #276697 is a reply to message #276692] Fri, 26 October 2007 00:47 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

once you reach beyond the end of the line you get the NO_DATA_FOUND error.the below link should help you.

http://www.oracle-base.com/articles/9i/UTL_FILERandomAccessOfFiles9i.php


regards,
Re: UTL_FILE [message #276705 is a reply to message #276612] Fri, 26 October 2007 01:01 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is line 5?

Use SQL*Plus and copy and paste the execution then we can what you call and what SQL*Plus returns along with the line numbers.

Regards
Michel
Previous Topic: basic and urgent
Next Topic: where are the statistics stored
Goto Forum:
  


Current Time: Fri Dec 02 18:40:31 CST 2016

Total time taken to generate the page: 0.25844 seconds