12.3.1 Setting the Source

The setsrc procedure assigns the type of source repository and defines the structure of the source (if a database table). The header for setsrc is as follows:

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.

12.3.2 Initializing the Source

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.

The header for the initsrc procedure is overloaded as follows:

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:

starting_at_in

The line number at which the query should start.

ending_at_in

The line number at which the query should end.

where_in

An optional WHERE clause to add to the existing clause of the source's SELECT statement.

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.

12.3.3 Using setsrc and initsrc

Here are some examples of calls to setsrc and initsrc .

1. Set the source to a string and then initialize the source.

   PLVio.setsrc (PLVio.c_string, long_code_string);
   PLVIO.initsrc;

2. Set the current object in PLVobj to the body of the PLVvu package. Set the source to the ALL_SOURCE data dictionary view and restrict access to only the first five lines of the code for the PLVvu package body.

   PLVobj.setcurr ('b:PLVvu');
   PLVio.setsrc (PLV.dbtab, 'all_source', schema_col_in => 'owner');
   PLVio.initsrc (1, 5);

3. Set the source to a table named temp_source with a set of completely different column names. Request that only those rows for the procedure calc_totals containing the string RAISE be read. Notice the use of named notation in my call to initsrc . Which of the two versions of initsrc is executed?

   PLVobj.setcurr ('p:calc_totals');
   PLVio.setsrc 
      (PLV.dbtab, 'temp_source', 'progname', 'progtype', 'line#', 'line');
   PLVio.initsrc (where_in => 'INSTR (line, ''RAISE'') > 0);

Answer: the string version of initsrc is executed, since there are default values for the string arguments. The integer version requires that the start and end numbers be provided.

12.3.4 High-Level Source Management Programs

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.