I have to write a simple bash script, which does the compilation of multiple sql scripts which can contain recursive references to the other sql scripts with Oracle SQLPlus convention, so in the end the result will be only one SQL file, containing all statements from all (possibly also recursively) referenced subscripts.
I have created a simple script called oracle-script-compressor.sh with following bash code:
#!/bin/bash
#
#
function err () {
echo $* > "/dev/stderr"
}
function replace_subscripts_placeholders() {
local PROCESSING_FILENAME=$1
echo "-- processing file $PROCESSING_FILENAME"
while read script_line; do
local script_line_NO_LEAD_SPACE="$(echo -e "${script_line}" | sed -e 's/^[[:space:]]*//')"
if [[ $script_line_NO_LEAD_SPACE == \@\@* ]] ; then
local file_name="./${script_line_NO_LEAD_SPACE#\@\@}"
echo "-- found reference to file $file_name"
echo "-- starting with processing, pwd=$PWD"
# for debug purposes:
ls -la
# but this returns always false:
if [ -f "$file_name" ]; then
# so this part is never started:
. replace_subscripts_placeholders $file_name
else
err_msg="WARNING: Could not find the referenced file $file_name"
echo "-- $err_msg"
err $err_msg
fi
else
echo "$script_line"
fi
done < $PROCESSING_FILENAME
}
if test -z "$1" ; then
err "Usage: oracle-script-compressor.sh {file_name_to_process} [> output_file]"
err " Be aware, if the referenced files within {file_name_to_process} are not specified by absolute paths, you have to start the script from corresponding directory."
err " If the part [> output_file] is omitted, then this script writes to standard output"
err " If the part [>> output_file] is used, then the result will be appended to output_file it it exists before the processing"
else
if [ -f "$1" ]; then
replace_subscripts_placeholders $1
else
echo "file $1 does not exist"
fi
fi
and I am stuck on interesting problem - it seems, inside of the function replace_subscripts_placeholders() the file check
if [ -f "$file_name" ]; then
. replace_subscripts_placeholders $file_name
else
err_msg="WARNING: Could not find the referenced file $file_name"
echo "-- $err_msg"
err $err_msg
fi
never goes to the recursion call and even, if I remove the if statement and really call the function in recursion with the correct referenced file name, which exists, it is still not recognized to be found and not passed into the loop over all lines of the referenced file in the recursive call (then the error file not found comes and the loop cannot be executed).
I have added the debug messages into script like mentioned above in the script, but still I am unable to find, why the hell should bash not find the file, if it is in the same directory. The scripts are placed in
user@mycomputer:/tmp/test$ ls -la
celkem 52
drwxr-xr-x 2 user user 4096 zář 14 21:47 .
drwxrwxrwt 38 root root 36864 zář 14 21:48 ..
-rw-r--r-- 1 user user 51 zář 14 21:45 a.sql
-rw-r--r-- 1 user user 51 zář 14 21:45 b.sql
-rw-r--r-- 1 user user 590 zář 14 21:46 start.sql
user@mycomputer:/tmp/test$
and the content of the file start.sql looks like this:
spool output__UpdSch.log
whenever sqlerror exit sql.sqlcode
--
--
PROMPT a.sql - starting
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as START_TIMESTAMP from dual;
@@a.sql
PROMPT a.sql - finished
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as FINISH_TIMESTAMP from dual;
--
--
PROMPT b.sql - starting
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as START_TIMESTAMP from dual;
@@b.sql
PROMPT b.sql - finished
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as FINISH_TIMESTAMP from dual;
--
--
spool off
and if I execute the script, it seems it decoded the filenames correctly, but there is still the problem in bash - the testing of the file existence returns always false:
user@mycomputer:/tmp/test$ ~/tmp/oracle-script-compressor.sh start.sql
-- processing file start.sql
spool output__UpdSch.log
whenever sqlerror exit sql.sqlcode
--
--
PROMPT a.sql - starting
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as START_TIMESTAMP from dual;
-- found reference to file ./a.sql
-- starting with processing, pwd=/tmp/test
celkem 52
drwxr-xr-x 2 user user 4096 zář 14 21:47 .
drwxrwxrwt 38 root root 36864 zář 14 21:48 ..
-rw-r--r-- 1 user user 51 zář 14 21:45 a.sql
-rw-r--r-- 1 user user 51 zář 14 21:45 b.sql
-rw-r--r-- 1 user user 590 zář 14 21:46 start.sql
-- WARNING: Could not find the referenced file ./a.sql
WARNING: Could not find the referenced file ./a.sql
PROMPT a.sql - finished
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as FINISH_TIMESTAMP from dual;
--
--
PROMPT b.sql - starting
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as START_TIMESTAMP from dual;
-- found reference to file ./b.sql
-- starting with processing, pwd=/tmp/test
celkem 52
drwxr-xr-x 2 user user 4096 zář 14 21:47 .
drwxrwxrwt 38 root root 36864 zář 14 21:48 ..
-rw-r--r-- 1 user user 51 zář 14 21:45 a.sql
-rw-r--r-- 1 user user 51 zář 14 21:45 b.sql
-rw-r--r-- 1 user user 590 zář 14 21:46 start.sql
-- WARNING: Could not find the referenced file ./b.sql
WARNING: Could not find the referenced file ./b.sql
PROMPT b.sql - finished
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS.FF3') as FINISH_TIMESTAMP from dual;
--
--
spool off
user@mycomputer:/tmp/test$
The script has read access to files, everything is in the same folder, where I start the script, It also does not matter, if I reference the file with the current directory prefix "./" or not, it is just never found. Interesting is, the check during the start of the script passes correctly, the only problem is the searching within the function... I have also tried to call the function with preceded "." and without it, it makes no difference... There are also no trailing spaces in the referenced file names... It also makes no difference if I declare the variables inside of the function as local or not. So I have no idea, what could be the problem, maybe I just looked too long on it and cannot see something - seems it has to be something trivial, like the function is started in some different current directory - (but pwd and ls shows the directory is always correct...???) - Any help or pointers will be appreciated.
Thank you for the comments, it brought me to the solution. I found out, this problem was related to the fact, the test sql files were created on Windows, so they had in the end of line always the <CR><LF> characters. This leads to the fact in the original bash script I posted, the script line
while read script_line
puts into the variable not only the given file name from the line, preceded by the characters @@
@@a.sql
but also the <CR> character - in the variable is then the value
@@a.sql<CR>
what was the cause the file a.sql etc. could never be found. Of course the character is invisible, so therefore it has not been shown on any debug echoing I made here - I had to put the content of $file_name between some another characters and then i could see it in the echoed test... I made also some other corrections and the final working script looks in the following way, if somebody needs to join referenced SQL scripts into one, this does it:
#!/bin/bash
#
#
function err () {
echo $* > "/dev/stderr"
}
function replace_subscripts_placeholders() {
local PROCESSING_FILENAME=$1
echo "-- processing file $PROCESSING_FILENAME"
while read script_line || [ -n "$script_line" ]; do
local script_line_NO_LEAD_SPACE="$(echo -e "${script_line}" | sed -e 's/^[[:space:]]*//' | sed -e 's/^M$//')"
if [[ $script_line_NO_LEAD_SPACE == \@\@* ]] ; then
local file_name="${script_line_NO_LEAD_SPACE#\@\@}"
echo "-- found reference to file $file_name"
if [ -f "$file_name" ]; then
replace_subscripts_placeholders $file_name
else
err_msg="WARNING: Could not find the referenced file $file_name"
echo "-- $err_msg"
err $err_msg
fi
else
echo "$script_line"
fi
done < $PROCESSING_FILENAME
}
if test -z "$1" ; then
err "Usage: oracle-script-compressor.sh {file_name_to_process} [> output_file]"
err " Be aware, if the referenced files within {file_name_to_process} are not specified by absolute paths, you have to start the script from corresponding directory."
err " If the part [> output_file] is omitted, then this script writes to standard output"
err " If the part [>> output_file] is used, then the result will be appended to output_file it it exists before the processing"
else
if [ -f "$1" ]; then
replace_subscripts_placeholders $1
else
echo "file $1 does not exist"
fi
fi