Home » SQL & PL/SQL » SQL & PL/SQL » error when excuting procedure
error when excuting procedure [message #391991] Sun, 15 March 2009 16:27 Go to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
I just began studying procedures and i have the following code (c below).
i am getting the following errors when i run:
sql>execute discount

error at line 1
ORA-06550: line 1; column 7
PLS-00905:object system.discount is invalid
ORA-06550: line 1; column 7
PL/SQL ignored


I copied this code from the textbook word by word and i have no idea why it does not work.
please help, thank you Smile
create or replace PROCEDURE Discount
AS 
	CURSOR c_group_discount
	IS
		select distinct s.course_no, c.description,
		from section s, enrollment e, course c
		where s.section_id = e.section_id
		and c.course_no = s.course_no
		GROUP BY s.course_no, c.description, e.section_id, s.section_id
		HAVING COUNT(*) >= 8;
	
	BEGIN 
		for r_group_discount IN c_group_discount
		LOOP
			UPDATE course
				SET cost = cost * .95
			where course_no = r_group_discount.course_no;
			DBMS_OUTPUT.PUT_LINE
				('A 5% discount has been given to'||
				r_group_discount.course_no ||' '||
				r_group_discount.description
				);
		END LOOP;
	END;
/
Re: error when excuting procedure [message #391992 is a reply to message #391991] Sun, 15 March 2009 16:32 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
above applies when i was logged in a generic SYSTEM account

after i did sql>connect student/learn
the procedure above worked fine. although there was absolutely no output like:
'A 5% discount has been given to 25'...
would anyone have any ideas why?
after all SYSTEM account has just as many priviliges if i was to choose student/learn user...

[Updated on: Sun, 15 March 2009 16:38]

Report message to a moderator

Re: error when excuting procedure [message #391993 is a reply to message #391992] Sun, 15 March 2009 16:50 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you forgot to enable output? If so, run
SET SERVEROUTPUT ON
before running the procedure.
Re: error when excuting procedure [message #391994 is a reply to message #391993] Sun, 15 March 2009 16:59 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
thank you it did produce output Smile
Re: error when excuting procedure [message #392021 is a reply to message #391994] Mon, 16 March 2009 01:16 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Received from happyjoshua777 as a private message
i wanted to ask you if you could tell me why while i was logged in under SYSTEM (default) account i was getting all these errors.
however once i did:
sql> connect student/learn
and complied and then executed that same procedure everything worked fine.

User SYSTEM doesn't own these objects (tables) so the procedure was invalid. If you aren't certain of what has happened, after you receive such a (actually any) error message, type (at the SQL*Plus prompt) SHOW ERRORS. It will tell you the reason:
SQL> connect scott/tiger@ora10
Connected.

SQL> create or replace procedure prc_will_not_compile
  2  is
  3    l_cnt number;
  4  begin
  5    select count(*)
  6      into l_cnt
  7      from table_scott_doesnt_own;
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PRC_WILL_NOT_COMPILE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
7/10     PL/SQL: ORA-00942: table or view does not exist
SQL> 
SQL> exec prc_will_not_compile
BEGIN prc_will_not_compile; END;

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


SQL>

By the way, what do you mean by saying that SYSTEM is a "default" account? Default to what? I hope you don't connect as SYSTEM and use its schema as your playground! Simply, leave SYSTEM, SYS and similar Oracle owned users alone. Create your own schema and test your skills there.
Previous Topic: SYSDATE
Next Topic: PLSQl Help
Goto Forum:
  


Current Time: Wed Dec 07 22:01:41 CST 2016

Total time taken to generate the page: 0.10427 seconds