OraFAQ Forum: SQL & PL/SQL » saving query Output in a single variable

Home » SQL & PL/SQL » SQL & PL/SQL » saving query Output in a single variable

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

saving query Output in a single variable [message #635796]

Thu, 09 April 2015 03:00

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

Hallo all..

How can I save query Output as in a single variable as boolean or number type?

can anybody please help me in this regards?

thankx in advance.

Regards,

Report message to a moderator

Re: saving query Output in a single variable [message #635797 is a reply to message #635796]

Thu, 09 April 2015 03:07

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Most often, yes.
Now you have to tell us which OS, which shell, which client tool, which programming language, which... in order we can help you more.

Report message to a moderator

Re: saving query Output in a single variable [message #635798 is a reply to message #635797]

Thu, 09 April 2015 03:10

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

Hallo Michel..

sry for my incomplete question.
I am trying to make query in SQL
OS Windows8
UNIX Shell
PLSQL

Report message to a moderator

Re: saving query Output in a single variable [message #635800 is a reply to message #635798]

Thu, 09 April 2015 03:13

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I am trying to make query in SQL
OS Windows8
UNIX Shell
PLSQL

Er, I mean for the specific question, what variable you want to put the result in? In a shell variable? In a program variable?...
Do you want to do it both for Windows and Unix? Both for shell and PL/SQL?

Report message to a moderator

Re: saving query Output in a single variable [message #635801 is a reply to message #635800]

Thu, 09 April 2015 03:18

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

well, I am just trying assign the Output of my query in a single variable in pl/SQL. and this variable will be used in my second part.
the purpose of second part is to check, if there is any overlapping and if yes then the Programm should be end.

Report message to a moderator

Re: saving query Output in a single variable [message #635803 is a reply to message #635801]

Thu, 09 April 2015 03:55

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Will this second be handled in PL/SQL or another program?

[Updated on: Thu, 09 April 2015 03:56]

Report message to a moderator

Re: saving query Output in a single variable [message #635804 is a reply to message #635801]

Thu, 09 April 2015 03:58

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

thank u michale.. i have got it.. Smile

well i have used
select case when exists (
select ......
.......
.....
)
then 'Y' else 'N'
end var1
from dual;

Report message to a moderator

Re: saving query Output in a single variable [message #635805 is a reply to message #635804]

Thu, 09 April 2015 04:03

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

yes, it will be handeled by another pl/SQL program too.
I mean, when the Output result 'Y' is then the this will end the all program.

Report message to a moderator

Re: saving query Output in a single variable [message #635806 is a reply to message #635804]

Thu, 09 April 2015 04:13

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, this is the point to which I wanted to go: you don't need to get the whole query i a variable you just have to put the result in it.
So in PL/SQL this means (with your query):

select case when exists (<query>) then 'Y' else 'N' end
       INTO MYVAR
from dual;

Where MYVAR is declared in the DECLARE section of your PL/SQL block with something like "MYVAR VARCHAR2(1);" or a parameter of procedure:

CREATE OR REPLACE PROCEDURE P (MYVAR OUT VARCHAR2) 
IS
BEGIN
  SELECT 'Y' INTO MYVAR FROM MYTABLE... WHERE <overlap condition> AND ROWNUM=1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN MYVAR := 'N';
END;

Report message to a moderator

Re: saving query Output in a single variable [message #635808 is a reply to message #635806]

Thu, 09 April 2015 04:18

palpali
Messages: 138
Registered: December 2014
Location: India

Senior Member

Thankyou very very much Michel Smile

Its really a great help and guidance from you and hope for the future too. Smile

Regards.

Report message to a moderator

Previous Topic:	Oracle Number length to SQL Numeric length
Next Topic:	Materialized is getting invalid after refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 27 01:06:41 CDT 2026