PROCEDURE get_line (line_inout IN OUT line_type, curr_line#_in IN INTEGER := NULL);
The first argument, line_inout , is a record of type line_type (defined in the PLVio specification). The second argument, curr_line# , provides a current line number; if that number is not NULL, it will be used to increment the line# value found in the line_inout record.
The record contains all the information about a line necessary either for PLVio activity or other actions on a line of text. The definition of the record TYPE is:
TYPE line_type IS RECORD (text VARCHAR2(2000) := NULL, len INTEGER := NULL, pos INTEGER := 1, line INTEGER := 0, /* line # in original */ line# INTEGER := 0, /* line # for new */ is_blank BOOLEAN := FALSE, eof BOOLEAN := FALSE);
The following table explains the different fields of a line_type record:
The get_line procedure has two main steps:
When these two steps are completed, the newly populated record is returned to the calling program.
To give you an idea of how you can put get_line to use, consider the SQL*Plus script shown below. Stored in file inline2.sql , this program displays all the lines of code in a given program that contain the specified string.
DECLARE line PLVio.line_type; BEGIN PLVobj.setcurr ('&1'); PLVio.asrc (where_in => 'INSTR (text, ''&2'') > 0'); LOOP PLVio.get_line (line); EXIT WHEN line.eof; p.l (line.text); END LOOP; PLVio.closesrc; END; /
I call PLVobj.setcurr to set the current object to the requested program. I then point the source repository to ALL_SOURCE and add an element to the WHERE clause that will find only those lines in which the INSTR on the second argument returns a nonzero location. Now I am all set to loop through the rows identified by this WHERE clause. I exit when the eof field is set to TRUE; otherwise, I display the line and then call get_line again. Finally, I close the source when I am done, freeing up the memory used to read through ALL_SOURCE.
Here is an example of output from the inline2 program:
SQL> start inline2 b:PLVio SUBSTR (SUBSTR (srcrep.select_sql, 1, loc-1) || SUBSTR (srcrep.select_sql, loc)); SUBSTR (srcrep.where_clause, 1, loc-1) || SUBSTR (srcrep.where_clause, loc2+cmnt_len-1); SUBSTR SUBSTR RETURN SUBSTR (line_in.text, pos_in);
You might compare the implementation of this functionality in inline2.sql with the approach taken in the inline.sql script. What are the differences between the two implementations? Which would you prefer to use and maintain?
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.