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
Using DDL (Create table) script in OCI o/p Stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Another issue in the same problem
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
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
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Re: Another issue in the same problem
Next time, when you come back with some other issue - start some other thread. Please.linit wrote:I am back with some other issue.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers