Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sql output to shell variable

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sql output to shell variable
# 1  
Old 08-17-2010
sql output to shell variable
Hi
I tried searching but I used to see , this has been answered may times.
But I could not find.

I am looking a SQLPLUS output to be assigned to shell variable.

This is what I coded.

Code:
##/bin/ksh
sqlplus dmishr/mishra#09@ps004d.world <<ENDOFSQL
spool abc.log
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select sysdate from dual;
exit
ENDOFSQL`
echo  $res

But I don't get the result in $res. This is a test for me TO Display sysdate using shell variable. I have count(*) from a table which I didn't show it here.
Please someone reply. It is urgent for me. If some one replies with earlier post
please give me a link to that.

Thanks and Regards
# 2  
Old 08-17-2010
Should be like this:
Code:
#!/bin/ksh

res=`sqlplus dmishr/mishra#09@ps004d.world <<ENDOFSQL
spool abc.log
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
select sysdate from dual;
exit
ENDOFSQL`
echo  $res

# 3  
Old 08-17-2010
here is the result .
Code:
res SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 17 02:33:49 2010 Copyright (c) 1982, 2007, Oracle. 
All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 
10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
SQL> SQL> SQL> SQL> 17-AUG-10 SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 
64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

I just want my output to be 17-AUG-10.ca you please tell me how do i get that.
# 4  
Old 08-17-2010
Quote:
Originally Posted by dgmm
here is the result .
Code:
res SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 17 02:33:49 2010 Copyright (c) 1982, 2007, Oracle. 
All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 
10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
SQL> SQL> SQL> SQL> 17-AUG-10 SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 
64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

I just want my output to be 17-AUG-10.ca you please tell me how do i get that.
Hi.

Try using sqlplus -s ...
# 5  
Old 08-24-2010
MySQL
Thank you. It worked.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to Assign the Output of an SQL Query to a Variable?

Hi iam new to shell scripting how to declare variables as redshift query and I have to compare two counts by using if condition . ex:count=select count(*) from prd; select count(*) from prd; select count(*) from tag; can any one help me . Please use CODE tags when displaying... (1 Reply)
Discussion started by: sam526
1 Replies

2. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

3. Shell Programming and Scripting

SQL output to UNIX variable

I have a sql statement , i need to assign to a variable in Unix sel count(*) AS num_files from TABLE_A; i need to use "num_files" in unix statements. let me know how to assign unix variable to above num_files (1 Reply)
Discussion started by: nani1984
1 Replies

4. Shell Programming and Scripting

Redirect output from SQL to unix variable

Hi, I have a requirement to store oracle sqlplus output to some unix variable, count the records and then print the output on the screen. Can you please point me to any sample program for reference purpose. Thanks a lot for your time. (0 Replies)
Discussion started by: bhupinder08
0 Replies

5. Shell Programming and Scripting

Assigning value of SQL output to a variable in shell scripting

I am trying to assgn the output of the select statement to a variable, like this "VARIABLE_NAME=$ db2 "select COLUMN_NAME_1 from TABLE_NAME where COLUMN_NAME_2='VALUE_TO_CHECK'"; " but the value that is getting into VARIABLE_NAME is "COLUMN_NAME_1 ----------------- VALUE 1... (3 Replies)
Discussion started by: sgmini
3 Replies

6. Shell Programming and Scripting

sql output into a variable

i have to do a check in my UNIX script to see whats saved in the database. depending on whats there will fork data to certain functions. However i do not know how to capture SQL output into a UNIX variable. Below is what i have tried, but i get an error: Error 3706 Failure 3706 Syntax error:... (3 Replies)
Discussion started by: purplebirky
3 Replies

7. Shell Programming and Scripting

have a shell script done in pl/sql and want output in html

I have this shell script where I have both pl/sql and sql. But want to have a snigle output file where the result of each cursors are in HTML tables. I was able to do that on my old script but it was only sql scripts (no pl/sql). Can I do have such outputs now with my new script where I... (2 Replies)
Discussion started by: arobert
2 Replies

8. Shell Programming and Scripting

How to store the sql query's output in a variable

Hi, My requirement is : We are calling an sql statement from a UNIX session, and fetching data into some variables from a table .. now we are unable to access these variables from outside the SQL part. Please let me know how can I achieve this. Can you please share a code snippet which... (4 Replies)
Discussion started by: venkatesh_sasi
4 Replies

9. Shell Programming and Scripting

redirecting SQL output from within a shell script

Hi all I would like to open a SQL session from within a shell script and then redirect the output of the SQL command to a file. e.g. #!/bin/bash sqlplus "/ as sysdba" <<EOF @$HOME/recovery_space.sql EOF I want to redirect the output of the SQL command to a temp file, because... (2 Replies)
Discussion started by: soliberus
2 Replies

10. Shell Programming and Scripting

how to assign sql output data to shell script variable

Hi Guys ! I am new to unix and want to find out how we can make sql statement data to shell script variable? Any help/suggestion is greatly appreciated -Chandra (1 Reply)
Discussion started by: kattics
1 Replies
Login or Register to Ask a Question