Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure
stored procedure [message #222710] Tue, 06 March 2007 01:48 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I am executing a stored procedure in Before Report Trigger,
function BeforeReport return boolean is
begin
  acad_standing(:scc,:syy);
  return (TRUE);
end;


which inserts data into a table.

procedure acad_standing(mysc number, mysy number) as
 cursor c1 is select * from it where sc=mysc and sy=mysy;
 HD number:=0;
 H number:=0;
 D number:=0;
 G number:=0;
 S number:=0;
 C number:=0;
 P number:=0;
 N number:=0;
 total number;
 rem varchar2(200);
 begin
 for x in c1 loop
 if round(x.sgpa,2) >= 3.90 then
     HD:=HD+1;
 elsif       x.sgpa>=3.75 then
     H:=H+1;
 elsif       x.sgpa>=3.50 then
     D:=D+1;
 elsif       x.sgpa>=3.00 then
     G:=G+1;
 elsif       (x.sgpa>1.99 and x.cgpa<1.99) then
    C:=C+1;
    rem:='Caution';
 elsif      (x.sgpa>1.99 and x.cgpa >=1.99) then
    S:=S+1;
    rem:='Satisfactory';
 elsif       x.sgpa<=1.99 then
    P:=P+1;
     rem:='Probation';
 else       n:=n+1;
    rem :='Normal';
 end if;
 end loop;
 total:=hd+h+d+g+c+s+p+n;
 insert into academic_standings values ('Registered Students ',total);
 insert into academic_standings values ('Highest Distinction ',hd);
 insert into academic_standings values ('High Distinction ',h);
 insert into academic_standings values ('Distinction ',d);
 insert into academic_standings values ('Good ',g);
 insert into academic_standings values ('Satisfactory ',s);
 insert into academic_standings values ('Caution ',c);
 insert into academic_standings values ('Probation ',p);
 end;


The Report Shows the data inserted by the procedure. But at SQL there is no data in the table even after COMMIT.

I wonder, how was this possible ?


RzKhan
Re: stored procedure [message #222712 is a reply to message #222710] Tue, 06 March 2007 01:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where did you issue the commit? In your sql session?
Re: stored procedure [message #222713 is a reply to message #222712] Tue, 06 March 2007 01:54 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Yes, Sir..


EX > select * from academic_standings;

no rows selected

EX > 




Rzkhan
Re: stored procedure [message #222715 is a reply to message #222713] Tue, 06 March 2007 02:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
but WHERE did you issue your commit?
Re: stored procedure [message #222718 is a reply to message #222715] Tue, 06 March 2007 03:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
EX > commit;

Commit complete.

EX > select * from academic_standings;
no rows selected

EX > 
Re: stored procedure [message #222722 is a reply to message #222718] Tue, 06 March 2007 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think about it. You are inserting records, and while you are at it and rethinking if you want to really really store them, I issue a commit. Do you think your records will be committed?
You have to commit in the session that inserts the records, so you have to add it to your report (preferrable) or your stored procedure (bad idea).
Re: stored procedure [message #222726 is a reply to message #222722] Tue, 06 March 2007 03:27 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thanks.....it works fine now...Furthermore I think I should read more about sessions.. Is int?

function BeforeReport return boolean is
begin
	 
  acad_standing(:scc,:syy);
  commit;
  return (TRUE);
end


RzKhan
Re: stored procedure [message #222740 is a reply to message #222726] Tue, 06 March 2007 04:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think you should read about transaction management.
Think at what moment you are sure you want to save your inserted records. You may have it correct now, but at least really think it over. If your report fails, the inserted records stay in the database nonetheless.
Previous Topic: Leading space when spooling to text file
Next Topic: ORA-06502: PL/SQL: numeric or value error
Goto Forum:
  


Current Time: Fri Dec 09 03:43:40 CST 2016

Total time taken to generate the page: 0.09212 seconds