OraFAQ Forum: Training & Certification

Home » Other » Training & Certification » 6 problems

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

6 problems [message #257509]

Wed, 08 August 2007 09:01

Moragg
Messages: 6
Registered: August 2007

Junior Member

[EDIT] I changed the code in number 6, now have only 1 error message.

I have a few problems so I'll appologise in advance for the huge post, if anyone can spare some time to have a look at even one of these problems that would be great, I have run out of ideas. Thanks very much in advance for any help anyone is willing to offer.

1. Here is my script:

declare
 	tempMembers number(5):=0;
        tempArea varchar2(40):= ('%&Area%');
 
begin
        select count(MemberID) into tempMembers from Member where address like tempArea;
        dbms_output.put_line('There are ' || tempMembers || ' members living in the ' || tempArea || ' area');
end;

My problem is that I need to prompt the user for a string value and store that in tempArea, I then need to query the database for another string, and check to see if it contains the first string, I thought that using % on either side of my first string would work, but it doesn't seem to like that the first string is contained inside a variable.

2. procedure 1:

CREATE OR REPLACE PROCEDURE OldStandardMembers(days NUMBER)
IS

members	StandardMember.MemberID%type:=0;

CURSOR getMembers
IS
	SELECT MemberID FROM StandardMember
  WHERE MemberID IN (
          SELECT MemberID FROM Subscribes 
          WHERE Subscribes.MemberID = StandardMember.MemberID and DateSubscribed = (SYSDATE - days)
          );
BEGIN
	OPEN getMembers;
		FETCH getMembers INTO members;
			WHILE getMembers%FOUND LOOP
				DBMS_OUTPUT.PUT_LINE(members);
				FETCH getMembers INTO members;
			END LOOP;
	CLOSE getMembers;
END;

It compiles and executes without any errors, but it doesn't seem to do anything, I assumed it would print out the value of the variable 'members' on each loop, but it doesn't, I have serveroutput on so I'm nto sure what's wrong.

3. The procedure (Calls a function, see below):

CREATE OR REPLACE PROCEDURE CalcSal(ID Gym.GymID%type)

IS
	total NUMBER;
  	sID Staff.SupervisorID%type;
	isSupervisor BOOLEAN:=FALSE;
	CURSOR changeSalaries
  	IS
		SELECT Salary, staffID FROM Staff WHERE GymID = ID;
BEGIN
  OPEN changeSalaries;
		WHILE changeSalaries%FOUND LOOP
			FETCH changeSalaries INTO total, sID;
				isSupervisor:= getSupervisors(sID);
				IF isSupervisor = TRUE THEN
				IF total < 35000 THEN
					UPDATE Staff SET salary = 35000 WHERE staffID = sID;
					DBMS_OUTPUT.PUT_LINE('Employee salary has been increased to 35000');
				Else
					DBMS_OUTPUT.PUT_LINE('Employee salary is already higher than 30000');
				End If;
			Else
				IF total < 30000 THEN
					UPDATE Staff SET salary = 30000 WHERE staffID = sID;
					DBMS_OUTPUT.PUT_LINE('Employee salary has been increased to 30000');
				Else
					DBMS_OUTPUT.PUT_LINE('Employee salary is already higher than 25000');
				End If;
			End IF;
		END LOOP;
	CLOSE changeSalaries;
END;

Again, this one cmpiles and executes ok but doesn't print out any of the messages and it never alters the database, I have gone over and over it and can't see what the problem is.

4. Here's the function called from the procedure above:

CREATE OR REPLACE FUNCTION getSupervisors(s Staff.SupervisorID%type)
RETURN BOOLEAN
IS
	isSupervisor BOOLEAN:=FALSE;
	tempS Staff.SupervisorID%type;
	CURSOR supervisors
	IS
		SELECT DISTINCT SupervisorID FROM Staff;
BEGIN
	OPEN supervisors;
		While supervisors%FOUND AND isSupervisor = FALSE LOOP
			FETCH supervisors INTO tempS;
			If tempS = s THEN
				isSupervisor := TRUE;
			End If;
		End Loop;
	CLOSE supervisors;
RETURN isSupervisor;
END;

This one compiles ok also.

5. This trigger compiles ok and works but I was unsure about RAISE_APPLICATION_ERROR, I wasn't sure what was meant to be at the beginning, I saw a few examples with -20000 so I used that but if someone could explain that to me I would be very grateful.

CREATE OR REPLACE TRIGGER CheckSalaryEdit
BEFORE INSERT OR UPDATE OF Salary ON Staff
FOR EACH ROW
declare
  newSalary Staff.Salary%type;

BEGIN
   If :new.Salary > 99999 THEN
      RAISE_APPLICATION_ERROR(-20000,'Salaries are 5 digits, maybe you have added an extra 0? Please try again.');
   Elsif :new.Salary > 40000 THEN
      RAISE_APPLICATION_ERROR(-20001,'No employees may be paid more than 40000.');
   Else
      DBMS_OUTPUT.PUT_LINE('No trigger Violation, Salary successfully changed');
   End If;
END;

6. Last one:

CREATE OR REPLACE TRIGGER addMembers
AFTER INSERT OR UPDATE of MemberID on Member
FOR EACH ROW
declare
 	membership char(1):=('&membership');
  newMember member.memberID%type:= new:MemberID;
  passcode number(10);
  
BEGIN
   If membership LIKE 's' then
      passcode:= ('&passcode');
      INSERT INTO StandardMember VAUES(newMember, passcode);
   Elseif membership LIKE 'p' then
     passcode:= ('&TrialPasscode');
     INSERT INTO StandardMember values(newMember, TrialPasscode);
   Else
     dbms_output.put_line(‘Please try again and enter either S or P’);
   End if;
End;

This one compiles ok but when the trigger event occurs(adding a new member to Member table) it throws up 3 errors:

insert into member values(0006, 'test name', 'test address', '11-JAN-2007')
*
ERROR at line 1:
ORA-04098: trigger '0403940.ADDMEMBERS' is invalid and failed re-validation

[Updated on: Wed, 08 August 2007 09:57] by Moderator