Solved

Appending data in a file by sql* plus spool command

Posted on 2001-07-25
5
2,204 Views
Last Modified: 2012-05-04
Dear all,
here is the scenario

SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
Now i want to append the same file with the dept table data but by the command

SQL> spool emdata
SQL> select * from dept
...
...
...

it washes all the emp data from file and writes the dept data.

My requirement is to write the dept data from the end of the emp data.

thanx in advance.
Dilshad Ch.
 
0
Comment
Question by:dilshad_ch
5 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6320856
SQL> spool empdata
SQL> select * from emp
SQL> select * from dept
SQL> spool Off
0
 

Author Comment

by:dilshad_ch
ID: 6321651
dear kertzschmar,
the commands you have written does not append data but washes all existing data and then write. i want to append data.

thanks for sparing time
dilshad ch.
0
 
LVL 5

Accepted Solution

by:
ser6398 earned 5 total points
ID: 6322160
I don't think there is an append ability in SQL*Plus spool.  As a work-around, you could use your OS to append the files together using the HOST command.

For DOS/Windows, it would look like:

SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.
Now i want to append the same file with the dept table data but by the command

SQL> spool temp
SQL> select * from dept
...
...
SQL>spool off


SQL>HOST COPY empdata.sql+temp.sql empdata.sql

0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6322832
create a file x.sql as

set embedded on;
set linesize 65
spool c:\temp\out.lst
select empno,ename from emp;
select deptno,dname from dept;
spool off;

execute the file from sqlplus
sql>@x.sql

 EMPNO ENAME                                                
---------- ----------                                            
      7369 SMITH                                                
      7499 ALLEN                                                
      7521 WARD                                                  
      7566 JONES                                                
      7654 MARTIN                                                
      7698 BLAKE                                                
      7782 CLARK                                                
      7788 SCOTT                                                
      7839 KING                                                  
      7844 TURNER                                                
      7876 ADAMS                                                
      7900 JAMES                                                
      7902 FORD                                                  
      7934 MILLER                                                

14 rows selected.

Elapsed: 00:00:00.60
    DEPTNO DNAME                                                
---------- --------------                                        
        10 ACCOUNTING                                            
        20 RESEARCH                                              
        30 SALES                                                
        40 OPERATIONS                                            

Elapsed: 00:00:00.10
0
 
LVL 7

Expert Comment

by:Mehul Shah
ID: 24554759
Not sure why other guys missed it. But below solution works perfectly as spool command does have Append option.

SQL> spool empdata
SQL> select * from emp
...
...
data displayed from emp table
...
...
SQL>spool off
emp data has been stored in the empdata.lst file.

SQL> spool emdata append
SQL> select * from dept
...
...
...

SQL>spool off
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Clone Oracle 12c Database 5 52
Get the parent node - XMLTYPE 9 75
Oracle 10g standard edition server with 4 processors 3 55
having some issue on pl sql procedure 1 20
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question