Passing date variable to sql dynamic statement-CodePudding

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!

CodePudding user response：

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)||'

CodePudding user response：

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;
/