Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql completed successfully with showing result
pl/sql completed successfully with showing result [message #212571] Fri, 05 January 2007 21:52 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello team

in my case i got message -
PL/SQL PROCEDURE COMPLETED SUCCESSFULLY.
but after enter data, it shows me nothing.

i thought it might be because of
SET SERVROUTPUT OFF but that's not the issue.

can u please tell what i had done wrong.

my procedure is -
declare
  v_gender varchar2(8);
  v_qualification varchar2(18);
  v_years_of_exp number(5);
begin
  v_gender:='&gender';
  v_qualification:='&qualification';
  v_years_of_exp:=&years;
if v_gender='male' then
if v_qualification='post-graduate' then
if v_years_of_exp>=10 then
  dbms_output.put_line('salary is rs 15000');
else 
  dbms_output.put_line('salary is rs 10000');
end if;
end if;
elsif v_qualification='graduate' then
if v_years_of_exp>=10 then
    dbms_output.put_line('salary is rs 10000');
else
  dbms_output.put_line('salary is rs 7000');
end if;
end if;
end;


please provide me some help. why i m getting output
like salary is rs 7000 or 15000.

regards
ishika
Re: pl/sql completed successfully with showing result [message #212572 is a reply to message #212571] Fri, 05 January 2007 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
begin
DBMS_OUTPUT.ENABLE(100000);
Re: pl/sql completed successfully with showing result [message #212581 is a reply to message #212571] Fri, 05 January 2007 23:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I think it's a matter of the logical grouping of your IF/ELSIF/END IF statements. Not sure what output you are really expecting?

SQL> set serverout on
SQL> set verify off
SQL> declare
  2    v_gender                 varchar2(8);
  3    v_qualification  varchar2(18);
  4    v_years_of_exp   number(5);
  5  begin
  6    v_gender:='&gender';
  7    v_qualification:='&qualification';
  8    v_years_of_exp:=&years;
  9
 10  if v_gender='male' then
 11    if v_qualification='post-graduate' then
 12      if v_years_of_exp>=10 then
 13        dbms_output.put_line('salary is rs 15000');
 14      else
 15        dbms_output.put_line('salary is rs 10000');
 16      end if;
 17    end if;
 18
 19  elsif v_qualification='graduate' then
 20    if v_years_of_exp>=10 then
 21      dbms_output.put_line('salary is rs 10000');
 22    else
 23      dbms_output.put_line('salary is rs 7000');
 24    end if;
 25
 26  end if;
 27  end;
 28  /
Enter value for gender: male
Enter value for qualification: post-graduate
Enter value for years: 10
salary is rs 15000

PL/SQL procedure successfully completed.

SQL> /
Enter value for gender: male
Enter value for qualification: graduate
Enter value for years: 10

PL/SQL procedure successfully completed.

SQL>

Re: pl/sql completed successfully with showing result [message #212589 is a reply to message #212581] Sat, 06 January 2007 01:55 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I agree ... there are combinations which are not covered in this code. For example, MALE + GRADUATE, all FEMALEs, etc. Perhaps you entered a combination which isn't captured in any of those IFs.
Re: pl/sql completed successfully with showing result [message #212835 is a reply to message #212571] Mon, 08 January 2007 08:50 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

declare
v_gender varchar2(8);
v_qualification varchar2(18);
v_years_of_exp number(5);
begin
dbms_output.put_line(10000);
v_gender:='&gender';
v_qualification:='&qualification';
v_years_of_exp:=&years;
if v_gender='male' then
if v_qualification='post-graduate' then
if v_years_of_exp>=10 then
dbms_output.put_line('salary is rs 15000');
else
dbms_output.put_line('salary is rs 10000');
end if;
end if;
elsif v_qualification='graduate' then
if v_years_of_exp>=10 then
dbms_output.put_line('salary is rs 10000');
else
dbms_output.put_line('salary is rs 7000');
end if;
end if;
end;
/


again i got error

v_gender:='male';
*
Error at line 7
ORA-06550: line 7, column 3:
PLS-00103: Encounter the symbol "V_GENDER" when expecting one of the following:
:=.( % ;
The symbol "_" was substituted for "V_GENDER" to continue.


please suggest me why i m getting error.

regards



[Updated on: Mon, 08 January 2007 08:50]

Report message to a moderator

Re: pl/sql completed successfully with showing result [message #212837 is a reply to message #212835] Mon, 08 January 2007 08:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you're gettin an error because the code you post isn't the same as the code you run. I ran the code you post and got this:
  1  declare
  2  v_gender varchar2(8);
  3  v_qualification varchar2(18);
  4  v_years_of_exp number(5);
  5  begin
  6  dbms_output.put_line(10000);
  7  v_gender:='&gender';
  8  v_qualification:='&qualification';
  9  v_years_of_exp:=&years;
 10  if v_gender='male' then
 11    if v_qualification='post-graduate' then
 12      if v_years_of_exp>=10 then
 13        dbms_output.put_line('salary is rs 15000');
 14      else
 15        dbms_output.put_line('salary is rs 10000');
 16      end if;
 17    end if;
 18  elsif v_qualification='graduate' then
 19    if v_years_of_exp>=10 then
 20      dbms_output.put_line('salary is rs 10000');
 21    else
 22      dbms_output.put_line('salary is rs 7000');
 23    end if;
 24  end if;
 25* end;
 26  /
Enter value for gender: male
old   7: v_gender:='&gender';
new   7: v_gender:='male';
Enter value for qualification: post-graduate
old   8: v_qualification:='&qualification';
new   8: v_qualification:='post-graduate';
Enter value for years: 10
old   9: v_years_of_exp:=&years;
new   9: v_years_of_exp:=10;
10000
salary is rs 15000

PL/SQL procedure successfully completed.

SQL> 
Looks fine to me.
I can't understand why you'd ever write something like this, and I'm pretty sure your logic is faulty - as @Littlefoot pointed out.
Re: pl/sql completed successfully with showing result [message #212838 is a reply to message #212571] Mon, 08 January 2007 08:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Show how it looks when you run it..
works for me...

SQL> declare
2 v_gender varchar2(8);
3 v_qualification varchar2(18);
4 v_years_of_exp number(5);
5 begin
6 dbms_output.put_line(10000);
7 v_gender:='&gender';
8 v_qualification:='&qualification';
9 v_years_of_exp:=&years;
10 if v_gender='male' then
11 if v_qualification='post-graduate' then
12 if v_years_of_exp>=10 then
13 dbms_output.put_line('salary is rs 15000');
14 else
15 dbms_output.put_line('salary is rs 10000');
16 end if;
17 end if;
18 elsif v_qualification='graduate' then
19 if v_years_of_exp>=10 then
20 dbms_output.put_line('salary is rs 10000');
21 else
22 dbms_output.put_line('salary is rs 7000');
23 end if;
24 end if;
25 end;
26 /
Enter value for gender: male
old 7: v_gender:='&gender';
new 7: v_gender:='male';
Enter value for qualification: post-graduate
old 8: v_qualification:='&qualification';
new 8: v_qualification:='post-graduate';
Enter value for years: 10
old 9: v_years_of_exp:=&years;
new 9: v_years_of_exp:=10;
10000
salary is rs 15000

PL/SQL procedure successfully completed.
Re: pl/sql completed successfully with showing result [message #212873 is a reply to message #212571] Mon, 08 January 2007 12:29 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
thanx to u all.

but i dnt know why i m getting error.
u runned block on your system & easily
got result, i failed to.

i will give u my feedback after solving it.

thanx one again for being with me.

regards
ishika
Re: pl/sql completed successfully with showing result [message #212895 is a reply to message #212873] Mon, 08 January 2007 15:08 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is this line all about?

dbms_output.put_line(10000);

What is your database version? Would it help if you change the previous line to

dbms_output.put_line('10000');

Note single quotes around 10000.
Re: pl/sql completed successfully with showing result [message #212972 is a reply to message #212895] Mon, 08 January 2007 23:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My guess is he meant dbms_output.enable instead of put_line, since it is the first command, and since a put_line('10000') does not make any sense...
Re: pl/sql completed successfully with showing result [message #213173 is a reply to message #212571] Tue, 09 January 2007 13:25 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

earlier u r asking for the format and
now u r saying that ur code is not right.
as far as ur format is concerened it is right in the code
dbms_output.put_line('salary rs is..');
whatever!!!!!!!!!!!!!!!!

and not getting why ur code generates error......
Re: pl/sql completed successfully with showing result [message #213203 is a reply to message #212571] Tue, 09 January 2007 16:11 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you want help then you have to give use some imformation. Do NOT say that it doesn't work, run the script and paste the ENTIRE session in sql*plus into the forum. Without seeing what you are doing, we can't help you.
Re: pl/sql completed successfully with showing result [message #213261 is a reply to message #212571] Wed, 10 January 2007 01:49 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
sorry for the late response.

now its working.

thanx to all of u. Cool

regards
ishika
Re: pl/sql completed successfully with showing result [message #213264 is a reply to message #212571] Wed, 10 January 2007 01:55 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello littlefoot

without
dbms_output.put_line(10000); (without single quotes)
i got result. i m using 9i.


regards
ishika
Re: pl/sql completed successfully with showing result [message #213273 is a reply to message #213264] Wed, 10 January 2007 02:43 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just as Frank said: instead of

-> dbms_output.put_line(10000);

there should have probably been

-> dbms_output.enable(10000);

OK; the most important thing is that you finally got your output.
Previous Topic: Correcting the year in a timestamp field
Next Topic: returning a PL/SQL boolean via ODBC from C
Goto Forum:
  


Current Time: Mon Dec 05 19:07:08 CST 2016

Total time taken to generate the page: 0.08819 seconds