How to pass Oracle sql script as argument to UNIX shell script?

Shell Programming and Scripting
1

Hi all,

$ echo $SHELL
/bin/bash

Requirement - How to pass oracle sql script as argument to unix shell script?

$ ./output.sh users.sql

Below are the shell scripts and the oracle sql file in the same folder.

Shell Script

$ cat output.sh
#!/bin/bash
. /test1/product/r12/TEST1/apps/apps_st/appl/APPSTEST1_xyz.env

/test1/product/r12/TEST1/apps/tech_st/10.1.2/bin/sqlplus apps/apps@TEST1  <<-EOF

spool fnd_user_output.xls
@users.sql
spool off
exit;
EOF

Oracle Sql script

cat users.sql
EXPLAIN PLAN SET STATEMENT_ID='TIM1' FOR
select q.concurrent_queue_name || ' - ' || target_node qname
     ,a.request_id "Req Id"
     ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
     ,a.concurrent_program_id "Prg Id"
     ,a.phase_code,a.status_code
     ,b.os_process_id "OS"
     ,vs.sid
     ,vp.spid
     ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
     ,c.concurrent_program_name||' - '||
      c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
   ,applsys.fnd_concurrent_queues q
   ,APPLSYS.fnd_concurrent_programs_tl c2
   ,APPLSYS.fnd_concurrent_programs c
   ,sys.v$session vs
   ,sys.v$process vp
where a.controlling_manager = b.concurrent_process_id
 and a.concurrent_program_id = c.concurrent_program_id
 and a.program_application_id = c.application_id
 and c2.concurrent_program_id = c.concurrent_program_id
 and a.phase_code in ('I','P','R','T')
 and b.queue_application_id = q.application_id
 and b.concurrent_queue_id = q.concurrent_queue_id
 and c2.language = 'US'
 and vs.process (+) = b.os_process_id
 and vs.paddr = vp.addr (+)
order by 1,2
/


SET LINESIZE 130
set feedback off verify off heading off pagesize 0
SELECT * FROM  TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TIM1','ADVANCED'));

Thank You for your time!

Regards,

2

If I correctly understand what you're trying to do, try changing the line:

@users.sql

in output.sh to:

$(cat users.sql)
1 Like
3

Thanks Don.

But is there a way to pass the file users.sql as an argument to the shell script for execution.

Regards,

4

Careful quoting.

1 Like
5

---------- Post updated at 02:40 PM ---------- Previous update was at 02:38 PM ----------

Hi,

I think I got it

./output.sh users.sql

$(cat $1)

Thank You Don for your help. This meets my requirement.

Regards,

6

Are you sure? Did you try to pass another sql script? I think it works now because you still have users.sql hardcoded:

#!/bin/bash
. /test1/product/r12/TEST1/apps/apps_st/appl/APPSTEST1_xyz.env

/test1/product/r12/TEST1/apps/tech_st/10.1.2/bin/sqlplus apps/apps@TEST1  <<-EOF

spool fnd_user_output.xls
@users.sql
spool off
exit;
EOF

This line probably needs to be changed to @$1 .

1 Like
7

Yes. You are right Junior-Helper.

I changed the line @users.sql to @$1 and it works!!
8

Oh, how does stdin work in the shell? Not an Oracle question! I prefer something like this:

#!/bin/bash
. /test1/product/r12/TEST1/apps/apps_st/appl/APPSTEST1_xyz.env
 
PATH=$PATH:/test1/product/r12/TEST1/apps/tech_st/10.1.2/bin
 
echo '
spool fnd_user_output.xls
@users.sql
spool off
' | sqlplus apps/apps@TEST1