How to replace variable inside the variable

mani_um · March 21, 2007, 1:16am

hi sir,
i need your help for this script

inside /rnmucdr/ednms05/ken/xMNBDF045_Script.sql content variable like this

select * from invoice where bill_date=$BILLDATE and startNum=$STARTPARTNNUM and total_partn=$TOTALPARTN

if i just paste this replace with the $SCRIPT it works great,if i put script other place with with the variable,and put in the varaible script,it not works because the variable is not subtitute..
can anyone help me to solve this problem,i need to replace it with the parameter given here

ahmedwaseem2000 · March 21, 2007, 1:34am

try replacing $script by cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql

like this

USERID=username/passwd
BILLDATE=19-FEB-07
STARTPARTNNUM=101
TOTALPARTN=20
SQLLOG=${BILLDATE}_xMNBDF045_P_CTEL.log
SCRIPT=`cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql`

sqlplus -s $USERID > $SQLLOG << EOF
WHENEVER SQLERROR EXIT 1
cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql
EOF
if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

anbu23 · March 21, 2007, 1:49am

Add slash

select \* from invoice where bill_date=$BILLDATE and startNum=$STARTPARTNNUM and total_partn=$TOTALPARTN

Replace

SCRIPT=`cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql`

by

SCRIPT=$( eval echo $(cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql))

mani_um · March 21, 2007, 3:20am

syntax error
0403-057 Syntax error at line 1 : `(' is not expected.

for

SCRIPT=$( eval echo $(cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql))

If i do this

echo $SCRIPT

nothing display..only empty line printed

anbu23 · March 21, 2007, 3:25am

can you show the code in /rnmucdr/ednms05/ken/xMNBDF045_Script.sql?

mani_um · March 21, 2007, 3:56am

here is the code

USERID=USER/pass
SCRIPTFILE=/rnmucdr/ednms05/ken/xMNBDF045_Script.sql
BILLDATE=19-FEB-07
STARTPARTNNUM=101
TOTALPARTN=20
SQLLOG=${BILLDATE}_xMNBDF045_P_CTEL.log
echo $SQLLOG
echo $SCRIPTFILE
SCRIPT=$( eval echo $(cat $SCRIPTFILE))

sqlplus -s $USERID > $SQLLOG << EOF
WHENEVER SQLERROR EXIT 1
$SCRIPT
EOF

if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

HERE IS THE SQL STAMENT /rnmucdr/ednms05/ken/xMNBDF045_Script.sql

create table acct_to_print_bak as
select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+$STARTPARTNNUM BP_PARTN_NUM,SYS_APPL_ID FROM (
select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2
,(select ceil(count(*)/$TOTALPARTN) N from inv_acct_bill_addr) T3 where t2.bill_date='$BILLDATE' and t2.bill_date=t1.bill_dat
e and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4;
commit;
truncate table acct_to_print;
insert into acct_to_print (select * from acct_to_print_bak);
commit;
drop table acct_to_print_bak;

Any Idea

ahmedwaseem2000 · March 21, 2007, 4:18am

what's the error you are getting from my code???? And also, remember all the multiple lines will be converted to one line when assignint it to a variable.

mani_um · March 21, 2007, 4:21am

the error is
cat is unknown command

ahmedwaseem2000 · March 21, 2007, 4:26am

have you put this at the begining of the code:

#! /usr/bin/ksh

and try

/usr/bin/cat /rnmucdr/ednms05/ken/xMNBDF045_Script.sql

anbu23 · March 21, 2007, 4:39am

mani_um:

here is the code

USERID=USER/pass
SCRIPTFILE=/rnmucdr/ednms05/ken/xMNBDF045_Script.sql
BILLDATE=19-FEB-07
STARTPARTNNUM=101
TOTALPARTN=20
SQLLOG=${BILLDATE}_xMNBDF045_P_CTEL.log
echo $SQLLOG
echo $SCRIPTFILE
SCRIPT=$( eval echo $(cat $SCRIPTFILE))

sqlplus -s $USERID > $SQLLOG << EOF
WHENEVER SQLERROR EXIT 1
$SCRIPT
EOF

if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

HERE IS THE SQL STAMENT /rnmucdr/ednms05/ken/xMNBDF045_Script.sql

create table acct_to_print_bak as
select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+$STARTPARTNNUM BP_PARTN_NUM,SYS_APPL_ID FROM (
select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2
,(select ceil(count(*)/$TOTALPARTN) N from inv_acct_bill_addr) T3 where t2.bill_date='$BILLDATE' and t2.bill_date=t1.bill_dat
e and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4;
commit;
truncate table acct_to_print;
insert into acct_to_print (select * from acct_to_print_bak);
commit;
drop table acct_to_print_bak;

Any Idea

Sed code adds backslash to * ( )

USERID=USER/pass
SCRIPTFILE=/rnmucdr/ednms05/ken/xMNBDF045_Script.sql
BILLDATE=19-FEB-07
STARTPARTNNUM=101
TOTALPARTN=20
SQLLOG=${BILLDATE}_xMNBDF045_P_CTEL.log

sed 's/\([()\*]\)/\\\1/g' $SCRIPTFILE > tmp
mv tmp $SCRIPTFILE

echo $SQLLOG
echo $SCRIPTFILE
SCRIPT=$( eval echo $(cat $SCRIPTFILE))

sqlplus -s $USERID > $SQLLOG << EOF
WHENEVER SQLERROR EXIT 1
$SCRIPT
EOF

if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

anbu23 · March 21, 2007, 4:44am

This wont work since this is sent as such to sqlplus and you will get the error cat is unknown command.

mani_um · March 21, 2007, 5:25am

anbu23:

Sed code adds backslash to * ( )

USERID=USER/pass
SCRIPTFILE=/rnmucdr/ednms05/ken/xMNBDF045_Script.sql
BILLDATE=19-FEB-07
STARTPARTNNUM=101
TOTALPARTN=20
SQLLOG=${BILLDATE}_xMNBDF045_P_CTEL.log

sed 's/\([()\*]\)/\\\1/g' $SCRIPTFILE > tmp
mv tmp $SCRIPTFILE

echo $SQLLOG
echo $SCRIPTFILE
SCRIPT=$( eval echo $(cat $SCRIPTFILE))

sqlplus -s $USERID > $SQLLOG << EOF
WHENEVER SQLERROR EXIT 1
$SCRIPT
EOF

if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

yeah i done that,when i echo the variable $SCRIPT only this quote display

create table acct_to_print_bak as select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+101 BP_PARTN_NUM,SYS_APPL_ID FROM ( select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2 ,(select ceil(count(*)/20) N from inv_acct_bill_addr) T3 where t2.bill_date=$BILLDATE and t2.bill_date=t1.bill_date and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4

other variable changed but $BILL_DATE not change, i think its maybe because $BILL_DATE is writen as '$BILL_DATE',if i put \'$BILL_DATE\',then can view the bill_date but i dunno how to manipulate the sed command ...

Another main thing is its only display that other SQL statment not display such as

commit;
truncate table acct_to_print;
insert into acct_to_print (select * from acct_to_print_bak);
commit;
drop table acct_to_print_bak;

After i echo the script it only show till T4 and not the semicolon also
so how can i do it plz help me
thanks for ur kind help till now

mani_um · March 21, 2007, 6:40am

Please Help Me sir with above problem
thanks in advance

ahmedwaseem2000 · March 21, 2007, 6:58am

try this with co-process

I havent tested the code as i dont have access to oracle

anbu23 · March 21, 2007, 7:14am

try this

sed -e "s/'/\\\'/g" -e 's/\([();*]\)/\\\1/g' $SCRIPTFILE > tmp
mv tmp $SCRIPTFILE

I am able to see all the sql statements.

ahmedwaseem2000 · March 21, 2007, 8:06am

I have tested this code and i working:

USERID=XXXXXX
SQLLOG=sqllog
SCRIPT=`cat sql`
value=`sqlplus -silent $USERID > $SQLLOG <<END
WHENEVER SQLERROR EXIT 1
$SCRIPT
END`
if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

mani_um · March 21, 2007, 9:03am

It says invalid semicolon
i need somecommand to enter after each line
if i type in straight it works but now it reads at one line
Here is the error

create table acct_to_print_bak as select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+101 BP_PARTN_NUM,SYS_APPL_ID FROM ( select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2 ,(select ceil(count(*)/20) N from inv_acct_bill_addr) T3 where t2.bill_date='19-FEB-07' and t2.bill_date=t1.bill_date and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4; commit; truncate table acct_to_print; insert into acct_to_print (select * from acct_to_print_bak); commit; drop table acct_to_print_bak
                                                                                                                                                                                                                                                                                                                                                                                                                                         *
ERROR at line 1:
ORA-00911: invalid character

ahmedwaseem2000 · March 21, 2007, 1:18pm

Now try with cat command:

USERID=XXXXXX
SQLLOG=sqllog
SCRIPT=`cat sql`
value=`sqlplus -silent $USERID > $SQLLOG <<END
WHENEVER SQLERROR EXIT 1
cat sql
END`
if [ $? -ne 0 ]
then
cat $SQLLOG
else
echo "SUCCESSFULLY FINISHED" > $SQLLOG
fi

mani_um · March 21, 2007, 10:32pm

Hi sir,i think the problem will solve if the variable keeps with new line,now it displays as one sentence for this

SCRIPT=$( eval echo $(cat $SCRIPTFILE))

and output
all in one sentences

if after each semi colon followed by newline then problem solve
expected output like this

create table acct_to_print_bak as
select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+$STARTPARTNNUM BP_PARTN_NUM,SYS_APPL_ID FROM (
select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2
,(select ceil(count(*)/$TOTALPARTN) N from inv_acct_bill_addr) T3 where t2.bill_date='$BILLDATE' and t2.bill_date=t1.bill_dat
e and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4;
commit;
truncate table acct_to_print;
insert into acct_to_print (select * from acct_to_print_bak);
commit;
drop table acct_to_print_bak;

if you use your code all will join as one sentences and the

select * from acct_to_print_bak

The * for above code will replace with all file names tht exist the the path for example like

select abc.txt fileA.cpp from acct_to_print_bak

other * work fine maybe its because printed in one line
plz help me

anbu23 · March 22, 2007, 3:44am

$ x=$( eval echo $(cat file))
$ echo $x
create table acct_to_print_bak as select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+101 BP_PARTN_NUM,SYS_APPL_ID FROM ( select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2 ,(select ceil(count(*)/20) N from inv_acct_bill_addr) T3 where t2.bill_date='19-FEB-07' and t2.bill_date=t1.bill_dat e and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4; commit; truncate table acct_to_print; insert into acct_to_print (select 01.xxxx 01.xxxx.Z 02.xxx.Z 90, a anbu b f f.s f.st f1 f1.s f2 ff ff.st file newfile oldfile s temp tmp from acct_to_print_bak); commit; drop table acct_to_print_bak;
$ echo "$x"
create table acct_to_print_bak as select BILL_DATE,ACCT_NO,STATUS_CODE,floor((ROWNUM-1)/N)+101 BP_PARTN_NUM,SYS_APPL_ID FROM ( select T1.BILL_DATE,T1.ACCT_NO,T1.STATUS_CODE,T1.BP_PARTN_NUM,T1.SYS_APPL_ID,T3.N from acct_to_print t1,inv_acct_bill_addr T2 ,(select ceil(count(*)/20) N from inv_acct_bill_addr) T3 where t2.bill_date='19-FEB-07' and t2.bill_date=t1.bill_dat e and t1.ACCT_NO=t2.ACCT_NO order by T2.postal_code ) T4; commit; truncate table acct_to_print; insert into acct_to_print (select * from acct_to_print_bak); commit; drop table acct_to_print_bak;

If you use quotes around the variable then it wont expand to all the files in that directory. But you dont need to worry about that. This expansion wont occur when you send variable to sqlplus.

To add newline after each semicolon

SCRIPT=$( eval echo $(cat ff) | sed "s/;/;\\
/g")