개발 참고자료

/* 오라클 접속시 접속 세션의 NLS_DATE_FORMAT을 설정해준다. */


/* mnp */
String ls_Alter
ls_Alter = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' "


EXECUTE IMMEDIATE :ls_Alter USING SQLCA;
COMMIT;


아래는 응용편...


=============================================================


String ls_Alter
DateTime ldtm


select to_date('2018-01-22 00:00:00') as sChk
into :ldtm
from dual
using sqlca;


if sqlca.sqlcode <> 0 then
ls_Alter = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' "


EXECUTE IMMEDIATE :ls_Alter USING SQLCA;
COMMIT;
end if


=============================================================


/* 개별적으로 처리할때는 함수를 만들어서 이용


to_date ==> to_date_ 로 치환하여 사용한다.


*/


create or replace function to_date_(as_date varchar2)
return date
is
data_error1 exception ;
rD_Return date;
begin

select to_date(as_date, 'yyyymmdd') into rD_Return from dual;

--rD_Return := to_Date(as_date, 'YYYY-MM-DD HH24:MI:SS');
/*
if trim(as_date) is null then
rD_Return := null;
else
if substr(as_date, 5, 2) > '12' or 1 > substr(as_date, 5, 2) then
raise data_error1;
else
rD_Return := to_Date(as_date, 'yyyymmdd');
end if;

end if;
*/
Return rD_Return;
EXCEPTION
when data_error1 then
raise_application_error (-20001, as_date || '-'||substr(as_date, 5, 2)||' 이건머야.') ;
WHEN OTHERS THEN
Return null;
end;