• Loading
    • How To Read Text Files Using Oracle PL/SQL?

      A C, C++ and Java Programmer Knows the significance and procedure of file handling very well. But most of the PL/SQL programmer misses the concept of File handling in PL/SQL. The Oracle PL/SQL file handling is very difficult to run at the very first attempt. You might encounter a lots of problems. Let us learn how to Read Text Files Using Oracle PL/SQL.

      1) Reading or writing data to physical files is required in reporting or transferring to legacy systems.

      2) A pre-defined package called "UTL_FILE" is used for the purpose. UTL_FILE" allows PL/SQL programs to both read and write to any operating system files that are accessible from the server on which your database instance is running. This package gets automatically installed when you install Oracle database on your system.

      3) Now the very first thing you would be requiring is to set the value for UTL_FILE_DIR parameter in init.ora file.
      The path for the init.ora file is <instance_home>\pfile.
      In my case it is:- E:\oracle\admin\ORCL\pfile



      4) Now, open the file and insert the line UTL_FILE_DIR=<directory_name>. In my case it is UTL_FILE_DIR=c:\harsh. This line indicates that the harsh folder in c drive is available for reading and writing the files inside it. Permissions to any folder from host operating system can be provided by modifying the content as shown below:
      E.g. UTL_FILE_DIR = *

      5) Bounce the database services to reflect the changes.

      6) Create a new text file inside the directory you set above. In my case it is test.txt.

      7) Create a variable of utl_file.file_type .
      e.g:- f utl_file.file_type
      "utl_file.file_type" is a different data type which points to a particular file specified by the user. On successfully identification, operations like reading, writing can be performed.

      8) Now, Open the file test.txt as shown below:-
      f := UTL_FILE.FOPEN('c:\harsh','test.txt','R');
      The file is opened in Read mode.

      9) You can also do the Exception Handling for error tracking.

      10) Code for reading single line from the file.
      Code:
      DECLARE
        f UTL_FILE.FILE_TYPE;
        s VARCHAR2(200);
      BEGIN
         f := UTL_FILE.FOPEN('c:\harsh','test.txt','R');
         UTL_FILE.GET_LINE(f,s);
         UTL_FILE.FCLOSE(f);
         DBMS_OUTPUT.PUT_LINE(s);
      END;
      11) Code for reading whole file.
      Code:
      DECLARE
        f UTL_FILE.FILE_TYPE;
        s VARCHAR2(200);
        c number:=0;
      BEGIN
         f := UTL_FILE.FOPEN('c:\harsh','test.txt','R');
        LOOP
         UTL_FILE.GET_LINE(f,s);
         DBMS_OUTPUT.PUT_LINE(s);
         c:=c+1;
        END LOOP;
       EXCEPTION  
          WHEN NO_DATA_FOUND THEN  
               UTL_FILE.FCLOSE(f);  
               DBMS_OUTPUT.PUT_LINE('Number of lines: ' || c);
               UTL_FILE.FCLOSE(f);
        
      END;
      12) Don't forget to run the command:-
      set serveroutput on before executing the above PL/SQL block.

      [P.S]:- steps 3 to 5 will save you from the error :- ORA-06510: PL/SQL: unhandled user-defined exception.
    • Enter your email address:

      Delivered by FeedBurner

    • Categories

    • Latest Posts

      kate01

      Re: Transfer iPhone SMS and MMS to Android

      You can transfer SMS messages to Android devices manually and freely by following the steps shared in this guide: goo.gl/L6rZNS

      kate01 Yesterday, 03:51 AM Go to last post
      Moveblelago

      Re: Nuestra historia

      “现在微博、朋友圈往往都是负面消息发酵的很快,但今天这位老人
      而在面对被栽赃成为杀妻凶手悬疑迷雾时,他又必须在悬疑色彩重重

      Moveblelago 06-14-2015, 07:05 PM Go to last post
      Moveblelago

      Re: hi I join Tech Forum

      理改编,将幼儿园段落单拿出来组成一部电影,删去了原作中很多败
      苦难学说”在该剧中逐渐升级,孙家兄弟的平凡生活将再次遭受磨难

      Moveblelago 06-14-2015, 04:46 PM Go to last post
      Moveblelago

      Re: hi I join Tech Forum

      众。梁文道户外走读遭“炮轰” 接受意见反“嘲”高晓松发布会上
      日探班《霍比特人&#50;》片场,采访甘道夫扮演者伊恩&#183;迈凯伦,越来

      Moveblelago 06-14-2015, 04:33 PM Go to last post
      Moveblelago

      Re: hi I join Tech Forum

      局。几乎场场爆满,其中三线城市上座率达到&#56;&#54;&#37;,首周末票房轻
      平凡的世界》的创作态度严谨,从筹备到制作超过七年。剧组除了赶

      Moveblelago 06-14-2015, 04:11 PM Go to last post



    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.