User defined SQL
Posted: Thu Jul 28, 2005 6:23 am
Hi,
I am trying to create a backup of an existing table dynamically in datastage job.
I do this using ODBC stage for connecting to an Oracle table and in userdefined SQL i give SQL as
create table xyz_backup as (select * from XYZ);
Till this point it works. however the requirement is to create the backup table with _timestamp appended to it as XYZ_backup_timestamp.
the below PL SQL block works when I run it in SQL Plus and creates the backup table.
declare
var_time date;
var_table_name varchar2(30) := 'backup_customer_';
queryToExecute VARCHAR2(500):='';
begin
select sysdate into var_time from dual;
var_table_name := var_table_name || var_time;
var_table_name := replace(var_table_name,'-','_');
queryToExecute:='create table ' || var_table_name ||' as (select * from address_type)';
EXECUTE IMMEDIATE queryToExecute;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/
However I am not able to run the same in user defined SQL. is there a way of achieving the same in user defined SQl else what are the alternatives. I cannot invoke SQL plus as the production box does not have it.
I am trying to create a backup of an existing table dynamically in datastage job.
I do this using ODBC stage for connecting to an Oracle table and in userdefined SQL i give SQL as
create table xyz_backup as (select * from XYZ);
Till this point it works. however the requirement is to create the backup table with _timestamp appended to it as XYZ_backup_timestamp.
the below PL SQL block works when I run it in SQL Plus and creates the backup table.
declare
var_time date;
var_table_name varchar2(30) := 'backup_customer_';
queryToExecute VARCHAR2(500):='';
begin
select sysdate into var_time from dual;
var_table_name := var_table_name || var_time;
var_table_name := replace(var_table_name,'-','_');
queryToExecute:='create table ' || var_table_name ||' as (select * from address_type)';
EXECUTE IMMEDIATE queryToExecute;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/
However I am not able to run the same in user defined SQL. is there a way of achieving the same in user defined SQl else what are the alternatives. I cannot invoke SQL plus as the production box does not have it.