Home » SQL & PL/SQL » SQL & PL/SQL » pls correct this and it is not completed successfully (merged)
pls correct this and it is not completed successfully (merged) [message #324941] Wed, 04 June 2008 09:52 Go to next message
Mihiroracle
Messages: 18
Registered: May 2008
Junior Member
declare
rno number:=&rno;
name varchar2(12):=&name;
m number:=&m;
p number:=&p;
c number:=&c;
tot number;
avg number(5,2);
res varchar2(12);
div varchar2(12);
begin
tot:=m+p+c;
avg:=tot/3;
if m>=35 and p>=35 and c>=35 then
res:='pass';
else res:='fail';
end if;
if res='pass' then
if avg>=60 then
div:='first';
else
if avg>=50 then div:='second';
else
div:='third';
end if;
end if;
else
div:='nil';
end if;
dbms_output.put_line('total is'||tot);
dbms_output.put_line('average'||avg);
dbms_output.put_line('result is'||res);
dbms_output.put_line('division is'||div);
end;

please tell where is the error and correct it.

thanks in advance..
Re: pls correct this [message #324942 is a reply to message #324941] Wed, 04 June 2008 09:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>please tell where is the error and correct it.
ERROR? What error?
I do not see any error.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
it is not completed successfully [message #325034 is a reply to message #324941] Wed, 04 June 2008 19:53 Go to previous messageGo to next message
Mihiroracle
Messages: 18
Registered: May 2008
Junior Member
if it is correct then why is the above procedure completed successfully?
Re: it is not completed successfully [message #325036 is a reply to message #325034] Wed, 04 June 2008 20:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please use sqlplus then CUT & PASTE the whole session which shows EXACTLY how Oracle responds.

[Updated on: Wed, 04 June 2008 21:15] by Moderator

Report message to a moderator

Re: pls correct this and it is not completed successfully (merged) [message #325070 is a reply to message #324941] Wed, 04 June 2008 23:31 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
you are using avg as an identifier name.
AVG is an oracle built in function and shuld not be used as an identifier.

Regards
Minto
Re: pls correct this and it is not completed successfully (merged) [message #325072 is a reply to message #324941] Wed, 04 June 2008 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Great observation.
Time will tell if 'avg' is part of problem/solution;
since unformatted post is unreadable.
Re: it is not completed successfully [message #325263 is a reply to message #325034] Thu, 05 June 2008 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Mihiroracle wrote on Wed, 04 June 2008 20:53
if it is correct then why is the above procedure completed successfully?


Is this an oxymoron??
Re: it is not completed successfully [message #325300 is a reply to message #325263] Thu, 05 June 2008 10:44 Go to previous messageGo to next message
dsean
Messages: 5
Registered: March 2006
Junior Member
I agree with minto..Change the variable 'avg' to 'avge' as 'avg' is an oracle reserve word and cannot used as an identifier. I changed the variable and executed the query. Its working fine. Please find it below.


SQL> set serveroutput on;
SQL> declare
2 rno number:=&rno;
3 name varchar2(12):=&name;
4 m number:=&m;
5 p number:=&p;
6 c number:=&c;
7 tot number;
8 avge number(5,2);
9 res varchar2(12);
10 div varchar2(12);
11 begin
12 tot:=m+p+c;
13 avge:=tot/3;
14 if m>=35 and p>=35 and c>=35 then
15 res:='pass';
16 else res:='fail';
17 end if;
18 if res='pass' then
19 if avge>=60 then
20 div:='first';
21 else
22 if avge>=50 then div:='second';
23 else
24 div:='third';
25 end if;
26 end if;
27 else
28 div:='nil';
29 end if;
30 dbms_output.put_line('total is'||tot);
31 dbms_output.put_line('average'||avge);
32 dbms_output.put_line('result is'||res);
33 dbms_output.put_line('division is'||div);
34 end;
35 /
Enter value for rno: 001
old 2: rno number:=&rno;
new 2: rno number:=001;
Enter value for name: 'rj'
old 3: name varchar2(12):=&name;
new 3: name varchar2(12):='rj';
Enter value for m: 77
old 4: m number:=&m;
new 4: m number:=77;
Enter value for p: 77
old 5: p number:=&p;
new 5: p number:=77;
Enter value for c: 77
old 6: c number:=&c;
new 6: c number:=77;
total is231
average77
result ispass
division isfirst

PL/SQL procedure successfully completed.

Thanks
Sean
Re: it is not completed successfully [message #325352 is a reply to message #325300] Thu, 05 June 2008 23:12 Go to previous message
Mihiroracle
Messages: 18
Registered: May 2008
Junior Member
thank u very much sean..
Previous Topic: How I add 2 hours to some time variable?
Next Topic: Persian datepicker
Goto Forum:
  


Current Time: Fri Dec 02 16:34:38 CST 2016

Total time taken to generate the page: 0.41633 seconds