This discussion is archived
11 Replies Latest reply: Dec 9, 2012 10:42 PM by Justin Cave RSS

Date  condition in execute immediate

user588120 Newbie
Currently Being Moderated
declare
dt date;

begin


select to_char(to_date('08-dec-2012'),'dd-mon-yyyy') into dt from dual;


execute immediate 'CREATE MATERIALIZED VIEW MV_test
--TABLESPACE DATA_MVtest
REFRESH COMPLETE ON DEMAND AS
SELECT *
FROM all_objects
WHERE created >='||dt;


end;


I got the error

ORA-00904: "DEC": invalid identifier
ORA-06512: at line 9




Can anybody help me to solve this error

Thanks in advance
  • 1. Re: Date  condition in execute immediate
    sb92075 Guru
    Currently Being Moderated
    user588120 wrote:
    declare
    dt date;

    begin


    select to_char(to_date('08-dec-2012'),'dd-mon-yyyy') into dt from dual;


    execute immediate 'CREATE MATERIALIZED VIEW MV_test
    --TABLESPACE DATA_MVtest
    REFRESH COMPLETE ON DEMAND AS
    SELECT *
    FROM all_objects
    WHERE created >='||dt;


    end;


    I got the error

    ORA-00904: "DEC": invalid identifier
    ORA-06512: at line 9




    Can anybody help me to solve this error

    Thanks in advance
    DDL should be accomplished via static SQL that is issued only during version upgrades.
    Since this "design" is flawed, you are facing challenges.
  • 2. Re: Date  condition in execute immediate
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    select to_char(to_date('08-dec-2012'),'dd-mon-yyyy') into dt from dual;
    its should look like
     SELECT  TO_CHAR(TO_DATE('08-DEC-12', 'DD-MON-RR'), 'DD-MON-YYYY') from dual 
  • 3. Re: Date  condition in execute immediate
    Purvesh K Guru
    Currently Being Moderated
    user588120 wrote:
    declare
    dt date;
    begin
    select to_char(to_date('08-dec-2012'),'dd-mon-yyyy') into dt from dual;
    execute immediate 'CREATE MATERIALIZED VIEW MV_test
    --TABLESPACE DATA_MVtest
    REFRESH COMPLETE ON DEMAND AS
    SELECT *
    FROM all_objects
    WHERE created >='||dt;
    end;
    You are doing un-necessary CHAR - DATE - CHAR conversion.

    To understand it, look at it this way:
    1. You have a date that is passed to TO_DATE as '08-dec-2012'.
    2. You convert it to a Date datatype using the nls_date_format formatting.
    3. You then convert it to a CHAR using the format you need it to be.

    Why do you need to do all this?

    Moreover, what you are doing after all this is incorrect. You are Comparing a Date with a String. Hence I would suggest to remove the TO_CHAR from the select and just compare date to a date.
    And you should resort to Bind variables when using Execute immediate; Although it might not be necessary considering you would be running this only once (Assumption based on DDL statement).

    Do it this way
     declare
         dt date;
      
     begin
        select to_date('08-dec-2012','dd-mon-yyyy') into dt from dual; 
         execute immediate 'CREATE  MATERIALIZED VIEW MV_test
         --TABLESPACE DATA_MVtest
         REFRESH COMPLETE  ON DEMAND AS 
         SELECT   *
           FROM   all_objects
          WHERE created >=:1' using dt;
     end;
    PS:- Creating Database Objects on the Fly is a Very bad database design and Architecture.
    You should think about doing Exception handling at least for known errors viz. Object already exists etc.
  • 4. Re: Date  condition in execute immediate
    Pavel Explorer
    Currently Being Moderated
    Hi

    may bee You really need next ?
    select to_char('08-dec-2012') into dt from dual;
    regards,
    Pavel
  • 5. Re: Date  condition in execute immediate
    user588120 Newbie
    Currently Being Moderated
    Osama,
    I got the same error
  • 6. Re: Date  condition in execute immediate
    user588120 Newbie
    Currently Being Moderated
    ORA-01027: bind variables not allowed for data definition operations
    ORA-06512: at line 6
  • 7. Re: Date  condition in execute immediate
    Purvesh K Guru
    Currently Being Moderated
    user588120 wrote:
    ORA-01027: bind variables not allowed for data definition operations
    ORA-06512: at line 6
    I am sorry. Then please remove the bind variable and append the Date variable as you were doing earlier.
  • 8. Re: Date  condition in execute immediate
    sb92075 Guru
    Currently Being Moderated
    user588120 wrote:
    Osama,
    I got the same error
    redundancy is the best way to teach idiots
    redundancy is the best way to teach idiots
    redundancy is the best way to teach idiots
    redundancy is the best way to teach idiots
    redundancy is the best way to teach idiots
    redundancy is the best way to teach idiots

    sooner or later you might learn what is Best Practice & what is foolishness.
  • 9. Re: Date  condition in execute immediate
    Justin Cave Oracle ACE
    Currently Being Moderated
    First, it seems really odd that you would want to use dynamic SQL to run DDL like this. Creating objects should be done when the application is installed. Creating objects at runtime is a poor application design.

    The simplest option that is independent of the session's NLS settings would be to use a date literal
    execute immediate 'CREATE MATERIALIZED VIEW MV_test
      --TABLESPACE DATA_MVtest
      REFRESH COMPLETE ON DEMAND 
    AS 
      SELECT *
        FROM all_objects
       WHERE created >= date ''2012-12-08''';
    Justin
  • 10. Re: Date  condition in execute immediate
    user588120 Newbie
    Currently Being Moderated
    Justin
    Thank you , that clicked

    Actually till recently i just refreshed the MV, but now a days it takes too much time , so i just tried to recreate it


    Thanks
  • 11. Re: Date  condition in execute immediate
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are you refreshing the materialized view by doing a delete and a subsequent insert (an atomic refresh)? If you can drop and re-create the materialized view, you can presumably do a non-atomic refresh which will truncate the data and do a direct-path insert. That should be just as fast as dropping and recreating the materialized view without the downside of doing DDL at runtime.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points