How to execute a dynamic sql using a variable inside a procedure

Question

How to execute a dynamic SQL in a stored procedure which uses a variable which is defined as a first day of the month. Consider this just an example

Declare

V_frst_day date date :=to_date('01-feb-||extract(year from sysdate));
V_sql_string varchar2(1000);

Begin

V_sql_string :=q'[select '||v_frst_day ||' from dual]';

Pkg.other_procedure(
P_sql_qry => v_sql_string);

End;
/

Answer 1

Here's how.

I'm setting the environment first (date format and language, as my database speaks Croatian, not English).

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

Session altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> set serveroutput on

As I don't have your package, I'm just

• displaying string to be executed so that you could verify whether it is correct or not; that's what you should be doing every time - don't just execute it and then wonder what went wrong
• displaying the result

As v_sql_string is now correct (it executes, right?), pass it to the procedure.

SQL> declare
  2     v_frst_day    date := to_date ('01-feb-' || extract (year from sysdate), 'dd-mon-yyyy');
  3     v_sql_string  varchar2 (1000);
  4     v_result      date;
  5  begin
  6     v_sql_string := 'select ' || chr (39) || v_frst_day || chr (39) || ' from dual';
  7
  8     dbms_output.put_line (v_sql_string);
  9
 10     execute immediate v_sql_string
 11        into v_result;
 12
 13     dbms_output.put_line ('Result = ' || v_result);
 14
 15     -- pkg.other_procedure (p_sql_qry => v_sql_string);
 16  end;
 17  /
select '01.02.2024' from dual
Result = 01.02.2024

PL/SQL procedure successfully completed.

SQL>

Answer 2

You do not need dynamic SQL, if your procedure is expecting a date:

CREATE PACKAGE pkg AS
  PROCEDURE other_procedure ( p_date IN DATE );
END;
/
CREATE PACKAGE BODY pkg AS
  PROCEDURE other_procedure ( p_date IN DATE )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_date, 'YYYY-MM-DD HH24:MI:SS'));
  END;
END;
/

Then pass a date (and not a query):

DECLARE
  v_month PLS_INTEGER := 2;
BEGIN
  Pkg.other_procedure(
    p_date => ADD_MONTHS(TRUNC(SYSDATE, 'YY'), v_month - 1)
  );
END;
/

or:

DECLARE
  v_month DATE := TO_DATE(
                    '01-feb-' || EXTRACT(YEAR FROM SYSDATE),
                    'DD-MON-YYYY',
                    'NLS_DATE_LANGUAGE=English'
                  );
BEGIN
  Pkg.other_procedure(
    p_date => v_month
  );
END;
/

If you do want to use a dynamic SQL statement then use bind variables:

DECLARE
  V_first_day DATE := TO_DATE(
                        '01-feb-' || EXTRACT(YEAR FROM SYSDATE),
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE=English'
                      );
  V_sql      VARCHAR2(1000) := 'select :1 from dual';
  v_date     DATE;
BEGIN
  EXECUTE IMMEDIATE v_sql INTO v_date USING v_first_day;
  Pkg.other_procedure( p_date => v_date );
END;
/

---

If you really do want to pass a dynamic query (don't, it opens your code up to SQL injection attacks):

CREATE OR REPLACE PACKAGE pkg AS
  PROCEDURE other_procedure (
    p_sql IN VARCHAR2
  );
END;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE other_procedure (
    p_sql IN VARCHAR2
  ) IS
    v_date DATE;
  BEGIN
    EXECUTE IMMEDIATE p_sql INTO v_date;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS'));
  END;
END;
/

Then convert the date to a string using TO_CHAR and embed it into the dynamic SQL where you can convert it back to a date using TO_DATE:

DECLARE
  V_first_day DATE := TO_DATE(
                        '01-feb-' || EXTRACT(YEAR FROM SYSDATE),
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE=English'
                      );
  V_sql      VARCHAR2(1000) := q'[select TO_DATE(']'
                               || TO_CHAR(v_first_day, 'YYYY-MM-DD HH24:MI:SS')
                               || q'[', 'YYYY-MM-DD HH24:MI:SS') from dual]';
BEGIN
  Pkg.other_procedure( p_sql => v_sql );
END;
/

fiddle