Sponsored Content
Top Forums UNIX for Advanced & Expert Users Set shell variables from SQLPLUS query results Post 302231058 by pranavagarwal on Monday 1st of September 2008 12:12:45 PM
Old 09-01-2008
Error Set shell variables from SQLPLUS query results

Hi All,

I needed to get the result of two sqlplus queris into shell variables.

After days of looking for the ultimate solution to this problem.. i found this...
Code:
sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ",
$1);}' | read VAR1 VAR2
set head off pagesize 0 echo off verify off feedback off
select name from v$database
/
select log_mode from v$database
/
EOF

from here...

I did not understand the logic used in the above peice of code. As far I was concerned, my objective was achieved. But then I saw perils of under-understanding! Knowledge shouldn't be a mission, it should be a journey!

The problem I am facing now is that, I now need to get just one value out of the sqlplus connection. So, i just simply tried..
Code:
sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1);' | read VAR1
set head off pagesize 0 echo off verify off feedback off
select name from v$database
/
EOF

But to no avail.. anybody willing to help me out on this, will be heartfully appreciated.

Thanks!
Pranav
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

sql query results in unix shell script

Hi I want to get the a field from a SQL query into unix shell script variable. the whole situation is like this. 1. Opened a cursor to a table in DB2 databse. 2. Fetching individual rows with the help of cursor. 3. Each row has 4 fields. I want each of the field in individual shell... (1 Reply)
Discussion started by: skyineyes
1 Replies

2. Shell Programming and Scripting

Oracle Query results to be stored in variables

Hi I would like to know if there is a way to just have one ORACLE connection established, using which we can execute different queries and store the results under different variables. For e.g the following uses to two silent ORACLE connection to store the result under two different... (4 Replies)
Discussion started by: ashokjaysiv
4 Replies

3. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

Can someone tell me why I'm getting error when I try to run this? #!/bin/csh -f source ~/.cshrc # set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus # set count=`$SQLPLUS -s ${DB_LOGIN} << END select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Discussion started by: gregrobinsonhd
7 Replies

4. Shell Programming and Scripting

Oracle Query results to be stored in variables using unix

I want to store the sql query output into a variable #!/bin/ksh ORACLE_SID=DB01; export ORACLE_SID; export FILE_PATH=/home/asg/Tmp # Order Checking echo " removing old files " rm $FILE_PATH/Malformed_Order.txt echo " Enter the Malformed Order ....!" read orders echo "Regrade... (5 Replies)
Discussion started by: Nareshp
5 Replies

5. Shell Programming and Scripting

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at... (2 Replies)
Discussion started by: gary_w
2 Replies

6. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

7. Shell Programming and Scripting

Multiple Query Results to Variables

Hello, I am very new to shell scripting and I am not sure of how best to handle the following scenario. I need to query a list of values from a table. I need to store those results and use them to selectively delete values in yet another table in a separate database. I do know how to store the... (3 Replies)
Discussion started by: flowervz
3 Replies

8. Shell Programming and Scripting

How to Assign SQL Query Results to Variables in Linux?

Hi, I am new to linux... How to Assign SQL Query Results to Variables in Linux,i want ti generate it in param files, Can anyone please explain me. Ex: SQL> Select * from EMP; O/P: Emp_No Emp_Name 1 AAA 2 BBB 3 CCC and I want expected... (5 Replies)
Discussion started by: Sravana Kumar
5 Replies

9. 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

10. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies
Net::Config(3pm)					 Perl Programmers Reference Guide					  Net::Config(3pm)

NAME
Net::Config - Local configuration data for libnet SYNOPSYS
use Net::Config qw(%NetConfig); DESCRIPTION
"Net::Config" holds configuration data for the modules in the libnet distribution. During installation you will be asked for these values. The configuration data is held globally in a file in the perl installation tree, but a user may override any of these values by providing their own. This can be done by having a ".libnetrc" file in their home directory. This file should return a reference to a HASH containing the keys described below. For example # .libnetrc { nntp_hosts => [ "my_preferred_host" ], ph_hosts => [ "my_ph_server" ], } __END__ METHODS
"Net::Config" defines the following methods. They are methods as they are invoked as class methods. This is because "Net::Config" inherits from "Net::LocalCfg" so you can override these methods if you want. requires_firewall HOST Attempts to determine if a given host is outside your firewall. Possible return values are. -1 Cannot lookup hostname 0 Host is inside firewall (or there is no ftp_firewall entry) 1 Host is outside the firewall This is done by using hostname lookup and the "local_netmask" entry in the configuration data. NetConfig VALUES nntp_hosts snpp_hosts pop3_hosts smtp_hosts ph_hosts daytime_hosts time_hosts Each is a reference to an array of hostnames (in order of preference), which should be used for the given protocol inet_domain Your internet domain name ftp_firewall If you have an FTP proxy firewall (NOT an HTTP or SOCKS firewall) then this value should be set to the firewall hostname. If your firewall does not listen to port 21, then this value should be set to "hostname:port" (eg "hostname:99") ftp_firewall_type There are many different ftp firewall products available. But unfortunately there is no standard for how to traverse a firewall. The list below shows the sequence of commands that Net::FTP will use user Username for remote host pass Password for remote host fwuser Username for firewall fwpass Password for firewall remote.host The hostname of the remote ftp server 0 There is no firewall 1 USER user@remote.host PASS pass 2 USER fwuser PASS fwpass USER user@remote.host PASS pass 3 USER fwuser PASS fwpass SITE remote.site USER user PASS pass 4 USER fwuser PASS fwpass OPEN remote.site USER user PASS pass 5 USER user@fwuser@remote.site PASS pass@fwpass 6 USER fwuser@remote.site PASS fwpass USER user PASS pass 7 USER user@remote.host PASS pass AUTH fwuser RESP fwpass ftp_ext_passive ftp_int_passive FTP servers can work in passive or active mode. Active mode is when you want to transfer data you have to tell the server the address and port to connect to. Passive mode is when the server provide the address and port and you establish the connection. With some firewalls active mode does not work as the server cannot connect to your machine (because you are behind a firewall) and the firewall does not re-write the command. In this case you should set "ftp_ext_passive" to a true value. Some servers are configured to only work in passive mode. If you have one of these you can force "Net::FTP" to always transfer in passive mode; when not going via a firewall, by setting "ftp_int_passive" to a true value. local_netmask A reference to a list of netmask strings in the form "134.99.4.0/24". These are used by the "requires_firewall" function to determine if a given host is inside or outside your firewall. The following entries are used during installation & testing on the libnet package test_hosts If true then "make test" may attempt to connect to hosts given in the configuration. test_exists If true then "Configure" will check each hostname given that it exists POD ERRORS
Hey! The above document had some coding errors, which are explained below: Around line 220: Expected text after =item, not a number Around line 225: Expected text after =item, not a number Around line 232: Expected text after =item, not a number Around line 240: Expected text after =item, not a number Around line 248: Expected text after =item, not a number Around line 253: Expected text after =item, not a number Around line 260: Expected text after =item, not a number perl v5.18.2 2014-01-06 Net::Config(3pm)
All times are GMT -4. The time now is 10:49 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy