Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
sql plus prompt for different date and time

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
Best Answers
-
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.
-
Hi, @Uday_N
We shouldn't set header off
If you don't expicitly
SET HEADING OFF
(or implicitly do it bySET PAGESIZE 0
) thenSELECT * 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 youSET HEADING OFF
?
Answers
-
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
-
Hi,
Thanks for your reply . We shouldn't set header off . Is it possible to given in prompt itself ?
-
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.
-
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.
-
Hi, @Uday_N
We shouldn't set header off
If you don't expicitly
SET HEADING OFF
(or implicitly do it bySET PAGESIZE 0
) thenSELECT * 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 youSET HEADING OFF
? -
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