Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql
dynamic sql [message #217216] Thu, 01 February 2007 03:51 Go to next message
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 #217219 is a reply to message #217216] Thu, 01 February 2007 04:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot use bind variables for object names.
Re: dynamic sql [message #217225 is a reply to message #217219] Thu, 01 February 2007 04:19 Go to previous messageGo to next message
vetrivel_ts
Messages: 3
Registered: February 2007
Location: chennai
Junior Member
Then what is the option to do that?
Re: dynamic sql [message #217227 is a reply to message #217216] Thu, 01 February 2007 04:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Frank
Messages: 7880
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?
Previous Topic: problem with plsql tables
Next Topic: Store Variable in SQL
Goto Forum:
  


Current Time: Mon Dec 05 20:55:46 CST 2016

Total time taken to generate the page: 0.08643 seconds