wviewweather.com

obarlow · Posted: Tue Apr 18, 2006 4:16 pm Post subject: Oracle

Any chance of adding oracle to the list of supported data bases? I've created the table in oracle and have mapped the datatypes from mysql to oracle.

To login to oracle you would execute the statement

sqlplus userid/password@oracle_instance

example
sqlplus wview/wviewpasswd@zk

SQL> SQL> drop table wview.wview_data;

Table dropped.

SQL> create table wview.wview_data
2 ( RecordTime date
3 , ArcInt NUMBER
4 , OutTemp NUMBER
5 , HiOutTemp float
6 , LowOutTemp float
7 , InTemp float
8 , Barometer float
9 , OutHumid float
10 , InHumid float
11 , Rain float
12 , HiRainRate float
13 , WindSpeed float
14 , HiWindSpeed float
15 , WindDir NUMBER(11)
16 , HiWindDir NUMBER(11)
17 , Dewpoint float
18 , WindChill float
19 , HeatIndex float
20 , solarRad NUMBER(11)
21 , hiSolarRad NUMBER(11)
22 , UV float
23 , hiUV float
24 , ET float
25 , leafTemp1 NUMBER(11)
26 , leafTemp2 NUMBER(11)
27 , leafTemp3 NUMBER(11)
28 , leafTemp4 NUMBER(11)
29 , leafWet1 NUMBER(11)
30 , leafWet2 NUMBER(11)
31 , leafWet3 NUMBER(11)
32 , leafWet4 NUMBER(11)
33 , soilTemp1 NUMBER(11)
34 , soilTemp2 NUMBER(11)
35 , soilTemp3 NUMBER(11)
36 , soilTemp4 NUMBER(11)
37 , soilTemp5 NUMBER(11)
38 , soilTemp6 NUMBER(11)
39 , soilMoist1 NUMBER(11)
40 , soilMoist2 NUMBER(11)
41 , soilMoist3 NUMBER(11)
42 , soilMoist4 NUMBER(11)
43 , soilMoist5 NUMBER(11)
44 , soilMoist6 NUMBER(11)
45 );

Table created.

SQL> desc wview.wview_data
Name Null? Type
----------------------------------------------------- -------- -----------
RECORDTIME DATE
ARCINT NUMBER
OUTTEMP NUMBER
HIOUTTEMP FLOAT(126)
LOWOUTTEMP FLOAT(126)
INTEMP FLOAT(126)
BAROMETER FLOAT(126)
OUTHUMID FLOAT(126)
INHUMID FLOAT(126)
RAIN FLOAT(126)
HIRAINRATE FLOAT(126)
WINDSPEED FLOAT(126)
HIWINDSPEED FLOAT(126)
WINDDIR NUMBER(11)
HIWINDDIR NUMBER(11)
DEWPOINT FLOAT(126)
WINDCHILL FLOAT(126)
HEATINDEX FLOAT(126)
SOLARRAD NUMBER(11)
HISOLARRAD NUMBER(11)
UV FLOAT(126)
HIUV FLOAT(126)
ET FLOAT(126)
LEAFTEMP1 NUMBER(11)
LEAFTEMP2 NUMBER(11)
LEAFTEMP3 NUMBER(11)
LEAFTEMP4 NUMBER(11)
LEAFWET1 NUMBER(11)
LEAFWET2 NUMBER(11)
LEAFWET3 NUMBER(11)
LEAFWET4 NUMBER(11)
SOILTEMP1 NUMBER(11)
SOILTEMP2 NUMBER(11)
SOILTEMP3 NUMBER(11)
SOILTEMP4 NUMBER(11)
SOILTEMP5 NUMBER(11)
SOILTEMP6 NUMBER(11)
SOILMOIST1 NUMBER(11)
SOILMOIST2 NUMBER(11)
SOILMOIST3 NUMBER(11)
SOILMOIST4 NUMBER(11)
SOILMOIST5 NUMBER(11)
SOILMOIST6 NUMBER(11)

SQL> insert into wview.wview_data
( RecordTime
, ArcInt
, OutTemp
, HiOutTemp
, LowOutTemp
, InTemp
, Barometer
, OutHumid
, InHumid
, Rain
, HiRainRate
, WindSpeed
, HiWindSpeed
, WindDir
, HiWindDir
, Dewpoint
, WindChill
, HeatIndex
, solarRad
, hiSolarRad
, UV
, hiUV
, ET
, leafTemp1
, leafTemp2
, leafTemp3
, leafTemp4
, leafWet1
, leafWet2
, leafWet3
, leafWet4
, soilTemp1
, soilTemp2
, soilTemp3
, soilTemp4
, soilTemp5
, soilTemp6
, soilMoist1
, soilMoist2
, soilMoist3
, soilMoist4
, soilMoist5
, soilMoist6
)
values
( to_date('2006-04-07 14:20:00','YYYY-MM-DD HH24:MI:SS')
, 5
, 30.4
, 30.4
, 30.3
, 72.3
, 30.174
, 81
, 18
, 0
, 0
, 0
, 0
, -1
, -1
, 25.2807
, 30.4
, 30.4
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
);
1 row created.

SQL> commit;

Commit complete.

SQL> delete wview.wview_data;

1 row deleted.

SQL> commit;

Commit complete.

data type conversion
MySQL Data Type Oracle Data Type

TINYINT NUMBER(3, 0)
SMALLINT NUMBER(5, 0)
MEDIUMINT NUMBER(7, 0)
INT NUMBER(10, 0)
INTEGER NUMBER(10, 0)
BIGINT NUMBER(19, 0)
FLOAT FLOAT
DOUBLE FLOAT (24)
DOULBE PRECISION FLOAT (24)
REAL FLOAT (24)
DECIMAL FLOAT (24)
NUMERIC NUMBER
DATE DATE
DATETIME DATE
TIMESTAMP NUMBER
TIME DATE
YEAR NUMBER
CHAR CHAR
VARCHAR VARCHAR2
TINYBLOB RAW
TINYTEXT VARCHAR2
BLOB BLOB, RAW
TEXT VARCHAR2, CLOB
MEDIUMBLOB BLOB, RAW
MEDIUMTEXT RAW, CLOB
LONGBLOB BLOB, RAW
LONGTEXT RAW, CLOB
ENUM VARCHAR2, set to 100 by default
SET VARCHAR2, set to 100 by default