Need script to pass all sql file names in a directory to DB query

Shell Programming and Scripting
1

Hi All,

In this path

/home/all_files

we have follwing files and direcotries

proc_edf_sot.sql
proc_ssc_sot.sql
func_dfg_sot.sql
sot
unic
cmr
sdc

under sot directory we have other directories

sql 
pas 
ref

under sql directory we have

sql_sot 
sql_mat 
sql_mdr

We have the directory information in the database table for each sql file.

Now my requirement is I have to get all sql files from /home/all_files
in this example these three files

proc_edf_sot.sql
proc_ssc_sot.sql
func_dfg_sot.sql

and pass the file name to the data base query get the diretory for that file and move the file to that directory.

This will give diretory information

dir_paths=`sqlplus -s $DB_CONNECTION/$PASS_WORD << EOF
	  
	          set termout off;
              set verify off;
              set feedback off;
              set heading off;
              set newpage 0;
              set pagesize 0;
              set linesize 200;
              SELECT  SUBSTR(trunk_path,3,INSTR(trunk_path,'sql')) FROM directory_map
              WHERE object_name=SUBSTR(UPPER('$1'),1,INSTR(UPPER('$1'),'.')-1);
              exit success;
              EOF`

Can you please help me the script.

Thanks

2

Not clear. If

find /home/all_files -name "*.sql"

does not yield what you need, please be WAY more specific, precise, and detailed in your request.

3

Hi Rudi,

Thanks for your reponse.
I am able to build the script for my requirement.

I am executing the follwig script in cygwin bash.

But I am facing some issue it's weird for me. Not able to resolve it.

dir_paths=`sqlplus -s $DB_CONNECTION/$PASS_WORD << EOF
	  
	          set termout off;
              set verify off;
              set feedback off;
              set heading off;
              set newpage 0;
              set pagesize 0;
              set linesize 200;
              SELECT  SUBSTR(trunk_path,3,INSTR(trunk_path,'sql')) FROM directory_map
              WHERE object_name=SUBSTR(UPPER('$1'),1,INSTR(UPPER('$1'),'.')-1);
              exit success;
              EOF`

echo $dir_paths 
sfsmde/sql

If I do

mv file_name $dir_paths

getting error

mv : cannot move file_name sfsmde/sql\r

Why that extra \r is coming.

Please help me.

Thanks in advance.

4

That \r (= ^M, 0x0D, <CR>) char is part of the DOS/windows line terminator <CR><NL> . As *nix uses <NL> only, it considers \r as part of the file name. To get rid of it, use e.g. dos2unix . What surprises me is that cygwin seems not to know how to handle it.

5

Hi,

Thanks a lot.
The issue has been resolved.
How to get another column values of SQL query to another variable.
I have added column new_file_name in my query.
While moving te file I have to move with new file name.

dir_paths=`sqlplus -s $DB_CONNECTION/$PASS_WORD << EOF
	  
	          set termout off;
              set verify off;
              set feedback off;
              set heading off;
              set newpage 0;
              set pagesize 0;
              set linesize 200;
              SELECT  SUBSTR(trunk_path,3,INSTR(trunk_path,'sql')),new_file_name FROM directory_map
              WHERE object_name=SUBSTR(UPPER('$1'),1,INSTR(UPPER('$1'),'.')-1);
              exit success;
              EOF`

mv file_name $dir_paths/$new_file_name

Please help me.

Thanks in advance.

---------- Post updated at 05:12 PM ---------- Previous update was at 04:08 PM ----------

Hi Rudi,

Can you please help me.

Thanks.

6

I'm afraid I can't as I absolutely don't understand your request (see post#2). Any comment up to now was just shooting in the dark - and neither has (yet) been approved nor disapproved.

7

What output does the command:

sqlplus -s $DB_CONNECTION/$PASS_WORD << EOF
	  
	          set termout off;
              set verify off;
              set feedback off;
              set heading off;
              set newpage 0;
              set pagesize 0;
              set linesize 200;
              SELECT  SUBSTR(trunk_path,3,INSTR(trunk_path,'sql')),new_file_name FROM directory_map
              WHERE object_name=SUBSTR(UPPER('$1'),1,INSTR(UPPER('$1'),'.')-1);
              exit success;
EOF

produce? Is it one line with a space or tab between the selected part of the trunk_path and the new_file_name ; or do those values appear on separate lines?

Does the output contain any quoting characters around the strings produced?

What operating system and shell are you using? On many shells, the script you showed us would generate a syntax error for an unterminated here-document.

Did you get any diagnostic messages when you ran the script you showed us in post #5?

What command line did you use to invoke your script?