albert's blog

2014/09/06

A good web site to learn AngularJS

If we would like to learn AngularJS, you can go to here, http://jsbin.com/, to learn. You just need a web browser, do not need to have any IDE(Integrated Development Environment) tools.

Here has a quick start.
1. Go to http://jsbin.com/

2. Click "Add Library"

3. Choose "Angular 1.2.14 stable"

4. It will import "angular.min.js" automatically

5. Place "ng-app" directive in html tag

6. create a input text for typing name

7. Check output preivew

8. Design the welcome message as user type in name

9. Demo

2014/09/05

SQL Query Order of Operations

Problem

Here is the select SQL statement:

SELECT YEAR,
       PERIOD,
       NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
       START_DATE, --日期
       NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
       NVL(AMT2, 0) AS AMT2,  --舉借數額 -短期借款(2)
       NVL(AMT3, 0) AS AMT3,  --償還數額-國庫券(3)
       NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC

And I would to get the first record from the result set.

Therefore, I add rownum=1 in my where clause.
Owning to I need to do union with another select statement, so I move it into subquery as bellows:

SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE,  --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231' AND ROWNUM=1
   ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
UNION ALL .....

But I get this record!

Root Cause
This unexpected result result from the SQL query order of operations, the order is:
FROM clause --> WHERE clause --> GROUP BY clause --> HAVING clause --> SELECT clause -->ORDER BY clause

If we marked rownum=1 and order by cluase

SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE, --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'-- AND ROWNUM=1
 --ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC
 )
UNION ALL .....

We can find the first record is '1021226' not '1021227', if we marked rownum=1 and order by cluase. Owing the query order operation is from --> where --> select --> order by, 'rownum=1' have already get the first record before order by. So it's the reason why we get the unexpected result.

Solution
We need to get the result which had already been sorted, and apply rownum=1 to get the first record.

SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE, --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
   ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
WHERE ROWNUM=1
UNION ALL .....

Reference
[1] http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

2014/08/28

[Oracle] How to Create Before Insert Trigger

Requirement
Here has one of my table schema, and the first column, SEQ_NO, will be assigned serial number which value will be get from SE_FMS_CF0006 sequence.

I hope I do not need get the sequence value manually and assign to SEQ_NO column manually.

Column	Type	Size	Nulls	Default	Comments
SEQ_NO	number	20			流水號SELECT SE_FMS_CF0006.NEXTVAL FROM DUAL
AGE	varchar2	7	√	null	資料提供機構代碼
FILE_TYPE	varchar2	8	√	null	傳輸檔案識別
PDATE	varchar2	7			資料日期
FYR	varchar2	3	√	null	會計年度
ACC	varchar2	11			科目代碼
ACC_APPENDIX	varchar2	3			科目代碼附加碼
AMOUNT	number	15,2	√	0	金額
UPDATE_DATE	timestamp(6)	11,6	√	now	更新日期
USER_ID	varchar2	20	√	null	異動者

Solution
We can make good use of trigger to fulfill this requirement, you can check the before insert trigger.
As you do insert, you can skip SEQ_NO, this trigger will get the serial number before you do insert.

CREATE OR REPLACE TRIGGER AP_PSR.TR_BI_CF0006  
  BEFORE INSERT ON AP_PSR.CF0006  FOR EACH ROW  
 BEGIN  
  IF :NEW.SEQ_NO IS NULL THEN  
   SELECT SE_FMS_CF0006.NEXTVAL INTO :NEW.SEQ_NO FROM DUAL;  
  END IF;  
 END;

Demo
Assume I would like to execute the insert statement, I only provide values to mandatory columns except SEQ_NO.

INSERT INTO cf0006(pdate, acc, acc_appendix)
VALUES('1030401', '123', '123');

Then execute the select statement:

SELECT * FROM cf0006;

See.... I did not assign any value to SEQ_NO, its value assigned by TR_BI_CF0006 trigger automatically.

Reference
[1] http://www.techonthenet.com/oracle/triggers/before_insert.php

2014/08/27

ORA-17004 : Invalid Column Type

Problem
I am using JPA(Java Persistence API) as my persistence tier technology to write data into Oracle database. Here is the database schema for the targeted table:

Column	Type	Size	Nulls	Default	Comments
EXE_ID	number	20			執行ID, Calendar.getInstance().getTimeInMillis()
FUNCTION_ID	varchar2	20			功能代號
MESSAGE	nvarchar2	2000	V	null	執行訊息
EXECUTE_TYPE	varchar2	1	V	null	C:CRON JOB, M: MANNUAL, U:Upload
USER_ID	varchar2	20	V	null	批次執行時，帶功能名稱，手動執行時，帶執行人USER_ID
UPDATE_DATE_TIME	timestamp(6)	11,6	V	now	執行時間
FILE_NAME	varchar2	40	V	null	檔案名稱

Here is the code snippet:

1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", BigInteger.valueOf(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:    }

But program throw SQLException

1:  Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)  VALUES(?, ?, ?, ?, ?, ?) ]; SQL state [99999]; error code [17004]; invalid column type; nested exception is java.sql.SQLException: invalid column type  
2:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
3:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
4:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
5:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
6:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
7:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:933) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
8:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
9:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
10:       at com.cht.commons.persistence.query.SqlExecutor.execute(SqlExecutor.java:97) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
11:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:157) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
12:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:146) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
13:       at gov.nta.fms.service.FmsBatchLog.writeLog(FmsBatchLog.java:139) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
14:       at gov.nta.fms.service.Fms435xService.initFms435fa(Fms435xService.java:228) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
15:       at gov.nta.fms.service.Fms435xService$$FastClassBySpringCGLIB$$75f6a95.invoke() ~[na:na]  
16:       at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
17:       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:711) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
18:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
19:       at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
20:       at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
21:       at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
22:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
23:       at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:58) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
24:       ... 111 common frames omitted  
25:  Caused by: java.sql.SQLException: invalid column type  
26:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8761) ~[na:na]  
27:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259) ~[na:na]  
28:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012) ~[na:na]  
29:       at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8993) ~[na:na]  
30:       at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230) ~[na:na]  
31:       at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:986) ~[na:na]  
32:       at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
33:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
34:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
35:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:300) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
36:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:252) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
37:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
38:       ... 129 common frames omitted

How to solve it?
This SQLException, ORA-17004 : Invalid Column Type, results from EXE_ID this column.

We use BigInteger originally, but fail to do insert. As we change to use BigDecimal, it's working fine now. But the root cause is still unknown.

1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", new BigDecimal(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:

2014/08/22

Oracle : DBMS_OUTPUT

Requirement

We would like to print debugging information from stored procedures, packages or triggers

For example.

We have an merge SQL statement to generate initial data and will be executed from 097~104 (Chinese year) by each month before system go life. i.e. passing 09701, 09702, 09703, 09704, 09705, 09706, 09707, 09708, 09709, 09710, 09711, 09712, 09801, .....10412 into merge SQL.

We would like to print the value from while loop in stored procedure to confirm the value is correct or not.

Solution
We can utilize DBMS_OUTPUT.put_line for debugging, ex.

DECLARE sYr number := 97;

 eYr number := 104;

 fyr varchar2(7);

 sMon number := 1;

 mon varchar2(2);


 BEGIN while sYr <= eYr LOOP IF length(sYr) = 2 THEN fyr := '0' || sYr;

 ELSE fyr := sYr;

 END IF;

 while sMon <=12 LOOP IF length(sMon) = 1 THEN mon := '0' || sMon;

 ELSE mon := sMon;

 END IF;

 --ignore merge sql
 DBMS_OUTPUT.put_line('fyr='||fyr||';month='||mon);

COMMIT;

SELECT sMon + 1 INTO sMon FROM dual;

 END LOOP;

 sMon :=1;


SELECT sYr + 1 INTO sYr FROM dual;

 END LOOP;

 END;

The following is the debugging information

1:  fyr=097;month=01  
2:  fyr=097;month=02  
3:  fyr=097;month=03  
4:  fyr=097;month=04  
5:  fyr=097;month=05  
6:  fyr=097;month=06  
7:  fyr=097;month=07  
8:  fyr=097;month=08  
9:  fyr=097;month=09  
10:  fyr=097;month=10  
11:  fyr=097;month=11  
12:  fyr=097;month=12  
13:  fyr=098;month=01  
14:  fyr=098;month=02  
15:  fyr=098;month=03  
16:  fyr=098;month=04  
17:  fyr=098;month=05  
18:  fyr=098;month=06  
19:  fyr=098;month=07  
20:  fyr=098;month=08  
21:  fyr=098;month=09  
22:  fyr=098;month=10  
23:  fyr=098;month=11  
24:  fyr=098;month=12  
25:  fyr=099;month=01  
26:  fyr=099;month=02  
27:  fyr=099;month=03  
28:  fyr=099;month=04  
29:  fyr=099;month=05  
30:  fyr=099;month=06  
31:  fyr=099;month=07  
32:  fyr=099;month=08  
33:  fyr=099;month=09  
34:  fyr=099;month=10  
35:  fyr=099;month=11  
36:  fyr=099;month=12  
37:  fyr=100;month=01  
38:  fyr=100;month=02  
39:  fyr=100;month=03  
40:  fyr=100;month=04  
41:  fyr=100;month=05  
42:  fyr=100;month=06  
43:  fyr=100;month=07  
44:  fyr=100;month=08  
45:  fyr=100;month=09  
46:  fyr=100;month=10  
47:  fyr=100;month=11  
48:  fyr=100;month=12  
49:  fyr=101;month=01  
50:  fyr=101;month=02  
51:  fyr=101;month=03  
52:  fyr=101;month=04  
53:  fyr=101;month=05  
54:  fyr=101;month=06  
55:  fyr=101;month=07  
56:  fyr=101;month=08  
57:  fyr=101;month=09  
58:  fyr=101;month=10  
59:  fyr=101;month=11  
60:  fyr=101;month=12  
61:  fyr=102;month=01  
62:  fyr=102;month=02  
63:  fyr=102;month=03  
64:  fyr=102;month=04  
65:  fyr=102;month=05  
66:  fyr=102;month=06  
67:  fyr=102;month=07  
68:  fyr=102;month=08  
69:  fyr=102;month=09  
70:  fyr=102;month=10  
71:  fyr=102;month=11  
72:  fyr=102;month=12  
73:  fyr=103;month=01  
74:  fyr=103;month=02  
75:  fyr=103;month=03  
76:  fyr=103;month=04  
77:  fyr=103;month=05  
78:  fyr=103;month=06  
79:  fyr=103;month=07  
80:  fyr=103;month=08  
81:  fyr=103;month=09  
82:  fyr=103;month=10  
83:  fyr=103;month=11  
84:  fyr=103;month=12  
85:  fyr=104;month=01  
86:  fyr=104;month=02  
87:  fyr=104;month=03  
88:  fyr=104;month=04  
89:  fyr=104;month=05  
90:  fyr=104;month=06  
91:  fyr=104;month=07  
92:  fyr=104;month=08  
93:  fyr=104;month=09  
94:  fyr=104;month=10  
95:  fyr=104;month=11  
96:  fyr=104;month=12

Reference
[1] http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#i1000105

Total Pageviews