Creating Logs while execution of SQL file using Command Promt

Hi I am using Oracle9i database.I was trying to execute one SQL inside one schema in that database using command line.
I opened command line,and connected to that database SCHEMA in SQLPLUS and was executing sqlfile. It was like that
@sqlfile.sql and then executed sql in that database schema.But what I am looking is,If we can maintain logfile for each line which commandline display while sql execution.Please help me.
SudhanshumAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
yes.

--this will echo every command as it is executed
set echo on

--capture the output
spool myfile.txt
--the rest of the script

--turn off and flush the output
spool off
SudhanshumAuthor Commented:
Please tell me How i will remove zones?
SudhanshumAuthor Commented:
Hi Thanks for reply.So command should be like below:
C:>sqlplus
Enter user-name:
Enter password:
SQL>spool myfile.txt
SQL>@sqlfile.sql

 using above command it will record all sqlfile logs in myfile.txt file? Right now when I try to copy commanline full screen then it copy full screen but my starting query disappear from window.Please help.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>> using above command it will record all sqlfile logs in myfile.txt file?

It will capture everything displayed to the screen.

Don't forget to spool off at the end.

You might also need to use the set echo on depending on exactly what you are after.


Just do some simple tests:
spool myfile.txt
select 'Hello' from dual;
select 'World' from dual;
spool off
SudhanshumAuthor Commented:
Hi,Thanks for reply.This command seems to be working fine.I executed this
spool myfile.txt
select 'Hello' from dual;
select 'World' from dual;
spool off
and it recorded logs for select outputquery.I want to understand use of set echo,If I need that too?

Regards,
Sudhanshu
slightwv (䄆 Netminder) Commented:
>I want to understand use of set echo,If I need that too?

That test showed the selects because you see them on the screen... remember the spool command captures what is displayed.

When you execute a script, do you see the commands?

Take the test one step farther.

create a file: myscript.sql with the following contents:
select 'Hello' from dual;
select 'World' from dual;


Then from the SQL prompt:
spool myfile.txt
@myscript
spool off


What is in the file?

Then experiment with the echo command...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.