linux sqlplus select results writes into file twice

 
Thread Tools Search this Thread
Special Forums UNIX and Linux Applications linux sqlplus select results writes into file twice
# 1  
Old 04-12-2011
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 ;
Code:
       118             1000     143373
       103             1000     143698
        65             1000     143188
       101             1000     145821
        77             1000     145829
       114             1000     143189

What to do now ?

Last edited by Franklin52; 04-12-2011 at 06:30 AM.. Reason: Please use code tags, thank you
# 2  
Old 04-12-2011
Might be a very very Oracle centric solution, but why not use a database link?
# 3  
Old 04-12-2011
Higher ups dont allow it. So we get only what we need from the first table to not cause much trouble on server 1.
# 4  
Old 04-12-2011
I should be missing something,
with your code I get only the inserts in the spool file.

You could use SQL Loader (sqlldr) to load the flat file into the database.
# 5  
Old 04-12-2011
@radoulov

Well, I think the same as you and I cannot see why it writes twice...

I'll try sqlldr, I hope it works for my output file, but I would like to learn the first one too.
# 6  
Old 04-12-2011
Could you post a small part of the spool file?
# 7  
Old 04-12-2011
Let's say;

( its first 5 lines and 5 lines after %50 which is the first 5 lines of the second part, but i changed table, column names )

Code:
      144             1000     100111
       129             1000     102377
        33             1000     100056
       157             1000     103417
        47             1000     102351
insert into server2.table2 (columnA, columnB, columnC) values(144,1000, 100111);
insert into server2.table2 (columnA, columnB, columnC) values(129,1000, 102377);
insert into server2.table2 (columnA, columnB, columnC) values(33,1000, 100056);
insert into server2.table2 (columnA, columnB, columnC) values(157,1000, 103417);
insert into server2.table2 (columnA, columnB, columnC) values(47,1000, 102351);

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQLPLUS command with more than 1 select statement

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)
Discussion started by: Pratiksha Mehra
8 Replies

2. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

3. Shell Programming and Scripting

Connecting sqlplus from UNIX with multiple select statement

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)
Discussion started by: lovelysethii
1 Replies

4. Shell Programming and Scripting

iterating over results from sqlplus

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)
Discussion started by: cesarNZ
2 Replies

5. UNIX for Advanced & Expert Users

SQLPlus Select with Grep

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)
Discussion started by: RDR
2 Replies

6. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

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)
Discussion started by: bongo
6 Replies

7. Shell Programming and Scripting

sqlplus: could it be dirrected do not printout anything, beside results?

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)
Discussion started by: alex_5161
5 Replies

8. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

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)
Discussion started by: gregrobinsonhd
7 Replies

9. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

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)
Discussion started by: pranavagarwal
2 Replies

10. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies
Login or Register to Ask a Question