Display Dynamic EXECUTE Output Within pl/sql From sqlplus

oracle, plsql, sqlplus

Solution

you will need to retrieve the result from your SELECT in order to display it. You would use the synthax `EXECUTE IMMEDIATE sql_query INTO var1, var2.. varn`. However in your case the number of columns is unknown at compile time.

There are a number of ways you could deal with this:

- you could use DBMS_SQL and loop on the columns of the output.

- you could build a column with all the results with a readable format like CSV of XML

I will demo 1:

SQL> DEFINE scheme_name=SYS
SQL> DEFINE table_name=ALL_OBJECTS
SQL> DECLARE
  2     sql_query VARCHAR2(32767);
  3     l_cursor  NUMBER := dbms_sql.open_cursor;
  4     l_dummy NUMBER;
  5     l_columns dbms_sql.desc_tab;
  6     l_value NUMBER;
  7  BEGIN
  8     sql_query := 'select ';
  9     FOR column_rec IN (SELECT column_name
 10                          FROM ALL_TAB_COLS
 11                         WHERE owner = '&scheme_name'
 12                           AND table_name = '&table_name'
 13                           AND data_type = 'NUMBER') LOOP
 14        sql_query := sql_query || 'SUM(' || column_rec.column_name
 15                     || ') "SUM(' || column_rec.column_name || ')", ';
 16     END LOOP;
 17     sql_query := substr(sql_query, 0, length(sql_query) - 2)
 18                  || ' from &scheme_name' || '.&table_name';
 19     dbms_sql.parse(l_cursor, sql_query, dbms_sql.NATIVE);
 20     dbms_sql.describe_columns(l_cursor, l_dummy, l_columns);
 21     FOR i IN 1..l_columns.count LOOP
 22        dbms_sql.define_column(l_cursor, i, l_columns(i).col_type);
 23     END LOOP;
 24     l_dummy := dbms_sql.execute_and_fetch(l_cursor, TRUE);
 25     FOR i IN 1..l_columns.count LOOP
 26        dbms_sql.column_value(l_cursor, i, l_value);
 27        dbms_output.put_line(l_columns(i).col_name ||' = '||l_value);
 28     END LOOP;
 29  END;
 30  /

SUM(DATA_OBJECT_ID) = 260692975
SUM(OBJECT_ID) = 15242783244

Problem

How to get the dynamic select results of `EXECUTE` within PL/SQL from `Oracle sqlplus`? I'm writing a simple sqlplus script to collect the sum of all `NUMBER` columns of a given table: ``` SET SERVEROUTPUT ON DECLARE CURSOR column_cur IS SELECT column_name FROM ALL_TAB_COLS WHERE owner = '&scheme_name' AND table_name = '&table_name' AND data_type = 'NUMBER'; sql_query VARCHAR2(32767); BEGIN sql_query := 'select '; FOR column_rec IN column_cur LOOP sql_query := sql_query || 'SUM(' || column_rec.column_name || ') "SUM(' || column_rec.column_name || ')", '; END LOOP; sql_query := substr(sql_query, 0, length(sql_query)-2) || -- remove trailing ', ' ' from &scheme_name' || '.&table_name'; EXECUTE IMMEDIATE sql_query; END; / ``` The dynamically generated SQL statement, when executed, gives something like: ``` SUM(X) | SUM(Y) | SUM(Z) | -------------------------- 111 | 222 | 333 | ``` However, even with `SET SERVEROUTPUT ON`, running the sqlplus script gives only: ``` PL/SQL procedure successfully completed. ```

Original source