(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
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:
Mod Comment Please use code tags when posting data and code samples!

Last edited by Franklin52; 01-04-2011 at 03:50 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

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='PATCH.zip'; 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
Login or Register to Ask a Question