| Update date field using execute immediate statement.. [message #133282] |
Thu, 18 August 2005 06:51  |
binduaghil
Messages: 5
Registered: May 2005
Location: India
|
Junior Member |
|
|
I need to update a date field dynamically .
Below is the code I have written for the same..
EXECUTE IMMEDIATE
'UPDATE Temp_Emp ' ||
' SET ' || V_Fieldname || ' = ' || D_Value ||
'WHERE Emp_Id = ' || 8447;
I am getting the following error..
ORA-00904: "AUG": invalid identifier
Pls anyone have any ideas..
Thanks,
Xyz
|
|
|
|
| Re: Update date field using execute immediate statement.. [message #133287 is a reply to message #133282] |
Thu, 18 August 2005 07:11  |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
EXECUTE IMMEDIATE
'UPDATE Temp_Emp ' ||
' SET ' || V_Fieldname || ' = to_date(''' || D_Value ||
''',''<enter the date format by which d_value is stored>'') WHERE Emp_Id = ' || 8447;
for eg;
1 declare
2 v_field_name varchar2(30) := 'hiredate';
3 v_fieldvalue date := to_date('Aug-1980','Mon-yyyy');
4 sqlstr varchar2(1000);
5 begin
6 sqlstr := 'update emp set '||v_field_name ||' = to_date('''||v_fieldvalue||
'',''dd-Mon-yyyy hh24:mi:ss'') where '|| v_field_name ||' is null';
7 execute immediate sqlstr;
8 dbms_output.put_line(sqlstr);
9 commit;
0* end;
SCOTT@ORACLE> /
update emp set hiredate = to_date('01-Aug-1980 00:00:00','dd-Mon-yyyy
hh24:mi:ss') where hiredate is null
PL/SQL procedure successfully completed.
[Updated on: Thu, 18 August 2005 07:13] Report message to a moderator |
|
|
|
| Re: Update date field using execute immediate statement.. [message #133296 is a reply to message #133282] |
Thu, 18 August 2005 07:37 |
kiran
Messages: 503
Registered: July 2000
|
Senior Member |
|
|
Date Conversion is one thing.
But the error that you have posted says that you have used an "Invalid Identifier" and not about the DATE.
Onething that i understood from the error is that your D_value variable has only value "AUG"(month) not the complete date.
Can you post the total code.
--Kiran.
|
|
|
|
| Re: Update date field using execute immediate statement.. [message #133303 is a reply to message #133282] |
Thu, 18 August 2005 07:49 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
The case is that you aren't using binding variable
and as a result of concatenation you have worng syntax for
the update statement.
See example below:
SQL> set serveroutput on
SQL> alter session set nls_date_format = 'DD-MON-YY';
Session altered.
SQL> alter session set nls_language = english;
Session altered.
SQL> declare
2 text varchar2(200) := 'update emp set hiredate = ';
3 my_date date := sysdate;
4 begin
5 dbms_output.put_line(text || my_date);
6 end;
7 /
update emp set hiredate = 18-AUG-05
PL/SQL procedure successfully completed.
SQL> declare
2 text varchar2(200) := 'update emp set hiredate = ';
3 my_date date := sysdate;
4 begin
5 execute immediate text || my_date;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00904: "AUG": invalid identifier
ORA-06512: at line 5
You have to use binding variables wherever it's allowed:
SQL> declare
2 text varchar2(200) := 'update emp set hiredate = :1';
3 my_date date := sysdate;
4 begin
5 execute immediate text using my_date;
6 end;
7 /
PL/SQL procedure successfully completed.
In your case it looks like:
EXECUTE IMMEDIATE
'UPDATE Temp_Emp ' ||
'SET ' || V_Fieldname || ' = :1 ' ||
'WHERE Emp_Id = :2' using D_Value, 8447;
Rgds.
[Updated on: Thu, 18 August 2005 07:49] Report message to a moderator |
|
|
|
| Re: Update date field using execute immediate statement.. [message #133321 is a reply to message #133303] |
Thu, 18 August 2005 09:02 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
| Quote: |
Orginally posted dmitry.nikiforov
You have to use binding variables wherever it's allowed:
|
Yeah I agree, I made cardinal error of using string literals instead of using Bind variables while writing dynamic SQL.
It should be correctly written as shown by dmitry
EXECUTE IMMEDIATE
'UPDATE Temp_Emp ' ||
'SET ' || V_Fieldname || ' = :1 ' ||
'WHERE Emp_Id = :2' using D_Value, 8447;
|
|
|
|
|
|
| Re: Update date field using execute immediate statement.. [message #217218 is a reply to message #133410] |
Thu, 01 February 2007 04:04 |
vetrivel_ts
Messages: 3
Registered: February 2007
Location: chennai
|
Junior Member |
|
|
I am using a similar query and facing the same problem.
'DELETE :1 WHERE :2 < :3' using in_table_name,COL_NAME, in_purge_date;
Here 'in_table_name' and 'COL_NAME' are varchar2 type. 'in_purge_date' is date type.
I am getting error message: ORA-00903: invalid table name.
I already tried the suggestions given in this topic.
thanks in advance
vetri
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
