LOB STORAGE Clause-Datatypes

And lastly, the CREATE TABLE statement returned from DBMS_METADATA previously included the following for SecureFiles:LOB (“TXT”) STORE AS SECUREFILE (…STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) And here is the corresponding output for BasicFiles:LOB (“TXT”) STORE AS BASICFILE ( …STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
Read more

Getting the Difference Between Two DATEs-Datatypes

Another frequently asked question is, “How do I retrieve the difference between two dates?” The answer is deceptively simple: you just subtract them. This will return a number representing the number of days between the two dates.Additionally, you have the built-in function MONTHS_BETWEEN that will return a number representing the number of months—including fractional months—between
Read more

Dates, Timestamps, and Interval Types-Datatypes

The native Oracle datatypes of DATE, TIMESTAMP, and INTERVAL are closely related. The DATE and TIMESTAMP types store fixed date/times with varying degrees of precision. The INTERVAL type is used to store an amount of time, such as “8 hours” or “30 days,” easily. The result of subtracting two timestamps might be an interval; the
Read more

Coping with Legacy LONG Types-Datatypes

A question that arises frequently is, “What about the data dictionary in Oracle?” It is littered with LONG columns, and this makes using the dictionary columns problematic. For example, it is not possible using SQL to search the ALL_VIEWS dictionary view to find all views that contain the text HELLO:$ sqlplus eoda/foo@PDB1SQL> select * from
Read more

Adding or Subtracting Time to/from a TIMESTAMP-Datatypes

The same techniques we applied to DATE for date arithmetic work with a TIMESTAMP, but the TIMESTAMP will be converted into a DATE in many cases using the preceding techniques. For example:SQL> alter session set nls_date_format = ‘dd-mon-yyyy hh24:mi:ss’; Session altered.SQL> select systimestamp ts, systimestamp+1 dt 2 from dual;TS DT Note that adding 1 did,
Read more

BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage-Datatypes

The BINARY_FLOAT and BINARY_DOUBLE are the IEEE standard floating-points many programmers are used to working with. For a full description of what these number types look like and how they are implemented, I suggest reading http://en.wikipedia.org/ wiki/Floating-point. It is interesting to note the following in the basic definition of a floating-point number in that reference
Read more

DATE Type-Datatypes

The DATE type is a fixed-width 7-byte date/time datatype. It will always contain the seven attributes of the century, the year within the century, the month, the day of the month, the hour, the minute, and the second.Oracle uses an internal format to represent that information, so it is not really storing 20, 05, 06,
Read more
Copyright All right reserved
BACK TO TOP