SQL> with the_data as (Ģ select 'a' || chr(0) || 'b' as str from dualĪs you can see by concatenating percentage signs around CHR(0) (which is equivalent to nul) you can return rows with the nul in.ĭUMP() returns the data type (1 means VARCHAR2) the length of the string in bytes and the internal representation of the data the default is binary. A nul is an ASCII 0 and CHR() will return the character representation of the number you pass in.
Personally I'd use CHR() to identify the nul values. || ':' || TO_CHAR( EXTRACT( SECOND FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' ) || ':' || TO_CHAR( EXTRACT( MINUTE FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' ) || ' ' || TO_CHAR( EXTRACT( HOUR FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' ) || '-' || TO_CHAR( EXTRACT( DAY FROM date_column ), 'fm00' ) || '-' || TO_CHAR( EXTRACT( MONTH FROM date_column ), 'fm00' ) TO_CHAR( EXTRACT( YEAR FROM date_column ), 'fm00000' ) TO_CHAR( date_column, 'YYYY-MM-DD' ) AS value, To get the values stored in the table you can use DUMP to get a string containing the byte values or you can use EXTRACT to get the individual components. TO_CHAR does not work when the year exceeds the normal bounds of a date. INSERT INTO table_name ( date_column ) VALUES ( createDate( 12017, 2, 21, 0, 0, 0 ) ) INSERT INTO table_name ( date_column ) VALUES ( createDate( 15599, 12, 31, 1, 2, 3 ) ) VALUES ( DATE '' + INTERVAL '1:02:03' HOUR TO SECOND ) Then if you have a date column you can insert values you are not normally allowed to insert: CREATE TABLE table_name ( date_column DATE ) || TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )ĭBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d ) This function is an example of that: CREATE FUNCTION createDate( You can convert a raw value to a date using the DBMS_STATS.CONVERT_RAW_VALUE which means we can bypass the normal methods of creating dates and directly generate the byte values which will be stored. So the maximum date that can (technically) be stored is when those two bytes have the values 255 and 199 which would give the a year of 15599 (I'm ignoring that you could theoretically store 255 in the second byte as that opens up a whole heap of separate issues). Oracle stores DATEs in tables using 7 bytes where the first 2 bytes are: