3 Replies Latest reply: Mar 13, 2011 3:55 PM by 843389 RSS

    PL SQL using variable in SQL statement

    843389
      I am trying to execute several sql statements, that have the same format but different values. Does this
      mean I need a bind variable?

      ie

      select TO_CHAR ( (SYSDATE - 2), 'YYYY_MM_DD') from dual
      select TO_CHAR ( (SYSDATE - 4), 'YYYY_MM_DD') from dual
      select to_char(add_months(sysdate,-2*1) from dual

      When I try to put the values into a varaiable (date, varchar2 or number) I am getting a conversion
      error.

      Can somebody show me an example of how to do something like this? Or at least point me to the correct
      part of the documentation that provides and example. Pardon my syntax as I know it is incorrect

      val :=add_months(sysdate,-2*1

      select to_char(val) from dual

      Thanks in advance to all who answer
        • 1. Re: PL SQL using variable in SQL statement
          Etbin
          Maybe
          select TO_CHAR(SYSDATE - :days_back,'YYYY_MM_DD') from dual
          select to_char(add_months(sysdate,:month_shift) from dual
          Regards

          Etbin
          • 2. Re: PL SQL using variable in SQL statement
            Frank Kulash
            Hi,
            840386 wrote:
            I am trying to execute several sql statements, that have the same format but different values. Does this
            mean I need a bind variable?
            No, you don't need a bind variable, though bind variables may be more efficient than using PL/SQL variables. I don't see where you're trying to use any varibables at all in your examples. Is it in place of the literals, such as 2 or 'YYYY_MM_DD'? You can use either bind varibales or PL/SQL variables in those places.
            ie

            select TO_CHAR ( (SYSDATE - 2), 'YYYY_MM_DD') from dual
            select TO_CHAR ( (SYSDATE - 4), 'YYYY_MM_DD') from dual
            select to_char(add_months(sysdate,-2*1) from dual
            In PL/SQL, when you have a SELECT statement, you must specify what you want to do with the results. For example, a SELECT ... INTO statement:
            SELECT  AVG (sal)
            INTO    avg_salary
            FROM    scott.emp;
            There's usually no point in SELECTing from dual in PL/SQL. It's not an error, but it's simpler just to use an assignment statement.
            When I try to put the values into a varaiable (date, varchar2 or number) I am getting a conversion
            error.
            Post a complete procedure or anonymous block, including variable declarations, that shows exactly what you're trying to do.
            >
            Can somebody show me an example of how to do something like this? Or at least point me to the correct
            part of the documentation that provides and example. Pardon my syntax as I know it is incorrect
            val :=add_months(sysdate,-2*1
            Assuming val is a DATE, that's basically correct. You have unbalanced parentheses (there's a '(', but no matching ')' ), and you need a semicolon (';') at the end of the statement. Perhaps ');' just got cut off when you were posting this.
            select to_char(val) from dual
            Again, SELECTing from dual is unnecessary, but if you had some way to catch the returned value, that would work.
            Usually, the reason why you need to call TO_CHAR is that you want a value in a particular format, which is specified in the 2nd argument to TO_CHAR. Calling TO_CHAR with only one argument is a possible mistake, but not something that would raise an error.
            Here's an example that works:
            SET     SERVEROUTPUT     ON
            
            
            DECLARE
                 d     DATE;
                 v     VARCHAR2 (30);
            BEGIN
                 d := ADD_MONTHS (SYSDATE, -2);          -- -2 = (-2*1)
                 v := TO_CHAR (d, 'DD-Mon-YYYY');
                 dbms_output.put_line (v || ' = v');
            END;
            /
            Output (when run on March 13, 2011):
            13-Jan-2011 = v
            • 3. PL SQL using variable in SQL statement
              843389
              Frank, thanks!! your solution was perfect for me