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

try to create a procedure result in complition errors

SOLVED
Go to solution
Deanna Tran
Frequent Advisor

try to create a procedure result in complition errors

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
6 REPLIES
Jeanine Kone
Trusted Contributor
Solution

Re: try to create a procedure result in complition errors

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
Frequent Advisor

Re: try to create a procedure result in complition errors

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
Trusted Contributor

Re: try to create a procedure result in complition errors

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
Frequent Advisor

Re: try to create a procedure result in complition errors

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
Frequent Advisor

Re: try to create a procedure result in complition errors

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
Honored Contributor

Re: try to create a procedure result in complition errors

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