Dynamic SQL Stored Procedure in Oracle-CodePudding

Morning All, Hopefully someone can help. I'm an MSSQL specialist and have very little experience of Oracle PL/SQL. I've been asked to write an Oracle Stored Procedure which gets specific fields from the latest Monthly audit table. This is what I've come up with but it doesn't seem to run or output anywhere.

Can someone help?

CREATE OR REPLACE PROCEDURE GetLatestMonthAuditTable
AS
BEGIN
   DECLARE
    v_lastmonth date := interval '-1' month  systimestamp;
    v_year varchar2(4) := extract(year from v_lastmonth);
    v_month varchar2(2) := extract(month from v_lastmonth);
    v_day varchar2(2) := extract(day from LAST_DAY(v_lastMonth));

    v_sql varchar2(256) := 'SELECT ACT_CODE, CHANGE_BY, CHANGE_DATE FROM AUDIT_' || v_year || v_month || v_day;

    BEGIN
        EXECUTE IMMEDIATE v_sql;
    END;
END;

CodePudding user response：

You didn't actually run it - you just created a procedure.

Could've been like this:

Sample table:

SQL> CREATE TABLE AUDIT_20211031
  2  AS
  3     SELECT 1 act_code, 'Littlefoot' change_by, SYSDATE change_date FROM DUAL;

Table created.

Procedure (it is a good habit to display statement you'll run using dbms_output.put_line; once you make sure it is OK, remove that line):

SQL> CREATE OR REPLACE PROCEDURE GetLatestMonthAuditTable
  2  AS
  3     v_lastmonth  DATE := INTERVAL '-1' MONTH   SYSTIMESTAMP;
  4     v_year       VARCHAR2 (4) := EXTRACT (YEAR FROM v_lastmonth);
  5     v_month      VARCHAR2 (2) := EXTRACT (MONTH FROM v_lastmonth);
  6     v_day        VARCHAR2 (2) := EXTRACT (DAY FROM LAST_DAY (v_lastMonth));
  7     v_sql        VARCHAR2 (200);
  8  BEGIN
  9     v_sql :=
 10           'SELECT ACT_CODE, CHANGE_BY, CHANGE_DATE FROM AUDIT_'
 11        || v_year
 12        || v_month
 13        || v_day;
 14
 15     DBMS_OUTPUT.put_line (v_sql);
 16
 17     EXECUTE IMMEDIATE v_sql;
 18  END;
 19  /

Procedure created.

Testing:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2     GetLatestMonthAuditTable;
  3  END;
  4  /
SELECT ACT_CODE, CHANGE_BY, CHANGE_DATE FROM AUDIT_20211031

PL/SQL procedure successfully completed.

SQL>

Now, your procedure doesn't do anything - it runs that select, but it isn't displayed anywhere on the screen.

If it were a function instead, you could return ref cursor and see something. For example:

SQL> CREATE OR REPLACE FUNCTION GetLatestMonthAuditTable
  2     RETURN SYS_REFCURSOR
  3  AS
  4     v_lastmonth  DATE := INTERVAL '-1' MONTH   SYSTIMESTAMP;
  5     v_year       VARCHAR2 (4) := EXTRACT (YEAR FROM v_lastmonth);
  6     v_month      VARCHAR2 (2) := EXTRACT (MONTH FROM v_lastmonth);
  7     v_day        VARCHAR2 (2) := EXTRACT (DAY FROM LAST_DAY (v_lastMonth));
  8     v_sql        VARCHAR2 (200);
  9     rc           SYS_REFCURSOR;
 10  BEGIN
 11     v_sql :=
 12           'SELECT ACT_CODE, CHANGE_BY, CHANGE_DATE FROM AUDIT_'
 13        || v_year
 14        || v_month
 15        || v_day;
 16
 17     OPEN rc FOR v_sql;
 18
 19     RETURN rc;
 20  END;
 21  /

Function created.

Testing the function:

SQL> SELECT GetLatestMonthAuditTable FROM DUAL;

GETLATESTMONTHAUDITT
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

  ACT_CODE CHANGE_BY  CHANGE_DATE
---------- ---------- -------------------
         1 Littlefoot 16.11.2021 12:14:18


SQL>

CodePudding user response：

Thank you for the guidance. I've put this together and it works fine when the table has a few records in it. Our issue is that each month the application writes to a new audit table (this is a legacy solution from the Software suppliers) which then gets unioned with all the other Audit Tables to return data surfaced through a huge view. This view grows on a monthly basis;

e.g.
SELECT {lots of fields} FROM AUDIT_20020131'
UNION
SELECT {lots of fields} FROM ...' (1 file each month for 21 years!)
UNION
SELECT {lots of fields} FROM AUDIT_20211031'

We have to provide some Compliance reporting for our internal Audit team and it takes and age to produce the monthly report, primarily because of the size of the dataset from the 'unioned' Oracle Tables.

We thought we might use the same technique that we do in MSSQL where we create a view 'on the fly' and our data read performance is v. good. But we can't seem to get this working with any kind of performance in Oracle. Any suggestions?