[20241118]invalid date 0000-00-00(zero year)的输入.txt
[20241118]invalid date 0000-00-00(zero year)的输入.txt
--//昨天看了链接：https://www.anbob.com/archives/8511.html，类似的问题以前我也遇到过，我记忆里第一次在toad下，如果在浏
--//览数据模式，点击显示0000-00-00的grid会报错，提示invalid date。
--//我一直不明白这些日期输入如何输入的，在sqlplus下无法输入。最后只能认为一个程序OCI接受这些输入。
--//不过作者提供在sqlplu输入的方式，测试看看。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> create table test2(ct date);
Table created.
SCOTT@book01p> insert into test2 values (0);
insert into test2 values (0)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
SCOTT@book01p> insert into test2 values(to_date('00000000','yyyymmdd'));
insert into test2 values(to_date('00000000','yyyymmdd'))
*
ERROR at line 1:
ORA-01843: not a valid month
SCOTT@book01p> select to_date('0000-00-00','yyyy-mm-dd');
select to_date('0000-00-00','yyyy-mm-dd')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
--//与我以前测试类似，sqlplus下无法插入0000-00-00日期。
--//按照日期的转换格式计算RAW换算方法，'0000-00-00 00:00:00'拼接RAW 后是100,100,0,0,1,1,1， 转换为16进制是64640000010101。
WITH
FUNCTION display_raw(rawval RAW, type VARCHAR2)
RETURN VARCHAR2
IS
cn NUMBER;
cv VARCHAR2(128);
cd DATE;
cnv NVARCHAR2(128);
cr ROWID;
cc CHAR(128);
BEGIN
IF (type = 'NUMBER') THEN
dbms_stats.convert_raw_value(rawval, cn);
RETURN to_char(cn);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cv);
RETURN to_char(cv);
ELSIF (type = 'DATE') THEN
dbms_stats.convert_raw_value(rawval, cd);
RETURN to_char(cd);
ELSIF (type = 'NVARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cnv);
RETURN to_char(cnv);
ELSIF (type = 'ROWID') THEN
dbms_stats.convert_raw_value(rawval, cr);
RETURN to_char(cr);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cc);
RETURN to_char(cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
select display_raw('64640000010101','DATE') x from dual;
/
X
------------------------------
0000-00-00 00:00:00
WITH
FUNCTION display_raw(rawval RAW, type VARCHAR2)
RETURN VARCHAR2
IS
cn NUMBER;
cv VARCHAR2(128);
cd DATE;
cnv NVARCHAR2(128);
cr ROWID;
cc CHAR(128);
BEGIN
IF (type = 'NUMBER') THEN
dbms_stats.convert_raw_value(rawval, cn);
RETURN to_char(cn);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cv);
RETURN to_char(cv);
ELSIF (type = 'DATE') THEN
dbms_stats.convert_raw_value(rawval, cd);
RETURN to_char(cd);
ELSIF (type = 'NVARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cnv);
RETURN to_char(cnv);
ELSIF (type = 'ROWID') THEN
dbms_stats.convert_raw_value(rawval, cr);
RETURN to_char(cr);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cc);
RETURN to_char(cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
insert into test2 select display_raw('64640000010101','DATE') x from dual;
/
insert into test2 select display_raw('64640000010101','DATE') x from dual
*
ERROR at line 34:
ORA-00928: missing SELECT keyword
--//oracle不支持这样写法,不知道写错在哪里。
create or replace function stats_raw_to_date (p_in raw) return date is
v_date date;
v_char varchar2(25);
begin
dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
return v_date;
exception
when others then return null;
end;
/
SCOTT@book01p> insert into test2 select stats_raw_to_date('64640000010101') x from dual;
1 row created.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> select * from test2;
CT
-------------------
2024-11-18 09:58:22
0000-00-00 00:00:00
--//21c也提供dbms_stats.CONVERT_RAW_TO_DATE函数。
SCOTT@book01p> @ desc_proc sys dbms_stats CONVERT_RAW_to_%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
---------- ------------ ------------------------------ ---------- ------------- ------------- --------- ----------
SYS DBMS_STATS CONVERT_RAW_TO_VARCHAR2 1 VARCHAR2 OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_DATE 1 DATE OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_NUMBER 1 NUMBER OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_BIN_FLOAT 1 BINARY_FLOAT OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_BIN_DOUBLE 1 BINARY_DOUBLE OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_NVARCHAR 1 NVARCHAR2 OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_ROWID 1 ROWID OUT N
2 RAWVAL RAW IN N
14 rows selected.
--//有了这些函数方便多了，以前11g版本仅仅有dbms_stats.convert_raw_value，而且返回值保存在第2个参数里面。
--//于是才有了上面的函数display_raw。
SCOTT@book01p> select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ;
DBMS_STATS.CONVERT_
-------------------
0000-00-00 00:00:00
SCOTT@book01p> insert into test2 select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ;
1 row created.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> select * from test2 where ct=dbms_stats.CONVERT_RAW_TO_DATE('64640000010101');
CT
-------------------
0000-00-00 00:00:00
0000-00-00 00:00:00
--//顺便提一下目前的toad 12.7版本，显示的是 0001/1/1.
select dump(ct,16) c10 ,test2.* from test2
C10 CT
-------------------------------- ---------------------
Typ=12 Len=7: 78,7c,b,12,a,3b,17 2024-11-18 09:58:22
Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00
Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00
已选择 3 行。
--//昨天看了链接：https://www.anbob.com/archives/8511.html，类似的问题以前我也遇到过，我记忆里第一次在toad下，如果在浏
--//览数据模式，点击显示0000-00-00的grid会报错，提示invalid date。
--//我一直不明白这些日期输入如何输入的，在sqlplus下无法输入。最后只能认为一个程序OCI接受这些输入。
--//不过作者提供在sqlplu输入的方式，测试看看。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> create table test2(ct date);
Table created.
SCOTT@book01p> insert into test2 values (0);
insert into test2 values (0)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
SCOTT@book01p> insert into test2 values(to_date('00000000','yyyymmdd'));
insert into test2 values(to_date('00000000','yyyymmdd'))
*
ERROR at line 1:
ORA-01843: not a valid month
SCOTT@book01p> select to_date('0000-00-00','yyyy-mm-dd');
select to_date('0000-00-00','yyyy-mm-dd')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
--//与我以前测试类似，sqlplus下无法插入0000-00-00日期。
--//按照日期的转换格式计算RAW换算方法，'0000-00-00 00:00:00'拼接RAW 后是100,100,0,0,1,1,1， 转换为16进制是64640000010101。
WITH
FUNCTION display_raw(rawval RAW, type VARCHAR2)
RETURN VARCHAR2
IS
cn NUMBER;
cv VARCHAR2(128);
cd DATE;
cnv NVARCHAR2(128);
cr ROWID;
cc CHAR(128);
BEGIN
IF (type = 'NUMBER') THEN
dbms_stats.convert_raw_value(rawval, cn);
RETURN to_char(cn);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cv);
RETURN to_char(cv);
ELSIF (type = 'DATE') THEN
dbms_stats.convert_raw_value(rawval, cd);
RETURN to_char(cd);
ELSIF (type = 'NVARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cnv);
RETURN to_char(cnv);
ELSIF (type = 'ROWID') THEN
dbms_stats.convert_raw_value(rawval, cr);
RETURN to_char(cr);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cc);
RETURN to_char(cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
select display_raw('64640000010101','DATE') x from dual;
/
X
------------------------------
0000-00-00 00:00:00
WITH
FUNCTION display_raw(rawval RAW, type VARCHAR2)
RETURN VARCHAR2
IS
cn NUMBER;
cv VARCHAR2(128);
cd DATE;
cnv NVARCHAR2(128);
cr ROWID;
cc CHAR(128);
BEGIN
IF (type = 'NUMBER') THEN
dbms_stats.convert_raw_value(rawval, cn);
RETURN to_char(cn);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cv);
RETURN to_char(cv);
ELSIF (type = 'DATE') THEN
dbms_stats.convert_raw_value(rawval, cd);
RETURN to_char(cd);
ELSIF (type = 'NVARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cnv);
RETURN to_char(cnv);
ELSIF (type = 'ROWID') THEN
dbms_stats.convert_raw_value(rawval, cr);
RETURN to_char(cr);
ELSIF (type = 'VARCHAR2') THEN
dbms_stats.convert_raw_value(rawval, cc);
RETURN to_char(cc);
ELSE
RETURN 'UNKNOWN DATATYPE';
END IF;
END;
insert into test2 select display_raw('64640000010101','DATE') x from dual;
/
insert into test2 select display_raw('64640000010101','DATE') x from dual
*
ERROR at line 34:
ORA-00928: missing SELECT keyword
--//oracle不支持这样写法,不知道写错在哪里。
create or replace function stats_raw_to_date (p_in raw) return date is
v_date date;
v_char varchar2(25);
begin
dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
return v_date;
exception
when others then return null;
end;
/
SCOTT@book01p> insert into test2 select stats_raw_to_date('64640000010101') x from dual;
1 row created.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> select * from test2;
CT
-------------------
2024-11-18 09:58:22
0000-00-00 00:00:00
--//21c也提供dbms_stats.CONVERT_RAW_TO_DATE函数。
SCOTT@book01p> @ desc_proc sys dbms_stats CONVERT_RAW_to_%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
---------- ------------ ------------------------------ ---------- ------------- ------------- --------- ----------
SYS DBMS_STATS CONVERT_RAW_TO_VARCHAR2 1 VARCHAR2 OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_DATE 1 DATE OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_NUMBER 1 NUMBER OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_BIN_FLOAT 1 BINARY_FLOAT OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_BIN_DOUBLE 1 BINARY_DOUBLE OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_NVARCHAR 1 NVARCHAR2 OUT N
2 RAWVAL RAW IN N
CONVERT_RAW_TO_ROWID 1 ROWID OUT N
2 RAWVAL RAW IN N
14 rows selected.
--//有了这些函数方便多了，以前11g版本仅仅有dbms_stats.convert_raw_value，而且返回值保存在第2个参数里面。
--//于是才有了上面的函数display_raw。
SCOTT@book01p> select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ;
DBMS_STATS.CONVERT_
-------------------
0000-00-00 00:00:00
SCOTT@book01p> insert into test2 select dbms_stats.CONVERT_RAW_TO_DATE('64640000010101') from dual ;
1 row created.
SCOTT@book01p> commit ;
Commit complete.
SCOTT@book01p> select * from test2 where ct=dbms_stats.CONVERT_RAW_TO_DATE('64640000010101');
CT
-------------------
0000-00-00 00:00:00
0000-00-00 00:00:00
--//顺便提一下目前的toad 12.7版本，显示的是 0001/1/1.
select dump(ct,16) c10 ,test2.* from test2
C10 CT
-------------------------------- ---------------------
Typ=12 Len=7: 78,7c,b,12,a,3b,17 2024-11-18 09:58:22
Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00
Typ=12 Len=7: 64,64,0,0,1,1,1 0001-01-01 00:00:00
已选择 3 行。
