User defined SQL

[purvimehrotra]

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.

[elavenil]

Stored procedure can be created to achieve this and the same SP can be called from a DataStage job.

HTWH.

Regards
Saravanan

[roy]

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,

[purvimehrotra]

Thansk for your reply.
However, we want to do the same without using a stored procedure as the client does nto want objects outside DS objects. So any other suggestions would help.

[ray.wurlod]

DataStage does not support dynamic metadata. Therefore the answer is no, given your client's imposed constraint.

[purvimehrotra]

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;

[elavenil]

Are you able to execute the procedure in the DB environment by using the same id that was used to run SP from DataStage?

Pls check executing the SP and tell us if there is any error.

If it yes, as of my best knowledge you should be able to execute the SP from DataStage as well.

Regards
Saravanan

[purvimehrotra]

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

[srinagesh]

Hi Puvin,

Try to add Exception block to this piece of code and try to catch the exception.

-Nagesh