Home » Developer & Programmer » Forms » Stored Procedure in Forms (Oracle Forms, 9i, XP)
Stored Procedure in Forms [message #310529] Tue, 01 April 2008 09:14 Go to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
Hi,

Iam having a problem with using a procedure i created in SQL Plus in Oracle Forms 9i. When i create a new program unit in Forms and transfer the code over it compiles fine, i have ammended the code so the parameters are the text item boxes within the form which also compiles. However the procedure is not working.

Can anyone advise on this?

Many Thanks

Jay

PROCEDURE school_det_sp 
( BEHAVIOUR_ID IN behaviour.behaviour_id%TYPE,
  BEH_TYPE IN behaviour.beh_type%TYPE,
  BE_STU_ID IN behaviour.be_stu_id%TYPE,
  BE_CLASS_ID IN behaviour.be_class_id%TYPE,
  DATE_RECIEVED IN behaviour.date_recieved%TYPE
--b_id IN behaviour.behaviour_id%TYPE,
--   b_type IN behaviour.beh_type%TYPE,
--   s_id IN behaviour.be_stu_id%TYPE,
--   c_id IN behaviour.be_class_id%TYPE,
--   d_id IN behaviour.date_recieved%TYPE
	) 
IS
	lv_beh_total NUMBER (8);
  lv_punish VARCHAR2(40);
BEGIN
  INSERT INTO behaviour (BEHAVIOUR_ID, BEH_TYPE, BE_STU_ID , BE_CLASS_ID, DATE_RECIEVED) 
		VALUES(:BEHAVIOUR_ID,:BEH_TYPE, :BE_STU_ID, :BE_CLASS_ID, :DATE_RECIEVED);
SELECT COUNT(behaviour_id)
   INTO lv_beh_total
   FROM behaviour 
   WHERE be_stu_id =:BE_STU_ID
   AND  be_class_id = :BE_CLASS_ID
   AND  date_recieved =:DATE_RECIEVED;
IF lv_beh_total = 2 THEN
	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,1,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
ELSIF lv_beh_total = 3 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,2,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
ELSIF lv_beh_total = 5 THEN
 	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,1,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
ELSIF lv_beh_total = 6 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,2,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
ELSIF lv_beh_total = 8 THEN
 	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,1,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
ELSIF lv_beh_total = 9 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR_ID,2,:BE_STU_ID,:BE_CLASS_ID, :DATE_RECIEVED);
END IF;
END;
Re: Stored Procedure in Forms [message #310616 is a reply to message #310529] Tue, 01 April 2008 15:32 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
However the procedure is not working.
Yes, it is by far the most common Oracle error. Did you use Google to find the solution? Searching through English and Croatian pages, it returns almost 2.500.000 results.

Now don't tell me that you can't use MySQL or SQL Server links; OK, restrict it to Oracle-related pages and you'll get ~200.000 results.

True, I'm making fun of you. Saying that "procedure does not work" means absolutely NOTHING. It is useless. It requires at least one iteration too much before you'll get the answer because we have to ask: What does it mean? Does Oracle return an error? If so, which one? If not, does forms runtime unexpectedly terminate? Something different?

Now a blind guess: did you, perhaps, forget to COMMIT changes this procedure has made in order to make them visible to other sessions?
Previous Topic: dbms packages
Next Topic: How i get Last Record of Current Data Block using forms 6i
Goto Forum:
  


Current Time: Tue Dec 06 12:30:54 CST 2016

Total time taken to generate the page: 0.05806 seconds