OraFAQ Forum: SQL & PL/SQL » suppressing unwanted entries while spooling of data

Home » SQL & PL/SQL » SQL & PL/SQL » suppressing unwanted entries while spooling of data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

suppressing unwanted entries while spooling of data [message #8753]

Wed, 24 September 2003 02:06

ARANGASWAMY.V.
Messages: 29
Registered: April 2002

Junior Member

Sir,

While spooling of data, I am able to suppress feedback, heading, trailing spaces, etc. But not in a position to suppress the command, @file_name and also spool off given in the end.

Is there any way to suppress, the file name given in the beginning and spool off given in the end. In otherwords, these two should not find a place in the spooling file, which is not at all required.

with advance thanks

V.A.Swamy

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8754 is a reply to message #8753]

Wed, 24 September 2003 06:15

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator


do the job from OS using sqlplus and call the script

bash-2.03$ cat one.sql
set feed off
set head off
spool myfile.txt
select * from dept;
spool off;
exit;
bash-2.03$ sqlplus -s mag/mag @one

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
         3 mydept         3rd street
         4 asdf           asdfad
         6 asdf           asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        23 mydept         mystreet
        23 mydept         mystreet
        25 asdf           asdf
bash-2.03$ cat myfile.txt

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
         3 mydept         3rd street
         4 asdf           asdfad
         6 asdf           asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        23 mydept         mystreet
        23 mydept         mystreet
        25 asdf           asdf
bash-2.03$

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8756 is a reply to message #8754]

Wed, 24 September 2003 06:44

ARANGASWAMY.V.
Messages: 29
Registered: April 2002

Junior Member

Thanks.

I tested the same on UNIX. It is working fine.

Will it work on WINDOWS.

I also wanted to know whether it will work only on OS, but not in SQLPLUS environment.

i.e. you can not suppress within ORACLE, by using set commands as like feedback off, head off, etc.

with regards,

V.A.Swamy

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8759 is a reply to message #8756]

Wed, 24 September 2003 07:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

definatly will work
mag@mutation > get one
  1  set echo off;              
  2  set feed off;              
  3  set head off;
  4  spool c:myfile.txt
  5  select * from dept;
  6* spool off;
  7  .
mag@mutation > @one

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
         3 mydept         3rd street
         4 asdf           asdfad
         6 asdf           asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        23 mydept         mystreet
        23 mydept         mystreet
        25 asdf           asdf
mag@mutation.wi.mit.edu_mutation > host 
Microsoft Windows 2000 [[Version 5.00.2195]]
(C) Copyright 1985-2000 Microsoft Corp.

C:>cat myfile.txt

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
         3 mydept         3rd street
         4 asdf           asdfad
         6 asdf           asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        22 aasdf          asdf
        23 mydept         mystreet
        23 mydept         mystreet
        25 asdf           asdf

C:>

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8775 is a reply to message #8759]

Thu, 25 September 2003 04:07

ARANGASWAMY.V.
Messages: 29
Registered: April 2002

Junior Member

Thanks a lot.

Tested in WINDOWS, working well. Whatever, suggested by you, comes out of SQLPLUS environment. To continue, the work, I have to log in to ORACLE again. In other words, in between my work, it comes out of ORACLE.

Is there any way, i can capture the required report instead of going out of ORACLE.

with regards,

V.A.Swamy

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8779 is a reply to message #8775]

Thu, 25 September 2003 06:17

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

please see my previous postings.
if there is an 'exit' statemnt in the script, the session is closed after the script is done.
see this posting
http://www.orafaq.net/msgboard/newbies/messages/10590.htm

You can see that, my session is not closed after the report is finished. I DIDNT use any exit statement.
If you have trouble still, post your script.

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8806 is a reply to message #8779]

Fri, 26 September 2003 06:00

ARANGASWAMY.V.
Messages: 29
Registered: April 2002

Junior Member

Dear Mahesh,

It works beautifully. Thanks. That is exactly what I wanted.

In continuation, I wanted to know where to use set echo off and what it will do.

sorry for linking this with the current topic.

with regards,

V.A.Swamy

Report message to a moderator

Re: suppressing unwanted entries while spooling of data [message #8808 is a reply to message #8806]

Fri, 26 September 2003 06:26

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

-- ECHO CONTROLS THE DISPLAY OF THE 'START' COMMAND 
-- or, if u set echo OFF , the commands that are executed in script are not displyaed. only result is displayed

mag@mutation_mutation > get one
  1  set feed off
  2  set head off
  3  spool myfile.txt
  4  select * from dept;
  5* spool off;

-- lets set echo on and run the above script.
-- you will seee every command executed is shown ( and spooled

mag@mutation_mutation > set echo on
mag@mutation_mutation > @one
mag@mutation_mutation > set feed off
mag@mutation_mutation > set head off
mag@mutation_mutation > spool myfile.txt
mag@mutation_mutation > select * from dept;

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
mag@mutation_mutation > spool off;

-- check the spool file, the commands are there
mag@mutation_mutation > !cat myfile.txt
mag@mutation_mutation > select * from dept;

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
mag@mutation_mutation > spool off;

-- now set the echo off and try the same
-- no command or calling script is displayed

mag@mutation_mutation > set echo off
mag@mutation_mutation > @one

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
mag@mutation_mutation > !cat myfile.txt

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

mag@mutation_mutation >

Report message to a moderator

Previous Topic:	KNOW THE TABLE STATUS
Next Topic:	How to find the most wanted "Joinned" TABLEs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 01:44:46 CDT 2024