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:

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!