1 2 Previous Next 22 Replies Latest reply: May 17, 2013 6:56 AM by EdStevens Go to original post RSS
      • 15. Re: Date range calculation
        user11237452
        what is :the date?
        • 16. Re: Date range calculation
          jeneesh
          Same as shown here in the same thread previous message: {message:id=11020484}

          These bind variables (prefixed with colon) will be replaced by the values of the variables passed in USING clause
          • 17. Re: Date range calculation
            Etbin
            it's the bind variable as explained above.
            check http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01102

            Regards

            Etbin
            • 18. Re: Date range calculation
              user11237452
              How can I declare :the_date and bind with ln_date?
              • 19. Re: Date range calculation
                Smaipady
                Hi,

                I have picked your code and just made a small change. Just added (''''||ln_date||''''||) in below code. Hope this works for you.
                set serveroutput on
                Declare
                ln_Date date :='12-apr-2013';
                sql_stmt varchar2(1000);
                begin
                sql_stmt :=
                            'BEGIN '
                         || ' INSERT INTO '
                         || 'MSG_LOG'
                         || ' (SELECT * FROM '
                         || 'MSG'
                         || ' WHERE (SYSDATE - '
                         || '(TO_DATE('||''''||ln_date||''''||','||'''dd/mm/yyyy'''||'))'
                         || '-' 
                         || 20 
                         || ') >'
                         ||10    
                         || ');' 
                         || 'DELETE FROM '
                         || 'MSG'
                         || ' WHERE (SYSDATE - '
                         || '(TO_DATE('||''''||ln_date||''''||','||'''dd/mm/yyyy'''||'))'
                         || '-' 
                         || 20
                         || ') >' 
                         ||10
                         ||'; end;';
                dbms_output.put_line(sql_stmt);
                end;
                • 20. Re: Date range calculation
                  jeneesh
                  Smaipady wrote:
                  Hi,

                  I have picked your code and just made a small change. Just added (''''||ln_date||''''||) in below code. Hope this works for you.
                  Dont do that..
                  Use bind variables...
                  • 21. Re: Date range calculation
                    user11237452
                    ''''||ln_date||''''|| is working.Any difference with bind variable?
                    • 22. Re: Date range calculation
                      EdStevens
                      user11237452 wrote:
                      No, If I do not pass it to TO_DATE, it still getting 'APR' is invalid. What should be the correct parsing?
                      >
                      sql_stmt :=
                      'BEGIN INSERT INTO '
                      || 'MSG_LOG'
                      || ' (SELECT * FROM '
                      || 'MSG'
                      || ' WHERE (SYSDATE - '
                      || ln_date
                      || '-' 
                      || 20
                      || ') >'
                      ||10    
                      || '); DELETE FROM '
                      || 'MSG'
                      || ' WHERE (SYSDATE - '
                      || ln_date
                      || '-' 
                      || 20
                      || ') >' 
                      ||10
                      || '; end;';
                      Think about what you are doing here. The above statement is building a text string. And that text string itself is to be an executable SQL statement. Break your problem down to two parts. Don't try to actually execute 'sql_stmt' just yet. Just build it, then show it with dbms_output.put_line, so you can examine it and see if it is what you want. Get that down before you try to actually execute it.

                      as for the reference to ln_date, maybe something like this?
                      ..
                      ||' where (sysdate - to_date('''
                      || tochar(ln_date,'yyyymmdd')
                      || ''',''yyyymmdd'')
                      That is untested, and I'm sure you'll have to play around with the quotes. Quoting a quote is always a bit tricky. But that's the idea.
                      1 2 Previous Next