Page 1 of 1
Bulk load
Posted: Fri Apr 13, 2007 10:24 am
by 47shailesh
i have designed a job in DS using 2 OCI and a transformer.
Source OCI does SELECT 1 FROM DUAL and has a dummy column named "Dummy"
Target OCI has a Insert SQL that does the bulk insert in target table.
I used Dummy column as i do no want to use OraBulk stage as it wud'nt serve my purpose i.e. i have to test the bulk insert of DS vs Oracle frm backend.
Now problem is that when i execute the job DS gives error
"ORA-01036: illegal variable name/number" any hint where i go wrong
Posted: Fri Apr 13, 2007 10:35 am
by DSguru2B
OCI stage will not allow you to perform bulk loads as this option is not there in the OCI stage. Only with the ORA Bulk stage, which leverages the database's bulk load utility.
As for your error, search on the ORA code in google.
Posted: Fri Apr 13, 2007 10:35 am
by chulett
You might want to explain how you are doing a 'bulk load' from an OCI stage. I also don't understand why the true Bulk Loader stage wouldn't "serve your purpose"?
Posted: Fri Apr 13, 2007 10:42 am
by 47shailesh
chulett wrote:You might want to explain how you are doing a 'bulk load' from an OCI stage. I also don't understand why the true Bulk Loader stage wouldn't "serve your purpose"?
sir i have a insert query in the Target OCI, that does the bulk insert.
i want to load data on condition say for 2 years only even if i have 10 years data in my DB.
when i fire that insert query in the Oracle it take 30 sec to fetch the data.
Same thing i want to check how much time the same query will take if it runs through DS.
Posted: Fri Apr 13, 2007 10:54 am
by DSguru2B
An insert is a DML statement. It will be a logged activity. A bulk load is not the same as insert.
So you want to test the inserts. Take the same sets of data and test it out. It does not depend upon how many years of data you have. It depends upon a lot of factors like record byte size, netword traffic, constraints, triggers, indices etc etc.
Posted: Fri Apr 13, 2007 11:09 am
by 47shailesh
DSguru2B wrote:An insert is a DML statement. It will be a logged activity. A bulk load is not the same as insert.
So you want to test the inserts. Take the same sets of data and test it out. It does not depend upon how many years of data you have. It depends upon a lot of factors like record byte size, netword traffic, constraints, triggers, indices etc etc.
Agreed what you said. But my prob is i using a dummy column in both the Target and Source OCI but not using it anywhere in the insert query.. that's y there is an error in Executing the job. Is there any way to avoid that.
I want the whole Insert Query to be in the target OCI rest design can be anything which you people suggest.
Posted: Fri Apr 13, 2007 11:27 am
by Krazykoolrohit
47shailesh wrote:DSguru2B wrote:An insert is a DML statement. It will be a logged activity. A bulk load is not the same as insert.
So you want to test the inserts. Take the same sets of data and test it out. It does not depend upon how many years of data you have. It depends upon a lot of factors like record byte size, netword traffic, constraints, triggers, indices etc etc.
Agreed what you said. But my prob is i using a dummy column in both the Target and Source OCI but not using it anywhere in the insert query.. that's y there is an error in Executing the job. Is there any way to avoid that.
I want the whole Insert Query to be in the target OCI rest design can be anything which you people suggest.
why not drop the dummy by adding a transformer before bulk loading?
Posted: Fri Apr 13, 2007 1:19 pm
by DSguru2B
Start over. Please supply the following:
- Source metadata
- Source sql
- Target metadata
- Target sql
- Job Design.
Posted: Sat Apr 14, 2007 7:57 am
by 47shailesh
DSguru2B wrote:Start over. Please supply the following:
- Source metadata
- Source sql
- Target metadata
- Target sql
- Job Design.
Source- ORA OCI one column -> DUMMY <type varchar>
Source sql - SELECT 1 FROM DUAL
Transformer doing no transformation
Target - ORA OCI one column -> DUMMY <type varchar>
Target SQL - Insert /*+ append*/ into <column name> from view
There is nothing more to describe in job design
Posted: Sat Apr 14, 2007 8:23 am
by DSguru2B
I bet the error message is for the target stage. What is the column you have defined in the target OCI stage? Does that table exist in the database? Does it have the column you are using? Are you using user defined sql in the target stage or generated, if generated then what is your update action?
Posted: Sat Apr 14, 2007 9:28 am
by chulett
47shailesh wrote:Target SQL - Insert /*+ append*/ into <column name> from view
There is nothing more to describe in job design
Sure there is. Can we get a little more precise in our description of the target SQL, espcially seeing as how that's where the problem lies?
You don't insert without selecting from somewhere. And you don't insert into a 'column name'. You really should have posted the
complete sql, however I would bet your problem is the fact that you haven't bound your 'dummy' column into your sql somewhere. And you
must bind them or the OCI interface gets all kinds of upset.
If your column plays no role in the DML, just add it to your where clause such that it equates to 'true'. For example, declare it as a numeric field, mark it as a Key, set it to a value of 1 in the transformer and add to your where clause this:
Which will end up as "AND 1=1" after the bind variable is... bound, satisfying the OCI stage and not affecting your query. Other than to make it work, that is.
Posted: Mon Apr 16, 2007 4:22 am
by 47shailesh
chulett wrote:47shailesh wrote:Target SQL - Insert /*+ append*/ into <column name> from view
There is nothing more to describe in job design
Sure there is. Can we get a little more precise in our description of the target SQL, espcially seeing as how that's where the problem lies?
You don't insert without selecting from somewhere. And you don't insert into a 'column name'. You really should have posted the
complete sql, however I would bet your problem is the fact that you haven't bound your 'dummy' column into your sql somewhere. And you
must bind them or the OCI interface gets all kinds of upset.
If your column plays no role in the DML, just add it to your where clause such that it equates to 'true'. For example, declare it as a numeric field, mark it as a Key, set it to a value of 1 in the transformer and add to your where clause this:
Which will end up as "AND 1=1" after the bind variable is... bound, satisfying the OCI stage and not affecting your query. Other than to make it work, that is.
i can't paste the whole sql query..
but still the query without column names look like this
INSERT /*+APPEND*/ INTO <target table name>
(<Columns>)
SELECT <Columns>
FROM (Select <columns>
FROM (select *
from<view name>
where <condition>))
group by <columns>)
DUMMY column is not used any where nor it is present in the DB.
Posted: Mon Apr 16, 2007 7:25 am
by chulett
47shailesh wrote:DUMMY column is not used any where nor it is present in the DB.
Which is - as noted - more than likely your problem. Did you even
try what I suggested?
Posted: Tue Apr 17, 2007 6:14 am
by 47shailesh
chulett wrote:47shailesh wrote:DUMMY column is not used any where nor it is present in the DB.
Which is - as noted - more than likely your problem. Did you even
try what I suggested?
thankx chulett for the previous solution, it worked unable to run the job immediatly after the reply due to some dependencies.. :D