OraFAQ Forum: Client Tools » Declaring variable in SQLPLUS command

Home » SQL & PL/SQL » Client Tools » Declaring variable in SQLPLUS command (Oracle 9i)

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

Declaring variable in SQLPLUS command [message #424497]

Fri, 02 October 2009 14:14

me_arindam
Messages: 26
Registered: March 2008
Location: India

Junior Member

Hi,

I am generating an HTML report via SQLPLUS.
In the script total 14 individual SQL queries are there. Every query has used one table "Compare" .

In every query I have to check one condition like below:

where compare_date > = '01-Jan-09'

This date value ( '01-Jan-09') is same across all the 14 queries. But every time when the date value needs to be changed, I have change in all the 14 places.

Is there any way , I can declare a variable and assign the value only once. And use the variable in my where condition?

Report message to a moderator

Re: Declaring variable in SQLPLUS command [message #424499 is a reply to message #424497]

Fri, 02 October 2009 14:19

BlackSwan
Messages: 20109
Registered: January 2009

Senior Member

>This date value ( '01-Jan-09')

In Oracle characters between single quote marks are STRINGS
'this is a string, 2009-10-02, not a Date'
When you need to convert STRING to DATE use TO_DATE() function.

Report message to a moderator

Re: Declaring variable in SQLPLUS command [message #424500 is a reply to message #424499]

Fri, 02 October 2009 14:22

me_arindam
Messages: 26
Registered: March 2008
Location: India

Junior Member

Thanks for your response. Yes you are right it is string value.

But I want to know how to declare a variable and assign the value to it. So that in all of my queries I can use the variable ?

Note: This is a simple SQL scripts where 14 queries are written one after one.

Report message to a moderator

Re: Declaring variable in SQLPLUS command [message #424501 is a reply to message #424500]

Fri, 02 October 2009 14:39

BlackSwan
Messages: 20109
Registered: January 2009

Senior Member

When all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12050.htm#i2699801

BTW, posting the same problem 5 times is rude, excessive & unnecessary.

[Updated on: Fri, 02 October 2009 14:42]

Report message to a moderator

Re: Declaring variable in SQLPLUS command [message #424503 is a reply to message #424501]

Fri, 02 October 2009 15:02

me_arindam
Messages: 26
Registered: March 2008
Location: India

Junior Member

I did not want to post it 5 times. I have submitted only once. But while submitting the page got expired and finally come up with 5 posting.

I really apologize for such thing but it was never intentional.

Thanks for sending the manual. But still I am struggling with my requirement.

Report message to a moderator

Re: Declaring variable in SQLPLUS command [message #424504 is a reply to message #424503]

Fri, 02 October 2009 15:06

BlackSwan
Messages: 20109
Registered: January 2009

Senior Member

http://www.lmgtfy.com/?q=oracle+SQLPLUS+variable+example

Report message to a moderator

Previous Topic:	1st timer with writing procedures, please assist
Next Topic:	how to stop query execution in SQL*plus (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 02:23:42 CDT 2013

Total time taken to generate the page: 0.16845 seconds