Another way to initialize variables in SQL*PLUS

Everyone has some way to inititalize variables in SQL*PLUS. My favorite is to use the "col" directive with "new_val" option.

Everyone has some way to inititalize variables in SQL*PLUS. My favorite is to use the "col" directive with "new_val"...

option. This approach does not require a spoolfile making it somewhat easier to use in SQL*PLUS coding choirs.

-- Here is one way to set an ARCHIVE_KEY variable 
--=============================================== 

-- Clear the variable 
undefine ARCHIVE_KEY ; 

-- Set the column to define 
col ARCHIVE_KEYval new_val ARCHIVE_KEY 

-- Select and initialize the variable 
-- **** 
-- NOTE: This is my format of ARCHIVE_KEY. 
--       You can change the format as needed. 
select 
    to_char(sysdate 
           ,'YYYYMMDD_HH24MISS') ARCHIVE_KEYval 
from dual; 

-- This is just to display the value 
select 
 '&&ARCHIVE_KEY' ARCHIVE_KEY 
from dual ; 
--eof-- 

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in March 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close