Bulk load

47shailesh · Post by **47shailesh** » Fri Apr 13, 2007 10:24 am

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

DSguru2B · Post by **DSguru2B** » Fri Apr 13, 2007 10:35 am

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.

chulett · Post by **chulett** » Fri Apr 13, 2007 10:35 am

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"?

47shailesh · Post by **47shailesh** » Fri Apr 13, 2007 10:42 am

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.

DSguru2B · Post by **DSguru2B** » Fri Apr 13, 2007 10:54 am

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.

47shailesh · Post by **47shailesh** » Fri Apr 13, 2007 11:09 am

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.

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Apr 13, 2007 11:27 am

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?

DSguru2B · Post by **DSguru2B** » Fri Apr 13, 2007 1:19 pm

Start over. Please supply the following:
- Source metadata
- Source sql
- Target metadata
- Target sql
- Job Design.

47shailesh · Post by **47shailesh** » Sat Apr 14, 2007 7:57 am

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

DSguru2B · Post by **DSguru2B** » Sat Apr 14, 2007 8:23 am

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?

chulett · Post by **chulett** » Sat Apr 14, 2007 9:28 am

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:

Code: Select all

AND :1 = 1

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.

47shailesh · Post by **47shailesh** » Mon Apr 16, 2007 4:22 am

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:
Code: Select all
AND :1 = 1
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.

chulett · Post by **chulett** » Mon Apr 16, 2007 7:25 am

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?

47shailesh · Post by **47shailesh** » Tue Apr 17, 2007 6:14 am

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