topic Re: compliation during the creation of the procedure in Operating System - HP-UX

compliation during the creation of the procedure

Deanna Tran_1 — Mon, 15 Jul 2002 19:57:55 GMT

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;

Re: compliation during the creation of the procedure

A. Clay Stephenson — Mon, 15 Jul 2002 20:01:35 GMT

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.

Re: compliation during the creation of the procedure

Deanna Tran_1 — Mon, 15 Jul 2002 20:49:52 GMT

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;

Re: compliation during the creation of the procedure

Brian Crabtree — Mon, 15 Jul 2002 21:10:01 GMT

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

Re: compliation during the creation of the procedure

Deanna Tran_1 — Mon, 15 Jul 2002 22:33:11 GMT

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 ...

Re: compliation during the creation of the procedure

Brian Crabtree — Mon, 15 Jul 2002 23:51:59 GMT

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

Re: compliation during the creation of the procedure

Deanna Tran_1 — Tue, 16 Jul 2002 14:23:43 GMT

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

Re: compliation during the creation of the procedure

Deanna Tran_1 — Tue, 16 Jul 2002 15:46:44 GMT

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