12 Replies Latest reply on Feb 28, 2008 4:15 AM by 625456


      Please anyone can explain what is difference between SQL Loader and UTL_FLIE.

        • 1. Re: UTL_FILE
          As different as day and night.

          SQL*Loader = software utility to load data (in all kinds of formats) into Oracle.

          UTL_FILE = supplied PL/SQL package by Oracle that provides PL/SQL applications with a file I/O API (Application Programming Interface).

          The only thing in commin is that both use file I/O. But so does every other single piece of software (directly and indirectly) on a standard computer plarform.
          • 2. Re: UTL_FILE
            Thanks Billy, so SQL Loader can read and write any format data and it is faster. But in UTL_FILE can read and write text document only. This is correct as per my understanding.

            • 3. Re: UTL_FILE
              SQL Loader does what it says on the tin. It loads data onto the database in from differing formats. It doesn't write data back out again. To write data out you'll need to use UTL_FILE.

              • 4. Re: UTL_FILE
                UTL_FILE can perform both binary and text mode I/O.

                Can UTL_FILE do what SQL*Loader does? No.

                UTL_FILE is an API. A software tool. It is not a software application like SQL*Loader. SQL*Loader has internally its own UTL_FILE like API that does file I/O for it.

                With a lot of effort, and without the same efficiency and flexibility, one can write a PL/SQL application to load data, using the UTL_FILE API. This resulting application will then be similar to SQL*Loader.

                Also, if you need to load data from files from a PL/SQL application, there are numerous superior features/tools than the UTL_FILE API. There are external tables (which in fact invokes the SQL*Loader engine to load data), there is XML parsing, XMLDB, BLOBs, BFILEs SQL data types, etc.

                So why then have UTL_FILE API in PL/SQL? Just in case... In the old Oracle 7 days, that was the only tool we had in PL/SQL for getting data from files into Oracle PL/SQL. Today there are vastly superior tools in PL/SQL that can be used instead of UTL_FILE.

                Another consideration. File I/O deals with data. The Very Best Place for Data is inside Oracle. Not in a file, even a log file, outside Oracle.

                So it makes very little sense most of the time to use UTL_FILE to read data (as this can be done with various other loading tools), or write data (as this data should be written to Oracle tables instead). Obviously there are exceptions.. but these should be few and far in between.
                • 5. Re: UTL_FILE
                  hi !

                  " Today there are vastly superior tools in PL/SQL that can be used instead of UTL_FILE"

                  which r the tools in PL/SQL that can be used instead of UTL_FILE for reading & writing
                  files ,specifically image files like JPG,BMP,GIF,etc..

                  I was trying to write JPG file using UTL_FILEs PUT_RAW function.
                  but the outfile file is displaying junk data.

                  can u suggest any solution on this .

                  • 6. Re: UTL_FILE
                    That depends entirely on the requirements - e.g. how are the images obtained, purpose of storing, how these will be accessed again, etc.

                    You can get images into Oracle by using the DBMS_LOB API (see the Oracle® Database PL/SQL Packages and Types Reference guide).

                    You can get images into and out of Oracle via HTTP using MOD_PLSQL (an Apache shared object library) and a document table in Oracle and the WPG_DOCLOAD API (see the Oracle® Application Server mod_plsql User's Guide guide)

                    Or you can simply use Oracle Application Express (a web development system running ontop of MOD_PLSQL that allows you to build Oracle web applications using PL/SQL and SQL).

                    Or you may select to keep the image files on the file system (e.g. you want simply to index o/s files) and interface into these files using the BFILE data type.

                    UTL_FILE should be the last option to look at. Why? Because it only interfaces to the local Oracle server's file system and not direct to the client. Thus if you use UTL_FILE, you still need to another gear to move that file's contents to a client. Which means lots of moving parts in the delivery mechanism to get that content to a client. That is why I consider UTL_FILE as an inferior tool to use to get data to the client.

                    Ditto for getting data into Oracle. If files need to be loaded, external tables and the DBMS_LOB API is far easier and simpler to use - again less moving parts which you need to code.

                    I would think twice before using UTL_FILE. It is a very primitive API that is seldom really needed because it's the only one than can do the job and meet the requirements.
                    • 7. Re: UTL_FILE
                      thanks Billy for reply.
                      My problem is little bit different.
                      I am using Oracle9iR2 .
                      The table structure :-
                      Table Name - Image
                      Table Fields
                      1. ImageId (Varchar)
                      2 BlobImage (Blob)

                      Procedure "Write_Blob_to_File" reads data from field 'BlobImage' (contains JPG image file) and saves on server hard drive.

                      ( ImgID IN varchar2
                      v_lob_loc BLOB;
                      v_buffer RAW(32767);
                      v_buffer_size BINARY_INTEGER;
                      v_amount BINARY_INTEGER;
                      v_offset NUMBER(38) := 1;
                      v_chunksize INTEGER;
                      v_out_file UTL_FILE.FILE_TYPE;
                      fname varchar2(100);
                      -- -------------------------------------------------------------
                      -- | SELECT THE LOB LOCATOR |
                      -- -------------------------------------------------------------
                      SELECT BLOBIMAGE INTO v_lob_loc
                      FROM Image
                      WHERE IMAGEID = ImgID;

                      fname:=concat(ImgId , '.jpg');
                      -- -------------------------------------------------------------
                      -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN |
                      -- -------------------------------------------------------------
                      v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

                      IF (v_chunksize < 32767) THEN
                      v_buffer_size := v_chunksize;
                      v_buffer_size := 32767;
                      END IF;

                      v_amount := v_buffer_size;

                      -- -------------------------------------------------------------
                      -- | OPENING THE LOB IS OPTIONAL |
                      -- -------------------------------------------------------------
                      DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

                      -- -------------------------------------------------------------
                      -- | WRITE CONTENTS OF THE LOB TO A FILE |
                      -- -------------------------------------------------------------
                      v_out_file := UTL_FILE.FOPEN(
                      location => 'BFILE_DIR',
                      filename => fname,
                      open_mode => 'w',
                      max_linesize => 32767);

                      WHILE v_amount >= v_buffer_size

                      lob_loc => v_lob_loc,
                      amount => v_amount,
                      offset => v_offset,
                      buffer => v_buffer);

                      v_offset := v_offset + v_amount;

                      UTL_FILE.PUT_RAW (
                      file => v_out_file,
                      buffer => v_buffer,
                      autoflush => true);

                      UTL_FILE.FFLUSH(file => v_out_file);

                      END LOOP;

                      UTL_FILE.FFLUSH(file => v_out_file);


                      -- -------------------------------------------------------------
                      -- | CLOSING THE LOB
                      -- -------------------------------------------------------------



                      this procedure creates new .jpg file but the quality of the image is very poor .
                      The problem is in the statement

                      v_out_file := UTL_FILE.FOPEN(
                      location => 'BFILE_DIR',
                      filename => fname,
                      open_mode => 'w',
                      max_linesize => 32767);

                      Oracle 10g supports open_mode 'wb' but Oracle 9i not.
                      So the output jpg image is very poor

                      Pl. suugest some solution for this prob.

                      • 8. Re: UTL_FILE
                        In defence of UTL_FILE what I find it is perfect for is debugging PL/SQL packages & procedures.

                        In our applications we include a PACK_LOG_FILE package which simply consists of 4 functions: 1 generic "log message" function which in turn calls a create log file, write to log file, and close log file function as appropriate. Grant the Read/Write Directory privilege to the package owner and voila, you get debugging messages straight into a text file. The package includes a "run mode" flag so you can switch it off in your production databases.

                        We tend to put these log files in the /tmp directory on the server, so using Samba you can map that directory as a drive on your PC, which then allows you to browse them using your favourite text editor.

                        A very powerful and simple tool!

                        If you want a copy of our PACK_LOG_FILE package let me know & I'll post it for you.

                        • 9. Re: UTL_FILE
                          hi !!
                          thanks Tariks for ur help & ur PACK_LOG_FILE package suggestion.

                          I solved my prob. by writing small Java stored procedure by reading article from the site -


                          • 10. Re: UTL_FILE
                            Beg to differ - as I harped on, the best place for data is inside Oracle. Not outside.

                            Having a log table is by far superior. It can be queried, ordered, counted, filtered, etc.

                            Also, when calling the LOG procedure (which inserts a log message via autonomous transaction), it also records the PL/SQL stack and saved that as a column (nested table) with the row.

                            Now when a developer needs to view his log/debug messages, he only needs TOAD or SQL*Plus, SQL*Developer or whatever he uses. No network mappings needed. No issues with network (SAMBA) security. Firewalls. Etc.

                            At the same time he can get a complete dump of the entire PL/SQL stack, plus actual line numbers, of the PL/SQL code that was active at the time the log/debug message call was made.

                            Cleaning a log table is also a lot easier than cleaning a /tmp directory - and a log table (in a tablespace with a space quota) cannot cause Unix process to fail because of a lack of /tmp filesystem space.

                            I honestly think that one should think very carefully about using UTL_FILE. It has very little use in today's Oracle environment using the Oracle tools and features we now have.
                            • 11. Re: UTL_FILE
                              Hey I'm just offering an example of when you might use the package. 100% agree the best place for data is in the database, but when you're debugging code 9 times out of 10 you're just trying to follow the path the code has taken, so the data itself is pretty meaningless, ie you don't want to analyse it as data. As soon as you do, you're right, you're collecting data and you should put it in a table.

                              Also, I'm a big fan of samba, so it works for me!
                              • 12. Re: UTL_FILE
                                i have used the same code as above for uploading files but when it comes to .bmp files this code fails. Any idea what may be the problem.