#

| Chapter 12 PLVio: Reading and Writing PL/SQL Source Code |

## 12.3 Managing the Source Repository

To read information from a PLVio repository, you must do the following:

1. Define the source using the `setsrc` program.

2. Fine-tune your access to the source repository. You can modify the WHERE clause of your SELECT from a database table source with a number of procedures.

3. Initialize the source using the `initsrc` program.

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.

### 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.

12.2 Code Repositories Supported by PLVio |  | 12.4 The Source WHERE Clause

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.