Can anyone find the mistake in this script file

#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
sqlplus -s $db_user/$db_pwd@$db_sid << EOF
a = select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094'
set -- echo $a | sed 's,[aA-zZ]*,,1'
Run -i $1 -e $2
EOF

:wall::wall::wall:

It would be helpful if you could also tell us what error you're getting, or what doesn't work as expected and what you're expecting.

What error are you getting?

The error am getting while executing the script is lyk this :

SP2-0158: unknown SET option "--"
  1* = select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094'
= select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094'
*
ERROR at line 1:
ORA-24374: define not done before fetch or execute and fetch

change your sed with like below and re-try

sed 's,[Aa-zZ]*,,1'

Might be the below one will help you ..

a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094';
EOF`
echo $a | sed 's,[aA-zZ]*,,1'
1 Like

Again the same error mentioned above...no change in error description..:frowning:

---------- Post updated at 04:05 AM ---------- Previous update was at 03:57 AM ----------

I ran with the changes you have mentioned but the arguments are passing as null..
The values are not passed to the command

#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094';
EOF`
set -- echo $a | sed 's,[aA-zZ]*,,1'
echo  Run -i $1 -e $2

o/p:

Run -i -e

what must be $1 and $2 ?
and what is your goal with this?

a = select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094'

this is invalid expression if you want to assign value to any variable.
you must use this way with double quotes

a=" select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094'"

Hi,
I ran the script but unfortunately the o/p is not correct..instead of the column values to be passed as arguments...it is passing the column names..
o/p from sql :

 
    ACK_PARTY_NAME               BUS_EVENT_SEQ_NBR 
  MOVE_USAGE_DAEMON1                3969413 

ran the script:

#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and bus_event_seq_nbr='3969413';
EOF`
set -- $(echo $a | sed 's,[aA-zZ]*,,1')
echo Run -i $1 -e $2

and the o/p is :

Run -i BUS_EVENT_SEQ_NBR -e ----------------------------------------

required o/p is :

Run -i 1 -e 3969413

---------- Post updated at 04:27 AM ---------- Previous update was at 04:23 AM ----------

The output values of the above select query will be like:
Move_Usage_Daemon1 , 3969413

and I should pass 1(digit appended at the end of frst field) and other variable to command.
It will be like:

Run -i 1 -e 3969413

can you write the output of this?

.............
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and bus_event_seq_nbr='3969413';
EOF`
echo "$a"

yeah I ran it..

O/p is :
ACK_PARTY_NAME BUS_EVENT_SEQ_NBR
---------------------------------------- -----------------
MOVE_USAGE_DAEMON1 3969413

---------- Post updated at 05:51 AM ---------- Previous update was at 05:35 AM ----------

The query result is displayed

try this instead of your sed

set -- $(echo $a|sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/')

Got the same output

script I ran is :

#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select ACK_PARTY_NAME,bus_event_seq_nbr from bus_event where ack_party_name like 'MOVE_USAGE_DAEMON%' and bus_event_seq_nbr='3969413';
EOF`
set -- $(echo $a|sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/')
echo "$a"

:wall:

did you try this? what is the output? is same?

#!/bin/ksh
db_user=`echo $DB_USER_NAME`
db_pwd=`echo $DB_PASSWORD`
db_sid=`echo $TWO_TASK`
a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094';
EOF`
set -- $(echo $a|sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/')
echo Run -i $1 -e $2

ygemici code is working for me .. R u on the right track ..?

$ echo $a
ACK_PARTY_NAME BUS_EVENT_SEQ_NBR ---------------------------------------- ----------------- MOVE_USAGE_DAEMON1 3969413
$ echo $a | sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/'
1 3969413

yes @jayan_jay,it also must work at @rkrish script

1 Like

try this one

a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
set pagesize 0 feedback off verify off heading off echo off
select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094';
exit;
EOF`

echo $a | sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/'

1 Like

It worked with the below code:

a=`sqlplus -s $db_user/$db_pwd@$db_sid << EOF
set pagesize 0 feedback off verify off heading off echo off
select   ACK_PARTY_NAME,bus_event_seq_nbr  from bus_event where  ack_party_name like 'MOVE_USAGE_DAEMON%'  and bus_event_seq_nbr='3969094';
exit;
EOF`
echo $a | sed 's/.*\([^ ]1*\) \([^ ]*\)/\1 \2/'

Thanks for the effort :slight_smile: