Shell - running queries as a different user

Hi,

Im running the script as root user but I need this command to be issued as dpa user....



query1=$(sudo -H -u dpa bash -c 'cd; . .profile; sqlplus -s xxx/xxx@xxx;
       set pagesize 0 feedback off verify off heading off echo off linesize 30000;
       select A||' '||alarm||' '||ALARM_CURRENT||' '||alarm_text from alarm order by alarm;
       exit;
')
echo "$query1" | awk -v W1=20 -v W2=20 -v W3=20 -v W4=50 -v LC="=" 'BEGIN {FORMAT="%-"W1"s%-"W2"s%-"W3"s%-"W4"s\n"; printf FORMAT,"Instance","Date Raised","Severity","Description"; while (L++<W1+W2+W3+W4) R=R LC; print R} !/^Unable/ {printf FORMAT,$1,$2,$3,$4}'

But its hanging like it connects to sqlplus and does run the following commands....(the sets and the query).

So is this the right syntax for this kind of job?

Im suppose to get this output from the query:

sfm1a 29-APR-15 3 TS_EVENT_SEND_DISABLE from Transport Server
wig1a 29-APR-15 3 Host: demo-app-01 Name: ts1a
mdm1a 29-APR-15 3 TS server load status
.
.

And it would be transferred to the table format as for the last command in the script.

Instance            Date Raised         Severity            Description                                      
==============================================================================================================
sfm1a                29-APR-15                3                      TS_EVENT_SEND_DISABLE from Transport Server


Tks,
Joao
joaotellesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

woolmilkporcCommented:
You''ll have to use the "here document" method to let sqlplus know which commands it should execute:

query1=$(sudo -H -u dpa bash -c 'cd; . .profile;
sqlplus -s xxx/xxx@xxx <<EOF
       set pagesize 0 feedback off verify off heading off echo off linesize 30000;
       select A||' '||alarm||' '||ALARM_CURRENT||' '||alarm_text from alarm order by alarm;
       exit;
EOF
')

PS: I didn't check the sqlplus syntax nor the awk syntax yet. Let's first get the heredoc to work.
joaotellesAuthor Commented:
The command is not returning any data...

++ sudo -H -u dpa bash -c 'cd; . .profile; sqlplus -s xxxx/xxx@xxxx<<EOF
       set pagesize 0 feedback off verify off heading off echo off linesize 30000;
       select A||' '||alarm||' '||ALARM_CURRENT||' '||alarm_text from alarm order by alarm;
       exit;
EOF
'
+ query1=
+ echo ''

====

If I run the commands manually it returns (as dpa), so the query is correct:

SQL> set pagesize 0 feedback off verify off heading off echo off linesize 30000;
SQL> select A||' '||alarm||' '||ALARM_CURRENT||' '||alarm_text from alarm order by alarm;
sfm1a 29-APR-15 3 TS_EVENT_SEND_DISABLE from Transport Server
wig1a 29-APR-15 3 Host: demo-app-01 Name: ts1a
.
.

Tks,
Joao
woolmilkporcCommented:
I'm rather sure that this is because of the embedded single quotation marks.
Why do you need the concatenation? You have set a huge linesize, so

 select A, alarm, ALARM_CURRENT, alarm_text from alarm order by alarm;

should do!

You could also reduce the column width, e.g.:

column A format A10;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
woolmilkporcCommented:
Once you got the query running and the variable filled our awk script must be changed a bit:

echo "$query1" | awk -v W1=10 -v W2=14 -v W3=10 -v W4=50 -v LC="="   '
  BEGIN {FORMAT="%-"W1"s%-"W2"s%-"W3"s%-"W4"s\n";
   printf FORMAT,"Instance","Date Raised","Severity","Description";
   while (L++<W1+W2+W3+W4) R=R LC; print R}
  {E=$4; for(n=5;n<=NF;n++) E=E" "$n; printf FORMAT,$1,$2,$3,E} '
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.