Advanced Oracle Pl/Sql: Programming With Packages (Nutshell Handbook)
ISBN: B00006AVR6
EAN: N/A
EAN: N/A
Year: 1995
Pages: 195
Pages: 195
Authors: Steven Feuerstein, Debby Russell
| Chapter 12 PLVio: Reading and Writing PL/SQL Source Code |
The put_line procedure of PLVio writes text to the target repository. Its header is overloaded as follows:
PROCEDURE put_line (string_in IN VARCHAR2, line#_in IN INTEGER := NULL); PROCEDURE put_line (line_in IN line_type);
The first, "string" version of put_line simply bundles the text and line number into a record of type line_type and then calls the second, "record" version of put_line as shown below:
PROCEDURE put_line (string_in IN VARCHAR2, line#_in IN INTEGER := NULL) IS v_line line_type; BEGIN v_line.text := string_in; v_line.line# := line#_in; put_line (v_line); END;
Why do I bother with these two versions? To make the package as easy as possible to use. In many situations, you will simply want to take a string and an optional line number and throw it out into the target. You aren't dealing with the more complex aspects of PLVio and therefore have no need for a line record. In this situation, calling the "record version" of put_line becomes a hassle. By writing a few extra lines of code into the package itself, I relieve my users of the burden of declaring a throw-away data structure -- the line_type record.
If you plan to build reusable code that will really and truly be reused, you will need to make this kind of extra effort.
The put_line procedure (which from this point on refers to the record version) hides all of the complexity about the current target. You simply tell PLVio that you want to put a line in the target; it worries about the specific mechanics required for the current type of repository, as discussed below.
If writing to a file, put_line calls its comrade-in-code, PLVfile.put_line. All details are pushed down to this building block package. This lower-level layer of code helps PLVio avoid being bogged down in writing to an operating system file.
If writing to a string, put_line concatenates the new text onto the existing string_repos.text_out value, making sure to append the specified line to the current value of string_repos.text_out with an intervening newline character:
ELSIF string_target THEN IF string_repos.text_out IS NULL THEN string_repos.text_out := line_in.text; ELSE string_repos.text_out := string_repos.text_out || PLVchr.newline_char || line_in.text; END IF;
The use of the newline character allows you to dump the contents of this string into a readable format either for display purposes or for spooling to a file.
When writing to a PL/SQL table, put_line assigns the value to the appropriate row in the table. If standard output is the target, p.l is used to display the text.
Finally, if the target is a database table, put_line makes use of dynamic SQL (using the builtin DBMS_SQL package and the PL/Vision PLVdyn and PLVobj packages) to insert a row in the table and then (at the interval specified by PLVcmt) possibly perform a commit as well. The program name and type that are written to the database table (see settrg for information on specifying the names of the columns holding this information) are taken from the PLVobj current object.
NOTE: When you are writing to an operating system file, you must execute the PLVio.closetrg command to close the file before you can see any of the new information you have written to the file.
PLVio provides a procedure named src2trg; with a single line of code, this procedure copies the specified contents of the source repository to the target. The header for src2trg is:
PROCEDURE src2trg (close_in IN BOOLEAN := TRUE);
If you pass a value of TRUE in your call to src2tg, then src2trg will also close the target when it is done performing the transfer. You will almost certainly want to do this when you are writing to a file.
The src2trg procedure executes a loop to read through the source with get_lines and write to the target with put_lines, as the body of the procedure makes clear:
PROCEDURE src2trg IS line line_type; BEGIN LOOP get_line (line); EXIT WHEN line.eof; put_line (line); END LOOP; IF close_in THEN closetrg; ENDIF; END;
I wrote this procedure for the PLVhlp.show procedure. This program needs to read all the help text from the source and transfer it to a PL/SQL table. From that point on, the more program displays a page's worth of text from the PL/SQL table using the PLVio.disptrg procedure (described in the next section).
The disptrg procedure displays the contents of the target repository. Its header is:
PROCEDURE disptrg (header_in IN VARCHAR2 := NULL, start_in IN INTEGER := 1, end_in IN INTEGER := target_row);
The first argument, header_in, is an optional header to describe the output. The second and third arguments, also optional, restrict the lines to be displayed. These arguments are currently used only when the target type is PL/SQL table. For all other target types, all the code found in the target will be displayed.
Let's look at an example of using disptrg. In the following script (stored in file dumpemp.sql), I employ PLVio to write information to a table and then display it. This script simply transfers employee names from emp to the target PL/SQL table and then displays the contents of the target. Notice that I do not use PLVio for reading from any kind of source repository. I am only using the target side of PLVio. No one says you have to employ both source and target repositories of PLVio.
BEGIN PLVio.settrg (PLV.pstab); FOR emp_rec IN (SELECT ename FROM emp WHERE deptno = &1) LOOP PLVio.put_line (emp_rec.ename); END LOOP; PLVio.disptrg; END; /
Here is an example of the execution of dumpemp.sql:
SQL> start dumpemp 20 Contents of Table JONES FORD SMITH SCOTT ADAMS
I could remove the first line of the script, which sets the target, and the rest of the script would work just fine. With this simple action, the script would work with whatever target has been selected outside of the script. The call to put_line would add a line to the target. And the final call to PLVio.disptrg would display the current target's contents. The utility and applicability of disptrg are kept distinct from the particular target.
| 12.6 Reading From the Source | 12.8 Saving and Restoring Settings |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.