OraFAQ Forum: Client Tools » Having multiple input values

Home » Other » Client Tools » Having multiple input values (Oracle , 9.0.1.0, Win XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Having multiple input values [message #502339]

Wed, 06 April 2011 00:24

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.

Report message to a moderator

Re: Having multiple input values [message #502343 is a reply to message #502339]

Wed, 06 April 2011 01:06

Michel Cadot
Messages: 68501
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

Report message to a moderator

Re: Having multiple input values [message #502350 is a reply to message #502343]

Wed, 06 April 2011 01:17

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.

Report message to a moderator

Re: Having multiple input values [message #502360 is a reply to message #502343]

Wed, 06 April 2011 01:54

balakris.t
Messages: 21
Registered: November 2010
Location: IN

Junior Member

Hi michel,

Still waiting for your valuable reply.

Thanks.

Report message to a moderator

Re: Having multiple input values [message #502361 is a reply to message #502350]

Wed, 06 April 2011 01:56

Michel Cadot
Messages: 68501
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

Report message to a moderator

Re: Having multiple input values [message #502371 is a reply to message #502361]

Wed, 06 April 2011 02:32

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.

Report message to a moderator

Re: Having multiple input values [message #502373 is a reply to message #502371]

Wed, 06 April 2011 02:38

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

Report message to a moderator

Re: Having multiple input values [message #502374 is a reply to message #502371]

Wed, 06 April 2011 02:39

ThomasG
Messages: 3211
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.

Report message to a moderator

Re: Having multiple input values [message #502375 is a reply to message #502371]

Wed, 06 April 2011 02:40

Michel Cadot
Messages: 68501
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

balakris.t
Messages: 21
Registered: November 2010
Location: IN

Junior Member

Now, I understood as well.

Thank You so Much to ALL.

Report message to a moderator

Previous Topic:	escape/quote Ampersand (&) because no variable substitution
Next Topic:	Unable to insert Euro Symbol in database with Character set WE8MSWIN1252

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 24 11:33:01 CDT 2023