This content has been marked as final. Show 3 replies
840386 wrote: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.
I am trying to execute several sql statements, that have the same format but different values. Does this
mean I need a bind variable?
ieIn 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 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
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.
SELECT AVG (sal) INTO avg_salary FROM scott.emp;
When I try to put the values into a varaiable (date, varchar2 or number) I am getting a conversionPost 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*1Assuming 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 dualAgain, 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:
Output (when run on March 13, 2011):
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; /
13-Jan-2011 = v