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

compliation during the creation of the procedure

SOLVED
Go to solution
Deanna Tran_1
Frequent Advisor

compliation during the creation of the procedure

this is the scrip that I used; I have analyze this over and over again..and i still can't see the pl/sql error? please help

create PROCEDURE test_update IS
numcount number:=0;
begin
numcount:=numcount+1;
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;
if numcount=1000 then
commit;
numcount:=0;
endif;
END;
7 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: compliation during the creation of the procedure

There is no loop so that this will only update those rows where empid = 1 AND because numcount is never 1000 there is no commit and therefore nothing gets done.
If it ain't broke, I can fix that.
Deanna Tran_1
Frequent Advisor

Re: compliation during the creation of the procedure

hi, I forgot this while true loop...
thing...

create PROCEDURE test_update IS
numcount number:=0;
begin
while true loop
numcount:=numcount+1;
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;
if numcount=1000 then
commit;
numcount:=0;
endif;
end loop;
END;
Brian Crabtree
Honored Contributor
Solution

Re: compliation during the creation of the procedure

1. This assumes that there are 'EMPLOYEE1', 'EMPLOYEE2', 'EMPLOYEE3', and 'EMPLOYEE' tables.

2. That each table has a "empid" up to 1000.

You might want to perform an insert statement to make sure that you have data in the for those rows, otherwise it is not going to update anything.

Also, this statment will not do anything other than build up a large amount of redolog data, as you are going through the same number of records each time. If this is something for I/O testing, you would be better running jobs in parallel, as well as defining ahead of time exactly what you are trying to tets.

Your error in the code though is the "endif;" statement. This should be "end if;". I would suggest checking the "DBA_ERRORS" view, or running "show errors" if you have errors compiling something, as this will normally show you where the problem is.

Brian
Deanna Tran_1
Frequent Advisor

Re: compliation during the creation of the procedure

hi Brian,
I had another procedure doing the insert...this is just doing the update...
the purpose for this script...is to pump up the I/O.... and i want the data to be comited
every 1000 update ?
Can you give me any more suggestion on how to improve this script?...and how to increase the I/O rate? and yes..your assumption is correct about the tables ...
Brian Crabtree
Honored Contributor

Re: compliation during the creation of the procedure

I would be tempted to put a timestamp on each run of the job.

Create a table like "jobtime" with a "jobid" (VARCHAR2) and a "jobtime" (DATE) field. Then do something like this:
--------------------------
declare
runcount number:=0;
begin
loop until numcount:=50000;
numcount:=numcount+1;
update...
if numcount=1000 then
runcount:=runcount+10;
insert to jobtime values (runcount,sysdate);
commit;
end if;
end loop;
end;
--------------------------

Since you should run the job in parallel, you would want to increment jobid in the procedure by 1. By adding 10 to the runcount though, it will make it so know which job was running, as it will always end in a 0, 1, 2, etc. This would give you a maximum of 10 jobs, however that should be more than enough to get an idea of the amount of I/O that is being done.

Brian
Deanna Tran_1
Frequent Advisor

Re: compliation during the creation of the procedure

As suggested.. to create a table name jobtime with 2 values jobid and jobtime...and jobid takes varchar(2), however i was unsuccessful to do so...what do i miss ?


SQL> create table jobtime
2 (id_job varchar(2) constraint employee_2a_k primary key
3 jobtimes (date))
4 tablespace saki_datat02;
(id_job number(26) constraint employee_2a_k primary key
*
ERROR at line 2:
ORA-00922: missing or invalid option
Deanna Tran_1
Frequent Advisor

Re: compliation during the creation of the procedure

hi Brian,
I figure it out what was the error...
there wasn't a comma to separate the two fiels...and there is no () around date...
everything is fine..now..i was able to create the table.... still testing out your suggestion for this table..thanks