Home » Infrastructure » Unix » assign sql output to unix shell variable
assign sql output to unix shell variable [message #135105] Mon, 29 August 2005 23:39 Go to next message
contactrg
Messages: 1
Registered: August 2005
Junior Member
Hi,

I am tryingto assign sql query output to unix shell variable. When it assigns spaces are getting added.

query look like this.

cp ../ctl/ABC.ctl abc.temp
set -x
v_file_name='Siebel_daily_activities_20050822.csv'
v_file_date=`sqlplus -s abc/abc <<++
set heading off
set feedback off
select trim(to_date(substr('${v_file_name}',instr('${v_file_name}','_',-1)+1,Cool,'RRRRMMDD')) from dual;
++`
sed -e "s/#/$v_file_date/" abc.temp > temp1.ctl

the output is:

v_file_date=
22-AUG-05

sed: command grabled.

Please tell me how to get output v_file_date=22-AUG-05
Re: assign sql output to unix shell variable [message #135149 is a reply to message #135105] Tue, 30 August 2005 02:56 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
Remove the newlines with something like this:

v_file_date=`echo $v_file_date | sed 's/\n//g'


Best regards.

Frank
Re: assign sql output to unix shell variable [message #135546 is a reply to message #135149] Thu, 01 September 2005 03:23 Go to previous messageGo to next message
tarundua
Messages: 1077
Registered: June 2005
Location: India
Senior Member


My requirement it opposite , but thought 2 use the same thread cos its similar.

I have a shell script which takes 4 arguments :

1. username/passwd
2. SQL file name
3. Output file name
4. Arguments for SQL file

for eg :

$ Sqlusage scott/tiger file1.sql file_out.log emp


Now ,
The sql file can look like this --
Quote:


spool &&1
select * from &&2



now i want to know , how do i replace &&1 with file_out.log and &&2 with emp (table name) , so that my spool file file_out.log can contain output from the query
" select * from emp"

Or i should say how do i pass arguments from a shell script to a SQL file.
Hope the problem is clear.

thanks,
tarun
Re: assign sql output to unix shell variable [message #135552 is a reply to message #135546] Thu, 01 September 2005 03:34 Go to previous messageGo to next message
tarundua
Messages: 1077
Registered: June 2005
Location: India
Senior Member


Hi i got the solution but please suggest me. Is it the appropriate way to do it like this ?

$ set file_out.log emp
$ sqlplus scott/tiger << EOF
define 1=$1
define 2=$2
@$HOME/file1.sql
EOF

Now i got the answer. Is there any other way?

regards,
tarun

Re: assign sql output to unix shell variable [message #135568 is a reply to message #135552] Thu, 01 September 2005 05:00 Go to previous messageGo to next message
tarundua
Messages: 1077
Registered: June 2005
Location: India
Senior Member


I got the real command , it wil actually work for me.

$ sqlplus Login_Str SQL_Script SQL_Parms


Issue is over from my side.

Re: assign sql output to unix shell variable [message #260617 is a reply to message #135105] Mon, 20 August 2007 08:21 Go to previous messageGo to next message
xaris78
Messages: 2
Registered: August 2007
Location: Athens, Greece
Junior Member
Hello guys!!

The above posts where very helpful for an issue I'm dealing with.

Now I'm trying to pass a variable's value into this code:

rptFileName="J001"

UID="test"
PSWD="test"
SID="testdb"


seq_num=`sqlplus -s $UID/$PSWD@$SID <<++
	set heading off
	set feedback off
	select LPAD(LTRIM(last_number,' '), 3, '0') from dba_sequences where sequence_name = '"$rptFileName"_SEQ';
   exit;
++`

The variable I'm trying to pass is rptFileName. This code returns no rows. If I hardcode it like "... where sequence_name='J001_SEQ';" it works just fine.

Any ideas??

Re: assign sql output to unix shell variable [message #260627 is a reply to message #260617] Mon, 20 August 2007 08:59 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

use
"$rptFileName"
as
"${rptFileName}"
and it will work fine.

[Updated on: Mon, 20 August 2007 08:59]

Report message to a moderator

Re: assign sql output to unix shell variable [message #260629 is a reply to message #260627] Mon, 20 August 2007 09:16 Go to previous message
xaris78
Messages: 2
Registered: August 2007
Location: Athens, Greece
Junior Member
sanka_yanka wrote on Mon, 20 August 2007 08:59
use
"$rptFileName"
as
"${rptFileName}"
and it will work fine.

Thank you sanka_yanka!! That was a very helpfull post.

The correct syntax is:
select LPAD(LTRIM(last_number,' '), 3, '0') from dba_sequences where sequence_name = '${rptFileName}_SEQ';

I also had to omit the double quotes. Thank you!!!
Previous Topic: syncsort
Next Topic: substracting/change date value
Goto Forum:
  


Current Time: Sat Dec 20 22:11:24 CST 2014

Total time taken to generate the page: 0.07239 seconds