12

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.
Improve this question

1 Answer 1

Reset to default
12

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:

  1. you could use DBMS_SQL and loop on the columns of the output.
  2. 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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Jerry, now you can (I believe you only need 15 rep to up-vote)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.