#

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

## 12.2 Code Repositories Supported by PLVio

The various programs in the PLVio package that manage the source and target repositories and that read and write lines of text are independent of the particular sources and targets. When you call `put_line`, for instance, you do not write code that says "write this line to a file." You simply "say" with your code: "Write this line to the target." You define the target independently of the actual read and write commands. This separation of logical and physical aspects of PL/SQL code I/O makes it easy to support a wide range of repositories -- and to add to that range as PL/SQL's capabilities expand.

When I first built PLVio, I was working with Release 2.1 of the PL/SQL language. I was able, therefore, to write PLVio to read to and write from database tables, but I could not read and write operating system files. That feature was not available until Release 2.3. I was still able to build the package and put it to use throughout PL/Vision. When Release 2.3 became available, I enhanced PLVio to support this new repository option and, with the simple act of a recompile only of the PLVio package body, my existing utilities could now manipulate PL/SQL source code in operating system files!

The PLVio package supports the following types of repositories:

- PL/SQL string

- Database table

- PL/SQL table

- Operating system file (server side)

- Standard output (the screen, usually)

You set the source by calling `PLVio.setsrc`: you set the target by calling `PLVio.settrg`. Both are described in later sections.

Before diving into all the different programs, here are some details about how these different repositories are handled in PLVio, both as source and target.

### 12.2.1 String Source or Target

When you specify a string as source, you pass that string of text in to PLVio when you call the `PLVio.setsrc` procedure. At that time, your string will be assigned to the `text_in` field of the `string_repos`. The `string_repos` record is defined as an instance of the following record TYPE:

>        TYPE string_repostype IS RECORD
>           (text_in PLV.max_varchar2%TYPE,
>            start_pos INTEGER := 1,
>            text_len INTEGER := NULL,
>            text_out PLV.max_varchar2%TYPE := NULL);

PLVio defines a line within this string source as all text up to the next newline character in the string (equivalent to CHR(10) and available as a named constant of `PLVchr.newline_char`). The maximum size of a line in PLVio is 2000 bytes, so you will need to break up a large program into multiple strings separated by a newline if you want to specify a string as a source. The maximize size of the entire text is 32,767 -- the maximum length of a PL/SQL variable length string (represented in the above record definition by the `PLV.max_varchar%TYPE` anchored declaration).

You can view the current contents of the source or target strings by calling the `PLVio.srcstg` or `PLVio.trgstg` functions, respectively.

### 12.2.2 Database Source or Target

You can use a database table as a source or target for PL/SQL code. In either case, you can use the default table (which is the USER_SOURCE data dictionary view for source and the `PLV_source` table for target) or you can specify your own table. Since PLVio uses PLVdyn to execute dynamic SQL, you can provide the name of the table and its columns. Regardless of their names, however, the columns of a database repository for PLVio must have at least four columns structured as shown in the record TYPE for a repository below:

>        TYPE repos_rectype IS
>        RECORD
>           (name VARCHAR2(60),
>            type VARCHAR2(10) := c_notset,
>            name_col VARCHAR2(60) := 'name',
>            type_col VARCHAR2(60) := 'type',
>            line#_col VARCHAR2(60) := 'line',
>            text_col VARCHAR2(60) := 'text',
>            select_sql VARCHAR2(2000),
>            insert_sql VARCHAR2(2000),
>            where_clause VARCHAR2(1000) := NULL,
>            starting_at VARCHAR2(1000) := NULL);

In other words, you will need a name string column, a type string column, a line number column, and a text string column. These columns can be named whatever you want and you can have other columns in addition to these four, but these columns must be available and specified to PLVio.

Given these requirements, the table shown in the left-hand column below is valid for use in PLVio, while the table in the right-hand column cannot be used, since it lacks a line number column:

Valid for PLVio Source | Not Usable for PLVio Source
--- | ---
>     CREATE TABLE temp_source
>        (progname VARCHAR2(100),
>         progtype VARCHAR2(30),
>         linenum INTEGER,
>         linetext VARCHAR2(120));
>     CREATE TABLE temp_source
>        (objname VARCHAR2(100),
>         objtype VARCHAR2(30),
>         objline VARCHAR2(120));

As you can see, the record TYPE for a PLVio repository also stores other database-related information, such as the dynamically constructed SELECT and INSERT strings and the optional WHERE clause.

You need to have SELECT privileges only on the source database table. You will need INSERT and DELETE authority on the target database table. You may not, therefore, specify the USER_SOURCE data dictionary view as the target for output from PLVio.

When you specify a database table as the source repository, you will also make use of the PLVobj package to indicate the schema, program name, and program type you are interested in. Examples of this dependency are shown in Section 12.3, "Managing the Source Repository".

### 12.2.3 PL/SQL Table Target

If you want to avoid the SQL layer, you can use a PL/SQL table defined inside PLVio as the target for PL/SQL source code. PLVio does not currently support PL/SQL tables as sources for reading PL/SQL code. The PL/SQL table is defined in the PLVio specification as follows:

>        target_table PLVtab.vc2000_table;
>        target_row BINARY_INTEGER;

Since the `target_table` is in the specification, a user of PLVio can directly access and change the contents of `target_table`. It is up to you to only use this table in ways that are appropriate to PLVio and/or your specific coding objectives.

The `target_row` variable will tell you how many lines of code are defined in the PL/SQL table. The row number is treated as the line number for the source code. Once you have populated the table, you can display its contents or pass the table as an argument to another program to process the data in that table.

### 12.2.4 File Source or Target

You can request that `PLVio.put_line` write its text to an operating system file. In this case, `PLVio.put_line` calls the `PLVfile.put_line` program. This procedure in turn calls the appropriate elements of the builtin UTL_FILE package to interact with the operating system file. For more information on the requirements and restrictions when working with UTL_FILE, see Chapter 13, PLVfile: Reading and Writing Operating System Files.

### 12.2.5 Standard Output Target

You can request that output from calls to `PLVio.put_line` be directed to standard output or the screen. When you do this, `PLVio.put_line` is, in effect, calling the DBMS_OUTPUT.PUT_LINE program to display output (although it does happen through the `p` package). This is the way that `PLVgen` generates its PL/SQL source code, for example.

So if you ever execute a PLVgen program to generate code and you don't see anything, check your PLVio target type (with a call to the `PLVio.trgtype` function). You might be writing your code to a file or PL/SQL table or database table!

12.1 Why PLVio? |  | 12.3 Managing the Source Repository

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