linux sqlplus select results writes into file twice
Hello,
This is my first post and its because I could not find solution for myself I decided to ask help here.
What I want to do;
I want to get some data from a table 1 on server 1 and insert those datas into a table 2 on server 2. ( lets say schema names are server1 and server 2 also ). After my script works on server 1, I'll put the result file on server 2 and will just execute it.
Here is my script;
Code:
cd /tmp
if [ ! -e pid.pid ];then
touch pid.pid
sqlplus -S user/password << EOF >/dev/null
SET HEAD OFF FEEDBACK OFF VERIFY OFF TERMOUT OFF ECHO OFF TRIMSPOOL ON SERVEROUTPUT OFF showmode off pagesize 0 heading off
SPOOL /tmp/selectResults.sql
select 'insert into server2.table2 (columnA, columnB, columnC) values(' || columnA || ',' || columnB || ', ' || columnC || ');' from server1.table2 where columnB = '1000';
SPOOL OFF
exit
EOF
rm -fr pid.pid
fi
What I get;
When I check "/tmp/selectResults.sql" file I see select query's results AND "insert into server2.table2 (columnA, columnB, columnC) values(' || columnA || ',' || columnB || ', ' || columnC || ');" lines.
In the end, I get results twice, first as raw data and second in my "insert into" lines. I just want my insert into lines so I can use it on the server 2.
Another solution would be; how can I use raw data from select results ?
On the net everyone says load data for oracle but my result file has no "," . What I see is like this ;
Hi all,
I'm using below code
processId=`sqlplus -s ${sysuser}/${syspwd} <<CHK_PROCESS
whenever sqlerror exit sql.sqlcode;
set head off feedback off echo off pages 0
SELECT PROCESS_ID FROM LSHADMIN.DATA_DOMAIN WHERE DOMAIN_NAME = '${tabname}'
... (8 Replies)
hi,
i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command:
cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID << EOF
set pagesize 0
set feedback off
set verify off
... (1 Reply)
hi all,
i am writing a ksh script, i am logging into an oracle db via sqlplus and running a select statement but i dont know how i can store the results from the sql so i can iterate over it and do more operations over it. I dont want to write a stored procedure with a cursor since i need to... (2 Replies)
I m trying to grep 'select * from' from a script, but the problem is with the * ...
I think its taking * as some special character ...
when I try to do grep '*' test (test is my test file) its displaying *
But when I am trying to do grep 'select * from' test its not showing anything... (2 Replies)
So, I would like to run differen select queries on multiple databases..
I made a script wich I thought to be called something like..
./script.sh sql_file_name out.log
or to enter select statement in a command line..
(aix)
and I did created some shell script wich is not working..
it... (6 Replies)
I try to prepare a sufficient function to execute a sql-statement, getting back ONLY retrieved results!
What I can't figured out how to make the sqlplus not printing the 'Connected to ...', 'Disconnected from...' and the executed statements after 'SQL> '.
I am under impression that having... (5 Replies)
Can someone tell me why I'm getting error when I try to run this?
#!/bin/csh -f
source ~/.cshrc
#
set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus
#
set count=`$SQLPLUS -s ${DB_LOGIN} << END
select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Hi All,
I needed to get the result of two sqlplus queris into shell variables.
After days of looking for the ultimate solution to this problem.. i found this...
sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ",
$1);}' | read VAR1 VAR2
set head off... (2 Replies)
