sql plus prompt for different date and time

Uday_N

Hi All,

I am writing a script in sqlplus which should have a header displayed with day yesterday's date and 28 days before with timestamp . I have written the script but i am not able to get it. My script

My script :

SET echo OFF ver OFF pages 0 trimspool ON feed OFF und OFF;

SET term OFF;

SET linesize 30000;

SET sqlblanklines ON;

SET WRAP OFF;

spool &3

prompt "Header" , &(Date -2) , &(timestamp -28)

prompt "Value"

select * from dual ;

spool off ;

exit ;

I should get output as

'Header' ,29-01-2022 , 03-01-2022 09:30:20

Value

X

Sysdate:31- Jan-2022 .

Please kind your advice.

Regards,

Uday

Frank Kulash

Hi, @Uday_N

You can do arithmetic (such as X - Y ) in a SQL statement, but not in a PROMPT command. To get output like you requested, you can define substitution variables in a SQL statement, like this:

COLUMN	d0_col	NEW_VALUE d0
COLUMN d2_col	NEW_VALUE d2
COLUMN	d28_col NEW_VALUE d28

SELECT  TO_CHAR ( SYSDATE
		, 'DD-Mon-YYYY'
		) AS d0_col
,       TO_CHAR ( SYSDATE - 2
		, 'DD-MM-YYYY'
		) AS d2_col
,       TO_CHAR ( SYSDATE - 28
		, 'DD-MM-YYYY HH24:MI:SS'
		) AS d28_col
FROM  dual;

-- Main query
SPOOL	&3

PROMPT 'Header' ,&d2 , &d28
PROMPT	Value

SELECT *
FROM	dual;

PROMPT	Sysdate:&d0

SPOOL  OFF

All of the above can come after the SET commands.

Frank Kulash

Hi, @Uday_N

We shouldn't set header off 

If you don't expicitly SET HEADING OFF (or implicitly do it by SET PAGESIZE 0) then SELECT * FROM dual; will produce this output:

D
-
X

where D is short for DUMMY. But earlier, you said:

I should get output as

'Header' ,29-01-2022 , 03-01-2022 09:30:20

Value

X

Sysdate:31- Jan-2022 

Do you want the header lines with D and - or not? If you don't want them, why shouldn't you SET HEADING OFF ?

User_H3J7U

SQL> set heading off
SQL> select 'Header, '||to_char(sysdate-2,'yyyy-mm-dd, ')||to_char(sysdate-28, 'yyyy-mm-dd hh24:mi') nohead from dual;

Header, 2022-01-29, 2022-01-03 12:59

Uday_N

Hi,

Thanks for your reply . We shouldn't set header off . Is it possible to given in prompt itself ?

Frank Kulash

Hi, @Uday_N

You can do arithmetic (such as X - Y ) in a SQL statement, but not in a PROMPT command. To get output like you requested, you can define substitution variables in a SQL statement, like this:

COLUMN	d0_col	NEW_VALUE d0
COLUMN d2_col	NEW_VALUE d2
COLUMN	d28_col NEW_VALUE d28

SELECT  TO_CHAR ( SYSDATE
		, 'DD-Mon-YYYY'
		) AS d0_col
,       TO_CHAR ( SYSDATE - 2
		, 'DD-MM-YYYY'
		) AS d2_col
,       TO_CHAR ( SYSDATE - 28
		, 'DD-MM-YYYY HH24:MI:SS'
		) AS d28_col
FROM  dual;

-- Main query
SPOOL	&3

PROMPT 'Header' ,&d2 , &d28
PROMPT	Value

SELECT *
FROM	dual;

PROMPT	Sysdate:&d0

SPOOL  OFF

All of the above can come after the SET commands.

BluShadow

The key point is that SQL*Plus is NOT SQL itself. It doesn't have the concept of values of sysdate, or systimestamp etc. are; those are things that the database server provides when you issue SQL to it from SQL*Plus.

SQL*Plus is the interface and can do things to format resultant data or even prompt for values to bind in to queries before they are submitted to Oracle. But it's not a programming or query language itself.

Sounds like you want a user interaction for some reporting... which would be better done using a decent user interface (SQL*Plus is great for us developers, but not ideal for users) like Oracle Application Express (APEX) which can provide a web based front end, can do all the SQL and PL/SQL stuff you want, or even include Javascript and suchlike, then submit the results and present the data in ways that can be downloaded to PDF, Excel and all sorts.

Frank Kulash

Hi, @Uday_N

We shouldn't set header off 

If you don't expicitly SET HEADING OFF (or implicitly do it by SET PAGESIZE 0) then SELECT * FROM dual; will produce this output:

D
-
X

where D is short for DUMMY. But earlier, you said:

I should get output as

'Header' ,29-01-2022 , 03-01-2022 09:30:20

Value

X

Sysdate:31- Jan-2022 

Do you want the header lines with D and - or not? If you don't want them, why shouldn't you SET HEADING OFF ?

Uday_N

Hi Frank,

Really Thanks . It worked . I am really grateful to you and people like Blushadow as i learn many from this forum for many years . Thanks once again.

Hi Blushadow,

Thanks for your reply . Our system is very old where we still use sql plus for all reporting purposes . So only we have to use sql plus . Thanks once again . I have learned many things from you over the years .

Regards,

Uday