Home » Other » Training & Certification » 6 problems
6 problems [message #257509] Wed, 08 August 2007 09:01 Go to next message
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

Report message to a moderator

Re: 6 problems [message #257519 is a reply to message #257509] Wed, 08 August 2007 09:24 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Did you type

set serveroutput on

in sql plus befor running your queries. Nothing will display unless you do.


type in the following command and paste the results


desc StandardMember
Re: 6 problems [message #257524 is a reply to message #257519] Wed, 08 August 2007 09:31 Go to previous messageGo to next message
Moragg
Messages: 6
Registered: August 2007
Junior Member
Yes I have serveroutput on, only some of them don't display anything, other do.

StandardMember has two attributes,
MemberID, type Char(4) and can not be null
HealthSpaTrialPass, type Number(10)
Re: 6 problems [message #257525 is a reply to message #257509] Wed, 08 August 2007 09:34 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I think the fundamental problem is that you don't have much idea what you are doing with PL/SQL and need to go on a training course or spend a lot of time studying the manuals.

Here is a random selection of the umpteen problems.

2) Why should it display the output ? All you have done is fetched each row from the table and then done nothing with the result. You would need to insert the results into a table or use DBMS_OUTPUT to display them (use "set serveroutput on" to see the output).

3) What will changeSalaries%FOUND be set to ? You have not yet fetched from the cursor. It does not get set until after you have fetched. Since getting the semantics of the loop right is such a problem, I suggest you change it to a simpler "cursor for" loop

for v_sal in changeSalaries loop
...
end loop;

4) Ditto.

6) This is complete rubbish. You can't prompt for input in a trigger. A trigger does not accept SQL*Plus style substitution variables.

The error is coming from:

membership := ('&membership');

This will attempt to set membership to the literal string '&membership', which obviously exceeds 1 character.

[Updated on: Wed, 08 August 2007 09:42]

Report message to a moderator

Re: 6 problems [message #257533 is a reply to message #257509] Wed, 08 August 2007 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without CREATE TABLE and INSERT scripts, it's tricky to provide accurate information.

1) Why the brackets around the Varchar2 assignation? I've never seen that syntax before

YOur logic works:
create table like_test   (col_1  varchar2(10));

insert into like_test values ('FISH');
insert into like_test values ('DISH');
insert into like_test values ('ISH');
insert into like_test values ('FIST');
insert into like_test values ('FISHY');

declare
  v_like   varchar2(10) := '%ISH%';
  v_count  number;
begin
  select count(*)
  into   v_count
  from   like_test
  where col_1 like v_like;
  
  dbms_output.put_line('Rows wit value like '||v_like||' = '||v_count);
end;

drop table like_test succeeded.
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
anonymous block completed
Rows wit value like %ISH% = 4
Without more information, I don't know what you're doing wrong.

2) I think that the problem is that your query is returning no rows, probably because of this
DateSubscribed = (SYSDATE - days));

Sysdate includes a fractional element, representing the current time. Try using TRUNC(sysdate).

3) You're not doing a FETCH after the OPEN - therefore %FOUND is not yet set.

4) Same as 3

5) Read the documentation

6) As the squid-lipped poster pointed out - triggers just don't work like this.
Re: 6 problems [message #257549 is a reply to message #257533] Wed, 08 August 2007 10:50 Go to previous messageGo to next message
Moragg
Messages: 6
Registered: August 2007
Junior Member
Thank you for being so polite JRowbottom, I'm not sure Cthulhu's aggresive post was really necessary as I am just trying to learn.

I'm not sure how on earth I managed to miss the mistakes with the loops in 3 and 4, but thank you for pointing that out for me.

As for brackets around the Varchar2 assignation, I was just trying different variations and forgot to remove them.

For 2 I have tried TRUNC(sysdate) and it still comes up with just procedure successfully completed when I run it, it doesn't display the value of members on each loop.

Re: 6 problems [message #257551 is a reply to message #257509] Wed, 08 August 2007 11:00 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Did you correct the problem with the syntax of the loop ? You have to test the %FOUND condition after the fetch, not before. Alternatively recode it to:

for rec_member in (SELECT MemberID FROM StandardMember
                    WHERE EXISTS (
                   SELECT 1 FROM Subscribes 
                    WHERE Subscribes.MemberID = StandardMember.MemberID and TRUNC(subscribes.DateSubscribed) = (trunc(SYSDATE) - days)
          ) loop
    DBMS_oUTPUT.PUT_LINE(REC_MEMBER.MemberID);
end loop;
Re: 6 problems [message #257567 is a reply to message #257533] Wed, 08 August 2007 12:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Wed, 08 August 2007 11:07

1) Why the brackets around the Varchar2 assignation? I've never seen that syntax before



Just when you thought you pretty much knew all the words in the language, an oddball words comes up and actually turns out to be a real word.
Re: 6 problems [message #257710 is a reply to message #257549] Thu, 09 August 2007 01:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Moragg wrote on Wed, 08 August 2007 17:50
For 2 I have tried TRUNC(sysdate) and it still comes up with just procedure successfully completed when I run it, it doesn't display the value of members on each loop.

This "procedure successfully completed" message is just an indication that there were no exceptions thrown. The "cursor for loop" by Cthulhu is a good alternative. Note that he put TRUNC() on both sides of the operator.

MHE
Re: 6 problems [message #257819 is a reply to message #257710] Thu, 09 August 2007 04:49 Go to previous messageGo to next message
Moragg
Messages: 6
Registered: August 2007
Junior Member
I have changed my loop to what Cthulhu advised but I must have done something stupid because it now isn't compiling, I'm quite confused about rec_member, is this a variable I am supposed to declare before hand or something else? Sorry for the stupid questions but I haven't had much luck trying to figure this out on my own and I can't get a hold of my lecturer to ask.

CREATE OR REPLACE PROCEDURE OldStandardMembers(days NUMBER)
IS

members	StandardMember.MemberID%type:=0;

BEGIN
  for rec_member in  (SELECT MemberID FROM StandardMember 
                          WHERE EXISTS 
                         (SELECT 1 FROM Subscribes 
                          WHERE (Subscribes.MemberID = StandardMember.MemberID AND TRUNC(Subscribes.DateSubscribed) = (TRUNC(SYSDATE) - days)
                     )loop
                          dbms_output.put_line(rec_member.MemberID);
  end loop;
                     
END;


Re: 6 problems [message #257821 is a reply to message #257819] Thu, 09 August 2007 04:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are missing some closing brackets. And you don't need to declare rec_member in advance. As they state in the manuals: PL/SQL creates a record variable ("rec_member") with fields corresponding to the columns of the result set.

MHE
Re: 6 problems [message #257831 is a reply to message #257821] Thu, 09 August 2007 05:18 Go to previous messageGo to next message
Moragg
Messages: 6
Registered: August 2007
Junior Member
Ah brackets, I knew it would be something stupid, well now it compiles but still does nothing, maybe I am missunderstanding, as far as I can see it should be Selecting MemberId from the StandardMemebr table Where the same MemberID also exists in the Subscribes table and DateSubscribed in the Subscribes table is equal to the current date minus days. It should then display the current value of rec_member which should be a MemberId from the StandardMember table.

Is this right? or am I missunderstanding
Re: 6 problems [message #257841 is a reply to message #257831] Thu, 09 August 2007 05:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, you can test the query you use in the loop. Does it produce the results you wanted?

MHE
Re: 6 problems [message #257849 is a reply to message #257831] Thu, 09 August 2007 06:16 Go to previous messageGo to next message
Moragg
Messages: 6
Registered: August 2007
Junior Member
Ah, always stupid mistakes, I was asking it to return MemberIDs subscried on an exact date, I've changed it from = to <= and it works perfectly now, and as for the rest I've corrected all the stupid mistakes with loops and they work now too.

As for my last trigger, if it's not possible to prompt for input from a trigger, is it possible to use 'execute procedureName;'? That way I can just call a procedure which prompts for input and update the database from the procedure.
Re: 6 problems [message #257860 is a reply to message #257849] Thu, 09 August 2007 06:38 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No, you can't do that as a workaround. You got to understand that triggers are ... triggered. They are fired by a given event. You can call procedures from triggers, but not to interact with end users. Oracle doesn't provide such an option in server side PL/SQL. User interaction shouldn't take place in the database. It is not designed for that. You have front end applications to interact with the end users.

MHE
Previous Topic: Database Design
Next Topic: forms6i,reports6i,oracle
Goto Forum:
  


Current Time: Fri Dec 02 22:49:03 CST 2016

Total time taken to generate the page: 0.17028 seconds