Shell Script!

[kkelly]

Hi guys

Been asked to make a shell script at work and this is part of it!

 Connection stuff :)!


VALUE=$( sqlplus -s userdetailshere<<EOF
set feedback off;
set linesize 60;
set serveroutput on;
[B]select to_char(sysdate, 'DDMMYYYYHHMM') from dual;[/B]
EXIT;
EOF)
echo $VALUE

The problem is the sql statement marked in bold, when i add formatting to the date i get a 'ORA-000904' error stating that 'DDMMYYY' is not a column ?
It works if i take the formatting out but i need it in though for the task i am trying!

Thanks Kevin

 

[kkelly]

 Connection stuff :)!


VALUE=$`sqlplus -s userdetailshere<<EOF
set feedback off;
set linesize 60;
set serveroutput on;
[B]select to_char(sysdate, 'DDMMYYYYHHMM') from dual;[/B]
EXIT;
EOF`
echo $VALUE

Fixed

 

[Manlove is my forte]

 Connection stuff :)!


VALUE=$`sqlplus -s userdetailshere<<EOF
set feedback off;
set linesize 60;
set serveroutput on;
[B]select to_char(sysdate, 'DDMMYYYYHHMM') from dual;[/B]
EXIT;
EOF`
echo $VALUE

Fixed

Nit picking but the sysdate, DDMMYYYYHHMM will give you DayMonthYearHours(in 12hr time)Month

You want to use DDMMYYYYHH24MI

personally I would also do the following at the top of your script

set pages 0
alter session set nls_date_format = 'DDMMYYYYHH24MI';

This will stop the output heading being displayed and you will only need the sql

select sysdate from dual;

If you were to add more sql which had date outputs in it, then your date output would all be in the same format (unless you needed a specific date format then you would use the to_char function to specify the desired output

 

[kkelly]

Hi,

Thanks for the reply the SQL statement was just for testing i was getting any output from the oracle box

I am still working on the proper version at work the problem is that I have sysdate - another date I am trying to get the resulting output in minutes, however i am getting 1400 - 1335 = 65 and not 25

 

[Manlove is my forte]

Hi,

Thanks for the reply the SQL statement was just for testing i was getting any output from the oracle box

I am still working on the proper version at work the problem is that I have sysdate - another date I am trying to get the resulting output in minutes, however i am getting 1400 - 1335 = 65 and not 25

select (sysdate - STARTUP_TIME)*86400 "Difference In Seconds" from v$instance
select (sysdate - STARTUP_TIME)*1440 "Difference In Minutes" from v$instance
select (sysdate - STARTUP_TIME)*24 "Difference In Hours" from v$instance

 

[kkelly]

Thanks Manlove is my forte,

I shall try it later on tonight !

Kevin

 

[kkelly]

Hi there,

Just got around to trying this still not working correctly also tried it in excel but the results are the same

#! /bin/sh

DateVal1='201220081000'
DateVal2='201220080945'
sum=$(($DateVal1-$DateVal2))

echo $DateVal1
echo $DateVal2
echo $sum

sum returns 55 when i do sum * 1440 the result is 79200 I have been googling and they seem to suggest multiplying but 1440 also am I missing something silly ?

Thanks Kevin.

 

[AndrewP]

If you want to do arithmetic with dates I think your best bet is converting into timestamp format (seconds since 00:00 01/01/70), doing the maths on those and displaying as required.

E.g.

date -d "2008-12-27 10:45" +%s

gives 1230374700

Then have a look at the date command's man page for the different formats you can output in.

 

[Manlove is my forte]

Hi there,

Just got around to trying this still not working correctly also tried it in excel but the results are the same

#! /bin/sh

DateVal1='201220081000'
DateVal2='201220080945'
sum=$(($DateVal1-$DateVal2))

echo $DateVal1
echo $DateVal2
echo $sum

sum returns 55 when i do sum * 1440 the result is 79200 I have been googling and they seem to suggest multiplying but 1440 also am I missing something silly ?

Thanks Kevin.

use sqlplus to do the maths. Its a lot less of ball breaker than using shell script