Passing date variable to sql dynamic statement

Question

I have a package where I need to do similar unions many times . This package is executed every day and I need evey day different data(depending on day). So I though I create a temp view and do operations on this view. As it it seems not possible to create view in a procedure in a static way and it is not possible to use bind variables I tried to do it in this way:

PROCEDURE CREATE_VIEW( from_date DATE) IS
sqlCommand VARCHAR2(32000);
BEGIN
    sqlCommand :=
    'CREATE VIEW TMP_HELPER_VIEW AS 
    SELECT ID, IMPORT1_ID, IMPORT2_ID, PROD_ID
    FROM 
    (
        SELECT ID,
               IMPORT1_ID,
               -1,
               PROD_ID                
          FROM TABLE1
         WHERE     
            TS >= '||from_date||'
            AND TS < ADD_MONTHS('||from_date||', 1)
        UNION
        SELECT ID,
               -1,
               IMPORT2_ID,
               PROD_ID               
          FROM TABLE2
         WHERE     
            TS >= '||from_date||'
            AND TS < ADD_MONTHS('||from_date||', 1)';
    EXECUTE IMMEDIATE  sqlCommand;       
            
        

END CREATE_VIEW;

However I'm getting here ORA-00907: missing right parenthesis . I think I'm concateing date variable in a wrong way. Can someone give me a clue how can I fix it?

Many thanks!

Do you really need a view with constant date? Looks quite strange. It can be replaced by a general view with two date columns and or condition or by pipelined function — astentx
– astentx, Commented Nov 17, 2021 at 17:07
The tables are very huge(every day come many entries). Would the performance not be faster when I choose the dates? — hajduk
– hajduk, Commented Nov 17, 2021 at 17:12
It will not be faster just because of filtering. To make it faster you need to use partitioning by date. What about hardcoded dates compared to filter by or: Oracle's optimizer is smart enough to distinguish constants, so it will not compare each null coming from the table with absent column, but just skip that table. See db<>fiddle: it accesses required partitions. — astentx
– astentx, Commented Nov 17, 2021 at 17:59
Better use UNION ALL. When you have indexes on column TS then the view should not bring any performance improvement. — Wernfried Domscheit
– Wernfried Domscheit, Commented Nov 17, 2021 at 18:54

MT0 · Accepted Answer · 2021-11-17 19:05:44Z

Use DATE literals and name the columns:

CREATE PROCEDURE CREATE_VIEW(
  from_date DATE
)
IS
BEGIN
  EXECUTE IMMEDIATE
    'CREATE OR REPLACE VIEW TMP_HELPER_VIEW (ID, IMPORT1_ID, IMPORT2_ID, PROD_ID) AS 
     SELECT ID, IMPORT1_ID, -1, PROD_ID                
     FROM TABLE1
     WHERE TS >= DATE '''||TO_CHAR(from_date, 'YYYY-MM-DD')||'''
     AND   TS < ADD_MONTHS(DATE '''||TO_CHAR(from_date, 'YYYY-MM-DD')||''', 1)
     UNION
     SELECT ID, -1, IMPORT2_ID, PROD_ID
     FROM TABLE2
     WHERE  TS >= DATE '''||TO_CHAR(from_date, 'YYYY-MM-DD')||'''
     AND    TS < ADD_MONTHS(DATE '''||TO_CHAR(from_date, 'YYYY-MM-DD')||''', 1)';
END CREATE_VIEW;
/

Or, if you want to time include a time component then use a TIMESTAMP literal:

CREATE OR REPLACE PROCEDURE CREATE_VIEW(
  from_date DATE
)
IS
BEGIN
  EXECUTE IMMEDIATE
    'CREATE OR REPLACE VIEW TMP_HELPER_VIEW (ID, IMPORT1_ID, IMPORT2_ID, PROD_ID) AS 
     SELECT ID, IMPORT1_ID, -1, PROD_ID                
     FROM TABLE1
     WHERE TS >= TIMESTAMP '''||TO_CHAR(from_date, 'YYYY-MM-DD HH24:MI:SS')||'''
     AND   TS < ADD_MONTHS(TIMESTAMP '''||TO_CHAR(from_date, 'YYYY-MM-DD HH24:MI:SS')||''', 1)
     UNION
     SELECT ID, -1, IMPORT2_ID, PROD_ID
     FROM TABLE2
     WHERE  TS >= TIMESTAMP '''||TO_CHAR(from_date, 'YYYY-MM-DD HH24:MI:SS')||'''
     AND    TS < ADD_MONTHS(TIMESTAMP '''||TO_CHAR(from_date, 'YYYY-MM-DD HH24:MI:SS')||''', 1)';
END CREATE_VIEW;
/

Robert Pastijn · Accepted Answer · 2021-11-17 18:27:35Z

1

What you can do is dump the exact command text using DBMS_OUTPUT.PUT_LINE. You will see that you will have something like this:

 WHERE TS >= 08-DEC-20

This, because the date is without quotes, is the reason for your error. You actually want the date to be quoted to look like this:

 WHERE TS >= '08-DEC-20'

So your code would need something like

 WHERE TS >= '||chr(39)||from_date||chr(39)||'

answered Nov 17, 2021 at 18:27

Robert Pastijn

615 bronze badges

4 Comments

astentx Over a year ago

It will not work for, for example, Russian locale. Guaranteed way is to use date literal date '2020-02-01', which is not culture dependent

MT0 Over a year ago

@astentx In the last part of this answer, there is an implicit cast of from_date to a string in PL/SQL to create the view and then another implicit cast of the string back to a date when the view is run. Provided the NLS_DATE_LANGUAGE and NLS_DATE_FORMAT are consistent between the view being created and being run then, regardless of locale, this will run successfully as the implicit casts in both directions use the same settings (but its not good practice). db<>fiddle shows it working (and failing).

MT0 Over a year ago

But, yes, the middle part of the answer will fail in different locales and it would be better to use a locale-independent solution (and one that does not rely on implicit casts to and from strings).

Robert Pastijn Over a year ago

I agree, better to add a 'TO_DATE' function in there but I did not want to make it more difficult to read; wanted to make clear that the single quotes were missing.

Collectives™ on Stack Overflow

Passing date variable to sql dynamic statement

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related