Skip to Main Content
Advanced Oracle PL/SQL Programming with Packages
book

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
October 1996
Intermediate to advanced content levelIntermediate to advanced
687 pages
16h 41m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin

Publisher Resources

ISBN: 1565922387Supplemental ContentCatalog PageErrata