topic Re: try to create a procedure result in complition errors in Operating System - HP-UX

try to create a procedure result in complition errors

Deanna Tran — Thu, 13 Jun 2002 16:12:25 GMT

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

Re: try to create a procedure result in complition errors

Jeanine Kone — Thu, 13 Jun 2002 16:17:13 GMT

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.

Re: try to create a procedure result in complition errors

Deanna Tran — Thu, 13 Jun 2002 16:34:14 GMT

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?

Re: try to create a procedure result in complition errors

Jeanine Kone — Thu, 13 Jun 2002 16:43:55 GMT

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.

Re: try to create a procedure result in complition errors

Deanna Tran — Thu, 13 Jun 2002 17:53:59 GMT

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

Re: try to create a procedure result in complition errors

Deanna Tran — Thu, 13 Jun 2002 18:12:09 GMT

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

Re: try to create a procedure result in complition errors

Brian Crabtree — Fri, 14 Jun 2002 03:41:21 GMT

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