Home » SQL & PL/SQL » SQL & PL/SQL » Update date field using execute immediate statement..
Update date field using execute immediate statement.. [message #133282] Thu, 18 August 2005 06:51 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #133410 is a reply to message #133321] Thu, 18 August 2005 23:35 Go to previous messageGo to next message
binduaghil
Messages: 5
Registered: May 2005
Location: India
Junior Member
Thanks a lot..
I was able to correct the error with all your help.

Thanks,
XYz.
Re: Update date field using execute immediate statement.. [message #217218 is a reply to message #133410] Thu, 01 February 2007 04:04 Go to previous messageGo to next message
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
Re: Update date field using execute immediate statement.. [message #217220 is a reply to message #217218] Thu, 01 February 2007 04:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You already created a new topic for this.
Previous Topic: how insert the data in procedure in run time?
Next Topic: Problem while accessing java code from oracle
Goto Forum:
  


Current Time: Sun Dec 04 20:48:03 CST 2016

Total time taken to generate the page: 0.08047 seconds