Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Does anyone has any script to load oracle data and do transcation

SOLVED
Go to solution
Deanna Tran_1
Frequent Advisor

Does anyone has any script to load oracle data and do transcation

currently, I have use the procedure to load up the oracle data..but it doesn' t seem to pump up lot of I/O...i am just wondering if anyone has any other idea to help me on this
8 REPLIES
Brian Crabtree
Honored Contributor
Solution

Re: Does anyone has any script to load oracle data and do transcation

SQL Loader would be a faster way to load the data, as it has a direct option. External routines can be used to load data quickly in some cases, although not always. In some of your code, I have noticed that you commit on each record. You should space your commits out so that you fill the rollback segment you are using each time. Normally commiting every 10,000 lines is the most common number, but this would need to be tuned for your application.

Brian
Jeanine Kone
Trusted Contributor

Re: Does anyone has any script to load oracle data and do transcation

I would say that if you really want to get he I/O going (and perhaps simulate a more realistic environment), you will need to insert into several tables at the same time. You may also want to throw in some longer running queries. You could kick off a few different procedures at the same time. One doing iserts, one dong updates, one runing queries, etc. This will stimulate a bit more activity then just doing one insert at a time.
Deanna Tran_1
Frequent Advisor

Re: Does anyone has any script to load oracle data and do transcation

I have tried to create a another procedure to do this but i was unable to do so...
the error message comes back as the object is already been used... or complilation errors..

if I have to spread out the commit of the datachange say for every 10,000 , can you help me to edit the code?
using the for loop?
create PROCEDURE tester1 IS
numcount number:=0;
begin
while true loop
numcount:=numcount+1;
insert into employee1 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employee2 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employeer3 values (numcount,'lkjljf','dlkjdeann',87970.80);
commit;
insert into employee values (numcount, 'dkjl','dlakdjfelsdjfe',10000);
update employee1 set salary = salary + 900
where empid = numcount;
update employee2 set salary = salary + 10000
where empid = numcount;
update employeer3 set salary = salary + 5000
where empid = numcount;
update employee set lastname = 'dkvnlkdoije'
where empid = numcount;
commit;
end loop;
END;
/
Deanna Tran_1
Frequent Advisor

Re: Does anyone has any script to load oracle data and do transcation

Hi...I was able to take your advice on creating
different procedure ....I now have one for loading and one for update ...and I am not quite sure what queries that i can do? Can you please lend me some example? In addition to that, I have still trying to figure out how to do commit every 10,000 as suggested?
Jeanine Kone
Trusted Contributor

Re: Does anyone has any script to load oracle data and do transcation

I don't have time to actually test this out right now, but for the commit I would do something like:

numcount := numcount+1
if numcount = 1000 then
commit;
numcount := 0;
endif;


For a select statement, I imagine most will be pretty quick, depending upon the size of your table. A basic one would be:

select field1, field2, field3
into var1, var2, var2
from employee
where empid = value;

Deanna Tran_1
Frequent Advisor

Re: Does anyone has any script to load oracle data and do transcation

hi jeannine,
I added your suggestion, but it failed with thisSQL> execute tester1
BEGIN tester1; END;

*
ERROR at line 1:
ORA-00001: unique constraint (TEST1.PK_EMPLOYEE) violated
ORA-06512: at "TEST1.TESTER1", line 6
ORA-06512: at line 1
code:


create PROCEDURE tester1 IS
numcount number:=0;
begin
while true loop
numcount:=numcount+1;
insert into employee1 values (numcount,'lkjljf','dlkjdeann',87970.80);
insert into employee2 values (numcount,'lkjljf','dlkjdeann',87970.80);
insert into employeer3 values (numcount,'lkjljf','dlkjdeann',87970.80);
insert into employee values (numcount, 'dkjl','dlakdjfelsdjfe',10000);
if numcount = 10000 then
commit;
numcount:=0;
end if;
end loop;
END;
/
Deanna Tran_1
Frequent Advisor

Re: Does anyone has any script to load oracle data and do transcation

I want to truncate the table when the table is fill up ...after the loading and updating ...how would I do that:
???
Can you help me on this
Brian Crabtree
Honored Contributor

Re: Does anyone has any script to load oracle data and do transcation

"truncate table ;".

To do this in a pl/sql statement, you need to add a varchar2 string in your variables and perform an "execute immediate".

Something like this:

declare
sqlstring varchar2(100);
intblname varchar2(40);
begin
intblname:='emp';
sqlstring:='truncate table '||intblname;
execute immediate sqlstring;
end;
/