• 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

      johansonkath

      Joomla Module for Online Meeting

      Joomla website developers can add latest module extension for add online meeting widget on Joomla websites. This widget will allow them to schedule and conduct online meeting, webinars and web...

      johansonkath 08-29-2014, 10:12 AM Go to last post
      sophialuis

      DATA BASE ADMINISTRATOR Job

      Skills/Qualifications: Database Performance Tuning, Database Security, Promoting Process Improvement, Problem Solving, Presenting Technical Information, Quality Focus, Database Management, Data...

      sophialuis 08-28-2014, 06:22 AM Go to last post
      johansonkath

      Download latest version of live chat plugin for WordPress

      Banckle has announced the latest version of live chat plugin for WordPress which now includes the enhanced features of Banckle.Chat cloud API. This new version now allows WordPress users to add live...

      johansonkath 08-20-2014, 06:42 AM Go to last post
      Joseph101

      Re: What is PPC?

      PPC is a type of online marketing where the advertiser only pays if their ad is clicked and a visitor arrives at their website. PPC is important part of Online Marketing Services. Read More...

      Joseph101 08-08-2014, 09:53 AM Go to last post
      johansonkath

      Live Chat Plugin for Moodle Website by Banckle

      Banckle live chat plugin for Moodle allows you to chat with your e-learning program participants in more social way. This plugin allows you to give your Virtual Learning Environment (VLE) a social...

      johansonkath 08-08-2014, 09:50 AM 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.