OraFAQ Forum: Unix » assign sql output to unix shell variable

Home » Infrastructure » Unix » assign sql output to unix shell variable

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

assign sql output to unix shell variable [message #135105]

Mon, 29 August 2005 23:39

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

Report message to a moderator

Re: assign sql output to unix shell variable [message #135149 is a reply to message #135105]

Tue, 30 August 2005 02:56

Frank Naude
Messages: 4581
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

Report message to a moderator

Re: assign sql output to unix shell variable [message #135546 is a reply to message #135149]

Thu, 01 September 2005 03:23

tarundua
Messages: 1080
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

Report message to a moderator

Re: assign sql output to unix shell variable [message #135552 is a reply to message #135546]

Thu, 01 September 2005 03:34

tarundua
Messages: 1080
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

Report message to a moderator

Re: assign sql output to unix shell variable [message #135568 is a reply to message #135552]

Thu, 01 September 2005 05:00

tarundua
Messages: 1080
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.

Report message to a moderator

Re: assign sql output to unix shell variable [message #260617 is a reply to message #135105]

Mon, 20 August 2007 08:21

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

Report message to a moderator

Re: assign sql output to unix shell variable [message #260627 is a reply to message #260617]

Mon, 20 August 2007 08:59

sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata

Senior Member

use

"$rptFileName"

"${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

xaris78
Messages: 2
Registered: August 2007
Location: Athens, Greece

Junior Member

sanka_yanka wrote on Mon, 20 August 2007 08:59

use

"$rptFileName"

"${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!!!

Report message to a moderator

Previous Topic:	syncsort
Next Topic:	substracting/change date value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 22:51:20 CST 2024