| dynamic sql [message #217216] |
Thu, 01 February 2007 03:51  |
vetrivel_ts
Messages: 3
Registered: February 2007
Location: chennai
|
Junior Member |
|
|
Hi,
I am using this dynamic sql to delete the rows in various tables.Here 'in_table_name' and 'COL_NAME' are varchar2 type.
'in_purge_date' is date type.
'DELETE :1 WHERE :2 < :3' using in_table_name,COL_NAME, in_purge_date;
I am getting 'ORA-00903: invalid table name'.
thanks in advance
Vetri
|
|
|
|
|
|
|
|
| Re: dynamic sql [message #217227 is a reply to message #217216] |
Thu, 01 February 2007 04:32  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
v_sql := 'DELETE '||tabname||' WHERE '||colname||' < :1';
execute immediate v_sql using in_purge_data; |
|
|
|
| Re: dynamic sql [message #217228 is a reply to message #217216] |
Thu, 01 February 2007 04:36 |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
|
Also, you shouldn't be comparing a VARCHAR2 with a DATE. You should apply a conversion to either the column_name or the input date (preferably the latter).
|
|
|
|
| Re: dynamic sql [message #217270 is a reply to message #217225] |
Thu, 01 February 2007 06:57 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| vetrivel_ts wrote on Thu, 01 February 2007 11:19 | Then what is the option to do that?
|
Not use dynamic sql.
Why don't you know in advance which tables you are going to delete from?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
