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

Login or Register to Ask a Question and Join Our Community

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

Tags

here document, shell scripts, solved

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to pass Oracle sql script as argument to UNIX shell script?

08-20-2014

Registered User

88, 1

Join Date: Apr 2005

Last Activity: 13 July 2019, 4:11 PM EDT

Posts: 88

Thanks Given: 3

Thanked 1 Time in 1 Post

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

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.

Code:

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

Code:

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,

a1_win

View Public Profile for a1_win

Find all posts by a1_win

08-20-2014

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

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

Code:

@users.sql

in output.sh to:

Code:

$(cat users.sql)

This User Gave Thanks to Don Cragun For This Post:

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

08-20-2014

Registered User

88, 1

Join Date: Apr 2005

Last Activity: 13 July 2019, 4:11 PM EDT

Posts: 88

Thanks Given: 3

Thanked 1 Time in 1 Post

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

Thanks Don.

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

Regards,

a1_win

View Public Profile for a1_win

Find all posts by a1_win

08-20-2014

Registered User

4,673, 588

Join Date: Oct 2010

Last Activity: 1 February 2016, 3:35 PM EST

Location: Southern NJ, USA (Nord)

Posts: 4,673

Thanks Given: 8

Thanked 588 Times in 561 Posts

Careful quoting.

This User Gave Thanks to DGPickett For This Post:

DGPickett

View Public Profile for DGPickett

Find all posts by DGPickett

08-20-2014

Registered User

88, 1

Join Date: Apr 2005

Last Activity: 13 July 2019, 4:11 PM EDT

Posts: 88

Thanks Given: 3

Thanked 1 Time in 1 Post

Quote:

Originally Posted by a1_win

Thanks Don.

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

Regards,

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

Hi,

I think I got it

Code:

./output.sh users.sql

Code:

$(cat $1)

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

Regards,

a1_win

View Public Profile for a1_win

Find all posts by a1_win

08-20-2014

Registered User

344, 126

Join Date: Aug 2014

Last Activity: 28 June 2017, 4:04 PM EDT

Posts: 344

Thanks Given: 37

Thanked 126 Times in 114 Posts

Quote:

Originally Posted by a1_win

I think I got it

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

Code:

#!/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.

This User Gave Thanks to junior-helper For This Post:

junior-helper

View Public Profile for junior-helper

Find all posts by junior-helper

08-21-2014

Registered User

88, 1

Join Date: Apr 2005

Last Activity: 13 July 2019, 4:11 PM EDT

Posts: 88

Thanks Given: 3

Thanked 1 Time in 1 Post

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

Yes. You are right Junior-Helper.

Code:

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

a1_win

View Public Profile for a1_win

Find all posts by a1_win

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file s1.sh ...

2. UNIX for Dummies Questions & Answers

How to pass command line argument in shell script?

I need to write a shell script, when I run that script I should pass those arguments if not, then script should not run and pass the error message like invalid option - - should pass the argument. and Exit from the script

3. Shell Programming and Scripting

How to pass command line argument in shell script?

I need to write a shell script, when I run that script I should pass those arguments if not, then script should not run and pass the error message like invalid option - - should pass the argument. and Exit from the script https://www.unix.com/images/misc/progress.gif

4. UNIX for Advanced & Expert Users

Use of Oracle pl/sql in UNIX shell script

Hi, I have basic knowledge on how to write pl/sql code inside shell script. I am looking for more advance thing. Is there any book for that which can just talk about how to write more advance plsql code inside shell script. Please help Thanks!!!!!!

5. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off...

6. Shell Programming and Scripting

How we can pass the argument when calling shell script from perl script

Can someone let me know how could I achieve this In one of per script I am calling the shell script but I need to so one thing that is one shell script call I need to pass pne argument.In below code I am calling my ftp script but here I want to pass one argument so how could I do this (e.g:...

7. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script...

8. UNIX for Advanced & Expert Users

How to use parameter in sql script pass from unix script?

Hi, I am unable to use parameter in sql script passed from unix script. my sql script CREATE_SBI_LIST_GROUP.sql is like this - ------------------------------- SELECT SDS.ID "SO_ID", SDS.SO a1, sgp.sga__code SGA_CODE, FROM sga sga,sales_genl_provision sgp , comm_product_condn cpc...

9. Shell Programming and Scripting

How to pass pl/sql table values to shell script

Hello, i am using '#!/bin/bash', i want to make a loop in pl/sql, this loop takes values from a table according to some conditions, each time the loop choose 3 different variables. What i am not able to do is that during the loop i want my shell script to read this 3 variables and run a shell...

10. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script

Login or Register to Ask a Question