Visit Our UNIX and Linux User Community

(solved) Shell scripting to access SQLPLUS using variable

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting (solved) Shell scripting to access SQLPLUS using variable
# 1  
Old 01-04-2011
Question (solved) Shell scripting to access SQLPLUS using variable

I have shell script which will try to login to SQL Plus and retrieve some data, based on the outcome i will proceed further

Below is the content of the file
pebblz02% cat test1.ksh
#! /bin/ksh

dummyvar=`sqlplus -S csm_admin/csm_admin@SIDNAME <<EOF echo hi; exit; EOF`

Error message on execution of test1.ksh
pebblz02% test1.ksh
SQL*Plus: Release - Production Copyright (c) 1982, 2008, Oracle. All rights reserved. Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements. 
Usage 1: sqlplus -H | -V -H Displays the SQL*Plus version and the usage help. 
-V Displays the SQL*Plus version. Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ] <option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S] -C <version> 
Sets the compatibility of affected commands to the version specified by <version>. 
The version has the form "x.y[.z]". For example, -C 10.2.0 -F Enables the failover mode for a RAC environment. -L Attempts to log on just once, 
instead of reprompting on error. -M "<options>" Sets automatic HTML markup of output. The options have the form: 
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] -R <level> 
Sets restricted mode to disable SQL*Plus commands that interact with the file system. The level can be 1, 2 or 3. The most restrictive is -R 3 which disables all 
user commands interacting with the file system. -S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands. 
<logon> is: (<username>[/<password>][@<connect_identifier>] | /) [AS SYSDBA | AS SYSOPER | AS SYSASM] | /NOLOG | [EDITION=value] Specifies the database 
account username, password and connect identifier for the database connection. Without a connect identifier, SQL*Plus connects to the default database. 
The AS SYSDBA, AS SYSOPER and AS SYSASM options are database administration privileges. <connect_identifier> can be in the form of Net Service Name or Easy 
Connect. @[<net_service_name> | [//]Host[:Port]/<service_name>] <net_service_name> is a simple name for a service that resolves to a connect descriptor. 
Example: Connect to database using Net Service Name and the database net service name is ORCL. sqlplus myusername/mypassword@ORCL Host specifies the host 
name or IP address of the database server computer. Port specifies the listening port on the database server. <service_name> specifies the service name of the 
database you want to access. Example: Connect to database using Easy Connect and the Service name is ORCL. sqlplus myusername/mypassword@Host/ORCL 
The /NOLOG option starts SQL*Plus without connecting to a database. The EDITION specifies the value for Application Edition <start> is:
@<URL>|<filename>[.<ext>] [<parameter> ...] Runs the specified SQL*Plus script from a web server (URL) or the local file system (filename.ext) 
with specified parameters that will be assigned to substitution variables in the script. When SQL*Plus starts, and after CONNECT commands, 
the site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile (e.g. login.sql in the working directory) are run. The files may contain 
SQL*Plus commands. Refer to the SQL*Plus User's Guide and Reference for more information.

In the shell script if i use the following things its able to login, not sure what's happening.
pebblz02% cat test.ksh
#! /bin/ksh

can some one point why i am not able to login in the 1st format.

---------- Post updated at 10:44 AM ---------- Previous update was at 10:24 AM ----------

I used the below format
dummyvar=`sqlplus -S csm_admin/csm_admin@AWCCFG4 << EOF
select PATCH_NAME from csm_admin.csm_config_log where patch_name = 'PATCH_LOG';
echo "system date is " $dummyvar

its working, I am not sure if i dont give space its not working as expected.

Moderator's Comments:
Mod Comment Please use code tags when posting data and code samples!

Last edited by Franklin52; 01-04-2011 at 03:50 AM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #805
Difficulty: Medium
The goal of a Digital Signal Processor (DSP) is usually to measure, filter or compress discrete digital signals.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing sqlplus output to shell variable

Hi , I am using below code : for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'` do ORACLE_SID=$i export ORACLE_SID; dest=`sqlplus "/ as sysdba" <<EOF set heading off feedback on verify off select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Discussion started by: admin_db
5 Replies

2. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

3. Shell Programming and Scripting

[Solved] Shell scripting

I am new to write scripts. I want help to write a shell script to create 10 users and passwords to users. Can any please help me. (9 Replies)
Discussion started by: DONFOX
9 Replies

4. Shell Programming and Scripting

Sending sqlplus output to a shell variable

I am trying to import a sqlplus output into a shell variable but it doesnt seem to be working. set -x export DEPENDENT_CR_NO=`sqlplus -s /nolog <<EOF conn username/passwd set heading off select dependency from custom_patches where patch_name=''; exit; EOF` echo $DEPENDENT_CR_NO ... (2 Replies)
Discussion started by: beginer314
2 Replies

5. Shell Programming and Scripting

[SOLVED] Book for shell scripting

I plan to buy a shell scripting book. Please suggest me a best book for shell Scripting. It will be great if the book contains more examples. Regards Kalai ---------- Post updated at 01:24 AM ---------- Previous update was at 01:09 AM ---------- I got the solution in the below post. (0 Replies)
Discussion started by: kalpeer
0 Replies

6. Shell Programming and Scripting

[Solved] need help in shell scripting

Hi Friends, Please help me with the following problem: I have a text file with the following lines: Dated: 8/11/2011 <br> Time : 0000 <br> ============== <br> --------------------------------------<br>Data Upload Time =4:51:52... (2 Replies)
Discussion started by: anuajay1988
2 Replies

7. Shell Programming and Scripting

[Solved] help me in this looping in shell scripting

Hi, #!/bin/ksh result='/TIA/app/UniQP/queue/document/CSB' i=0; while ; do i=`expr $i + 1` if ($i -lt 5);then echo "THerrFile_$i.err"; else break; fi done ... (0 Replies)
Discussion started by: sudhir_83k
0 Replies

8. Shell Programming and Scripting

error in passing a variable to sqlplus from a shell script

hi, I am using a shell script from where i will be conecting to sqlplus.. i am having a problem in passing a variable to sqlplus query.. i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Discussion started by: kripssmart
3 Replies

9. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies

10. UNIX for Dummies Questions & Answers

sqlplus and shell scripting

i would like to learn how to integrate my little knowledge in shell scripting with sqlplus. well... i know how to make basic query in sqlplus but i dont know how i can integrate it with shell script. can someone :) please help me on this? can you give me some basic example on how to do this kind of... (10 Replies)
Discussion started by: inquirer
10 Replies

Featured Tech Videos