Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Passing PL/SQL variable value to Shell Varible

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing PL/SQL variable value to Shell Varible
# 8  
Old 01-30-2007
Radoulov,

Your code is OK if I use only one variable. What the case for my below script?
== Uncoted $var1

printf "%s\n" "set pages 0 serverout on feed off" \
"select ENAME from emp;" \
"select sysdate from dual;" \
"select min(sal) from emp;" \
"select max(sal) from emp;" \
"select avg(sal) from emp;" \
| sqlplus -s scott/tiger \
| { read var1; read var2; read var3; read var4; read var5;
}
echo "Employee Name is" $var1
echo "Date is $var2"
echo "Minimum of Salary is $var3"
echo "Maximum of Salary is $var4"
echo "Average of Salary is $var5"

Result is as below:
Employee Name is SMITH
Date is ALLEN
Minimum of Salary is WARD
Maximum of Salary is JONES
Average of Salary is MARTIN


== For coted $var1

printf "%s\n" "set pages 0 serverout on feed off" \
"select ENAME from emp;" \
"select sysdate from dual;" \
"select min(sal) from emp;" \
"select max(sal) from emp;" \
"select avg(sal) from emp;" \
| sqlplus -s scott/tiger \
| { read var1; read var2; read var3; read var4; read var5;
}
echo "Employee Name is $var1"
echo "Date is $var2"
echo "Minimum of Salary is $var3"
echo "Maximum of Salary is $var4"
echo "Average of Salary is $var5"

Result is same as previous case (which is incorrect):
Employee Name is SMITH
Date is ALLEN
Minimum of Salary is WARD
Maximum of Salary is JONES
Average of Salary is MARTIN

Result which Iam expecting is:
Employee Name is SMITH ALLEN WARD JONES MARTIN BLAKE CLARK
Date is 30-JAN-07
Minimum of Salary is 12000
Maximum of Salary is 99999
Average of Salary is 24571.2857


Please help me again with your knowledge.

With Regards,
Ganapati Smilie
# 9  
Old 01-30-2007
What's wrong with this:

Code:
var1="$(printf "%s \n" "set pages 0 feed off" \
"select ename from emp;"|sqlplus -s scott/tiger)"; \
printf "%s;\n" "set pages 0 serverout on feed off" \
"select sysdate from dual" \
"select min(sal) from emp" \
"select max(sal) from emp" \
"select avg(sal) from emp" \
| sqlplus -s scott/tiger  |{ read var2; read var3; read var4; read var5;
echo "Employees' Names Are: " $var1
echo "Date is $var2"
echo "Minimum of Salary is $var3"
echo "Maximum of Salary is $var4"
echo "Average of Salary is $var5"
}

For example:

Code:
$ var1="$(printf "%s \n" "set pages 0 feed off" \
> "select ename from emp;"|sqlplus -s scott/tiger)"; \
> printf "%s;\n" "set pages 0 serverout on feed off" \
> "select sysdate from dual" \
> "select min(sal) from emp" \
> "select max(sal) from emp" \
> "select avg(sal) from emp" \
> | sqlplus -s scott/tiger  |{ read var2; read var3; read var4; read var5;
> echo "Employees' Names Are: " $var1
> echo "Date is $var2"
> echo "Minimum of Salary is $var3"
> echo "Maximum of Salary is $var4"
> echo "Average of Salary is $var5"
> }
Employees' Names Are:  SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
Date is 30-JAN-07
Minimum of Salary is 800
Maximum of Salary is 5000
Average of Salary is 2073.21429

# 10  
Old 01-30-2007
With one connection:

Code:
{ printf "set pages 0 feed off\n";
  printf "select %s from %s;\n" \
  "ename||' \ '" "emp" \
  "'
'||sysdate" "dual" \
  "min(sal),max(sal)||'
'||round(avg(sal),2)" "emp" 
} | sqlplus -s scott/tiger \
| { read var1; read var2; read; read var3; read var4; read var5;
printf "%s\n" "Employee Name is $var1" \
"Date is $var2" \
"Minimum of Salary is $var3" \
"Maximum of Salary is $var4" \
"Average of Salary is $var5"
}

But I repeat: it's OK only if you're doing it for learning purposes.
Writing a report like this is quite trivial and straightforward in sql.
Last edited by radoulov; 01-30-2007 at 05:24 PM..
# 11  
Old 01-31-2007
Bug
Great help to enhance my knowledge from you 'radoulov'.
I never forget your help. Pls keep in touch.

With Love and Regards,
Ganapati. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing variable from file to sql from script

Hi Friend, I have one file in which some number are mentioned and number of lines are vary every time And i need to pass that number to my sql command from script. Suppose i have file acc.txt 45456546456 45464564565 67854353454 67657612132 Number of records are vary every time.... (20 Replies)
Discussion started by: pallvi_mahajan
20 Replies

2. Shell Programming and Scripting

Passing variable to sql

How to pass variable to sql file. Im tryin in two ways, Method 1: my.sql select * from table where col1 = '$1' and col2 = 'text'; Method 1execute: dbaccess database my.sql $var Method2: select * from table col1 in (`cat inputfile`) and col2 = 'text'; method... (2 Replies)
Discussion started by: Roozo
2 Replies

3. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

4. Shell Programming and Scripting

Passing a string variable from Unix to Sql Plus

Hi Guys, I am trying to pass a string variable from Unix shell script to sqlplus as a parameter. I have tried using single quotes with the variable name but it does not work. Please help me with it. I am using BASH. My code: Your help is much appreciated. Thanks, shil (2 Replies)
Discussion started by: infintenumbers
2 Replies

5. Shell Programming and Scripting

Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Hi all, I am executing shell script in which I am using SQLLDR In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following Spool... (2 Replies)
Discussion started by: shekharjchandra
2 Replies

6. UNIX for Advanced & Expert Users

Passing Hash variable in to sql query in perl

Hi Everyone, Can anyone help me how do i call hash variable in to sql query in perl. Please see the script below i have defined two Hash %lc and %tab as below $lc{'REFF'}='V_RES_CLASS'; $lc{'CALE'}='V_CAP_CLASS'; $lc{'XRPD'}='V_XFMR_CLASS'; $tab{'V_RES_CLASS'}='V_MFR_SERS';... (6 Replies)
Discussion started by: jam_prasanna
6 Replies

7. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

8. Shell Programming and Scripting

Passing argumnets from shell script to sql

hi I all , I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment example : runsql.sh "1" "2" sql : updat tables_x set y=0 where A=:x should subsituted by "1" and B=:y shuold subsituted bt "2"... (1 Reply)
Discussion started by: habuzahra
1 Replies

9. UNIX for Advanced & Expert Users

passing value to a variable in a SQL

Hi Folks, This is a small chunk of the bigger problem which i am facing and some help here will help me resolve the rest of the issue. Problem is that i need to pass the value of a variable from a shell script to a SQL query (infact a lot of SQL's) i have the following solution but somehow... (4 Replies)
Discussion started by: kamitsin
4 Replies

10. Linux

Passing variables to sql from batch shell in linux

Hi, I need to put this command in a batch shell. sqlplus -s user/password @test.sql and in the test.sql I have this command select * from pbempl where pebempl_id = $1; How I can pass the variable $1 from the batch shell??? Thanks (2 Replies)
Discussion started by: rama71
2 Replies
Login or Register to Ask a Question