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
Code:
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`
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.
Code:
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
Code:
mv file_name $dir_paths
getting error
Code:
mv : cannot move file_name sfsmde/sql\r
Why that extra \r is coming.
Please help me.
Thanks in advance.
Last edited by ROCK_PLSQL; 05-17-2016 at 07:29 AM..
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.
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.
Code:
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 ----------
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.
We are not here to act as your personal programming staff.
Repeatedly bumping up your posts with "help me" messages is against forum rules and with three active infractions for bumping up posts and an active 3rd infraction for not using CODE tags correctly, this account is in read-only mode for three days.
Please take this time to review the rules you agreed to when you joined this forum.
If you continue ignoring the rules in the future, you may be banned from this site permanently.
What output does the command:
Code:
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?
I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line.
Main Code shell script:
this is my code:
#!/bin/bash
file="db_detail.txt"
. $file
rm /batch/corpplan/bin/dan.csv... (6 Replies)
Hi All,
I am new to shell script. I am trying to pass value from .sh file to .sql file .
But I am able to run the .sql file from .sh file with values in sql file.
But I am unable to pass the values from .sh file. can some one please help to resolve this.
here is my .sh file
s1.sh
... (4 Replies)
Hi Gurus,
I have a request which needs to pass string into sql.
dummy code as below:
sqlplus -s user/password@instance << EOF >>output.txt
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
select emp_no, emp_name from emp
where emp_no in ('a', 'b', 'c');
exit;... (4 Replies)
hi,
i want to pass an array parameters to a sftp script so that i can transfer each file in the array to the remote server by connecting only once to the sftp remote server.
i thought of using a variable that contains list of file names separated by a space and pass the variable to the sftp... (3 Replies)
Hi,
I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen.
snapshot of my script:... (13 Replies)
Hi all ,
I want to pass contents from a file say f1 as arguments to a sql query which has In statement using a script
example
select * from table_1 where login in ( `cat f1`) ;
will this work or is there any other way to do it. (1 Reply)
I need to run sql script from shell script which takes the input from a file and contents of file will be like :
12345
34567
78657
and query will be like :
select seq_nbr from bus_event where event_nbr='12345';
select seq_nbr from bus_event where event_nbr='34567';
select seq_nbr... (1 Reply)
Below i have the sample code. i need to pass the entire query from file or as parameter and read the results and write into a output file.
here the number of columns are unknown. some times it may be 2,3 or entire columns from the table.
read all the column results and write into a comma... (0 Replies)
Hi,
I am using SYBASE database. in my script i am connecting to DB via using isql.
isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b0 -w3000 -h0 -s"|" -i${MYDIR}/ABC.sql -oXYZ.txt << FINSQL
i am taking a ABC.sql file to use the queries written in it and storing the output in... (3 Replies)
Hi,
I know in oracle a .sql file is called by @ <path> /<filename>.
But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
05-18-2016
