User defined SQL
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 4
- Joined: Thu Dec 09, 2004 11:28 pm
User defined SQL
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.
Hi and welcome aboard ,
Since such operations might need other clauses like table space and such, usually I tend to ask the DBAs to build me a SP performing the operation to their standards and invoke it as a before or after sql command or as a user defined sql.
IHTH,
Since such operations might need other clauses like table space and such, usually I tend to ask the DBAs to build me a SP performing the operation to their standards and invoke it as a before or after sql command or as a user defined sql.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
- Participant
- Posts: 4
- Joined: Thu Dec 09, 2004 11:28 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 4
- Joined: Thu Dec 09, 2004 11:28 pm
Hi,
Thanks for your reply. I tried with stored procedure to do the same. However though the same proceudre executes from SQL Plus prompt it gives error 'ORA-06512' in the warning in datastage and does not create a the backup table. The user id that we are using to run the stored procedure from DS has been explicitly granted 'create table' permission.'
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00922: missing or invalid option
ORA-06512: at "COMET_ETL.P_CREATE_BACKUP_ACCOUNT", line 5
ORA-06512: at line 1
The procedure is as following (Country ID is the only input coloumn to the ODBC stage)
procedure p_create_backup_Account(v_countryID IN varchar2) IS
queryToExecute VARCHAR2(500):='';
BEGIN
queryToExecute:='CREATE TABLE '|| 'backup_Account_' || v_countryID || '_' || replace(sysdate,'-','_') ||' as (select * from address_type)';
EXECUTE IMMEDIATE queryToExecute;
END;
Thanks for your reply. I tried with stored procedure to do the same. However though the same proceudre executes from SQL Plus prompt it gives error 'ORA-06512' in the warning in datastage and does not create a the backup table. The user id that we are using to run the stored procedure from DS has been explicitly granted 'create table' permission.'
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00922: missing or invalid option
ORA-06512: at "COMET_ETL.P_CREATE_BACKUP_ACCOUNT", line 5
ORA-06512: at line 1
The procedure is as following (Country ID is the only input coloumn to the ODBC stage)
procedure p_create_backup_Account(v_countryID IN varchar2) IS
queryToExecute VARCHAR2(500):='';
BEGIN
queryToExecute:='CREATE TABLE '|| 'backup_Account_' || v_countryID || '_' || replace(sysdate,'-','_') ||' as (select * from address_type)';
EXECUTE IMMEDIATE queryToExecute;
END;
-
- Participant
- Posts: 4
- Joined: Thu Dec 09, 2004 11:28 pm
Quest "Are you able to execute the procedure in the DB environment by using the same id that was used to run SP from DataStage? "
yes I can run the same SP from SQL plus. It works fine there but in DS it is giving the error mentioned. Is it because execute Immediate statement. I also tried dbms_sql.parse but got the same results.
Regards
Purvi
yes I can run the same SP from SQL plus. It works fine there but in DS it is giving the error mentioned. Is it because execute Immediate statement. I also tried dbms_sql.parse but got the same results.
Regards
Purvi