This discussion is archived
1 2 Previous Next 22 Replies Latest reply: May 17, 2013 4:56 AM by EdStevens Go to original post RSS
  • 15. Re: Date range calculation
    user11237452 Newbie
    Currently Being Moderated
    what is :the date?
  • 16. Re: Date range calculation
    jeneesh Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    How can I declare :the_date and bind with ln_date?
  • 19. Re: Date range calculation
    Smaipady Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    ''''||ln_date||''''|| is working.Any difference with bind variable?
  • 22. Re: Date range calculation
    EdStevens Guru
    Currently Being Moderated
    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

Legend

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