Home » Developer & Programmer » Forms » when New Form Instance
when New Form Instance [message #668031] Thu, 01 February 2018 03:51 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
declare
A VARCHAR2(200);
	B VARCHAR2(29);
	C DATE;
	D NUMBER;
	E VARCHAR2(200);
	TT VARCHAR2(25);
	cursor c1 is
		select distinct DOB,mobile,substr(bph,1,25),STUID,NAME from family,basic,student
    where family.fam_id=student.fam_id and dob like TO_CHAR(SYSDATE,'DD-MON-%');
begin
	loop
		
	open c1;
	 fetch c1 into C,A,B,D,E;
	INSERT INTO MSGOUT (ID,MSGTO,MSG)
	VALUES
	(D,'+92'||a,'>> SiS wishing you a day filled with fun and delight! Happy Birthday'||' '|| E || '  Regards. >>SiS<<  '||b);
	NEXT_RECORD;
	EXIT WHEN (:SYSTEM.LAST_RECORD)='TRUE';
END LOOP;
MESSAGE('SMS has been sent to Queue...');
COMMIT;
close c1;
END;
SET_WINDOW_PROPERTY(forms_mdi_window, window_state, maximize);
please view the above code.
select distinct DOB,mobile,substr(bph,1,25),STUID,NAME from family,basic,student
    where family.fam_id=student.fam_id and dob like TO_CHAR(SYSDATE,'DD-MON-%');
on sqlplus this query return 4-records. but when this form runs . its only return first 1 record. .please help me to get all record which is fall on specific date.
Re: when New Form Instance [message #668039 is a reply to message #668031] Thu, 01 February 2018 05:16 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
What are you trying to do here? This filter does not make a lot of sense:
and dob like TO_CHAR(SYSDATE,'DD-MON-%');
I am surprised it returned any rows at all. If you are trying to select dates with a match on day and month, you probably need to use the EXTRACT function.
Also, your cursor is joining three tables but you have only one join predicate. Is that correct?

Apart from specific mistakes, if this is a college homework assignment then to get better marks you may need to adjust your programming style:
Use meaningful variable names
Prefix columns with table names or aliases
Do not say "record" when you mean "row".
Re: when New Form Instance [message #668042 is a reply to message #668039] Thu, 01 February 2018 05:52 Go to previous messageGo to next message
Littlefoot
Messages: 21599
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

on sqlplus this query return 4-records. but when this form runs . its only return first 1 record.
That might be because you wrote it that way, i.e. you EXIT loop as soon as you're on the last record in a block (which is TRUE instantly as you don't move from the first - which is also the last - record).

It isn't clear whether you want to
a) insert into a table
b) insert into a (tabular) form
because your code contains a little bit of both.

Presuming that it is an a) option, then consider such a modification: as cursor's SELECT returns several rows, you should loop through all of them. Personally, I'd rather use a cursor FOR loop as it is easier to maintain, but it'll work this way as well.

   open c1;

   loop
      fetch c1 into c, a, b, d, e;
      exit when c1%notfound;

      insert into msgout (id, msgto, msg)
              values (
                        d,
                        '+92' || a,
                           '>> SiS wishing you a day filled with fun and delight! Happy Birthday'
                        || ' '
                        || e
                        || '  Regards. >>SiS<<  '
                        || b);
   end loop;

   close c1;

[Updated on: Thu, 01 February 2018 05:52]

Report message to a moderator

Re: when New Form Instance [message #668044 is a reply to message #668042] Thu, 01 February 2018 07:14 Go to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
thanks for your help. i have solve my problem.
Previous Topic: How to Open HTML file in same form with current items
Next Topic: oracle form opening on specific time.
Goto Forum:
  


Current Time: Thu Oct 01 14:38:23 CDT 2020