Unloading Oracle data to flat files is still very common. There are numerous unloader utilities on the web for this purpose and there are also many related topics in the Oracle forums. Methods for writing data to flat files vary, but strangely Oracle has never provided a tool to do this for us. The tools that are provided by Oracle export, Data Pump, writeable external tables write data quickly, but in a proprietary format, so for true ASCII flat files, we have to resort to our own homegrown utilities. There are several ways to generate flat files from data stored in Oracle. We can use Perl, C, C, ProC, Java and other languages that interface with Oracle but it is far more common for us to use SQL or PLSQL to perform this operation. For example, sqlplus can spool data to flat files very quickly, but it is a command utility and not part of the database. This makes it generally unsuited to being part of a robust database application. UTLFILE, on the other hand, is a built in package around which we can very easily base our unloader utilities. The main issue with UTLFILE, however, is that it is a relatively slow tool for unloading data, but in this article we will demonstrate some methods for speeding this up. We will see that with simple techniques we can achieve significant performance gains for our data unloads. Looking to upgrade to the latest Oracle certification Why Choose Oracle University 100 Student Satisfaction Why Get Oracle Certified. PLSQL 0 PLSQL interview questions and 23 answers by expert members with experience in PLSQL subject. Discuss each question in detail for better understanding and. DECLARE message varchar220 Hello, World BEGIN dbmsoutput. END How can I execute above plsql program in Oracle SQL Developer. Can any. We are going to test several versions of a standard data unload routine. We will run these on an 1. Release 1 database, but all examples are compatible with 1. The data to be dumped to flat file will be sourced from a single table of 1 million rows, which we will create as follows. SQL CREATE TABLE sourcedata. CONSTRAINT sourcedatapk. PRIMARY KEY x,y,z. Pl Sql Developer Program' title='Pl Sql Developer Program' />ORGANIZATION INDEX. SELECT ROWNUM AS x. B28359_01/appdev.111/b31695/debugging.gif' alt='Pl Sql Developer Program' title='Pl Sql Developer Program' />RPADx,5. AS y. RPADy,5. 0,y AS z. FROM dual. 1. 2 CONNECT BY ROWNUM lt 1. We have created an IOT index organized table to enable us to fully cache the data and eliminate any physical IO. Each test we run will therefore incur roughly the same IO costs. Using Autotrace, we will run a couple of full scans of this data until it is all in the buffer cache, as follows. SQL set autotrace traceonly statistics. Como Descobrir Senha De Arquivo Rar more. SQL SELECT FROM sourcedata. SQLet to client. SQLet from client. Pl Sql Developer Programs' title='Pl Sql Developer Programs' />SQLet roundtrips tofrom client. SQL SELECT FROM sourcedata. SQLet to client. SQLet from client. SQLet roundtrips tofrom client. We have reduced the physical IO as intended, so to complete our setup we will create an Oracle directory below. This is where we will write our flat files. SQL CREATE DIRECTORY dumpdir AS u. Directory created. Before we can tune our code, we need to know our baseline performance. In this case, it will be the time it takes to unload 1 million rows to a flat file using UTLFILE. Pl Sql Developer Program' title='Pl Sql Developer Program' />PUTLINE calls, using the following PLSQL block. SQL DECLARE. 3 vfile UTLFILE. FILETYPE. 4 vname VARCHAR21. PLSINTEGER 0. UTLFILE. FOPENDUMPDIR,vname,W,3. FOR r IN SELECT x, y, z AS csv. FROM sourcedata. UTLFILE. PUTLINEvfile, r. END LOOP. UTLFILE. FCLOSEvfile. DBMSOUTPUT. PUTLINEFilevname Linesvlines. Fileutlfileuntuned. Pl Sql Developer Program Window' title='Pl Sql Developer Program Window' />Lines1. PLSQL procedure successfully completed. Elapsed 0. 0 0. Our baseline performance is approximately 6. Note the following Line 1. SQL cursor for convenience. A dump utility would usually accept the delimiter as a parameter and concatenate a record piecemeal especially if using dynamic SQL Lines 1. From Oracle 1. 0g Release 1, this will be optimised by the PLSQL compiler into bulk collects with an array size of 1. All examples in this article will use either implicit or explicit bulk fetches of the same size. Note that readers on 9i databases will need to convert this and other implicit cursor examples to use explicit array fetches with BULK COLLECT to ensure that all examples are comparable. Our baseline code has two repetitive operations. First there is an incremented counter which is used to instrument the example. This has a negligible impact overall. Dub Taylor Biography. More importantly there are 1 million UTLFILE IO operations and these have a far more significant impact. Tracing the baseline example with the PLSQL Profiler and the new PLSQL Hierarchical Profiler clearly shows that 5. UTLFILE. PUTLINE calls. Our first technique for tuning this, therefore, will be to reduce the number of IO operations by buffering the output data. This is quite a simple method. We will use a local variable to buffer up to 3. K of data before writing it to file, as follows. SQL DECLARE. 3 vfile UTLFILE. FILETYPE. 4 vbuffer VARCHAR23. VARCHAR21. 28 utlfilebuffered. PLSINTEGER 0. CONSTANT VARCHAR21 CHR1. CONSTANT PLSINTEGER LENGTHceol. CONSTANT PLSINTEGER 3. UTLFILE. FOPENDUMPDIR,vname,W,3. FOR r IN SELECT x, y, z AS csv. FROM sourcedata. IF LENGTHvbuffer ceollen LENGTHr. THEN. 2. 0 vbuffer vbuffer ceol r. ELSE. 2. 2 IF vbuffer IS NOT NULL THEN. UTLFILE. PUTLINEvfile, vbuffer. END IF. 2. 5 vbuffer r. END IF. 2. 8 vlines vlines 1. END LOOP. 3. 2 UTLFILE. PUTLINEvfile, vbuffer. UTLFILE. FCLOSEvfile. DBMSOUTPUT. PUTLINEFilevname Linesvlines. Fileutlfilebuffered. Lines1. 00. 00. 00. PLSQL procedure successfully completed. Elapsed 0. 0 0. The algorithm highlighted above is reasonably self explanatory, but we will describe it anyway Lines 1. UTLFILE has a maximum write size of 3. If there is enough room in the buffer for a new record to be added including newline, then we simply append the new data together with a newline Lines 2. The buffer will only be null on the very first entry to the loop, so we make it the very last test Lines 2. UTLFILE Line 2. Line 3. This technique is simple yet extremely effective. We have reduced our elapsed time to less than half the baseline in other words, this unloading routine is now twice as fast for this dataset. With an average record size of 1. UTLFILE calls to roughly 3,2. Given the effectiveness of the buffering technique, we will continue to use it for the remaining examples in this article. It is possible to replicate this buffering algorithm using the UTLFILE. PUT, UTLFILE. NEWLINE and UTLFILE. FFLUSH procedures, but a test with our sample data took over 8. For this reason, the example is omitted from this article, but is included in the accompanying download. From Oracle 1. 0g onwards, it is possible to write a CLOB to a file with a single call, using the DBMSXSLPROCESSOR. CLOB2. FILE procedure. In the following example, we will prepare a temporary CLOB with our data instead of writing it with UTLFILE. When all source data has been added to the CLOB, we will write it to a flat file in a single call. SQL DECLARE. 3 vfile CLOB. VARCHAR23. 27. 67. VARCHAR21. 28 clob. PLSINTEGER 0. VARCHAR22. PLSINTEGER. CONSTANT PLSINTEGER 3. CASE. 1. 4 WHEN DBMSUTILITY. PORTSTRING LIKE IBMPC. THEN CHR1. 3CHR1. ELSE CHR1. 0. 1. END. LENGTHveol. DBMSLOB. CREATETEMPORARYvfile, TRUE. FOR r IN SELECT x, y, z AS csv. FROM sourcedata. IF LENGTHvbuffer veollen LENGTHr. THEN. 2. 7 vbuffer vbuffer veol r. ELSE. 2. 9 IF vbuffer IS NOT NULL THEN. DBMSLOB. WRITEAPPEND.