LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This 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


Reply
  Search this Thread
Old 11-13-2017, 02:34 AM   #1
usman_oracle
LQ Newbie
 
Registered: Nov 2017
Posts: 3

Rep: Reputation: Disabled
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.
 
Old 11-13-2017, 02:38 AM   #2
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 13,102

Rep: Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144
please use [code]here comes your script[/code] to keep formatting (and avoid smiley).
Is this your homework?
 
1 members found this post helpful.
Old 11-13-2017, 07:35 AM   #3
rtmistler
Moderator
 
Registered: Mar 2011
Location: MA, USA
Distribution: MINT Debian, Angstrom, SUSE, Ubuntu, Debian
Posts: 8,060
Blog Entries: 13

Rep: Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503Reputation: 3503
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
 
Old 11-14-2017, 12:53 AM   #4
usman_oracle
LQ Newbie
 
Registered: Nov 2017
Posts: 3

Original Poster
Rep: Reputation: Disabled
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.
 
Old 11-14-2017, 12:56 AM   #5
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 13,102

Rep: Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144Reputation: 4144
would be nice to explain what did you really try...
 
Old 11-14-2017, 06:51 AM   #6
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 21,964

Rep: Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830
Quote:
Originally Posted by usman_oracle View Post
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.
 
Old 11-15-2017, 04:09 AM   #7
usman_oracle
LQ Newbie
 
Registered: Nov 2017
Posts: 3

Original Poster
Rep: Reputation: Disabled
#!/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.
 
Old 11-15-2017, 07:06 AM   #8
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 21,964

Rep: Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830
Quote:
Originally Posted by usman_oracle View Post
Code:
#!/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.
 
Old 11-15-2017, 07:36 AM   #9
rhubarbdog
Member
 
Registered: Apr 2015
Location: Yorkshire, England
Distribution: Linux Mint
Posts: 134

Rep: Reputation: Disabled
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
 
Old 11-15-2017, 07:43 AM   #10
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 21,964

Rep: Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830Reputation: 5830
Quote:
Originally Posted by rhubarbdog View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to rename/copy files with the locked symbol on it in Kali Linux. Pamplemousse Linux - Newbie 7 10-21-2017 04:13 AM
rename files after copy is finished arrals.vl Programming 6 06-15-2015 03:14 AM
bash script to copy rename files dunstable Linux - Newbie 4 10-08-2012 10:53 AM
[SOLVED] Linux Script to Delete and/or Rename Files marspl Programming 5 04-18-2010 09:16 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 08:05 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration