Forum Stats

  • 3,827,715 Users
  • 2,260,808 Discussions
  • 7,897,357 Comments

Discussions

sql plus prompt for different date and time

Uday_N
Uday_N Member Posts: 318 Bronze Badge

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

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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.

    Uday_N
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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 ?

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    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
  • Uday_N
    Uday_N Member Posts: 318 Bronze Badge

    Hi,

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

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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.

    Uday_N
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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.

    Uday_N
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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
    Uday_N Member Posts: 318 Bronze Badge

    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