SELECT * FROM [table] with PL/SQL procedure

View All Scripts Login to Run Script

Script Name SELECT * FROM [table] with PL/SQL procedure
Description "In Table" utility: use DBMS_SQL to implement a Method 4 dynamic SQL challenge. Overview: Show the rows of the table specified by the table name and optional where clause. This is more of a prototype than a production-quality utility; its main purpose is to provide a demonstration of the steps you must go through to implement Method 4 dynamic SQL with DBMS_SQL. Method 4 means that at the time you are writing your program, you either don't know the number and types of columns being selected or you don't know the number of bind variables in the dynamic string. In this case, since the user can supply the table name, we have no way of knowing which columns (and how many) will need to be selected. We will get the column information from ALL_TAB_COLUMNS and then use that to drive the dynamic SQL processing with DBMS_SQL. Note: in most cases, you will want to use Native Dynamic SQL for dynamic SQL requirements. DBMS_SQL is, however, very well suited to implementing Method 4. Parameters: table_in - name of the table or view, in the from schema.table or table. where_in - optional where clause. Do not include the WHERE keyword. If, however, you start the where clause with ORDER BY or GROUP BY, then that entire string will be added to the query. Your where clause could also contain an ORDER BY or GROUP BY after the where clause. colname_like_in - an optional filter that is applied to the set of columns to restrict which columns are displayed. A NULL value is treated as "%" - all columns. Requirements: * Oracle91 Database or higher: intab BULK COLLECTs into a collection of records. Restrictions of functionality in intab: * Prior to Oracle Database 10g Release 2, The contents of a row, converted to a string, cannot be longer than 255 characters. In Oracle10g Database Release 2, up to 32767 characters are supported. * Datatypes of columns must be string, number or date -- or be of a type that can implicitly converted to one of these types. * Does not support table or column names that contain double quotes. */
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday May 11, 2017

Statement 1

CREATE OR REPLACE PROCEDURE intab ( 
   table_in IN VARCHAR2 
 , where_in IN VARCHAR2 DEFAULT NULL 
 , colname_like_in IN VARCHAR2 := '%' 
) 
AUTHID CURRENT_USER 
IS 
   -- Avoid repetitive "maximum size" declarations for VARCHAR2 variables. 
   SUBTYPE max_varchar2_t IS VARCHAR2 (32767); 
 
   -- Minimize size of a string column. 
   c_min_length   CONSTANT PLS_INTEGER    := 10; 
 
   -- Collection to hold the column information for this table. 
   TYPE columns_tt IS TABLE OF all_tab_columns%ROWTYPE 
      INDEX BY PLS_INTEGER; 
 
   g_columns               columns_tt; 
   -- 
   -- Open a cursor for use by DBMS_SQL subprograms throughout this procedure. 
   g_cursor                INTEGER        := DBMS_SQL.open_cursor; 
   -- 
   -- The constructed select statement 
   g_query                 max_varchar2_t; 
   -- Formatting and SELECT elements used throughout the program. 
   g_header                max_varchar2_t; 
   g_select_list           max_varchar2_t; 
   g_row_line_length       INTEGER        := 0; 
 
   /* Utility functions that determine the "family" of the column datatype. 
      They do NOT comprehensively cover the datatypes supported by Oracle. 
     You will need to expand on these programs if you want your version of 
     intab to support a wider range of datatypes. 
   */ 
   FUNCTION is_string (row_in IN INTEGER) 
      RETURN BOOLEAN 
   IS 
   BEGIN 
      RETURN (g_columns (row_in).data_type IN ('CHAR', 'VARCHAR2', 'VARCHAR') 
             ); 
   END; 
 
   FUNCTION is_number (row_in IN INTEGER) 
      RETURN BOOLEAN 
   IS 
   BEGIN 
      RETURN (g_columns (row_in).data_type IN ('FLOAT', 'INTEGER', 'NUMBER') 
             ); 
   END; 
 
   FUNCTION is_date (row_in IN INTEGER) 
      RETURN BOOLEAN 
   IS 
   BEGIN 
      RETURN (g_columns (row_in).data_type IN ('DATE', 'TIMESTAMP')); 
   END; 
 
   PROCEDURE load_column_information 
   IS 
      l_dot_location   PLS_INTEGER; 
      l_owner          VARCHAR2 (100); 
      l_table          VARCHAR2 (100); 
      l_index          PLS_INTEGER; 
      -- 
      no_such_table    EXCEPTION; 
      PRAGMA EXCEPTION_INIT (no_such_table, -942); 
   BEGIN 
      -- Separate the schema and table names, if both are present. 
      l_dot_location := INSTR (table_in, '.'); 
 
      IF l_dot_location > 0 
      THEN 
         l_owner := SUBSTR (table_in, 1, l_dot_location - 1); 
         l_table := SUBSTR (table_in, l_dot_location + 1); 
      ELSE 
         l_owner := USER; 
         l_table := table_in; 
      END IF; 
 
      -- Retrieve all the column information into a collection of records. 
      SELECT * 
      BULK COLLECT INTO g_columns 
        FROM all_tab_columns 
       WHERE owner = l_owner 
         AND table_name = l_table 
         AND column_name LIKE NVL (colname_like_in, '%'); 
 
      l_index := g_columns.FIRST; 
 
      IF l_index IS NULL 
      THEN 
         RAISE no_such_table; 
      ELSE 
           /* Add each column to the select list, calculate the length needed 
              to display each column, and also come up with the total line length. 
           Again, please note that the datatype support here is quite limited. 
         */ 
         WHILE (l_index IS NOT NULL) 
         LOOP 
            IF g_select_list IS NULL 
            THEN 
               g_select_list := g_columns (l_index).column_name; 
            ELSE 
               g_select_list := 
                     g_select_list || ', ' || g_columns (l_index).column_name; 
            END IF; 
 
            IF is_string (l_index) 
            THEN 
               g_columns (l_index).data_length := 
                  GREATEST (LEAST (g_columns (l_index).data_length 
                                 , c_min_length 
                                  ) 
                          , LENGTH (g_columns (l_index).column_name) 
                           ); 
            ELSIF is_date (l_index) 
            THEN 
               g_columns (l_index).data_length := 
                  GREATEST (c_min_length 
                          , LENGTH (g_columns (l_index).column_name) 
                           ); 
            ELSIF is_number (l_index) 
            THEN 
               g_columns (l_index).data_length := 
                  GREATEST (NVL (g_columns (l_index).data_precision, 38) 
                          , LENGTH (g_columns (l_index).column_name) 
                           ); 
            END IF; 
 
            g_row_line_length := 
                       g_row_line_length + g_columns (l_index).data_length + 1; 
               -- 
            -- Construct column header line incrementally. 
            g_header := 
                  g_header 
               || ' ' 
               || RPAD (g_columns (l_index).column_name 
                      , g_columns (l_index).data_length 
                       ); 
            l_index := g_columns.NEXT (l_index); 
         END LOOP; 
      END IF; 
   END load_column_information; 
 
   PROCEDURE construct_and_parse_query 
   IS 
      l_where_clause   max_varchar2_t := LTRIM (UPPER (where_in)); 
   BEGIN 
      -- Construct a where clause if a value was specified. 
      IF l_where_clause IS NOT NULL 
      THEN 
         -- 
         IF (    l_where_clause NOT LIKE 'GROUP BY%' 
             AND l_where_clause NOT LIKE 'ORDER BY%' 
            ) 
         THEN 
            l_where_clause := 'WHERE ' || LTRIM (l_where_clause, 'WHERE'); 
         END IF; 
      END IF; 
 
      -- Assign the dynamic string to a local variable so that it can be 
      -- easily used to report an error. 
      g_query := 
            'SELECT ' 
         || g_select_list 
         || '  FROM ' 
         || table_in 
         || ' ' 
         || l_where_clause; 
      /* 
        DBMS_SQL.PARSE 
 
         Parse the SELECT statement; it is a very generic one, combining 
         the list of columns drawn from ALL_TAB_COLUMNS, the table name 
         provided by the user, and the optional where clause. 
 
         Use the DBMS_SQL.NATIVE constant to indicate that DBMS_SQL should 
         use the native SQL parser for the current version of Oracle 
         to parse the statement. 
       */ 
      DBMS_SQL.parse (g_cursor, g_query, DBMS_SQL.native); 
   EXCEPTION 
      WHEN OTHERS 
      THEN 
         DBMS_OUTPUT.put_line ('Error parsing query:'); 
         DBMS_OUTPUT.put_line (g_query); 
         RAISE; 
   END construct_and_parse_query; 
 
   PROCEDURE define_columns_and_execute 
   IS 
      l_index      PLS_INTEGER; 
      l_feedback   PLS_INTEGER; 
   BEGIN 
      /* 
        DBMS_SQL.DEFINE_COLUMN 
 
         Before executing the query, I need to tell DBMS_SQL the datatype 
         of each the columns being selected in the query. I simply pass 
         a literal of the appropriate type to an overloading of 
         DBMS_SQL.DEFINE_COLUMN. With string types, I need to also specify 
         the maximum length of the value. 
      */ 
      l_index := g_columns.FIRST; 
 
      WHILE (l_index IS NOT NULL) 
      LOOP 
         IF is_string (l_index) 
         THEN 
            DBMS_SQL.define_column (g_cursor 
                                  , l_index 
                                  , 'a' 
                                  , g_columns (l_index).data_length 
                                   ); 
         ELSIF is_number (l_index) 
         THEN 
            DBMS_SQL.define_column (g_cursor, l_index, 1); 
         ELSIF is_date (l_index) 
         THEN 
            DBMS_SQL.define_column (g_cursor, l_index, SYSDATE); 
         END IF; 
 
         l_index := g_columns.NEXT (l_index); 
      END LOOP; 
 
      l_feedback := DBMS_SQL.EXECUTE (g_cursor); 
   END define_columns_and_execute; 
 
   PROCEDURE build_and_display_output 
   IS 
      -- Used to hold the retrieved column values. 
      l_string_value     VARCHAR2 (2000); 
      l_number_value     NUMBER; 
      l_date_value       DATE; 
      -- 
      l_feedback         INTEGER; 
      l_index            PLS_INTEGER; 
      l_one_row_string   max_varchar2_t; 
 
      -- Formatting for the output of the header information 
      PROCEDURE display_header 
      IS 
         l_border   max_varchar2_t := RPAD ('-', g_row_line_length, '-'); 
 
         FUNCTION centered_string (string_in IN VARCHAR2, length_in IN INTEGER) 
            RETURN VARCHAR2 
         IS 
            len_string   INTEGER := LENGTH (string_in); 
         BEGIN 
            IF    len_string IS NULL 
               OR length_in <= 0 
            THEN 
               RETURN NULL; 
            ELSE 
               RETURN    RPAD (' ', (length_in - len_string) / 2 - 1) 
                      || LTRIM (RTRIM (string_in)); 
            END IF; 
         END centered_string; 
      BEGIN 
         DBMS_OUTPUT.put_line (l_border); 
         DBMS_OUTPUT.put_line (centered_string ('Contents of ' || table_in 
                                              , g_row_line_length 
                                               ) 
                              ); 
         DBMS_OUTPUT.put_line (l_border); 
         DBMS_OUTPUT.put_line (g_header); 
         DBMS_OUTPUT.put_line (l_border); 
      END display_header; 
   BEGIN 
      display_header; 
 
      -- Fetch one row at a time, until the last has been fetched. 
      LOOP 
         /* 
         DBMS_SQL.FETCH_ROWS 
 
           Fetch a row, and return the numbers of rows fetched. 
           When 0, we are done. 
         */ 
         l_feedback := DBMS_SQL.fetch_rows (g_cursor); 
         EXIT WHEN l_feedback = 0; 
         -- 
         l_one_row_string := NULL; 
         l_index := g_columns.FIRST; 
 
         WHILE (l_index IS NOT NULL) 
         LOOP 
            /* 
            DBMS_SQL.COLUMN_VALUE 
 
               Retrieve each column value in the current row, 
               deposit it into a variable of the appropriate type, 
               then convert to a string and concatenate to the 
               full line variable. 
            */ 
            IF is_string (l_index) 
            THEN 
               DBMS_SQL.COLUMN_VALUE (g_cursor, l_index, l_string_value); 
            ELSIF is_number (l_index) 
            THEN 
               DBMS_SQL.COLUMN_VALUE (g_cursor, l_index, l_number_value); 
               l_string_value := TO_CHAR (l_number_value); 
            ELSIF is_date (l_index) 
            THEN 
               DBMS_SQL.COLUMN_VALUE (g_cursor, l_index, l_date_value); 
               l_string_value := TO_CHAR (l_date_value); 
            END IF; 
 
            l_one_row_string := 
                  l_one_row_string 
               || ' ' 
               || RPAD (NVL (l_string_value, ' ') 
                      , g_columns (l_index).data_length 
                       ); 
            l_index := g_columns.NEXT (l_index); 
         END LOOP; 
 
         DBMS_OUTPUT.put_line (l_one_row_string); 
      END LOOP; 
   END build_and_display_output; 
 
   PROCEDURE cleanup 
   IS 
   BEGIN 
      /* 
        DBMS_SQL.CLOSE_CURSOR 
 
       Make sure to close the cursor on both successful completion 
         and when an error is raised. 
      */ 
      -- De Meern (Patrick) Dec 2006: don't assume cursor is open! 
      IF DBMS_SQL.is_open (g_cursor) 
      THEN 
         DBMS_SQL.close_cursor (g_cursor); 
      END IF; 
   END cleanup; 
BEGIN 
   /* 
      I keep the executable section small and very readable by creating 
      a series of local subprograms to perform the tasks required. 
   */ 
   load_column_information; 
   construct_and_parse_query; 
   define_columns_and_execute; 
   build_and_display_output; 
   cleanup; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (   'Dynamic Query Error: ' 
                            || DBMS_UTILITY.format_error_stack 
                           ); 
      /* Note: backtrace is only available in Oracle Database 10g and above. */ 
      DBMS_OUTPUT.put_line (   'Error backtrace: ' 
                            || DBMS_UTILITY.format_error_backtrace 
                           ); 
      DBMS_OUTPUT.put_line (g_query); 
      cleanup; 
      RAISE; 
END intab;

Procedure created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database