Total Pageviews

2015/07/31

[Oracle to SQL Server Migration] TO_CHAR - Convert Datetime to String

In Oracle
SELECT to_char(MAX(PRT_DATE),'yyyymmdd')
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

But SQL Server does not have to_char function, so it need to translate as bellowing:
SELECT CONVERT(VARCHAR(8), MAX(PRT_DATE), 112)
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

Reference
[1] http://www.sqlines.com/oracle-to-sql-server/to_char_datetime
[2] https://msdn.microsoft.com/en-us/library/ms187928.aspx

[Oracle to SQL Server Migration] An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Problem
Here is my original SQL statement in Oracle:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTR(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND TO_CHAR(B.STA_DATE, 'yyyy') = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

Owing to I need to change database to Microsoft SQL Server. Therefore, the foregoing SQL statement will be translated as following:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

But as I executed this SQL statement in SQL Sever, it throw this exception unexpectedly:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(除非彙總置於 HAVING 子句或選取清單所包含的子查詢中,且彙總的資料行為外部參考,否則不得在 WHERE 子句中出現。)

Solution
Move CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911 from where clause to having clause:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
HAVING CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear+ 1911
ORDER BY org_type


Reference
[1] http://blog.xuite.net/a88370.a88370/cjoushua/73695699-SQL+%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-Having
[2] https://technet.microsoft.com/en-us//library/cc645611(v=sql.105).aspx

2015/07/29

oracle.net.ns.NetException: Got minus one from a read call

Problem
My web application environment is :

  • Database: Oracle
  • Application Server: JBoss

As I startup JBoss, it show this error message unexpectedly:
1
2
3
4
5
6
 Caused by: oracle.net.ns.NetException: Got minus one from a read call
  at oracle.net.ns.Packet.receive(Packet.java:311) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  ... 124 common frames omitted


Resolution
This exception result from the number of process exceed the maximum value.

Therefore, you need to alter the number of process in oracle :
ALTER SYSTEM
SET processes=<number> SCOPE=spfile;

Remember to retsart Oracle.

Reference
[1] http://oraclepath.blogspot.tw/2013/10/javasqlsqlexception-got-minus-one-from.html

2015/07/27

[Oracle to SQL Server Migration] How to convert Oracle User-Defined Function to SQL Server

Here is my oracle user-defined function:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
create or replace FUNCTION F_DBM_FIND_CODE_NM (PI_KIND_CODE IN VARCHAR2, PI_CODE_NO   IN NUMBER)
   RETURN VARCHAR2
IS
   CODE_NAME NVARCHAR2(50);
   V_KIND_CODE VARCHAR2(5) :=  PI_KIND_CODE;
   V_CODE_NO    NUMBER (10) := PI_CODE_NO;
   V_SQLSTR VARCHAR2 (200) := '';
BEGIN
   V_SQLSTR := 'SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = :1 AND CODE_NO = :2';
   EXECUTE IMMEDIATE V_SQLSTR INTO CODE_NAME USING V_KIND_CODE, V_CODE_NO;  
   
   RETURN CODE_NAME;
END F_DBM_FIND_CODE_NM;


But the syntax has big difference in Microsoft SQL Server:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Hence, the oracle user-defined function will be translated as bellows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE FUNCTION AP_NTA.F_DBM_FIND_CODE_NM 
    (
      @PI_KIND_CODE VARCHAR(5),
      @PI_CODE_NO NUMERIC(10,0)
    )
RETURNS NVARCHAR(50)
BEGIN
RETURN 
(
    SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = @PI_KIND_CODE AND CODE_NO = @PI_CODE_NO
)
END
;

Test function in Microsoft SQL Server:

select AP_NTA.F_DBM_FIND_CODE_NM('IK', 47) as codeName;




Reference
[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
[2] https://msdn.microsoft.com/en-us/library/ms186755.aspx

2015/07/26

2015/07 Kyoto

茶山站


清水寺



恵文社一乗寺店


京都文化博物館



南禪寺


鴨川納涼床


貴船神社


貴船流水麵線


鞍馬站



2015/07 Osaka

心齋橋筋


Harukas 300 - 展望台


Universal Studio Japan




2015/07/17

[MS SQL Server] How to insert an explicit value into datetime column

In Oracle, it would be wrote as bellows:
1
2
3
4
5
6
7
8
INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (to_date('01-1月 -14','DD-MON-RR'),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        to_date('20-3月 -15','DD-MON-RR'));


In Microsoft SQL Server, it should be modified as following:
1
2
3
4
5
6
7
8
INSERT INTO AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE)
VALUES (CAST('2014-01-01' AS datetime),
        '2',
        '397000000A',
        '高雄市政府',
        'A00000000A',
        'TEST',
        CAST('2015-03-20' AS datetime));


Reference
[1] http://stackoverflow.com/questions/10262426/sql-server-cannot-insert-an-explicit-value-into-a-timestamp-column

[Oracle to SQL Server Migration] Data Type Mapping for Oracle and MS SQL Server


Oracle data type
SQL Server data type
Alternatives
BFILE
VARBINARY(MAX)
Yes
BLOB
VARBINARY(MAX)
Yes
CHAR([1-2000])
CHAR([1-2000])
Yes
CLOB
VARCHAR(MAX)
Yes
DATE
DATETIME
Yes
FLOAT
FLOAT
No
FLOAT([1-53])
FLOAT([1-53])
No
FLOAT([54-126])
FLOAT
No
INT
NUMERIC(38)
Yes
INTERVAL
DATETIME
Yes
LONG
VARCHAR(MAX)
Yes
LONG RAW
IMAGE
Yes
NCHAR([1-1000])
NCHAR([1-1000])
No
NCLOB
NVARCHAR(MAX)
Yes
NUMBER
FLOAT
Yes
NUMBER([1-38])
NUMERIC([1-38])
No
NUMBER([0-38],[1-38])
NUMERIC([0-38],[1-38])
Yes
NVARCHAR2([1-2000])
NVARCHAR([1-2000])
No
RAW([1-2000])
VARBINARY([1-2000])
No
REAL
FLOAT
No
ROWID
CHAR(18)
No
TIMESTAMP
DATETIME
Yes
TIMESTAMP(0-7)
DATETIME
Yes
TIMESTAMP(8-9)
DATETIME
Yes
TIMESTAMP(0-7) WITH TIME ZONE
VARCHAR(37)
Yes
TIMESTAMP(8-9) WITH TIME ZONE
VARCHAR(37)
No
TIMESTAMP(0-7) WITH LOCAL TIME ZONE
VARCHAR(37)
Yes
TIMESTAMP(8-9) WITH LOCAL TIME ZONE
VARCHAR(37)
No
UROWID
CHAR(18)
No
VARCHAR2([1-4000])
VARCHAR([1-4000])
Yes

The DDL script is for Oracle database:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATE, 
 "ORG_TYPE" VARCHAR2(1 BYTE), 
 "AGE" VARCHAR2(12 BYTE), 
 "AGE_NAME" NVARCHAR2(30), 
 "MASTER_AGE" VARCHAR2(12 BYTE), 
 "USER_ID" VARCHAR2(20 BYTE), 
 "UPDATE_DATE" DATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
REM INSERTING into AP_NTA.DBM030FA
SET DEFINE OFF;
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','397000000A','高雄市政府','A00000000A','TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A00000000A','行政院',null,'TEST',to_date('20-3月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'2','382000000A','新北市政府','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376580000A','新竹市政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'1','A01000000A ','內政部','A00000000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'4','376446400A ','竹北市公所','376440000A','TEST',to_date('16-4月 -15','DD-MON-RR'));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (to_date('01-1月 -14','DD-MON-RR'),'3','376440000A','新竹縣政府','A01000000A ','TEST',to_date('16-4月 -15','DD-MON-RR'));
--------------------------------------------------------
--  DDL for Index SYS_C0072659
--------------------------------------------------------

  CREATE UNIQUE INDEX "AP_NTA"."SYS_C0072659" ON "AP_NTA"."DBM030FA" ("STA_DATE", "ORG_TYPE", "AGE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT" ;
--------------------------------------------------------
--  Constraints for Table DBM030FA
--------------------------------------------------------

  ALTER TABLE "AP_NTA"."DBM030FA" ADD PRIMARY KEY ("STA_DATE", "ORG_TYPE", "AGE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBM_DAT"  ENABLE;
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE_NAME" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("AGE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("ORG_TYPE" NOT NULL ENABLE);
  ALTER TABLE "AP_NTA"."DBM030FA" MODIFY ("STA_DATE" NOT NULL ENABLE);


The following DDL script had been translated for Microsoft SQL Sever database:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
  CREATE TABLE "AP_NTA"."DBM030FA" 
   ( "STA_DATE" DATETIME, 
 "ORG_TYPE" VARCHAR(1), 
 "AGE" VARCHAR(12), 
 "AGE_NAME" NVARCHAR(30), 
 "MASTER_AGE" VARCHAR(12), 
 "USER_ID" VARCHAR(20), 
 "UPDATE_DATE" DATETIME,
 CONSTRAINT DBM030FA_PK PRIMARY KEY (STA_DATE, ORG_TYPE, AGE)
   ) ;
   

Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','397000000A','高雄市政府','A00000000A','TEST',CAST('2015-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A00000000A','行政院',null,'TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'2','382000000A','新北市政府','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376580000A','新竹市政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'1','A01000000A ','內政部','A00000000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'4','376446400A ','竹北市公所','376440000A','TEST',CAST('2014-07-17' AS datetime));
Insert into AP_NTA.DBM030FA (STA_DATE,ORG_TYPE,AGE,AGE_NAME,MASTER_AGE,USER_ID,UPDATE_DATE) values (CAST('2014-01-01' AS datetime),'3','376440000A','新竹縣政府','A01000000A ','TEST',CAST('2014-07-17' AS datetime));

Reference
[1] https://msdn.microsoft.com/en-us/library/ms151817.aspx