Linux Script to copy and rename files through SQL statement
Linux - NewbieThis Linux forum is for members that are new to Linux.
Just starting out and have a question?
If it is not in the man pages or the how-to's this is the place!
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Linux Script to copy and rename files through SQL statement
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)
Last edited by usman_oracle; 11-13-2017 at 02:44 AM.
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.
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.
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.
Code:
OUTPUT="$(put your command in here)"
...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);
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.
#!/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);
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.