Comments in SQL*Plus

This Warning from Eddie Awad remind me of problem with comments I've had some time ago. I've prepared an script that was intended to run in sqlplus. Then I've extensively tested it, and as a last step, I've commented it. And ... did not test again. When this script was run on production caused errors. The problem was in the way that sqlplus interpret comments.
Example:

SQL> select * from dual;

D
-
X

runs fine, while this:

SQL> select * from dual; -- some comments

stops at second line waiting for input:

SQL> select * from dual; -- some comment
  2 

It was really unpleasant experience.

Cheers, Paweł

Extracting Data to Excel using only SQL*Plus

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:

1. start command line (CMD)
2. set NLS_LANG variable
   

   set NLS_LANG=.UTF8

3. start sqlplus and log to database
   

   sqlplus user/pwd@db

4. set long line size and avoid paging
   

   SQL> set linesize 4000
   SQL> set pagesize 0

5. set markup html in sqlplus
   

   SQL> set markup html on spool on

   
   It is important to use spool on clause, as it adds html header to spool file when spooling is started
6. start spooling
   

   SQL> spool a.html

7. run your query
   
8. turn off spooling
   

   SQL> spool off

9. open file in Excel

I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł

Dynamically set name of spool file in SQL*Plus

Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

If there is a need to have also name of database included into filename then it can be read from global_name view:

define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||
 to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'

Hope you find it useful.
I would also like to share an photo I made in Rome in February this year

To see more pictures you are welcome to follow Rome 1, Rome 2, Rome 3

Cheers, Paweł