Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Reading from txt file in PL SQL

MilosCNov 22 2014 — edited Nov 22 2014

I have sisrious question.

What is wrong with this code?

set serveroutput on;
   CREATE or replace DIRECTORY USER_DIR AS '/home/oracle';
   GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;

   DECLARE
  V1 VARCHAR2(200); --32767
  F1 UTL_FILE.FILE_TYPE;
   BEGIN
  F1 := UTL_FILE.FOPEN('USER_DIR','temp.txt','R');
  Loop
   BEGIN
  UTL_FILE.GET_LINE(F1,V1);
  dbms_output.put_line(V1);
  EXCEPTION WHEN No_Data_Found THEN EXIT; END;
   end loop;

   IF UTL_FILE.IS_OPEN(F1) THEN
  dbms_output.put_line('File is Open');
   end if;

  UTL_FILE.FCLOSE(F1);
   END;
   /
   set serveroutput off;
This post has been answered by Frank Kulash on Nov 22 2014
Jump to Answer

Comments

Frank Kulash
Answer

Hi,

2796614 wrote:




I have sisrious question.


What is wrong with this code?




set serveroutput on;
   CREATE or replace DIRECTORY USER_DIR AS '/home/oracle'; 
   GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;

   DECLARE 
  V1 VARCHAR2(200); --32767
  F1 UTL_FILE.FILE_TYPE; 
   BEGIN 
  F1 := UTL_FILE.FOPEN('USER_DIR','temp.txt','R'); 
  Loop
   BEGIN
  UTL_FILE.GET_LINE(F1,V1); 
  dbms_output.put_line(V1);
  EXCEPTION WHEN No_Data_Found THEN EXIT; END;
   end loop;

   IF UTL_FILE.IS_OPEN(F1) THEN
  dbms_output.put_line('File is Open');
   end if;

  UTL_FILE.FCLOSE(F1); 
   END; 
   /
   set serveroutput off;

What makes you think anything is wrong?  Are you getting an error message?  Does it produce the wrong results?  If anything is wrong, those would be important clues.  Don't hide important information from the people who want to help you.  If you're willing to share what you know about the problem, you'll find people willing to share what they know about Oracle.

Marked as Answer by MilosC · Sep 27 2020
1 person found this helpful
unknown-791521

Moderator Action:

Thread locked.

Stay with your "reading from file" post that you made the previous day.

https://community.oracle.com/thread/3633008

Keep all information in one place as you learn how to do that.

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 20 2014
Added on Nov 22 2014
2 comments
3,673 views