Dynamic SQL Stored Procedure in Oracle

Question

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;

Oracle's procedure doesn't return result set. What you need is a pipelined function or function returning `sql_macro`. — astentx, Nov 16 '21 at 11:12

Littlefoot · Answer 1 · 2021-11-16T11:22:00.283

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>

Dynamic SQL Stored Procedure in Oracle

1 Answers1