how to Parameterized column name and table name inside package body oracle

Question

--PACKAGE CODE-- the subquery with min aggregate funtion does not work. it can not find table. it is not very clear to me how to paremeterized the column and table name to make it work.

CREATE OR REPLACE PACKAGE TEST_PKG IS

PROCEDURE SP_METRICS 

(

          p_ENVIRONMENT         IN  VARCHAR2,
          p_DATABASE            IN  VARCHAR2,
          p_SCHEMA              IN  VARCHAR2,
          p_TABLE_NAME          IN  VARCHAR2,
          p_DATE_COLUMN         IN  VARCHAR2,
          o_TABLE_BASELINE      OUT SYS_REFCURSOR

);
END TEST_PKG;

--PROCEDURE CODE

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS

PROCEDURE SP_METRICS 

(

          p_ENVIRONMENT         IN  VARCHAR2,
          p_DATABASE            IN  VARCHAR2,
          p_SCHEMA              IN  VARCHAR2,
          p_TABLE_NAME          IN  VARCHAR2,
          p_DATE_COLUMN         IN  VARCHAR2,
          o_TABLE_BASELINE      OUT SYS_REFCURSOR

) AS

BEGIN



OPEN   o_TABLE_BASELINE FOR 

SELECT 
      t.num_rows BASELINE_ROW_COUNT,
        NVL(max( 
    (
        SELECT
            round( (nvl(SUM(e.bytes),0) / 1048576),2)
        FROM
            dba_extents e
        WHERE
            e.segment_type LIKE '%TABLE%'
            AND e.owner = t.owner
            AND e.segment_name = t.table_name
        GROUP BY
            e.owner,e.segment_name
    )
    ),0) BASELINE_TABLE_SIZE, 
   NVL( SUM( (
        SELECT
            round( (nvl(SUM(extd.bytes),0) / 1048576),2)
        FROM
            dba_extents extd
        WHERE
            extd.segment_type like ('%INDEX%')
            AND indx.owner = extd.owner
            AND indx.index_name = extd.segment_name
            AND indx.tablespace_name = extd.tablespace_name
        GROUP BY
            extd.owner,extd.segment_name
    ) ),0) BASELINE_INDEX_SIZE,
           (SELECT MIN(p_DATE_COLUMN)FROM p_TABLE_NAME)--THIS DOES NOT WORK- SAY UNDIFINED TABLE
FROM
   all_indexes indx RIGHT JOIN
    dba_all_tables t ON indx.table_owner = t.owner AND indx.table_name = t.table_name
WHERE t.owner      = p_SCHEMA
AND   t.table_name = p_TABLE_NAME
GROUP BY
    SYSDATE,
    'EDW',
    t.owner,
    t.table_name,
   t.num_rows;

END SP_METRICS;

END TEST_PKG;

--this is how i pass the values

var c refcursor;

execute USER.TEST_PKG.SP_METRICS('DEV', 'EDW', 'DW', 'SAIL_DIM','SAIL_DATE' :c);

print c;

Answer 1

You said that this doesn't work:

(SELECT MIN(p_DATE_COLUMN)FROM p_TABLE_NAME)

True - you'll need dynamic SQL for it. Regarding code complexity, I'd suggest you to perform just this query dynamically and use the result in the procedure. Simplified:

SQL> create or replace procedure p_test (p_table_name in varchar2,
  2                                      p_date_column in varchar2)
  3  is
  4    l_min_date date;
  5  begin
  6    execute immediate 'select min(' || p_date_column || ') from ' ||
  7      dbms_assert.sql_object_name(p_table_name) into l_min_date;
  8
  9    dbms_output.put_line(l_min_date);
 10  end;
 11  /

Procedure created.

SQL> set serveroutput on
SQL>
SQL> exec p_test('emp', 'hiredate');
17.12.80

PL/SQL procedure successfully completed.

SQL>

You'd do the same with your code (l_min_date is used in line #31):

SQL> CREATE OR REPLACE PROCEDURE sp_metrics(
  2    p_environment      IN                 VARCHAR2,
  3    p_database         IN                 VARCHAR2,
  4    p_schema           IN                 VARCHAR2,
  5    p_table_name       IN                 VARCHAR2,
  6    p_date_column      IN                 VARCHAR2,
  7    o_table_baseline   OUT                SYS_REFCURSOR
  8  )
  9  AS
 10    l_min_date date;
 11  BEGIN
 12    execute immediate 'select min(' || p_date_column || ') from ' ||
 13      dbms_assert.sql_object_name(p_table_name) into l_min_date;
 14
 15    OPEN o_table_baseline FOR SELECT t.num_rows   baseline_row_count,
 16                                     nvl(MAX((SELECT round((nvl(SUM(e.bytes),0)/ 1048576),2)
 17                                              FROM dba_extents e
 18                                              WHERE e.segment_type LIKE '%TABLE%'
 19                                                    AND e.owner = t.owner
 20                                                    AND e.segment_name = t.table_name
 21                                              GROUP BY e.owner,e.segment_name
 22                                             )),0)baseline_table_size,
 23                                     nvl(SUM((SELECT round((nvl(SUM(extd.bytes),0)/ 1048576),2)
 24                                              FROM dba_extents extd
 25                                              WHERE extd.segment_type LIKE('%INDEX%')
 26                                                    AND indx.owner = extd.owner
 27                                                    AND indx.index_name = extd.segment_name
 28                                                    AND indx.tablespace_name = extd.tablespace_name
 29                                              GROUP BY extd.owner,extd.segment_name
 30                                             )),0)baseline_index_size,
 31                                     l_min_date
 32                              FROM all_indexes indx
 33                              RIGHT JOIN dba_all_tables t ON indx.table_owner = t.owner
 34                                                             AND indx.table_name = t.table_name
 35                              WHERE t.owner = p_schema
 36                                    AND t.table_name = p_table_name
 37                              GROUP BY SYSDATE,
 38                                       'EDW',
 39                                       t.owner,
 40                                       t.table_name,
 41                                       t.num_rows;
 42
 43  END sp_metrics;
 44  /

Procedure created.

SQL>