Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Passing Variable to SQL*Plus

Oracle UNIX/Linux script tips

Passing variables to SQL*Plus

While Oracle provides some powerful ways to get data into a database sometimes we?ll want to pass things directly from our shell scripts.  There are a couple methods we can use for this.

Additional information on using Shell Scripting for passing variables is available HERE.

The SQL*Plus new_value clause

SQL*Plus has a new_value argument to the column clause, used to capture SQL results and pass them back as variable in SQL*Plus.  In this example we query for tab_col_name and place it into the my_var_name variable:

column tab_col_name new_value my_var_name

select
    tab_col_name
from
    mytab;

prompt "Variable value is: &my_var_name"

SQL Script Arguments

Just like our shell scripts can have arguments we can also have arguments for our SQL scripts (also known as script parameters).  The script parameters are given after the @scriptname.sql either within sqlplus or when sqlplus is initiated.

The script arguments will be set in the SQL session as substitution variables named 1, 2, 3 and so on.  They can be referenced with an ampersand.  Consider this SQL script:

select 'Selecting everything from the table &1' from dual;
select * from &1;
exit;

By passing a table name when we call the script we can use this script to select from a specific table:

$ sqlplus system/manager @select_table.sql 'v$instance' 

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 4 23:12:38 2006 

Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 

old   1: select 'Selecting everything from the table &1' from dual
new   1: select 'Selecting everything from the table v$instance' from dual 

'SELECTINGEVERYTHINGFROMTHETABLEV$INSTANCE'
----------------------------------------------
Selecting everything from the table v$instance

old   1: select * from &1
new   1: select * from v$instance

See code depot for full script 

Since these are substitution variables Oracle will place the values into the statement before the SQL is parsed.  While substitution variables are less efficient than bind variables in some circumstances they can be used to specify parts of the sql statement like column or table names while bind variables can only be used for values.

Multiple arguments can be specified after the script name and arguments that contain spaces must be enclosed in quotes.  In the example above the table name v$instance is enclosed in single quotes to keep the shell from interpreting the dollar sign as the beginning of a variable.

 

 

This is an excerpt from the book "Oracle Shell Scripting" by Rampant TechPress. 

You can buy it directly from the publisher for only $34.95.  Also, see the Oracle script collection for over 600 working Oracle scripts to download.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.