Home » SQL & PL/SQL » Client Tools » Having multiple input values (Oracle , 9.0.1.0, Win XP)
icon14.gif  Having multiple input values [message #502339] Wed, 06 April 2011 00:24 Go to next message
balakris.t
Messages: 21
Registered: November 2010
Location: IN
Junior Member
Hi,
I am new one to Oracle.
Using pl/sql block , i tried to have a certain input values from
Sql prompt but it doesn't work when i invoke it.
Can anyone rectify me what mistake i did?
Here the simplest code:

declare
  a number(4);
begin
  for i in 1..10 loop
    a := &a;
  end loop;
end;
/


Thanks in advance to all helping hands.
Re: Having multiple input values [message #502343 is a reply to message #502339] Wed, 06 April 2011 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sql prompt but it doesn't work when i invoke it.

I think it works but I think it does not waork as you expected it works.
So explain what you want to achieve and in which way it "does not work".

Regards
Michel
icon14.gif  Re: Having multiple input values [message #502350 is a reply to message #502343] Wed, 06 April 2011 01:17 Go to previous messageGo to next message
balakris.t
Messages: 21
Registered: November 2010
Location: IN
Junior Member
Hi Michel,
For example,I just want to give 5 input values and perform whether the given numbers are ODD or EVEN.
The main thing is want to invoke using sql*plus tool of oracle 9i.
Problem is 'For Loop' doesn't work.
So,Please show me right way.

Thanks in advance.
Re: Having multiple input values [message #502360 is a reply to message #502343] Wed, 06 April 2011 01:54 Go to previous messageGo to next message
balakris.t
Messages: 21
Registered: November 2010
Location: IN
Junior Member
Hi michel,

Still waiting for your valuable reply.

Thanks.
Re: Having multiple input values [message #502361 is a reply to message #502350] Wed, 06 April 2011 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not explain SHOW us, copy and paste what you do and have (in text inside the post NOT in attached file/image).

In addition, generally speaking, "does not work" means nothing for us.

Regards
Michel
Re: Having multiple input values [message #502371 is a reply to message #502361] Wed, 06 April 2011 02:32 Go to previous messageGo to next message
balakris.t
Messages: 21
Registered: November 2010
Location: IN
Junior Member
Here is my Program:
declare
   a number(4);
begin
   for i in 1..5 loop
	a := &a;
   if mod(a,2)=0 then
	dbms_output.put_line('Given Number is Even');
   else
	dbms_output.put_line('Given number is Odd');
   end if;	
   end loop;
end;
/
--Result is---

Enter value for a: 10
old   5:        a := &a;
new   5:        a := 10;
Given Number is Even
Given Number is Even
Given Number is Even
Given Number is Even
Given Number is Even

PL/SQL procedure successfully completed.

Re: Having multiple input values [message #502373 is a reply to message #502371] Wed, 06 April 2011 02:38 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Here is My program !

ind>  declare
  2      a number(4);
  3   begin
  4   a := &a;
  5     if mod(a,2)=0 then
  6   dbms_output.put_line('Given Number is Even');
  7     else
  8   dbms_output.put_line('Given number is Odd');
  9     end if; 
 10  end;
 11  /
Enter value for a: 10
old   4:  a := &a;
new   4:  a := 10;
Given Number is Even

PL/SQL procedure successfully completed.

ind> /
Enter value for a: 12
old   4:  a := &a;
new   4:  a := 12;
Given Number is Even

PL/SQL procedure successfully completed.

ind> /
Enter value for a: 15
old   4:  a := &a;
new   4:  a := 15;
Given number is Odd

PL/SQL procedure successfully completed.

ind> 



Quote:
So explain what you want to achieve
Re: Having multiple input values [message #502374 is a reply to message #502371] Wed, 06 April 2011 02:39 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It works as expected:

*First* the stuff you wrote is parsed in SQLPlus, and the &a is replaced by the 10.

*Then* the entire PL/SQL Block is send to the server, and executed on the server. Since PL/SQL runs on the server, there is no way to have additional client inputs inside PL/SQL.
Re: Having multiple input values [message #502375 is a reply to message #502371] Wed, 06 April 2011 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus works on the client.
SQL*Plus analyzes what you give and asks for values if anyone is needed.
Then SQL*Plus sends the PL/SQL block (in this case) to the server after substituting the (subsitution) variables by their values (you can see it by the old/new lines).
PL/SQL works on the server.
When the block is completed, PL/SQL returns the result to SQL*Plus/
SQL*Plus displays the result.

In short, a PL/SQL block does not interact with the client, it is a whole, executed as a whole.

Regards
Michel

[Updated on: Wed, 06 April 2011 02:41]

Report message to a moderator

Re: Having multiple input values [message #502384 is a reply to message #502374] Wed, 06 April 2011 03:10 Go to previous message
balakris.t
Messages: 21
Registered: November 2010
Location: IN
Junior Member
Now, I understood as well.

Thank You so Much to ALL.
Previous Topic: escape/quote Ampersand (&) because no variable substitution
Next Topic: Unable to insert Euro Symbol in database with Character set WE8MSWIN1252
Goto Forum:
  


Current Time: Mon Oct 15 13:58:04 CDT 2018