Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Red face Unanswered: Oracle SPOOL : URGENT!

    Hi All,

    I have used the oracle SPOOL to extract the Employee records to an output file, but find out that there is a newline after 13th record, as shown below. Wondering why this happen. How to solve it?

    Also, Can we set the linesize to variable length, so that the line character can grow itself, rather than by specifying a value (eg, I set linesize to 256 in my case). So, there are no space character on behind.

    Hope to get reply from you all, as I need it urgent. Thanks a million.

    1|abmf|
    2|act|
    3|abmf|
    4|act|
    5|jemf|
    6|act|
    7|act|
    8|bmf|
    9|jemf|
    10|act|
    11|jemf|
    12|act|
    13|jemf|

    14|abmf|
    15|act|
    16|abmf|
    17|jemf|
    18|jemf|
    19|jemf|

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try

    set trimspool on
    set pages 99999

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    18

    Question

    Hi Alan and all,

    I have manage to trim out the blank character...thanks.

    But, because of the maximum pages value allowed by Oracle is '50000', and currently I have 210,000 records, therefore newline will also appear 4 time. So, how can I generate the record file without any new line?

    Also, which command should I use to NOT display the selected records on SQL command line screen, as there are too many records keep displayed on SQL screen during generating of file.

    Thanks so much in advance...
    Last edited by dbnm; 10-16-03 at 01:16.

  4. #4
    Join Date
    Oct 2003
    Posts
    12
    If you can set pagesize to 0 and let them all run in a single page. The only thing is that you may not have a top column headers.

  5. #5
    Join Date
    Oct 2003
    Posts
    12
    Originally posted by dbnm


    Also, which command should I use to NOT display the selected records on SQL command line screen, as there are too many records keep displayed on SQL screen during generating of file.


    I assume you are doing

    select * from emp;

    try something like select * from emp where name like 'A%'

    That will give you anything starting with A on the name field. You can select what you want that way and display only what you want. I just started studying Oracle myself so my solution is not going to be optimized but should work.

  6. #6
    Join Date
    Oct 2003
    Posts
    18

    Red face

    Hi Seraph and All,

    Worsen now...as I can't open my output file with setting the pagesize to 0. Wonder why???

    By the way, I just need the selected records to be generated in output file and DON'T want it to be displayed on SQL plus screen after issued the following commands. The reason is because displaying of 555,100 records will slow down the process. So need advice from you all, as I still have million of records to be generated
    (is a nightmare for me :-/ ). Thanks.

    [SQL Plus screen]
    SQL> set line 32767;
    SQL> set pages 50000;
    SQL> set feedback off;
    SQL> spool sample1.txt
    SQL> select employee.emp_no, employee.emp_gen from employee;
    1 abmf
    2 act
    3 abmf
    4 act
    5 jemf
    6 act
    7 act
    ...
    ...
    ...
    555100 eef
    Last edited by dbnm; 10-16-03 at 05:38.

  7. #7
    Join Date
    Oct 2003
    Posts
    18

    Red face

    hi all...

    Any reply for my questions above?...thanks in advance.

  8. #8
    Join Date
    Oct 2003
    Location
    NH
    Posts
    1
    Originally posted by dbnm
    hi all...

    Any reply for my questions above?...thanks in advance.

    Try
    "SET ECHO OFF"

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    doesn't make any sense that you cannot open your spooled file.

    set pagesize 0
    set feedback off
    set echo off
    set linesize 1000

    spool c:\spool.txt
    @c:\myscript.sql
    spool off
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    permissions

    Maybe you can not open the file because of the permissions on that file. Are you the owner of the server? Can you get to root (administrator)? Are there others that are root on the same server? Check your file permissions.
    Michellea Southern-David

  11. #11
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    If u don't want the records to get on the screen, how do u think to spool the screen output to a file ? In other terms, using spool u have to get the records on the screen !
    When u r not able to access the file, did u type spool off ?

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •