Teach Yourself PL/SQL in 21 Days- P11

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

100
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Teach Yourself PL/SQL in 21 Days- P11: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Teach Yourself PL/SQL in 21 Days- P11

Generating Dynamic SQL 477 Of those three steps, only the first step represents a difference from what you would do to process a static SELECT statement by using PL/SQL. The Syntax for the OPEN...FOR Statement , SYNTAX OPEN cursor FOR string [USING bind[, bind]...]; In this syntax the parameters are as follows: 16 • cursor is the cursor that you want to open. This is actually a pointer to a cursor, and must be a REF CURSOR variable. • string is a variable or literal that contains the SQL statement you want to execute. • bind is a bind variable. You use these to pass parameters to the dynamic SQL state- ment. The parameter markers are numbered, and must be :1, :2, and so on. The first bind variable becomes the value :1, the second bind variable becomes the , value :2, and so forth. The cursor that you declare and use with the OPEN...FOR statements must be a REF CUR- SOR. You can declare a REF CURSOR like this: TYPE ref_cursor_type IS REF CURSOR; your_cursor ref_cursor_type; Listing 16.8 shows OPEN...FOR being used to open a cursor on a dynamic SQL state- ment. The rows returned by that statement are then fetched into a PL/SQL record, and from there they are displayed by using DBMS_OUTPUT. INPUT LISTING 16.8 Executing a SELECT by Using Native Dynamic SQL 1: DECLARE 2: TYPE your_cursor_type IS REF CURSOR; 3: your_cursor your_cursor_type; 4: 5: -- Declare a record type for the output 6: TYPE dyn_record IS RECORD ( 7: yourrow yourtable.yourrow%TYPE, 8: yourdesc yourtable.yourdesc%TYPE 9: ); 10: 11: -- Note, could also use: 12: -- dyn_rec yourtable%ROWTYPE; 13: -- 14: dyn_rec dyn_record; 15: continues
478 Day 16 LISTING 16.8 continued 16: dynamic_select_stmt VARCHAR2(100); 17: BEGIN 18: -- Generate the dynamic SELECT statement. 19: dynamic_select_stmt := 20: ‘SELECT yourrow, yourdesc FROM yourtable’; 21: dynamic_select_stmt := dynamic_select_stmt || 22: ‘ ORDER BY yourrow DESC’; 23: 24: -- Open a cursor on the dynamic statement. 25: OPEN your_cursor FOR dynamic_select_stmt; 26: 27: -- Loop through and display all the data. 28: LOOP 29: -- Fetch the next row, exit the loop when 30: -- no more data is left. 31: FETCH your_cursor 32: INTO dyn_rec; 33: EXIT WHEN your_cursor%NOTFOUND; 34: 35: -- Display the data using DBMS_OUTPUT 36: DBMS_OUTPUT.PUT_LINE(dyn_rec.yourrow || 37: ‘ ‘ || dyn_rec.yourdesc); 38: END LOOP; 39: 40: -- Close the cursor. 41: CLOSE your_cursor; 42: 43: END; 44: / 5 Five OUTPUT 4 Four 3 Three 2 Two 1 One PL/SQL procedure successfully completed. The REF CURSER variable used with the OPEN statement is declared in lines 2–3. ANALYSIS Lines 18–20 build on the initial SELECT statement, and the next two lines (21–22) add an ORDER BY clause. The cursor is opened in line 25, through the use of the OPEN...FOR statement. Note that OPEN FOR references the VARCHAR2 variable containing the SELECT statement. From here on out, it’s just normal everyday PL/SQL cursor pro- cessing. The FETCH in lines 31–32 fetches the data from the cursor into a record. You could also fetch the data into a list of variables. The DBMS_OUTPUT call in line 36–37 allow SQL*Plus to display the data, and the CLOSE statement in line 41 closes the cursor after all the data has been processed.
Generating Dynamic SQL 479 Executing PL/SQL Blocks You can execute PL/SQL blocks by using native dynamic SQL through the use of the EXECUTE IMMEDIATE statement. Listing 16.9 shows the native dynamic SQL version of Listing 16.4. INPUT LISTING 16.9 Executing a PL/SQL Block Using Native Dynamic SQL 1: DECLARE 16 2: block_to_execute VARCHAR2(200) := 3: ‘BEGIN 4: SELECT YourRow,YourDesc 5: INTO :1, :2 FROM YourTable 6: WHERE YourRow = 2; 7: END;’; 8: 9: YourRow NUMBER; 10: YourDesc VARCHAR2(100); 11: BEGIN 12: EXECUTE IMMEDIATE block_to_execute 13: USING OUT YourRow, OUT YourDesc; 14: 15: DBMS_OUTPUT.PUT_LINE(YourRow || ‘ ‘ || YourDesc); 16: END; 17: / You can see that this code is a lot simpler to understand than the Listing 16.4 ANALYSIS version. The block_to_execute variable, declared in lines 2–7, contains the PL/SQL block to be executed. The EXECUTE IMMEDIATE statement in lines 12–13 is used to execute the block. The crucial thing to note here is that the bind variables listed in the USING clause both have the keyword OUT in front of them. This allows them to receive values back from the PL/SQL block. So the PL/SQL block issues a SELECT...INTO state- ment that places values into these variables, and because they are OUT bind variables, those values are returned to you. This code fails if there are two rows in YOURTABLE with a value of 2 for the Caution YOURROW column. If you ran Listing 16.7 more than once, that might be the case.
480 Day 16 Summary Today’s lesson covers Oracle’s DBMS_SQL package, as well as the new native dynamic SQL features included with Oracle8i. Both let you dynamically build and execute SQL statements from within PL/SQL. DBMS_SQL is the way to go if your code must run on releases of Oracle prior to the 8i release. Otherwise, you should use native dynamic SQL if you can. You’ll find it much easier to deal with, and the resulting code will be more easily understood. With DBMS_SQL, you have to open a cursor for each statement, define bind variables, fetch rows returned by queries, and get each column one at a time. Quite a lot of code is need- ed to do all that, and that code is rather tedious to write. Native Dynamic SQL simplifies things. Using Native Dynamic SQL, as with DBMS_SQL, you open a cursor for a dynami- cally generated SQL statement. However, unlike with DBMS_SQL, you can then treat that cursor as an ordinary PL/SQL cursor. Fetching the data then becomes a very easy task. Q&A Q Now that native dynamic SQL has arrived, is there any reason I would ever use DBMS_SQL? A There probably are some reasons, but there sure aren’t many. One thing that DBMS_SQL can handle that native dynamic SQL can’t, at least not easily, is the situa- tion where you know absolutely nothing about the tables and columns that you will be querying. DBMS_SQL allows you to issue a query, and then dynamically discover how many columns the query returns, as well as what the datatypes are. That’s a fairly advanced use, but if you need to be able to do it, then you need to use DBMS_SQL. Q What three types of statements can be executed dynamically? A Using dynamic SQL, you can execute non-query DDL and DML statements, SQL queries, and anonymous PL/SQL blocks. Q Should dynamically executed queries be written with trailing semicolons? A No! This is a very common mistake to make. Do not include a trailing semicolon with any dynamic SQL statement. The reason is that strictly speaking, the semi- colon is not part of the SQL syntax. You need it when you write a static SQL state- ment, because Oracle needs to know where the SQL statement ends. However, when you are writing dynamic SQL, you are only working with one statement at a time, so a terminator is not needed.
Generating Dynamic SQL 481 Q What are the general steps for using DBMS_SQL? A You need to open a cursor, parse the statements to be executed, bind any variables that are necessary, define columns if you are executing a SELECT statement, execute the query, retrieve the data (if there is any) into some PL/SQL variables, and close the cursor. Workshop 16 You can use this to test your comprehension of this lesson and put what you’ve learned into practice. You’ll find the answers to the quiz and exercises in Appendix A, “Answers.” Quiz 1. For DML and DDL statements, and also for queries, what punctuation must not be included at the end of the query? 2. What is meant by the term dynamic SQL? 3. What is Oracle’s term for the new version of dynamic SQL? 4. When using native dynamic SQL, what new form of the OPEN statement is used to open a cursor on a dynamic SQL statement? Exercise Write a stored procedure to take a username as an argument, and create a version of mytable in that user’s schema.
WEEK 3 DAY 17 Writing to Files and the Display by Timothy Atwood and Jonathan Gennick The PL/SQL language itself does not have any mechanism for performing either file or screen output. However, Oracle supplies a number of built-in packages that allow you to perform I/O, and that can be called from PL/SQL. Today’s lesson talks about the following: • The DBMS_OUTPUT package • The UTL_FILE package • The TEXT_IO package You’ve already seen DBMS_OUTPUT used throughout this book as a way to dis- play output on the screen, using SQL*Plus. It has some other capabilities, too, which you’ll learn about today. The UTL_FILE and TEXT_IO packages allow you to read and write text files. UTL_FILE is a server-side built-in package that allows you to read and write files on the database server. TEXT_IO is an Oracle Developer package that allows you to do file input/output (I/O) on the client.
484 Day 17 Exploring the DBMS_OUTPUT Package Looking at many of the examples in this book might lead you to believe that DBMS_OUTPUT’s only function is to allow you to display PL/SQL output using SQL*Plus. That’s only part of the story, though. DBMS_OUTPUT is actually designed to let you write output to a buffer in memory, and then read that output back again. Figure 17.1 illustrates this, and also shows how SQL*Plus fits into the picture. FIGURE 17.1 From PL/SQL, you make PL/SQL Program Unit calls to PUT_LINE in order DBMS_OUTPUT allows to place text in the buffer you to read and write data to and from a SQL*Plus reads buffer in memory. DBMS_OUTPUT text from the buffer PUT_LINE Package by making calls to GET_LINE Memory Buffer GET_LINE SQL*Plus The usefulness of DBMS_OUTPUT becomes apparent when you realize that the procedure that reads data from the buffer does not have to be the same procedure that wrote it there in the first place. Any procedure can read the data. When you issue the command SET SERVEROUTPUT ON in SQL*Plus, you are really telling SQL*Plus to check the buffer for data after each statement executes, fetch any data that’s found, and display it for you to see. In its most generic sense, DBMS_OUTPUT can be used to communicate data between any two PL/SQL procedures. DBMS_OUTPUT allows you to communicate between two program units that Note are part of the same session. To communicate across sessions, you need to use the DBMS_PIPE package. You’ll learn about that on Day 19, “Alerting and Communicating with Other Procedures: The DBMS_ALERT and DBMS_PIPE Packages.” Enabling the DBMS_OUTPUT Package Before you can use DBMS_OUTPUT, you need to call the initialization procedure DBMS_OUTPUT.ENABLE. SQL*Plus does this for you automatically whenever you issue a SET SERVEROUTPUT ON command. However, you might want to do it yourself. The main reason that you might want to call DBMS_OUTPUT.ENABLE yourself is to allocate a buffer
Writing to Files and the Display 485 larger than the default of 20,000 characters. Another reason to call DBMS_OUTPUT.ENABLE yourself would be if SQL*Plus isn’t the destination for your messages. The Syntax for the DBMS_OUTPUT.ENABLE Procedure SYNTAX DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000); The buffer_size parameter controls the size of the buffer, and can be any value between 2,000 and 1,000,000. The default is 20,000. The following PL/SQL block shows a call to enable the DBMS_OUTPUT package: BEGIN DBMS_OUTPUT.ENABLE (1000000); END; If you are using DBMS_OUTPUT to send a lot of data to SQL*Plus, keep in mind that SQL*Plus can’t begin reading until all the data is sent. Therefore, your buffer must be 17 large enough to contain all the output. Also bear in mind that SQL*Plus release 8.0 and above allows you to specify the buffer size as an argument to the SET SERVEROUTPUT ON command. For example, the following command also enables DBMS_OUTPUT, and with a buffer size of 1,000,000 bytes: SET SERVEROUTPUT ON SIZE 1000000 After you enable the package, you can use it to write data to the buffer, and to read it back again. Disabling the DBMS_OUTPUT Package When you’re done using DBMS_OUPUT, you can disable the package by making a call to DBMS_OUTPUT.DISABLE. This has the effect of purging the buffer of any remaining infor- mation. The Syntax for the DBMS_OUTPUT.DISABLE Procedure SYNTAX DBMS_OUTPUT.DISABLE; There are no parameters to the DISABLE procedure. The following PL/SQL block shows how it is called: BEGIN DBMS_OUTPUT.DISABLE; END; SQL*Plus calls DBMS_OUTPUT.DISABLE for you whenever you issue a SET SERVEROUTPUT OFF command. After output has been disabled, any further calls to DBMS_OUTPUT.PUT_LINE, DBMS_OUTPUT.GET_LINE, and so forth are ignored.
486 Day 17 Writing Data to the Buffer You write data to the DBMS_OUTPUT buffer by using a combination of the PUT_LINE, PUT, and NEW_LINE procedures. PUT_LINE writes a line of text, followed by a newline charac- ter. PUT writes text, but doesn’t follow that text with a newline. The NEW_LINE procedure writes one newline character. The Syntax for the DBMS_OUTPUT.PUT_LINE, DBMS_OUTPUT.PUT, and , SYNTAX DBMS_OUTPUT.NEW_LINE Procedures DBMS_OUTPUT.PUT_LINE (item IN NUMBER); DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2); DBMS_OUTPUT.PUT_LINE (item IN DATE); DBMS_OUTPUT.PUT (item IN NUMBER); DBMS_OUTPUT.PUT (item IN VARCHAR2); DBMS_OUTPUT.PUT (item IN DATE); DBMS_OUTPUT.NEW_LINE; In this syntax item is the value written to the buffer. Items of type DATE and NUMBER are , always converted to text before being written. The PUT and PUT_LINE procedures each take one argument. The procedures are over- loaded in order to allow you to pass in dates, numbers, and text. If you pass in text, it is written to the buffer. If you pass in a date or a number, it is converted to text, and then written to the buffer. It’s usually best to convert things to text yourself, so that you can control the output format. The following example, in Listing 17.1, shows DBMS_OUTPUT being enabled, and some data being written to the buffer. SQL*Plus has been used to execute the block and cap- ture the output. INPUT LISTING 17.1 Using DBMS_OUTPUT to Place Data in the Buffer 1: SET SERVEROUTPUT ON 2: 3: BEGIN 4: --We only need a small buffer for this example. 5: DBMS_OUTPUT.ENABLE (2000); 6: 7: DBMS_OUTPUT.PUT_LINE(‘Three names will be written.’); 8: DBMS_OUTPUT.PUT(‘Jenny’); 9: DBMS_OUTPUT.NEW_LINE; 10: DBMS_OUTPUT.PUT(‘Shirley’); 11: DBMS_OUTPUT.NEW_LINE; 12: DBMS_OUTPUT.PUT(‘Tina’);
Writing to Files and the Display 487 13: DBMS_OUTPUT.NEW_LINE; 14: END; 15: / Three names will be written. OUTPUT Jenny Shirley Tina PL/SQL procedure successfully completed. This PL/SQL block writes one line into the buffer by using the PUT_LINE proce- ANALYSIS dure (line 5). It then writes three more lines using a combination of the PUT and NEW_LINE procedures (lines 6–11). Because SERVEROUTPUT had been turned on, SQL*Plus reads the data from the buffer and displays it onscreen. 17 The PUT and PUT_LINE procedures only handle lines up to 255 characters long. If you attempt to write a line longer than that, you get an error. You also get an error if you attempt to write more data into the buffer than it will hold. Reading Data from the Buffer Two procedures, GET_LINE and GET_LINES, allow you to read from the buffer. GET_LINE allows you to read one line at a time, and GET_LINES allows you to read several lines into an array. The Syntax for GET_LINE and GET_LINES , SYNTAX DBMS_OUTPUT.GET_LINE ( line OUT VARCHAR2, status OUT INTEGER); DBMS_OUTPUT.GET_LINES ( lines OUT CHARARR, numlines IN OUT INTEGER); In this syntax the parameters are as follows: • line is the line retrieved by GET_LINE. • status indicates whether a line was retrieved. A status of 1 means that the line parameter contains a line retrieved from the buffer. A status of 0 means that the buffer was empty[md]that is, nothing was retrieved. • lines is a table of VARCHAR2(255). You can declare this table by using the type DBMS_OUTPUT.CHARARR. See Listing 17.2 for an example. • numlines is both an input and an output. When calling GET_LINES, you should set this to the number of lines that you want to retrieve. GET_LINES replaces that value , with the number of lines that were actually retrieved.
488 Day 17 The example in Listing 17.2 is an extension of Listing 17.1. It shows GET_LINE being used to retrieve the three names from the buffer. It also demonstrates that the buffer con- tents are maintained across PL/SQL blocks. Note This listing should be executed from SQL*Plus. INPUT/ OUTPUT LISTING 17.2 Using GET_LINE to Retrieve Data From the Buffer 1: SET SERVEROUTPUT OFF 2: 3: BEGIN 4: --We only need a small buffer for this example. 5: DBMS_OUTPUT.ENABLE (2000); 6: 7: DBMS_OUTPUT.PUT_LINE(‘Three names will be written.’); 8: DBMS_OUTPUT.PUT(‘Jenny’); 9: DBMS_OUTPUT.NEW_LINE; 10: DBMS_OUTPUT.PUT(‘Shirley’); 11: DBMS_OUTPUT.NEW_LINE; 12: DBMS_OUTPUT.PUT(‘Tina’); 13: DBMS_OUTPUT.NEW_LINE; 14: END; 15: / 16: 17: PL/SQL procedure successfully completed. 18: 19: 20: SET SERVEROUTPUT ON 21: DECLARE 22: throw_away VARCHAR2(50); 23: name1 VARCHAR2(10); 24: name2 VARCHAR2(10); 25: name3 VARCHAR2(10); 26: status NUMBER; 27: BEGIN 28: --The first line in the buffer is a throw away. 29: DBMS_OUTPUT.GET_LINE(throw_away, status); 30: 31: --The next three lines will be the three names. 32: DBMS_OUTPUT.GET_LINE(name1, status); 33: DBMS_OUTPUT.GET_LINE(name2, status); 34: DBMS_OUTPUT.GET_LINE(name3, status); 35: 36: --Now that we have the names, write them out 37: --on one line. SQL*Plus will pick this up and 38: --display it. 39: DBMS_OUTPUT.PUT_LINE(name1 || ‘ and ‘ || 40: name2 || ‘ and ‘ || name3);
Writing to Files and the Display 489 41: END; 42: / Jenny and Shirley and Tina PL/SQL procedure successfully completed. The first PL/SQL block (lines 3–14) writes three names into the buffer, one name ANALYSIS to a line. Because this example is executed from SQL*Plus, the SERVEROUTPUT setting is turned off (line 1) to prevent SQL*Plus from reading and displaying the names. Instead, the names remain in the buffer, where they can be accessed by the second block. The second PL/SQL block (lines 21–41) reads the first line and throws it away (line 28–29). Then it reads each of the three names (lines 32–34). Finally, it concatenates those three names together in one line, and writes that line back out to the buffer (lines 17 36–40). Because the SERVEROUTPUT setting has been turned on for the second block, SQL*Plus reads the results and displays them onscreen. The buffer is a first-in, first-out (FIFO) buffer. The first line to be written is Note also the first to be read. If you need to retrieve several lines at once from the buffer, you can use the GET_LINES procedure. It accepts a PL/SQL table (the equivalent of an array) as an argument, and retrieves as many lines as you request into that array. By modifying the code shown in Listing 17.2 to use GET_LINES instead of GET_LINE, you can retrieve all three names by using only one procedure call, as shown in Listing 17.3. LISTING 17.3 Using GET_LINES to Retrieve Three Lines from the Buffer with INPUT/ OUTPUT One Procedure Call 1: SET SERVEROUTPUT OFF 2: 3: BEGIN 4: --We only need a small buffer for this example. 5: DBMS_OUTPUT.ENABLE (2000); 6: 7: DBMS_OUTPUT.PUT_LINE(‘Three names will be written.’); 8: DBMS_OUTPUT.PUT(‘Jenny’); 9: DBMS_OUTPUT.NEW_LINE; 10: DBMS_OUTPUT.PUT(‘Shirley’); 11: DBMS_OUTPUT.NEW_LINE; 12: DBMS_OUTPUT.PUT(‘Tina’); continues
490 Day 17 LISTING 17.3 continued 13: DBMS_OUTPUT.NEW_LINE; 14: END; 15: / 16: 17: PL/SQL procedure successfully completed. 18: 19: 20: SET SERVEROUTPUT ON 21: DECLARE 22: throw_away VARCHAR2(50); 23: names DBMS_OUTPUT.CHARARR; 24: lines_to_get NUMBER; 25: inx1 NUMBER; 26: combined_names VARCHAR2(80); 27: status NUMBER; 28: BEGIN 29: --The first line in the buffer is a throw away. 30: DBMS_OUTPUT.GET_LINE(throw_away, status); 31: 32: --The next three lines will be the three names. 33: lines_to_get := 3; 34: DBMS_OUTPUT.GET_LINES(names, lines_to_get); 35: 36: --Now that we have the names, write them out 37: --on one line. SQL*Plus will pick this up and 38: --display it. 39: combined_names := ‘’; 40: FOR inx1 IN 1 .. lines_to_get LOOP 41: IF inx1 > 1 THEN 42: combined_names := combined_names || ‘ and ‘; 43: END IF; 44: 45: combined_names := combined_names || names(inx1); 46: END LOOP; 47: DBMS_OUTPUT.PUT_LINE(combined_names); 48: END; 49: / Jenny and Shirley and Tina PL/SQL procedure successfully completed. The use of GET_LINES actually makes this version of the code more complex. ANALYSIS Line 23, in the second block, is where the table of type DBMS_OUTPUT.CHARARR is declared. That table is eventually used to hold the results. Lines 29–30 are the same as in Listing 17.2, and simply serve to throw away the first line in the buffer. The call to
Writing to Files and the Display 491 GET_LINES occurs in lines 32–34. The lines_to_get parameter contains the value 3, telling GET_LINES to return all three names. The loop in line 40–43 then uses the value that GET_LINES passes back to iterate through the array the proper number of times. If GET_LINES returned more than three lines, or fewer than three lines, the value of lines_to_get would be set appropriately, and the loop would concatenate all the lines together. Exceptions Raised from the DBMS_OUTPUT Package There are two exceptions you have to worry about when using the DBMS_OUTPUT package. These are described in Table 17.1, along with the actions required to fix the problems. TABLE 17.1 Exceptions Raised by DBMS_OUTPUT Exception Code Error Description Corrective Action 17 ORU-10027 Buffer overflow Increase the buffer size if possible. Otherwise, find a way to write less data. ORU-10028 Line length Make sure that all calls made to PUT and overflow, limit of PUT_LINE have fewer than 255 characters. 255 characters per line Now that you know the exceptions, you can trap errors as they occur. Reading and Writing Files with the UTL_FILE Package The UTL_FILE package enables you to read and write files on the database server. There are two prerequisites to using UTL_FILE: • You must be granted execute privileges on the UTL_FILE package. • Your database administrator must set a database initialization parameter named UTL_FILE_DIR. Granting access to the package is easy. If you don’t already have execute privileges on UTL_FILE, your database administrator can grant it by logging on as the user SYS, and issuing a command like this: INPUT GRANT EXECUTE ON utl_file TO username;
492 Day 17 The matter of the UTL_FILE_DIR parameter is a bit more difficult to explain. You can’t just read and write files in any directory on the server. When you make calls to UTL_FILE_DIR, Oracle is really reading and writing the files for you. On most systems, the Oracle software runs in privileged mode, giving it access to all the files. Needless to say, that presents a security risk. To mitigate that risk, before UTL_FILE can be used, your database administrator must set the UTL_FILE_DIR parameter to point to a specific list of directories. All file I/O done by UTL_FILE must be done in one of those directories. The examples in this book assume the following setting: UTL_FILE_DIR = c:\a If you’re experimenting with UTL_FILE on a workstation, you need to add this line to the database parameter file. You also need to stop and restart the database afterward, in order for the new setting to take effect. When UTL_FILE_DIR is set, and you have been granted EXECUTE access to the UTL_FILE package, you are ready to read and write files. File Input Using UTL_FILE, the overall process to read (or write) a file is as follows: 1. Declare a file handle variable to use in identifying the file when you make calls to the various UTL_FILE routines. You can use the type UTL_FILE.FILE_TYPE for this purpose. 2. Declare a string of type VARCHAR2 to act as a buffer for reading in the file one line at a time. 3. Make a call to UTL_FILE.FOPEN to open the file. When you open a file, you need to specify whether you want to read the file or write to the file. You can’t do both. 4. If you’re reading the file, make calls to UTL_FILE.GET_LINE. If you’re writing, make calls to UTL_FILE.PUT_LINE. 5. When you’re done, call UTL_FILE.FCLOSE to close the file. The next section talks briefly about the various UTL_FILE procedures and functions, and shows the syntax information for each. Following that is a section with an example showing UTL_FILE being used to write data to a file.
Writing to Files and the Display 493 Using UTL_FILE Procedures and Functions The UTL_FILE package implements the following procedures and functions: Procedure or Function Description FCLOSE Closes a file. FCLOSE_ALL Closes all the files. FFLUSH Flushes any buffered data to be written out to disk immediately. FOPEN Opens a file. GET_LINE Reads one line from a file. 17 IS_OPEN Checks whether a file is open. NEW_LINE Writes a newline character out to a file. PUT Writes a string of characters to a file, but doesn’t follow that with a newline. PUT_LINE Writes one line to a file. PUTF Formats and writes output. This is a crude imita- tion of C’s printf() procedure. Many of the procedure names in the UTL_FILE package are named identically to corre- sponding procedures in DBMS_OUTPUT. For example, GET_LINE and PUT_LINE are used in both packages to read and write lines. The difference is in whether that I/O is done to and from a file or to and from a memory buffer. The FCLOSE Procedure The FCLOSE procedure closes a file. If the buffer for the file being closed is not empty, it is flushed to disk before the file is closed. The Syntax for the FCLOSE Procedure SYNTAX PROCEDURE FCLOSE(file IN OUT file_type); The file parameter is the file handle returned from FOPEN when the file was originally opened. Table 17.2 shows a list of possible exceptions raised by FCLOSE.
494 Day 17 TABLE 17.2 Exceptions Raised by FCLOSE and FCLOSE_ALL Exception Raised Description of Error UTL_FILE.INVALID_FILEHANDLE You passed a file handle that didn’t represent an open file. UTL_FILE.WRITE_ERROR The operating system was unable to write to the file. UTL_FILE.INTERNAL_ERROR An internal error occurred. The first exception in the list, that of an invalid file handle, is one that you can easily pre- vent simply by being careful to write good code. Make sure you open a file before you use it, and that you keep track of which variable has the file handle in it. Write errors can occur if the disk is full, or if some other error prevents Oracle from writing the data for you. An internal error indicates that Oracle itself is messed up. The FCLOSE_ALL Procedure The FCLOSE_ALL procedure closes all the files at once, flushing any buffers that are not empty. The Syntax for the FCLOSE_ALL Procedure SYNTAX PROCEDURE FCLOSEALL; Refer to Table 17.2 for a list of possible exceptions raised by FCLOSE_ALL. You should be aware that although FCLOSE_ALL does close all files, it does not mark the files as closed. Future calls to IS_OPEN still indicate that they are open, even though in reality they are not. The FOPEN Procedure FOPEN opens a file for reading, or for writing. FOPEN is a function, and it returns a file handle pointing to the file that was opened. There are two versions of FOPEN. One allows you to specify a maximum line size, and the other does not. The Syntax for the FOPEN Procedure , SYNTAX FUNCTION FOPEN(location IN VARCHAR2, filename IN VARCHAR2, openmode IN VARCHAR2) RETURN FILE_TYPE; FUNCTION FOPEN(location IN VARCHAR2, filename IN VARCHAR2, openmode IN VARCHAR2, max_linesize IN BINARY_INTEGER) , RETURN FILE_TYPE;
Writing to Files and the Display 495 The parameters are as follows: , • location is the name of the directory containing the file. This must match one of the directories listed for the UTL_FILE_DIR parameter. • filename is the name of the file. The name can include an extension. • openmode is the mode in which you are opening the file. Valid values are R, W, and A. Use R to read a file, W to write to a file, and A to append to an existing file. • max_linesize allows you to specify the maximum line size. The allowed range is 1 through 32,767. If you omit this parameter, then the default, 1023, is used. The ability to specify a line size in the FOPEN call is a new feature in Oracle8i. Note With releases of Oracle prior to 8.1.5, you are limited to a line size of 1023 bytes or less. 17 If you open a file for write, and a file with the same name exists already, that file is overwritten. If you append to a file that does not exist, a new file is created. , After the file has been successfully opened, the FOPEN function returns a file handle. You must use that handle for all further operations on the file. FOPEN can raise several excep- tions, which are listed in Table 17.3. TABLE 17.3 Exceptions Raised by FOPEN Exception Raised Description of Error UTL_FILE.INVALID_PATH The directory is not valid. You should check it against UTL_FILE_DIR. UTL_FILE.INVALID_MODE An invalid mode was specified. The open mode must be either R, W, or A. UTL_FILE.INVALID_OPERATION The file could not be opened for some other reason. Verify that the Oracle software owner has access to the directory (it could be a permissions issue) and contact your database administrator for help. UTL_FILE.INTERNAL_ERROR An internal error occurred. If you get the invalid path exception, you need to check your directory path against your database’s UTL_FILE_DIR parameter setting. Remember, you can only write to the specif- ic directories listed for that parameter. The invalid mode exception implies a coding error. To correct it, just modify your code to use one of the valid modes. If you get an
496 Day 17 invalid operation error, then there is some sort of operating system related reason why you can’t open the file. Unfortunately, PL/SQL won’t give you any details about what that problem is. The internal error is something you should never get, and indicates that Oracle is not functioning properly. The GET_LINE Function When performing file input, in order to read data from the file into the buffer, you use the GET_LINE function. The Syntax for the GET_LINE Function , SYNTAX PROCEDURE GET_LINE(file IN FILE_TYPE, buffer OUT VARCHAR2); The parameters are as follows: • file is the file handle returned from the FOPEN function when the file was origi- nally opened. • buffer is where the data is placed after it is read from the file. This must be of , type VARCHAR2. Possible errors that could arise are shown in Table 17.4. TABLE 17.4 Exceptions Raised by GET_LINE Exception Raised Description of Error UTL_FILE.INVALID_FILEHANDLE You passed an invalid file handle. Possibly you forgot to open the file first. UTL_FILE.INVALID_OPERATION The file is not open for reading (R mode), or there are problems with file permissions. UTL_FILE.VALUE_ERROR The buffer is not long enough to hold the line being read from the file. Increase the size of the buffer. UTL_FILE.NO_DATA_FOUND The end of file has been reached. UTL_FILE.INTERNAL_ERROR An error internal to the UTL_FILE system occurred. UTL_FILE.READ_ERROR An operating system error occurred while reading from the file. When you use GET_LINE to read a file, the maximum line length that it can handle is the one specified when you opened the file. This defaults to 1023 bytes, not including the newline character. The newline characters are stripped out, and aren’t returned to you. The IS_OPEN Function The IS_OPEN function tests to see if a file is open. You can use it before you attempt to open a file to be sure that it’s not already open. You can also test to make sure a file is open before you attempt to close it.