[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

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:  

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

Oracle Reserved Word: UID

Problem

Assume we would like to retrieve information from UAA001V1 (Oracle database view). Here has its schema.

We can utilize this SQL statement to retrieve all information (including all column) : 

SELECT *
FROM UAA001V1;

Here is the result:

But if I would like to retrieve UID column only:

SELECT UID
FROM UAA001V1;

Then it will show 84, WHY?

Root Cause & Soltion
Owing to UID is reserved word for Oralce, so it will mislead Oracle returns an integer that uniquely identifies the session user (the user who logged on). That's why it return 84 this weird integer.

What we need to do is to add double quote before and after UID as following:

SELECT "UID"
FROM UAA001V1;

Then we can get the expected outcome:

Reference
[1] http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
[2] http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions188.htm

java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result

Problem
As I execute the following code snippet

   public static void main(String args[]) {  
     BigDecimal one = new BigDecimal(1);  
     BigDecimal two = new BigDecimal(2);  
     BigDecimal three = new BigDecimal(3);  
     System.out.println(one.divide(two));  
     System.out.println(one.divide(three));  
   }  

The console showed:

0.5  
 Exception in thread "main" java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result.  
      at java.math.BigDecimal.divide(BigDecimal.java:1616)  
      at gov.nta.fms.web.rest.Fms421rResource.main(Fms421rResource.java:204)  

Why one.divide(two) is working fine?  But failed to execute one.divide(three) ?

Root Cause
Because I am not specifying a precision and a rounding-mode. 
BigDecimal is complaining that it could use 10, 20, 5000, or infinity decimal places, so it cannot be able to show me the exact representation of the number.

We need to assign its scale and rounding mode, please check the following code snippets:

   public static void main(String args[]) {  
     BigDecimal one = new BigDecimal(1);  
     BigDecimal two = new BigDecimal(2);  
     BigDecimal three = new BigDecimal(3);  
     System.out.println(one.divide(two));  
     System.out.println(one.divide(three, 2, BigDecimal.ROUND_HALF_UP));  
   }  

Here is java doc:

• divide

  public BigDecimal divide(BigDecimal divisor,
                  int scale,
                  int roundingMode)

  Returns a BigDecimal whose value is (this / divisor), and whose scale is as specified. If rounding must be performed to generate a result with the specified scale, the specified rounding mode is applied.The new divide(BigDecimal, int, RoundingMode) method should be used in preference to this legacy method.

  Parameters:

  divisor - value by which this BigDecimal is to be divided.

  scale - scale of the BigDecimal quotient to be returned.

  roundingMode - rounding mode to apply.

  Returns:

  this / divisor

  Throws:

  ArithmeticException - if divisor is zero, roundingMode==ROUND_UNNECESSARY and the specified scale is insufficient to represent the result of the division exactly.

  IllegalArgumentException - if roundingMode does not represent a valid rounding mode.

  See Also:

  ROUND_UP, ROUND_DOWN, ROUND_CEILING, ROUND_FLOOR, ROUND_HALF_UP, ROUND_HALF_DOWN, ROUND_HALF_EVEN, ROUND_UNNECESSARY

The console showed:

 0.5  
 0.33  

Reference
[1] http://docs.oracle.com/javase/7/docs/api/java/math/BigDecimal.html#divide(java.math.BigDecimal,%20int,%20int)