Hi,

I require help to complete below requirement through Linux Script.

I have a SQL query which shows two columns as output. One is Report Name and other is report path. Query return multiple rows. below is the output.
Report Name
Cotton Stock Report (Net Weight)- Customized
Output Path
/opt/ora/oracle/inst/apps/PROD_prodapps/logs/appl/c onc/out/o16728954.out
Report Name
Daily Yarn Clearance Summary Report (NCML / ESML) -Customized
Output Path
/opt/ora/oracle/inst/apps/PROD_prodapps/logs/appl/c onc/out/o16728991.out
Report Name
Freight & Weight Of Arrival - Customized
Output Path
/opt/ora/oracle/inst/apps/PROD_prodapps/logs/appl/c onc/out/o16729009.out

Now requirement is to copy the file from output path to /opt/reports
further rename the report name which is in output path after out/ with .out extension
with the name in Report name column.

So script will copy the files and rename them in a loop, so that all the records available in the select statement will be copy and renamed.
below is the SQL Query.
Select
pt.user_concurrent_program_name ,
OUTFILE_NAME
FROm
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where
pt.concurrent_program_id = f.concurrent_program_id
and pt.application_id = f.program_application_id
and f.RESPONSIBILITY_ID = 52431
and trunc(f.request_date) = trunc(sysdate)

please use [code]here comes your script[/code] to keep formatting (and avoid smiley).
Is this your homework?

1 members found this post helpful.

Hi usman_oracle and welcome to LQ.

Please review the Welcome to LQ link as well as the LQ Site Rules regarding best ways to ask you questions.

For clarification:

• This looks to be an assignment
• You haven't shown any of your own work
• LQ members are all volunteers, including yourself, we're not paid support, and the intentions of the site are that you learn how to do things like this as part of your experience

Hi,
Yes, it's office assignment. I am Oracle developer further new to shell scripting.
Currently i do not find any help yet to get output of SQL query in shell script.

would be nice to explain what did you really try...

Ah, so since this is your 'office assignment', then you probably should be LEARNING how to do what you were assigned to do. If you cannot perform your assigned job, then you need to go to your boss and ask them to give this to someone else, or get a co-worker to help you.

Otherwise, there are THOUSANDS of very easily found scripting tutorials. And your assertion of "i do not find any help yet to get output of SQL query in shell script." is hard to believe. Putting "bash script get output of command into variable" pulls up LOADS of examples, many of which are in the bash scripting tutorials you could find.
...will get your command into the $OUTPUT variable.

#!/bin/sh
sqlplus -s "/ as sysdba" << EOF
SET HEADING OFF
SET FEEDBACK OFF
Select
pt.user_concurrent_program_name , OUTFILE_NAME
FROm
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where
pt.concurrent_program_id = f.concurrent_program_id
and pt.application_id = f.program_application_id
and f.RESPONSIBILITY_ID = 52431
and trunc(f.request_date) = trunc(sysdate);

above is the code which show below output

Cotton Stock Report (Net Weight)- Customized
/opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16735454.out

Daily Yarn Clearance Summary Report (NCML / ESML) -Customized
/opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16735457.out

Lab Test - Customized
/opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16735462.out

first row is the report name and second row is the report output location.
now i want to write loop to copy the file from output location and save to opt/report folder , Further rename it with report name column.

Ok, great....so where is the part where you have shown your efforts towards doing your own work assignment? Because this is just the SQL query, with no bash scripting at all. You were given the piece of code that will let you get the output of a command into a variable, so now it is time for YOU to actually try to write your script.

Again, you have been given the code to get what you need into a variable and have been directed to scripting tutorials which have examples on loops and using such variables. Go put them together, and let us know if you're stuck. But we WILL NOT write your scripts for you.

if that OUTPUT=${SQL sql-statement}
or OUTPUT=`SQL sql-statement` would also work

then

SQL sql-statement > results.txt

output your table to a text file. Can you split the columns with : or tab to make processing easier

You can then process results.txt with sed or awk or something

Right...this has been what's been told to the OP previously, on how to capture output of the SQL command into a variable. The output (based on what the OP gave), is delimited by a CRLF.