Advanced Oracle PL/SQL Programming with Packages

9.6. Implementing load_ from_dbms

The load_from_dbms procedure serves as a good example of a program for loading number-text combinations from any database table into a PL/SQL table using dynamic SQL. Since you can specify the table name, WHERE clause, and column names, you can load message text from multiple sources and for multiple purposes. You can even copy this program, modify it, and use it in other programs.

The implementation of this procedure is shown in Example 9.2. It is explained in the next section. (continued)

Example 9.2. The Implementation of load_ from_dbms

PROCEDURE load_from_dbms (table_in IN VARCHAR2, where_clause_in IN VARCHAR2 := NULL, code_col_in IN VARCHAR2 := 'error_code', text_col_in IN VARCHAR2 := 'error_text') IS select_string PLV.max_varchar2%TYPE := 'SELECT ' || code_col_in || ', ' || text_col_in || ' FROM ' || table_in; cur INTEGER; error_code INTEGER; error_text VARCHAR2(2000); PROCEDURE set_minmax (code_in IN INTEGER) IS BEGIN IF min_row IS NULL OR min_row > code_in THEN v_min_row := code_in; END IF; IF max_row IS NULL OR max_row < code_in THEN v_max_row := code_in; END IF; END; BEGIN IF where_clause_in IS NOT NULL THEN select_string := select_string || ' WHERE ' || where_clause_in; END IF; cur := PLVdyn.open_and_parse (select_string); DBMS_SQL.DEFINE_COLUMN (cur, 1, error_code); DBMS_SQL.DEFINE_COLUMN (cur, 2, error_text, 2000); PLVdyn.execute (cur); LOOP EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0; DBMS_SQL.COLUMN_VALUE (cur, 1, error_code); DBMS_SQL.COLUMN_VALUE ...