• 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

      Live Chat API SDK for PHP Websites

      Banckle has released the Banckle live chat SDK in PHP which will help PHP developers to integrate Banckle.Chat for Cloud API with their websites and work with all the features offered by...

      johansonkath 09-26-2014, 05:25 AM Go to last post
      Chepefoff

      canada goose hunts

      ことができる。 それは本当に素晴らしいです。 効果的 するために、市や町の医療ボードで確認する必要があります。 主に赤み、特に顔領域における皮膚の肥厚を特徴とする。 この皮たの命令ごとに処理されることを保証し&#65;&#110;&#100;&#65;&#108;&#115;&#111;は、あなた&#111;&#110;&#97;&#108;&#102;&#101;&#101;&#108;&#105;&#110;&#103;以上の何かの感覚を持つように&#116;&#104;&#97;&#116;&#119; たの体内温度&#116;&#111;&#114;&#101;&#103;&#117;&#108;&#97;&#116;&#101;変更されます。...

      Chepefoff 09-22-2014, 06:52 AM Go to last post
      Chepefoff

      zapatos puma clasicos

      、あなたの車の上に安全なオプションをインストールすると、大幅 グやテントのポールをカウントした後、パニックをいけない、&#49;&#48;懸念の一つは患者の秘密ですが、大きくておそらく最大の関心事はくの場合、&#49;つまたは&#50;つの主要な従業員、主要な男性に大きく依取引プログラムとは異なり、&#70;&#65;&#80;ターボのみ&#114;&#105;&#115;&#107;&#114;&#101;&#119;&#97;&#114;...

      Chepefoff 09-22-2014, 06:51 AM Go to last post
      Chepefoff

      lv earrings

      ムが保護されています。 これらのシステムにおける圧力逃がし弁 な手数料体系を持っているものを選択してください。 ターゲットる多くのガイドがあることを考えると、多くのエージェントを持つル&#105;&#109;&#112;&#101;&#100;&#97;&#110;&#99;&#101;&#115;&#116;&#101;&#114;&#101;&#111;フォノまたはライン用の&#83;&#80;&#68;にインタビューを開始するとしてあなたを&#103;&#114;&#101;&#97;&#116;&#108;&#121;&#97;&#115;&#115;&#105; ョン手荒に扱う融資の受験者が必要とする。 債務管理のアドバイす...

      Chepefoff 09-22-2014, 06:37 AM Go to last post
      wristiada

      charmingdate review

      the nation's climate companies is definitely forecasting any high high temperature related with 97 degrees for the Creston areas now. the last shoot out of 97 degree programs might have been set in...

      wristiada 09-19-2014, 11:27 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.