01-04-2011
(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
Code :
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
Code :
pebblz02% test1.ksh
SQL*Plus: Release 11.1.0.7.0 - 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.
Code :
pebblz02% cat test.ksh
#! /bin/ksh
sqlplus -S CSM_ADMIN/CSM_ADMIN@AWCCFG4 << EOF
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
Code :
dummyvar=`sqlplus -S csm_admin/csm_admin@AWCCFG4 << EOF
select PATCH_NAME from csm_admin.csm_config_log where patch_name = 'PATCH_LOG';
EOF`
echo "system date is " $dummyvar
its working, I am not sure if i dont give space its not working as expected.
Moderator's Comments:
Please use code tags when posting data and code samples!
Last edited by Franklin52; 01-04-2011 at 03:50 AM ..
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
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
2. UNIX for Dummies Questions & Answers
Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies
3. Shell Programming and Scripting
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
4. Shell Programming and 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
5. Shell Programming and 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
6. Shell Programming and 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
7. Shell Programming and Scripting
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='PATCH.zip';
exit;
EOF`
echo $DEPENDENT_CR_NO
... (2 Replies)
Discussion started by: beginer314
2 Replies
8. Shell Programming and 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
9. Shell Programming and Scripting
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
10. Shell Programming and Scripting
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