#!/bin/sh
. /opt/ora/oracle/db/tech_st/11.2.3/PROD_proddb.env
sqlplus -s "username/password" << EOF
no='sqlplus -s username/password<<EOF
set heading off
set feedback off
Select
pt.user_concurrent_program_name report_name , OUTFILE_NAME report_path
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);
exit
EOF
`
report_name=`echo $no | awk `{print $report_name}``
report_path=`echo $no | awk `{print $report_path}``
$ echo report_name
$ echo report_path
while running above script, shows below errors
-bash: /home/oracle/scripts/test2.sh: line 21: unexpected EOF while looking for matching ``'
-bash: /home/oracle/scripts/test2.sh: line 25: syntax error: unexpected end of file
[/CODE]
Well, the first thing that jumps out is that you're using backticks (`) instead of single quotes (') around your awk commands, and an erroneous backtick after the closing EOF for your SQLPlus call.
Generally speaking, it's better to use $(...) for command substitution than backticks.. it's also easier to read!
Using $report_name and $report_path inside your awk won't work, as they're shell variables, and not awk variables (and in awk, using $ before them would make them equate to the position of a variable, not the variable itself). You might find what you're looking for in positional variables ($1, $2, etc.) inside the awk, or you might not need awk at all, depending on, actually, what output you are looking for.
It's bad practice to put a username / password on the command's line like this - it will show up in processes (e.g. when using ps).
Two have two << EOFs. The first SQLPlus command looks to be erroneous, and the opening of the command substitution around the other one is a single quote (') not a backtick (`).
More readable would be:
no=$(sqlplus -s << EOF
conn user/pass
...
EOF
)
Instead of the two awk commands, if you're sure that $no contains exactly only those two words, you need, there are other, simpler ways to get that.
For example
set - $no
report_name=$1
report_path=$2
Please don't just put
```text
```
at the top of your post and assume that's "using code tags". They're supposed to go
#!/bin/sh
. /opt/ora/oracle/db/tech_st/11.2.3/PROD_darwin.env
#sqlplus -s "apps/apps" << EOF
no=$(sqlplus -s apps/apps<<EOF
set heading off
set feedback off
Select
pt.user_concurrent_program_name report_name , OUTFILE_NAME report_path
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);
exit
EOF
)
report_name=`echo $no | awk '{print $report_name}'`
report_path=`echo $no | awk '{print $report_path}'`
echo $report_path
echo $report_name
scp $report_path /opt/report
now variables are showing the assigned values of Report_name and report_path
i am copying the report output file to opt/report
but its showing error. below is the output of the above script
[oracle@darwin ~]$ . /home/oracle/scripts/u1.sh
Daily Yarn Clearance Summary Report (NCML / ESML) -Customized /opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16737993.out Lab Test - Customized /opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16737995.out
Daily Yarn Clearance Summary Report (NCML / ESML) -Customized /opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16737993.out Lab Test - Customized /opt/ora/oracle/inst/apps/PROD_darwin/logs/appl/conc/out/o16737995.out
cp: cannot stat `Daily': No such file or directory
cp: cannot stat `Yarn': No such file or directory
cp: cannot stat `Clearance': No such file or directory
cp: cannot stat `Summary': No such file or directory
cp: cannot stat `Report': No such file or directory
cp: cannot stat `(NCML': No such file or directory
cp: omitting directory `/'
cp: cannot stat `ESML)': No such file or directory
cp: invalid option -- C
Try `cp --help' for more information.
cp: cannot stat `Lab': No such file or directory
cp: cannot stat `Test': No such file or directory
cp: cannot stat `-': No such file or directory
cp: cannot stat `Customized': No such file or directory
First: It usually helps if people read the entire responses / comments, so you might want to reconsider
As used, in awk the variables report_name and report_path are undefined, and thus in both cases the entire line ($0) is referenced and printed, and assigned. This might not be the desired result.
From the (unstructured) output you present it can be inferred that
the resulting columns have spaces in them and are separated by spaces; might be difficult to distinguish - that's why the cp fails...
there's more than one result line, so you may need to individualise single items in a loop.
Plus, as both source and target seem to be local files, why do you run scp (sort of network copy)?
Actually report_path is the location where pdf report is saved on server. i have to copy it to /opt/report folder. As report name is with id so i will rename with report_name.
through SQL, path and required name for update has been achieved, Now it has to copied and rename.I think using cp command and passing variable with assign values can solve this. I will be thankful if you suggest any alternate or easy way for this
Show exact details, your description is too vague. And, consider to rephrase the SQL query; you might be able to immediately output the correct command(s) to be fed into a subshell. Again: spaces in the result may spoil the behaviour of the downstream commands.
Ok, let me describe the requirement again and that is copy the files from one location to another and rename the copied files.
Source location and names to rename the files comes from SQL query. Query results two columns. One is the report name and other is path where file is currently placed.
Shell script will read the report name and path from the first row of the query.
Copy the file then rename it and moves towards next record and so on till end of query records.
---------- Post updated 11-17-17 at 02:06 AM ---------- Previous update was 11-16-17 at 05:34 AM ----------
I would like to thanks for the support and advise. Lean something new from experts.
Problem has been solved.
