Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » Client Tools » Declaring variable in SQLPLUS command (Oracle 9i)
Declaring variable in SQLPLUS command [message #424497] Fri, 02 October 2009 14:14 Go to next message
me_arindam
Messages: 25
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 Go to previous messageGo to next message
BlackSwan
Messages: 3359
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 Go to previous messageGo to next message
me_arindam
Messages: 25
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 Go to previous messageGo to next message
BlackSwan
Messages: 3359
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 Go to previous messageGo to next message
me_arindam
Messages: 25
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 Go to previous message
BlackSwan
Messages: 3359
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 1 03:46:40 CST 2009

Total time taken to generate the page: 0.32085 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.