Using DDL (Create table) script in OCI o/p Stage

[linit]

I am trying to create & load a table (non-exisiting) using the User-defined DDL option. Now the job get the table created but failed to insert the data into it.

I am using the following script in the DDL SQL.

CREATE TABLE #CN_R#(
CNTRY_ISO_CD VARCHAR(8) NOT NULL,
PROD_CD VARCHAR(200))

P.S CN_R is job parameter (table name).

Please let me know if you see any error in the script and how I have to change the script.

Thanks in Advance.

Linit

[ray.wurlod]

No PRIMARY KEY clause?

[chulett]

Wouldn't be a problem, per se.

Make sure both the DDL and the DML statements use the same table name job parameter. You'll also need a DROP TABLE command or this will only work once - after that you'll get an 'Object already exists' error. Also, make sure the stage generates the DDL and not you, otherwise you stand a chance of it not matching up with your incoming data.

It would also help if you explained what 'failed to insert data' means - any specific warnings or errors you'd care to share with us?

[linit]

Thanks a lot guys, I think it got resolved. As Craig tole, I had problem in the DML statements.

[linit]

Hi,

I am back with some other issue.

Issue:
1. How can I use the Transformer variable in Table name of the Target OCI Stage?

2. How can I do a multiplication operation in the Userdefined SQL.

E.g. CMG_LIST_(6-1) = CMG_LIST_5
CMG_LIST_(6-2) = CMG_LIST_4

Here 6 will be given as Job parameter. So CMG_LIST_(#JobParameter#-1)

Advance Thanks,
Linit

[kumar_s]

1. You cannot do. Stage variable are limited to stages.
However, if you can pass it to a column value and pass the column value as an input to a stored procedure which creates the table.
2. If you intend to get CMG_LIST_4, pre process the Job parameter as required and pass it as just CMG_LIST_#JobParameter#. Or you can handle it in a stored procedure.

[chulett]

I am back with some other issue.

Next time, when you come back with some other issue - start some other thread. Please.