try to create a procedure result in complition errors

[Deanna Tran]

This is the procedure that i used before...and it works fine. Just now i have edit some line
in the code. I drop the procedure and run it again, and i can't run it....prior to do the
droping of the procedure, I have alter to add
extra datafile to the package...


create PROCEDURE tester1 IS
numcount number:= 0;
begin
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfkjebkg','dlkjglkj',5678.34,4567895);
update employee set salary = salary + 5000;
where empid = numcount;
update employee set lastname = 'dkvnlkdoije'
where empid = numcount;
commit;
end loop;
END;
/
SQL> @/u02/dtloader2.sql

Warning: Procedure created with compilation errors.

SQL> execute tester1
BEGIN tester1; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object TEST1.TESTER1 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[Jeanine Kone · Accepted solution]

I see one error off the bat which is:

"update employee set salary = salary + 5000;
where empid = numcount; "

There should not be a semicolon after the 5000.

After changing that, try again. If it still does not work, select * from user_errors and see if you can get more details on the error message.

[Deanna Tran]

hi jeannine,
after fixing that, oracle recompile without any errors. If i edit some code with in that
procedure, do i have to drop the procedure?

[Jeanine Kone]

You can do a "create or replace procedure". That will create it if it does not exist, or replace (i.e. update) it if it does.

If you have the Oracle development tools, you can use the Procedure Builder to create and edit stored procedures.

[Deanna Tran]

hi,
can i use the procedure to fill up
two different table...if so, why do i keep
having this compilation errors from oracle
create PROCEDURE tester1 IS
numcount number:=0;
begin
while true loop
numcount:=numcount+1;
insert into employee values (numcount,'dfkjebkg','dlkjglkj',5678.34,4567895);
insert into employee1 values (numcount,'lkjljf',87970.80);
update employee1 set salary = salary + 10000
where empid = numcount;
update employee set salary = salary + 5000
where empid = numcount;
update employee set lastname = 'dkvnlkdoije'
where empid = numcount;
commit;
end loop;
END;
/


this is the error
@/u02/dtloader2.sql

Warning: Procedure created with compilation errors.

SQL> execute tester1
BEGIN tester1; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object TEST1.TESTER1 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[Deanna Tran]

hi,
I was able to recompile the oracle ...
the reason it didn't compile because
when i created the table i had 4 field,
but i had only 3 field to insert and the other field were nul...after fixing it..i was able to have it up and run...
thank you for your time

[Brian Crabtree]

Deanna,

Just as an FYI, after trying to create or recompile an object, you can execute the command "show errors" immediately afterwards, and it will show you the errors associated with the command.

Also, you can query the errors from the database in the DBA_ERRORS view.

(ie: select * from dba_errors where name = ;)

Brian