Power to Build

Home » CodeProject » SQLPlus – spool name with embedded timestamp

SQLPlus – spool name with embedded timestamp

To be able to create a spool file, with filename embedded with timestamp:

col sysdt new_value sysdt_var
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
DEFINE LOGFILE=run_filename_&&sysdt_var..log
spool &&LOGFILE

Update 05/30/2017 – The above definitely works – I use this in all my scripts now. Just noticed that this post was referred to in Oracle community post as not working. Apparently, I did correct this post since then, but I noticed that they mentioned that the script didn’t work, because the OP probably had SET DEFINE OFF. After reading that post, decided to add few more clarifications here.

It is critical that you have

SET DEFINE ON

at the top. This is the default in SQL*Plus, but in some tools (for e.g., SQLTools, this is provided as a button and is typically OFF by default). Also, you might have to explicitly turn it off sometimes, so when you reach this script, it may be off. When you turn it on, it is set to ampersand character (“&”).

Substitution Variables

The ampersand is what tells SQL*Plus to interpret ampersand (& and &&), typically used for interactive prompt to get a value from the user into a variable. These are called substitution variables, because they are literally substituted at runtime.

SQL> SELECT ‘&1’ FROM DUAL;
Enter value for 1: test
old 1: select ‘&1’ from dual
new 1: select ‘test’ from dual

‘TES
—-
test

See that old and new? That’s where &1 is getting substituted. Oh yeah, if you don’t want to see the substitution (old and new) statements, you can turn that off, using

SET VERIFY OFF

 

COL and NEW_VALUE

Another important thing to remember is the user of Col and new_value helps to make it auto-populate the populate from the database. Here is a nice post on this on Orafaq site I love and follow. I typically see the column and the matching database column are named identical often times. I named them different (sysdt and sysdt_var) to distinguish them. I deliberately named the second entity after NEW_VALUE as sysdt_var, since this is the DEFINE Variable created after you do a SELECT a value to an alias sysdt!!

Following an example on the orafaq post, here is how the variable is defined:

 

SQL> COL sysdt new_value sysdt_var
SQL>
SQL>
SQL> DEFINE sysdt_var
SP2-0135: symbol sysdt_var is UNDEFINED
SQL>
SQL> SELECT TO_CHAR(SYSDATE, ‘yyyymmdd_hh24miss’) sysdt FROM DUAL;

SYSDT
—————
20170530_111202

SQL> DEFINE sysdt_var
DEFINE SYSDT_VAR = “20170530_111202” (CHAR)
SQL> DEFINE sysdt
SP2-0135: symbol sysdt is UNDEFINED
SQL>

There you have it – the second entity after NEW_VALUE directive is the DEFINEd variable available to SQL*Plus commands. It’s confusing, but when in doubt, try it out like above. Note, SQL*Plus is not case sensitive, but I make my keywords upper case

Apparently, you can use NEW_VALUE or OLD_VALUE, but I prefer the NEW_VALUE for our purpose. These 2 directives came into SQL*Plus to use it as a report writer!

Concat?

Yet another important fact I left out earlier is the reason for 2 dots in my LOGFILE name definition. It is because, Dot (“.”) itself is a concat operator in SQL*Plus. Note that this operator is applicable to only SQL*Plus, but *not* Oracle SQL. There you use “||” (or use the Concat function) for concatenation. Lots of symbols, lot of confusion, make sure you get them right!

Oh yeah, I almost forgot. You do want to turn off the DEFINE sometimes. What if your data contains ampersand (&) in it, like Johnson & Johnson!! So if you are doing,

SELECT customer_id, customer_name, effective_date
FROM customer
WHERE customer_name = 'JOHNSON & JOHNSON';

Here you will definitely have to turn off DEFINE. In a large script, you may end up turning it ON and OFF as needed.

And yes, if you absolutely have to, you can change the DEFINE character to something else:

SET DEFINE “^”

But everytime you turn it OFF and ON, it goes back to default (“&”).

 

Prior Updates

Updated 10/03/2014 – the SQL had some special chars, so when I copied it to my script, it failed. Corrected that above. I have also changed it store the spool file name in a DEFINED variable and use it throughout the script.

Also, where it has .log, it actually needs .. (two dots).

Update 12/07/12

This was confusing, as I had the same name for column name and the SQL variable created by new_value. I’ve renamed the two accordingly. This works.

Also, I posted about spooling to HTML some time back. You can combine the two, of course:

col spoolname new_value spoolname_var
SELECT 'tablist_'||to_char(sysdate, 'yymmdd_hh24miss') || '.html' spoolname
FROM dual;

SET MARKUP HTML ON SPOOL ON -
HEAD"<TITLE> Session Stats</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"

spool &&spoolname_var

SELECT username, logon_time, status FROM v$session;
spool OFF;

creates a nice HTML file with a list of all users currently logged on to the database. This is saved in a HTML file with timestamp in it’s name!


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: