You are then ready to use the get_line procedure to retrieve lines of text from the source repository.
This section explains the setsrc and initsrc procedures. The next section explores how to modify the WHERE clause of the source SELECT for database tables.
PROCEDURE setsrc (srctype_in IN VARCHAR2, name_in IN VARCHAR2 := 'user_source', name_col_in IN VARCHAR2 := 'name', srctype_col_in IN VARCHAR2 := 'type', line#_col_in IN VARCHAR2 := 'line', text_col_in IN VARCHAR2 := 'text', schema_col_in IN VARCHAR2 := NULL);
The first argument, srctype_in , is the type of repository. The second argument, name_in , is the name of the repository. Its content varies according to the type of repository and will be explained below. The third through seventh arguments provide the names of the columns required for a database table source. The default values match the structure of the default table (the USER_SOURCE data dictionary view). Notice, therefore, that the schema column name is NULL. This column would be used only if you specified a table/view like ALL_SOURCE, which contains the source code for all programs to which you have access, regardless of schema.
The setsrc procedure transfers the arguments to the srcrep record, which is defined using the repos_rectype shown earlier. If you are using a string source, then the string_repos record is updated. If you are using a database source, then the SELECT statement that will be used to query from that table is constructed as follows (this is a simplified version of the actual SELECT, but gives you an idea of its structure):
srcrep.select_sql := 'SELECT ' || source_text_col_in || ', ' || source_line#_col_in || ' FROM ' || name_in || ' ' || ' WHERE ' || source_name_col_in || ' = :name ' || ' AND ' || srctype_col_in || ' = :type' || ' ORDER BY ' || source_line#_col_in;
If you are using a string source, the name_in argument contains the string which holds the text for the program you want to read. All other arguments are ignored. This string is then assigned into the string repository record as shown below:
IF string_source THEN string_repos.text_in := name_in; string_repos.start_pos := 1; string_repos.text_len := LENGTH (name_in); string_repos.text_out := NULL;
Notice that the other fields of the string_repos record are also initialized.
If you are using a file source, then the name_in argument is the name of the file. The source_name_col_in argument should contain the type:name specification for the object you are reading. So if you are reading the package body of PLVvu from the file PLVvu.spb , you would call setsrc as follows:
PLVio.setsrc (PLV.file, 'PLVvu.spb', 'b:PLVvu');
You must supply this third argument if you are writing (have set the target to) a database table.
If you are using a file source, all other arguments (after the first three) are ignored.
Once you have set the source repository, you can either move directly to initializing that repository, or you can, in the case of a database table source, modify the WHERE clause of the SELECT constructed by setsrc . In most cases, you will simply call initsrc , so I will discuss that procedure below. The next section discusses how to modify the source repository WHERE clause.
PROCEDURE initsrc (starting_at_in IN INTEGER, ending_at_in IN INTEGER, where_in IN VARCHAR2 := NULL); PROCEDURE initsrc (starting_at_in IN VARCHAR2 := NULL, ending_at_in IN VARCHAR2 := NULL, where_in IN VARCHAR2 := NULL);
The "integer version" of initsrc accepts up to three arguments, as follows:
If the source is a database table, specifying start and/or end line numbers results in additional elements in the WHERE clause of the SELECT statement. The where_in string is also appended to the SELECT's WHERE clause, if provided. For any other code sources, these three arguments are currently ignored. In other words, if you work with non-database table sources, you will always read the full set of lines of text in those sources. It is easy to see how initsrc should be enhanced to support these arguments; it's just a matter of time and resources. I encourage you to try adding this functionality yourself.
The "string version" of initsrc allows you to specify starting and ending strings for the source repository. In this case (and only when the source is a database table), the get_line procedure will only read those lines that come after the first occurrence of the starting string and before the first occurrence of the ending string.
Recognizing the most common sources of PL/SQL code, PLVio offers two specialized programs to both set and initialize the source, usrc and asrc . The usrc procedure sets the source repository to the USER_SOURCE data dictionary view. The asrc procedure sets the source repository to the ALL_SOURCE data dictionary view. Both usrc and asrc are overloaded with the same arguments as initsrc : the "starting at" string or line number, the "ending at" string or line number, and the optional WHERE clause. The headers for these programs are shown below:
PROCEDURE usrc (starting_at_in IN VARCHAR2 := NULL, ending_at_in IN VARCHAR2 := NULL, where_in IN VARCHAR2 := NULL); PROCEDURE usrc (starting_at_in IN INTEGER, ending_at_in IN INTEGER, where_in IN VARCHAR2 := NULL); PROCEDURE asrc (starting_at_in IN VARCHAR2 := NULL, ending_at_in IN VARCHAR2 := NULL, where_in IN VARCHAR2 := NULL); PROCEDURE asrc (starting_at_in IN INTEGER, ending_at_in IN INTEGER, where_in IN VARCHAR2 := NULL);
With asrc , for example, I could replace these three lines of code:
PLVobj.setcurr ('b:PLVvu'); PLVio.setsrc (PLV.dbtab, 'all_source', schema_col_in => 'owner'); PLVio.initsrc (1, 5);
with the following two lines:
PLVobj.setcurr ('b:PLVvu'); PLVio.asrc (1, 5);
You shouldn't have to deal with all those details when it is the kind of source setting you will be performing again and again.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.