E-mail result of 'select' statement having multiple rows as result set in ORACLE

Question

I am new to ORACLE PL/SQL world. I am trying to figure out a way to calculate something as below.

Let's say you have a MASTER_TABLE as below :

SELECT * FROM MASTER_TABLE;

+----------+----------+------------------+-----------------------+
|  SCHEMA  | TABLE_NM | REQUIRED_COLUMNS |     TABLE_FILTER      |
+----------+----------+------------------+-----------------------+
| USER_SCH | A        | A1,A2,A3         | EXAM_DT > SYSDATE - 1 |
| USER_SCH | B        | B1,B2            | TRUNC(SYSDATE)        |
+----------+----------+------------------+-----------------------+

I would like to generate SELECT query from above table such as below:

SELECT 'SELECT SCHEMA || '.' || TABLE_NM ||' WHERE '|| TABLE_FILTER FROM MASTER_TABLE;

Obviously, the result of above query would generate multiple select statements. Now, I want to execute all such SELECT statements and send out the resultset via e-mail.

The tricky part is, the columns mentioned in the MASTER_TABLE varies (i.e. For table 'A' there can be 3 REQUIRED_COLUMNS to be selected, For table 'B' there can be 2 REQUIRED_COLUMNS to be selected - As shown in the MASTER_TABLE)

I have the e-mail utility ready which basically takes an argument as your_message and sends it out via e-mail.

Here is what I have tried :

Created CURSOR to generate such select statements.
Tried inserting the resultset (LIST OF SELECT QUERIES) to another temp table by concatenating the columns. (i.e.

 SELECT
    'SELECT '
    || replace(required_columns, ',', '||'',''||')
    || ' AS MSG_BDY'
    || ' FROM '
    || schema
    || '.'
    || table_nm
    || ' WHERE '
    || table_filter
as my_select_stmt
FROM
    master_table;

I am stuck after this.

Can you please help me out ? or is there any approach to achieve this ?.

Note : Tables mentioned in MASTER_TABLE can have 1 or more rows.

user_9097906 · Answer

I don't have your tables so I used Scott's.

Master table:

SQL> select * From master_table;

SCHEM TABL REQUIRED_COLUMN TABLE_FILTER
----- ---- --------------- ----------------------
scott emp  ename, job, sal hiredate < sysdate - 1
scott dept dname, loc      deptno = 20

SQL>

Procedure which simulates your mailing procedure; I'll just display those values.

SQL> CREATE OR REPLACE PROCEDURE p_mail (par_result IN SYS.odcivarchar2list)
  2  AS
  3  BEGIN
  4     FOR i IN par_result.FIRST .. par_result.LAST
  5     LOOP
  6        DBMS_OUTPUT.put_line (par_result (i));
  7     END LOOP;
  8  END;
  9  /

Procedure created.

SQL>

Procedure you actually need; as you composed the select statement(s), now you only have to run them. In order to do so, use dynamic SQL (e.g. execute immediate):

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     retval  SYS.odcivarchar2list;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT    'SELECT '
  6                   || REPLACE (required_columns, ',', '||'',''||')
  7                   || ' AS MSG_BDY'
  8                   || ' FROM '
  9                   || schema
 10                   || '.'
 11                   || table_nm
 12                   || ' WHERE '
 13                   || table_filter
 14                      AS my_select_stmt
 15              FROM master_table)
 16     LOOP
 17        EXECUTE IMMEDIATE cur_r.my_select_stmt BULK COLLECT INTO retval;
 18
 19        -- you'd call your mailing procedure here
 20        p_mail (retval);
 21     END LOOP;
 22  END;
 23  /
SMITH,CLERK,920
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,10000
TURNER,SALESMAN,1500
ADAMS,CLERK,1300
JAMES,CLERK,950
FORD,ANALYST,3000
MILLER,CLERK,1300
RESEARCH,DALLAS

PL/SQL procedure successfully completed.

SQL>

[EDIT: what if you wanted to display 'null' for missing values?]

Well, that's a new moment - probably not very simple. See if this helps.

In order to help myself, I modified master_table and added ID column to uniquely identify every row. It'll be used to split required columns' list to rows, apply NVL to them, apply CAST to columns (because NVL complains if datatypes don't match), aggregate them back using listagg. As this is quite a lot to do, I'm going to create a view and use it instead of the table itself.

SQL> CREATE OR REPLACE VIEW v_master_table
  2  AS
  3       SELECT id,
  4              schema,
  5              table_nm,
  6              LISTAGG ('NVL(cast(' || col || ' as varchar2(20)), ''null'')', '||'',''||')
  7                 WITHIN GROUP (ORDER BY lvl)
  8                 required_columns,
  9              table_filter
 10         FROM (SELECT id,
 11                      schema,
 12                      table_nm,
 13                      table_filter,
 14                      COLUMN_VALUE lvl,
 15                      TRIM (REGEXP_SUBSTR (required_columns,
 16                                           '[^,]+',
 17                                           1,
 18                                           COLUMN_VALUE))
 19                         col
 20                 FROM master_table
 21                      CROSS JOIN
 22                      TABLE (
 23                         CAST (
 24                            MULTISET (
 25                                   SELECT LEVEL
 26                                     FROM DUAL
 27                               CONNECT BY LEVEL <=
 28                                               REGEXP_COUNT (required_columns,
 29                                                             ',')
 30                                             + 1) AS SYS.odcinumberlist)))
 31     GROUP BY id,
 32              schema,
 33              table_nm,
 34              table_filter;

View created.

For example, it now looks like this:

SQL> select * from v_master_table where id = 2;

 ID SCHEM TABL REQUIRED_COLUMNS                                                                      TABLE_FILTER
--- ----- ---- ------------------------------------------------------------------------------------- ------------
  2 scott dept NVL(cast(dname as varchar2(20)), 'null')||','||NVL(cast(loc as varchar2(20)), 'null') deptno = 20

SQL>

The mailing procedure remains the same, no change.

Anonymous PL/SQL block is slightly changed - I removed REPLACE you previously used as view does it now; also, source is the view, not the table.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> DECLARE
  2     retval  SYS.odcivarchar2list;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT    'SELECT '
  6                   || required_columns
  7                   || ' AS MSG_BDY'
  8                   || ' FROM '
  9                   || schema
 10                   || '.'
 11                   || table_nm
 12                   || ' WHERE '
 13                   || table_filter
 14                      AS my_select_stmt
 15              FROM v_master_table)
 16     LOOP
 17        EXECUTE IMMEDIATE cur_r.my_select_stmt BULK COLLECT INTO retval;
 18
 19        -- you'd call your mailing procedure here
 20        p_mail (retval);
 21     END LOOP;
 22  END;
 23  /
CLARK,09.06.1981,null
KING,17.11.1981,null
MILLER,23.01.1982,null
RESEARCH,DALLAS

PL/SQL procedure successfully completed.

SQL>

E-mail result of 'select' statement having multiple rows as result set in ORACLE

1 Answers