Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic SQL Method 4 Example: Get and display data in table

Steven Feuerstein-OracleApr 5 2015 — edited Apr 6 2015

Method 4 dynamic SQL means that at the time you write your code you either don't know how many and types of columns, or  you don't know how many variables need to be bound. As a result, it is just about the most complicated dynamic SQL to implement, in part because you will usually need to rely on DBMS_SQL.

I built the "intab" procedure to demonstrate the basic flow of working with DBMS_SQL to implement a relatively simple method 4 scenario: you provide the table, I look up the columns, then display the data in the specified rows. I assume there are no bind variables in the where clause and only string, date and number columns are supported. So it is far from a generic, sophisticated procedure, but you might it useful as a learning exercise.

I offer two implementations. The first will run on all versions of Oracle Database. The second requires 11.1 and higher, since it takes advantage of the very helpful DBMS_SQL.to_cursor_number function to convert from a cursor variable to a DBMS_SQL cursor. I also refactored the second implementation to minimize "global" references and instead pass data through the parameter lists of nested subprograms.

Note: neither of these procedures offer protection from SQL injection!

I hope you find this helpful.

CREATE OR REPLACE PROCEDURE intab (

   table_in IN VARCHAR2

, where_in IN VARCHAR2 DEFAULT NULL

, colname_like_in IN VARCHAR2 := '%'

)

/*

| Demonstration of method 4 dynamic SQL with DBMS_SQL:

|   Show the contents "in" a "tab"le - intab.

|   Only supports number, date, string column types.

|

| Author: Steven Feuerstein, steven.feuerstein@oracle.com

*/

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

      -- 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

   load_column_information;

   construct_and_parse_query;

   define_columns_and_execute;

   build_and_display_output;

   cleanup;

EXCEPTION

   WHEN OTHERS

   THEN     

      cleanup;

      RAISE;

END intab;

/

And now the 11.1 implementation....

CREATE OR REPLACE PROCEDURE intab (table_in          IN VARCHAR2,

                                   where_in          IN VARCHAR2 DEFAULT NULL,

                                   colname_like_in   IN VARCHAR2 := '%')

   /*

   | Demonstration of method 4 dynamic SQL with DBMS_SQL:

   |   Show the contents "in" a "tab"le - intab.

   |   Only supports number, date, string column types.

   |

   | Oracle Database 11g version utilizes DBMS_SQL.to_cursor_number

   | to greatly simplify the code.

   |

   | Author: Steven Feuerstein, steven.feuerstein@oracle.com

   */

   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;

   l_columns               columns_tt;

   -- Open a cursor for use by DBMS_SQL subprograms throughout this procedure.

   l_cursor                INTEGER;

   --

   -- Formatting and SELECT elements used throughout the program.

   l_header                max_varchar2_t;

   l_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 (columns_in IN columns_tt, row_in IN INTEGER)

      RETURN BOOLEAN

   IS

   BEGIN

      RETURN (columns_in (row_in).data_type IN ('CHAR', 'VARCHAR2', 'VARCHAR'));

   END;

   FUNCTION is_number (columns_in IN columns_tt, row_in IN INTEGER)

      RETURN BOOLEAN

   IS

   BEGIN

      RETURN (columns_in (row_in).data_type IN ('FLOAT', 'INTEGER', 'NUMBER'));

   END;

   FUNCTION is_date (columns_in IN columns_tt, row_in IN INTEGER)

      RETURN BOOLEAN

   IS

   BEGIN

      RETURN (columns_in (row_in).data_type IN ('DATE', 'TIMESTAMP'));

   END;

   PROCEDURE load_column_information (

      select_list_io   IN OUT NOCOPY VARCHAR2,

      header_io        IN OUT NOCOPY VARCHAR2,

      columns_io       IN OUT NOCOPY columns_tt)

   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 columns_io

        FROM all_tab_columns

       WHERE     owner = l_owner

             AND table_name = l_table

             AND column_name LIKE NVL (colname_like_in, '%');

      l_index := columns_io.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 select_list_io IS NULL

            THEN

               select_list_io := columns_io (l_index).column_name;

            ELSE

               select_list_io :=

                  select_list_io || ', ' || columns_io (l_index).column_name;

            END IF;

            IF is_string (columns_io, l_index)

            THEN

               columns_io (l_index).data_length :=

                  GREATEST (

                     LEAST (columns_io (l_index).data_length, c_min_length),

                     LENGTH (columns_io (l_index).column_name));

            ELSIF is_date (columns_io, l_index)

            THEN

               columns_io (l_index).data_length :=

                  GREATEST (c_min_length,

                            LENGTH (columns_io (l_index).column_name));

            ELSIF is_number (columns_io, l_index)

            THEN

               columns_io (l_index).data_length :=

                  GREATEST (NVL (columns_io (l_index).data_precision, 38),

                            LENGTH (columns_io (l_index).column_name));

            END IF;

            g_row_line_length :=

               g_row_line_length + columns_io (l_index).data_length + 1;

            --

            -- Construct column header line incrementally.

            header_io :=

                  header_io

               || ' '

               || RPAD (columns_io (l_index).column_name,

                        columns_io (l_index).data_length);

            l_index := columns_io.NEXT (l_index);

         END LOOP;

      END IF;

   END load_column_information;

   PROCEDURE report_error (text_in IN VARCHAR2, cursor_io IN OUT INTEGER)

   IS

   BEGIN

      IF DBMS_SQL.is_open (cursor_io)

      THEN

         DBMS_SQL.close_cursor (cursor_io);

      END IF;

      DBMS_OUTPUT.put_line (text_in);

      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);

   END;

   PROCEDURE construct_and_open_cursor (select_list_in   IN     VARCHAR2,

                                        cursor_out          OUT INTEGER)

   IS

      l_query          max_varchar2_t;

      l_where_clause   max_varchar2_t := LTRIM (where_in);

      l_cursor         SYS_REFCURSOR;

   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.

      l_query :=

            'SELECT '

         || select_list_in

         || '  FROM '

         || table_in

         || ' '

         || l_where_clause;

      DBMS_OUTPUT.put_line (l_querY);

      -- 11.1 DBMS_SQL enhancement: convert to cursor variable.

      OPEN l_cursor FOR l_query;

      cursor_out := DBMS_SQL.to_cursor_number (l_cursor);

   EXCEPTION

      WHEN OTHERS

      THEN

         report_error ('Error constructing and opening cursor: ' || l_query,

                       cursor_out);

         RAISE;

   END;

   PROCEDURE define_columns_and_execute (cursor_io    IN OUT INTEGER,

                                         columns_in   IN     columns_tt)

   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 := columns_in.FIRST;

      WHILE (l_index IS NOT NULL)

      LOOP

         IF is_string (columns_in, l_index)

         THEN

            DBMS_SQL.define_column (cursor_io,

                                    l_index,

                                    'a',

                                    columns_in (l_index).data_length);

         ELSIF is_number (columns_in, l_index)

         THEN

            DBMS_SQL.define_column (cursor_io, l_index, 1);

         ELSIF is_date (columns_in, l_index)

         THEN

            DBMS_SQL.define_column (cursor_io, l_index, SYSDATE);

         END IF;

         l_index := columns_in.NEXT (l_index);

      END LOOP;

   EXCEPTION

      WHEN OTHERS

      THEN

         report_error ('Error defining columns', cursor_io);

         RAISE;

   END;

   PROCEDURE build_and_display_output (header_in    IN     VARCHAR2,

                                       cursor_io    IN OUT INTEGER,

                                       columns_in   IN     columns_tt)

   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 (l_header);

         DBMS_OUTPUT.put_line (l_border);

      END display_header;

   BEGIN

      display_header;

      /*

         DBMS_SQL.FETCH_ROWS

         Fetch a row, and return the numbers of rows fetched.

         When 0, we are done.

      */

      WHILE DBMS_SQL.fetch_rows (cursor_io) > 0

      LOOP

         l_one_row_string := NULL;

Comments

Etbin

Just to move the post forward

And now the 11.1 implementation....


BEGIN

   intab('ETBIN.EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM ETBIN.EMP WHERE job = 'CLERK'

------------------------------------------------------------------

  Contents of ETBIN.EMP

------------------------------------------------------------------

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------------------------------------------------------------

7369 SMITH CLERK 7902 12/17/1980 800 20

7876 ADAMS CLERK 7788 01/12/1983 1100 20

7900 JAMES CLERK 7698 12/03/1981 950 30

7934 MILLER CLERK 7782 01/23/1982 1300 10

Statement processed.

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-00942: table or view does not exist


Regards


Etbin

Just to move the post forward

And now the 11.1 implementation....


BEGIN

   intab('ETBIN.EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM ETBIN.EMP WHERE job = 'CLERK'

------------------------------------------------------------------

  Contents of ETBIN.EMP

------------------------------------------------------------------

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------------------------------------------------------------

7369 SMITH CLERK 7902 12/17/1980 800 20

7876 ADAMS CLERK 7788 01/12/1983 1100 20

7900 JAMES CLERK 7698 12/03/1981 950 30

7934 MILLER CLERK 7782 01/23/1982 1300 10

Statement processed.

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-00942: table or view does not exist


Regards


Etbin

So....from which schema did you run intab?

Etbin

So....from which schema did you run intab?

APEX.jpg

Regards

Etbin

APEX.jpg

Regards

Etbin

OK, I am puzzled. Do you understand what is going on in your database there?

Solomon Yakobson

So....from which schema did you run intab?

I am not sure what happens in Etbin's case, but few pointers about procedure:

1. RAISE no_such_table could be misleading. Procedure mistakenly assumes no_such_table if

      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, '%');

returns no rows. It can be simply bad colname_like_in value. For example

SQL> BEGIN
  2
  3     intab('EMP',
  4
  5            where_in          => 'job = ''CLERK''',
  6
  7            colname_like_in   => '%'
  8
  9          );
10
11  END;
12  /
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO  FROM EMP WHERE job =
'CLERK'
------------------------------------------------------------------
Contents of EMP
------------------------------------------------------------------
EMPNO ENAME      JOB       MGR  HIREDATE   SAL     COMM    DEPTNO
------------------------------------------------------------------
7369  SMITH      CLERK     7902 17-DEC-80  800             20
7876  ADAMS      CLERK     7788 23-MAY-87  1100            20
7900  JAMES      CLERK     7698 03-DEC-81  950             30
7934  MILLER     CLERK     7782 23-JAN-82  1300            10

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     intab('EMPLOYEE',
  3            where_in          => 'job = ''CLERK''',
  4            colname_like_in   => 'A%'
  5          );
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.INTAB", line 87
ORA-06512: at "SCOTT.INTAB", line 303
ORA-06512: at line 2


SQL>

2. Procedure asssumes table_in is table/view and checks all_tab_columns while table_in could be a synonym, for example. Or it could be nested table/table expression. I'd parse 'SELECT * FROM ' || table_in using DBMS_SQL.PARSE, then get all column names using DBMS_SQL.DESCRIBE3 and then apply colname_like_in to get desired columns only. This will give all the info needed to construct l_query without using ALL_TAB_COLUMNS.

SY.

Thanks, Solomon. Fine ideas (especially the switch to use DESCRIBE_COLUMNS to get the column information!) and I encourage a reader to take on the challenge if they would like to turn this into an actually useful utility. I don't think it is right now. I simply use it to demonstrate some of the basic steps involved in solving a method 4 challenge.

Solomon Yakobson

Thanks, Solomon. Fine ideas (especially the switch to use DESCRIBE_COLUMNS to get the column information!) and I encourage a reader to take on the challenge if they would like to turn this into an actually useful utility. I don't think it is right now. I simply use it to demonstrate some of the basic steps involved in solving a method 4 challenge.

Hi Steven,

Although it is nice to know DBMS_SQL supports method 4 and know how to use it, I don't see where it can be used. Method 4, IMHO, is client, not server side method.

SY.

Etbin

OK, I am puzzled. Do you understand what is going on in your database there?

It's not my database, it's my APEX Tablespace (i.e. your database in a sense ) anyway:

select owner,table_name,column_name,data_type from all_tab_columns where table_name = 'EMP'

OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPE
LUTZTESTEMPEMPNONUMBER
LUTZTESTEMPENAMEVARCHAR2
LUTZTESTEMPJOBVARCHAR2
LUTZTESTEMPMGRNUMBER
LUTZTESTEMPHIREDATEDATE
LUTZTESTEMPSALNUMBER
LUTZTESTEMPCOMMNUMBER
LUTZTESTEMPDEPTNONUMBER
ETBINEMPEMPNONUMBER
ETBINEMPENAMEVARCHAR2
ETBINEMPJOBVARCHAR2
ETBINEMPMGRNUMBER
ETBINEMPHIREDATEDATE
ETBINEMPSALNUMBER
ETBINEMPCOMMNUMBER
ETBINEMPDEPTNONUMBER

      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;

         raise_application_error(-20000,'owner = ' || l_owner || '; table = ' || l_table);

      END IF;

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-20000: owner = APEX_PUBLIC_USER; table = EMP

Regards

Etbin

It's not my database, it's my APEX Tablespace (i.e. your database in a sense ) anyway:

select owner,table_name,column_name,data_type from all_tab_columns where table_name = 'EMP'

OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPE
LUTZTESTEMPEMPNONUMBER
LUTZTESTEMPENAMEVARCHAR2
LUTZTESTEMPJOBVARCHAR2
LUTZTESTEMPMGRNUMBER
LUTZTESTEMPHIREDATEDATE
LUTZTESTEMPSALNUMBER
LUTZTESTEMPCOMMNUMBER
LUTZTESTEMPDEPTNONUMBER
ETBINEMPEMPNONUMBER
ETBINEMPENAMEVARCHAR2
ETBINEMPJOBVARCHAR2
ETBINEMPMGRNUMBER
ETBINEMPHIREDATEDATE
ETBINEMPSALNUMBER
ETBINEMPCOMMNUMBER
ETBINEMPDEPTNONUMBER

      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;

         raise_application_error(-20000,'owner = ' || l_owner || '; table = ' || l_table);

      END IF;

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-20000: owner = APEX_PUBLIC_USER; table = EMP

Regards

Etbin

Ah OK, that's make sense then. As I note below, the intention of this post is not to offer a truly, broadly useful utility but a demonstrate of method 4 steps.

Hi Steven,

Although it is nice to know DBMS_SQL supports method 4 and know how to use it, I don't see where it can be used. Method 4, IMHO, is client, not server side method.

SY.

If it's one thing I've learned over the decades it's that everything ends up getting used everywhere. But you may well be right, Solomon. And backend developers everywhere will be happy to hear it! :-)

James Su

If it's one thing I've learned over the decades it's that everything ends up getting used everywhere. But you may well be right, Solomon. And backend developers everywhere will be happy to hear it! :-)

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

unknown-7404

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

James Su wrote:

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

As BluShadow said in his reply there that is a totally unnecessary 'solution' to what OP ask about.

Oracle already provides a FREE solution to OPs problem in that thread: Sql Developer.

It unloads data very nicely using SIMPLE queries. Want CSV output?

SELECT /*csv*/ empno, ename, sal from emp;

Want other formats? Sql Dev supports plenty of them:

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

Sharp eye, James. Yes, since the collection was populated using BULK COLLECT, I can simply the code by using a FOR loop instead of a WHILE loop.

James Su

James Su wrote:

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

As BluShadow said in his reply there that is a totally unnecessary 'solution' to what OP ask about.

Oracle already provides a FREE solution to OPs problem in that thread: Sql Developer.

It unloads data very nicely using SIMPLE queries. Want CSV output?

SELECT /*csv*/ empno, ename, sal from emp;

Want other formats? Sql Dev supports plenty of them:

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

PL/SQL solution is useful in some automated jobs. I tried sqldeveloper's save as CSV feature and it's kind of slow when the table is big. I have been dreaming of a native CTAS external table organization CSV feature. If we have that then home grown solution can retire.

Solomon Yakobson

PL/SQL solution is useful in some automated jobs. I tried sqldeveloper's save as CSV feature and it's kind of slow when the table is big. I have been dreaming of a native CTAS external table organization CSV feature. If we have that then home grown solution can retire.

James Su wrote:

PL/SQL solution is useful in some automated jobs.

What jobs? Where you don't know what table you want to create CSV from? Or you don't know layout of the table you want to CSV? How many applications fit in that category? There is a saying in my old country, something like "go somewhere I don't know where and bring me something I don't know what". This is pretty much what method 4 is all about. Yes, it is a must when developing tools like SQL*Plus or SQL*Developer where tool user can issue any query and tool has to execute it and display results. It is also a must in reporting tools. One could say it is needed when using "exotic" features like ODCI table interface where we execute SQL which selects unknown number of columns by passing a dynamic query to ODCI and using method 4 to get column info and define selected columns. But unknown number of columns SQL is used for nothing but reporting and pretty-much any reporting tool does it much better.

And one more thing about using PL/SQL method 4 for producing CSV. Why would we produce CSV on server side? We don't want mixing database and application server. Application which is processing CSV file on database server will be affecting performance of all other applications using same database (or databases residing on same database server) with non-database related workload.

SY.

James Su

James Su wrote:

PL/SQL solution is useful in some automated jobs.

What jobs? Where you don't know what table you want to create CSV from? Or you don't know layout of the table you want to CSV? How many applications fit in that category? There is a saying in my old country, something like "go somewhere I don't know where and bring me something I don't know what". This is pretty much what method 4 is all about. Yes, it is a must when developing tools like SQL*Plus or SQL*Developer where tool user can issue any query and tool has to execute it and display results. It is also a must in reporting tools. One could say it is needed when using "exotic" features like ODCI table interface where we execute SQL which selects unknown number of columns by passing a dynamic query to ODCI and using method 4 to get column info and define selected columns. But unknown number of columns SQL is used for nothing but reporting and pretty-much any reporting tool does it much better.

And one more thing about using PL/SQL method 4 for producing CSV. Why would we produce CSV on server side? We don't want mixing database and application server. Application which is processing CSV file on database server will be affecting performance of all other applications using same database (or databases residing on same database server) with non-database related workload.

SY.

What jobs? Jobs to exchange data with other applications. CSV files are extracted by a procedure then packed and shipped to other applications. Yes I know what tables to extract, I can write static code to select data then output to files. But when I have tens, hundreds of queries I don't want to repeat my code again and again. I would employ a procedure to do this labour for me. Why shouldn't CSV be produced on server side? Expdp creates dump files on server side. It saves the round trips between server and client.

1 - 16

Post Details

Added on Apr 5 2015
16 comments
2,032 views