• 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.



    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.