Dynamic Sql Variable Date Error

[rmtiptoes]

I am trying to create a dynamic sql process based upon the following update code:

UPDATE WORKORDER
SET STATUS = 'COMP'
WHERE REPORTDATE >= TO_DATE('08/01/2006','mm/dd/yyyy')
AND STATUS = 'INPRG'
AND ACTFINISH IS NOT NULL
AND WONUM NOT LIKE '7%'

Which works fine. The dynamic sql code is as follows:

declare
WTABLE varchar2(64) := 'WORKORDER';
WCOL varchar2(64) := 'STATUS';
IVALUE varchar2(64) := 'INPRG';
CVALUE varchar2(64) := 'COMP';
ADATE DATE := 'to_date(ACTFINISH)';
dyn_sql varchar2(64);
WONUM varchar2(64) := '7%';
begin
dyn_sql := 'update '||WTABLE||' set '||WCOL||' = '''||IVALUE|| ''' WHERE '||ADATE||' IS NULL
AND '||WCOL||' = '''||CVALUE|| '''';
dbms_output.put_line(dyn_sql);
execute immediate dyn_sql;
end;
/

When I run in sql I get the following error:

ADATE DATE := 'to_date(ACTFINISH)';
*
ERROR at line 6:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6

I have tried to reformat the date field and nothing works. Can anyone offer any suggestions?

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.

 

[carp]

I got a different error, but suspect the source is the same: dyn_sql is too small. Also, ADATE should actually be a VARCHAR2, not a date (as an aside, the code would be more efficient if ADATE is initialized as 'ACTFINISH' - in order for to_date(ACTFINISH) to be null, ACTFINISH must be null).
I was able to get correct results with the following:

declare
   WTABLE varchar2(64) := 'WORKORDER';
   WCOL varchar2(64) := 'STATUS';
   IVALUE varchar2(64) := 'INPRG';
   CVALUE varchar2(64) := 'COMP';
   ADATE VARCHAR2(20) :='to_date(ACTFINISH)';
   dyn_sql varchar2(200);
   WONUM varchar2(64) := '7%';
begin
   dyn_sql := 'update '||WTABLE||' set '||WCOL||'  = '''||IVALUE|| ''' WHERE '||ADATE||' IS NULL 
AND '||WCOL||' = '''||CVALUE||'''';
   dbms_output.put_line(dyn_sql);
   execute immediate dyn_sql;
end;
/

 

[SantaMufasa]

TipToes,

First, if you are assigning literal values to variables, you don't need the variables to compose the dynamic SQL...you can just use the literals.

Next, in the WORKORDER table, do you have a column named, "ADATE" or a column named "ACTFINISH"?

Third, your code, "ADATE DATE := 'to_date(ACTFINISH)';" is attempting to assign a string literal (" 'to_date(ACTFINISH)' " to a DATE column...that's a no-go. If you remove the single quotes, then your code is attempting to assign a TO_DATE result to a DATE column, which would be fine if you composed the TO_DATE function properly (which you did not).

And perhaps the overriding question: "Why not just issue the UPDATE statement explicitly in either SQL*Plus, or explicitly in your PL/SQL block? Why are you doing an EXECUTE IMMEDIATE?"

Let's resove this much first, then we can bite off the next chunck of issues.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[rmtiptoes]

Thank you both!!! Been working on this all NITE!!! I actually tried all of those but in the wrong combinations. A million thanks!!!

Homer: But every time I learn something new, it pushes out something old! Remember that time I took a home wine-making course and forgot how to drive?
Marge Simpson: That's because you were drunk!
Homer: And how.