dynamic DDL

[jalal soud]

I would like to create a temporary table to hold data from an existing table using the following code:
declare
begin
execute immediate 'create table test_tmp as select * from test where col1 >= :create_date' USING SYSDATE ;
end;
/
when I run it using sqlplus I get the following message:
ERROR at line 1:
ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 3

Can somebody help me with it?
Thanks

[Mark Spritzler]

:create_date
It states that you can't use a bind variable in a DDL statement.
You could try the long way.
Use the create table, with the where clause being 1=2 so that it creates a table with the correct columns.
Then create a cursor for the dynamic query. You can use bind variables here.
Then loop through the cursor and insert the data into the new table.
Mark

[Robert Willems]

Because you use the EXECUTE IMMEDIATE statement you can also leave the bind variable out of it.
E.G.

declare
   create_date  DATE ;
begin
   create_date := SYSDATE ;
   execute immediate 'create table test_tmp as select * from test where col1 >= to_date( ''' || to_char( create_date, 'DD-MM-YYYY' ) || ''', ''DD-MM-YYYY'') ' ;
end;

You then just past in the date and Oracle will treat the whole as a string and parse it and you won't use any bind variables.
Regards,
Robert
[ July 11, 2003: Message edited by: Robert Willems ]