question about trim to_date combination with dynamic sql
512952Sep 23 2008 — edited Sep 23 2008I created:
CREATE TABLE test1
(birthday DATE NOT NULL)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE dnvk
STORAGE (
INITIAL 20480
NEXT 20480
PCTINCREASE 10
MINEXTENTS 1
MAXEXTENTS 99
)
/
Then I insert one row:
insert into test1
values(sysdate);
Then i do the following into sql-navigator:
DECLARE
v_test VARCHAR2(2000) := 'update test1 set birthday = trim(to_date(''07-07-1937'',''DD-MM-YYYY''))';
BEGIN
EXECUTE IMMEDIATE v_test;
commit;
END;
Result is: 07-07-1937
When i do the same in sql*plus:
DECLARE
v_test VARCHAR2(2000) := 'update test1 set birthday = trim(to_date(''07-07-1937'',''DD-MM-YYYY''))';
BEGIN
EXECUTE IMMEDIATE v_test;
commit;
END;
The result is 07-07-2037
Anyone an idea what the problem is?