11 Replies Latest reply: Dec 10, 2012 12:42 AM by JustinCave RSS

    Date  condition in execute immediate

    user588120
      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
          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
            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
              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
                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
                  Osama,
                  I got the same error
                  • 6. Re: Date  condition in execute immediate
                    user588120
                    ORA-01027: bind variables not allowed for data definition operations
                    ORA-06512: at line 6
                    • 7. Re: Date  condition in execute immediate
                      Purvesh K
                      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
                        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
                          JustinCave
                          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
                            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
                              JustinCave
                              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