Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Sqlplus variable UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Sqlplus variable UNIX
# 1  
Old 10-10-2017
Sqlplus variable UNIX
hi guys
i have a sqlplus :

Code:
sqlplus -s  username/password << EOF
@mysql.sql
EOF

in mysql.sql there is a count of a table, i want to write in a variabile unix.
how can i do?
Thanks a lot
Regards
Francesco.
# 2  
Old 10-10-2017
You could try:
Code:
somevar=$(sqlplus ...
EOF)

You'll need some directives in your SQL to throw away everything in the output except the value you're looking for.

SQL is / was a bit funny with file descriptors if I remember, and it's been a while since I've used it, so can't guarantee that will work.
# 3  
Old 10-10-2017
Could i suggest that you get your credentials moved too?

Something more like:-
Code:
somevar=$(sqlplus <<EOSQL
username/password
@my.sql
EOSQL)

This will, of course, take ALL the output fromt he sqlplus command, so you might need to think about that. Perhaps add the -s flag to suppress much of the informational output might help you there.



Robin
# 4  
Old 10-10-2017
For a task like that the file descriptors shouldn't be a problem.
I do not see why a heredoc is needed, sqlplus can process a sql-commandfile if told so:
Code:
$ a=$(sqlplus -s /nolog @a.sql)
$ echo "$a"

10-OCT-17
$ cat a.sql
set heading off
connect / as sysdba;
select sysdate from dual;
exit

Using bash on Debian 9, Oracle XE.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pass a VARIABLE to sqlplus script

Hi Team, I am trying to run a sqlplus script against several databases via a FOR/LOOP and also passing the loop variable to a sqlplus script I am calling, as follows: #!/bin/bash export ORACLE_SID=plgc1 export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1 export... (1 Reply)
Discussion started by: jonnyd
1 Replies

2. Shell Programming and Scripting

Scirpt to fetch the variable from sqlplus

Hi Gurus, I am stuck with the step where i need to fetch the location & sales from the below procedure by taking it from table field using the for loop. any idea how this can be done in unix. From one column both the location and sales are taken out. create or replace procedure newyork... (2 Replies)
Discussion started by: arun888
2 Replies

3. Shell Programming and Scripting

How to print huge values in sqlplus variable in UNIX?

Hi, I ahve a unix code as below. sqlTxt=$* sqlReturn=`echo "set feedback off; set heading off; set term off; set verify off; set serveroutput on size unlimited set lin 1000; VARIABLE GV_return_val NUMBER; VARIABLE GV_script_error varchar2(4000); EXEC :GV_return_code := 0; EXEC... (6 Replies)
Discussion started by: bhaski2012
6 Replies

4. Shell Programming and Scripting

awk with variable from sqlplus

Hi, I'm a it stuck on the below code where a variable is pulled from sqlplus and used in awk. It runs with no errors but still pulls back all records in the input file. It should pull the max reference from sql plus and then only print those records where the reference value in column 1 is... (4 Replies)
Discussion started by: jonathanb30
4 Replies

5. Shell Programming and Scripting

store sqlplus output in variable

hi how can i store sqlplus output to a variable in sh script (not bash) Thanks MM (1 Reply)
Discussion started by: murtymvvs
1 Replies

6. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 Replies

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

8. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

9. Shell Programming and Scripting

Using a variable in sqlplus

Hello, I'm trying to write a script that will loop a sql statement through an external list. Basically, the script enters a loop and runs the sql statement for each entry in the list file. Currently, the script will stop at a cursor where I can then manually enter the SQL statment. This is... (11 Replies)
Discussion started by: MadHatter
11 Replies

10. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies
Login or Register to Ask a Question