Home » SQL & PL/SQL » SQL & PL/SQL » how to run reports from sql (merged)
how to run reports from sql (merged) [message #311719] Sun, 06 April 2008 00:01 Go to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
i want to run reports from the oracle 8i sql interface ,
the problem arises when i want to spool the result to text file , but the text file always contains the sql statements , is there a way to avoid generating the sql statement while spooling the output



Re: how to run reports from sql [message #311721 is a reply to message #311719] Sun, 06 April 2008 00:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
sql> set echo off
Re: how to run reports from sql [message #311722 is a reply to message #311721] Sun, 06 April 2008 00:41 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thanks for the reply , i tried echo off , feedback off

but still the sql select statement command is shown on the on spooled text ,

is there any other way to not to show select statement in the following text
Re: how to run reports from sql [message #311723 is a reply to message #311722] Sun, 06 April 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
echo off is the way. It works for statements in scripts not for statements interactively entered. In this latter case, there is no way.

Regards
Michel

Re: how to run reports from sql [message #311733 is a reply to message #311723] Sun, 06 April 2008 04:39 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, you'll have to put this query into a file.

This is how it looks like when you type it all interactively - all the text behind "$type a.txt" is contained in the file (including SELECT statement and SPOOL OFF SQL*Plus command):
SQL> set echo off
SQL> spool a.txt
SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off;
SQL> $type a.txt
SQL> select * From dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NY

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON


SQL> spool off;


But, if you put it into a file, that's another story: let's call it 'test.sql'; now, output file contains nothing but data (as you'd want it to):
SQL> $type test.sql
set echo off;
spool a.txt
select * from dept;
spool off;
SQL>
SQL> @test

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
SQL> $type a.txt

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NY

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON



SQL>
Re: how to run reports from sql [message #311739 is a reply to message #311719] Sun, 06 April 2008 06:59 Go to previous messageGo to next message
kukreja
Messages: 33
Registered: February 2007
Member
Set verify off
Set feedback off

in your sql script

Then run the script in slient mode
sqlplus -s user/password @abc.sql
Re: how to run reports from sql [message #311741 is a reply to message #311739] Sun, 06 April 2008 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
The question is "is there a way to avoid generating the sql statement while spooling the output" and the answer is "set echo off" already given 3 times.

Regards
Michel
Re: how to run reports from sql [message #311744 is a reply to message #311723] Sun, 06 April 2008 08:08 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
could you please show me how to run scripts
for example if i have the following

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool c:\test.txt
select * from emp where rownum <10;
spool off

[Updated on: Sun, 06 April 2008 08:11]

Report message to a moderator

Re: how to run reports from sql [message #311746 is a reply to message #311744] Sun, 06 April 2008 08:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look at Littlefoot's reply. He explains it very clearly.

Re: how to run reports from sql [message #311752 is a reply to message #311744] Sun, 06 April 2008 09:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
melvinRav wrote on Sun, 06 April 2008 06:08
could you please show me how to run scripts
for example if i have the following

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool c:\test.txt
select * from emp where rownum <10;
spool off



From the SQL prompt, create a sql file:

SQL> edit test.sql

That should open up a text window in notepad or whatever editor you are using, then enter all of your commands in the test.sql file and save it (file->save). Then from the SQL prompt start the sql file:

SQL> start test.sql

Re: how to run reports from sql [message #311762 is a reply to message #311752] Sun, 06 April 2008 12:12 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thank you littlefoot , Barabara for the insights on how to run the script file ?

i would also like to know how to create a batch file to run the script ,
let say my script is test.sql
i was wondering how would one run it from a batch

[Updated on: Sun, 06 April 2008 12:21] by Moderator

Report message to a moderator

Re: how to run reports from sql [message #311764 is a reply to message #311762] Sun, 06 April 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read kukreja's answer.

Regards
Michel
problem with batch file [message #311769 is a reply to message #311719] Sun, 06 April 2008 13:19 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
i created a script test.sql

then i created a batch file test.bat with the following line

c:\oracle\ora\bin\sqlplus.exe @c:\test.sql
exit

but even though exit is there in the batch file it does not exit
it runs the sql and waits at
sql>

is there way do it automatically using batch file




Re: problem with batch file [message #311770 is a reply to message #311769] Sun, 06 April 2008 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
include the
exit
within test.sql file.
Re: problem with batch file [message #311773 is a reply to message #311770] Sun, 06 April 2008 13:44 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... because, YOUR (@melvinRav) "exit" exits batch script (which would, probably, close Command Prompt on XP), not SQL*Plus session.
Re: how to run reports from sql (merged) [message #311814 is a reply to message #311719] Sun, 06 April 2008 23:51 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thank you anacedent and littlefoot for your replies , i have created a batch file ,

the problem when i run the script it the resultant output leaves a line in between the successive rows
eg

my script contains the following
lines
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool c:\test.txt
select * from emp ;
spool off
exit

-----
the output contains
-------------------------
123 jack accounts 5000

124 mark IT 3000
---------------------------
is there a way to avoid the line spacing between two successive records

Re: how to run reports from sql (merged) [message #311817 is a reply to message #311719] Mon, 07 April 2008 00:21 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
set recsep off

regards,
Re: how to run reports from sql (merged) [message #311826 is a reply to message #311814] Mon, 07 April 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to read the following section of SQL*Plus User's Guide and Reference:
Chapter 12 SQL*Plus Command Reference
Section "SET System Variable Summary"

Regards
Michel
Re: how to run reports from sql (merged) [message #312139 is a reply to message #311817] Tue, 08 April 2008 01:32 Go to previous messageGo to next message
melvinRav
Messages: 43
Registered: April 2008
Location: India
Member
thank you mshrkshl
i tries using the set recsep off
but it did not still leave a line

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET RECSEP OFF
spool c:\test.txt
select * from emp ;
spool off
exit

is there something that i am doing wrong .
Re: how to run reports from sql (merged) [message #312486 is a reply to message #312139] Wed, 09 April 2008 00:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you read the doc Michel pointed you at?
Previous Topic: Unstable Query Execution Time
Next Topic: Changing a logon trigger does not work
Goto Forum:
  


Current Time: Sun Dec 04 20:51:08 CST 2016

Total time taken to generate the page: 0.22846 seconds