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)

四項成功選股原則

筆記一下漫步華爾街一書提到的四項成功選股原則：

• 只購買至少維持五年盈餘成長超過平均的股票


• 可以到以下網站取得相關資訊: http://goodinfo.tw/
• 不購買股價高於合理價值的股票
• 透過本益比來判斷，目前價格是否超過其價值。作者不斷強調，只要買進本益比低，就算成長未實現且盈餘下跌，損失也有限；但是如果預測成真，就可以賺錢，藉此增加勝算
• 購買有故事題材的股票
• 如iPhone概念股、雲端概念股等等
• 盡可能減少進出
• 頻繁進出股市者，平均來說獲利率都偏低，但是記得要汰弱留強。
• 投資普通股票和債券的持有期越長，風險就越低，但是你得有耐性忍受過程中，投資價值逐年波動的情形

2014/07 Travel

石碇千島湖

石碇雲海國小

基隆八斗子忘憂谷

基隆八斗子忘憂谷

[iReport] How to format text style in text field

Requirement
The first row in this report, the report name will be appended "總預算\n" before report name. And "總預算" should be bold font. 

Solution
Step 1. Set Text Field Expression

Step 2. Set properties Markup = styled for this text field.

Reference

[1] http://stackoverflow.com/questions/8654851/format-the-text-style-in-text-field-ireport

[iReport] How to apply conditional formatting to your text fields

Requirement
In iReport, I wish to apply different formats to my text fields based on different conditions. For instance, when "融資項目別"== "特別預算" then the font should be bold font.

Solution
You can apply "Conditional Styles" to fulfill this requirement.
Step1. Add new Style

Step2. Add new Conditional Style

Step3. Edit Condition Expression

Step4. Set Bold attribute to be true

Step5. Select text fields and apply this style

Step6. Test


Reference
[1] http://community.jaspersoft.com/wiki/how-apply-conditional-formatting-your-text-fields

ORA-30926: unable to get a stable set of rows in the source tables when Merging tables

Problem
When I executed the merge SQL statement in my web application

MERGE INTO FMS434FB T1 USING
  (SELECT ACCYR AS ACCYR,
          MONTH AS MONTH,
          ASP AS ASP,
          AMT7,
          AMT9,
          AMT7+AMT9 AS ACC_AMT9
   FROM FMS434FB
   WHERE ACCYR||MONTH = :LAST_YYY_MM) T2 ON(T1.ACCYR||T1.MONTH = :YYY_MM
   AND T1.ASP=T2.ASP) WHEN MATCHED THEN
UPDATE
SET T1.AMT7 = T2.ACC_AMT9

It throw this exception

1:  Caused by: java.sql.SQLException: ORA-30926: unable to get a stable set of rows in the source tables when Merging tables  
2:       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
3:       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
4:       at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
5:       at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
6:       at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  
7:       at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]  

Root Cause
ORA-30926 results from the using statement has duplicate data.
Therefore, as I execute this part of SQL statement

SELECT ACCYR AS ACCYR,
       MONTH AS MONTH,
                ASP AS ASP,
                AMT7,
                AMT9,
                AMT7+AMT9 AS ACC_AMT9
FROM FMS434FB
WHERE ACCYR||MONTH = :LAST_YYY_MM

It really has duplicated data !

本益比的計算

漫步華爾街的作者提到:
選擇低本益比的股票比較好，如果成長實現，將帶來加倍利潤；若成長失敗，損失也有限

本益比計算公式如下
本益比=股價/EPS，所以股價=本益比 * EPS

先到網站上查詢最近五年的最高與最低本益比，算出最高本益比平均、最低本益比平均以及平均本益比

再從網站上查詢最近四季的EPS總和

再推算出，當本益比是多少的時候，股價應該落在哪邊

例如本檔股票目前本益比是13.77

若按照目前的本益比，大致落在低本益比此區間，目前股價是27.95，可搭配K線來看進場時機

雖然分析工具很多，但是還是回歸漫步華爾街作者所說的，你無法預測未來成長與盈餘、你也無法有100%可以賺錢的分析工具、你只能降低失敗機率，這樣才能提升你賺錢的機率，故作者建議選擇低本益比的股票，勝算較高

參考資料
[1] http://www.cmoney.tw/finance/f00041.aspx?m=3&s=2034
[2] http://jsjustweb.jihsun.com.tw/z/zc/zca/zca_2034.djhtm
[3] http://www.cnyes.com/twstock/profile/2034.htm

net.sf.jasperreports.engine.JRException: org.xml.sax.SAXParseException; Premature end of file.

Problem
When I try to build my project, it show this error message

1:  [ERROR] Could not compile fms435r1.jrxml because org.xml.sax.SAXParseException; Premature end of file.  
2:  net.sf.jasperreports.engine.JRException: org.xml.sax.SAXParseException; Premature end of file.  
3:       at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:247)  
4:       at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:230)  
5:       at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:218)  
6:       at net.sf.jasperreports.engine.JasperCompileManager.compileToStream(JasperCompileManager.java:191)  
7:       at net.sf.jasperreports.engine.JasperCompileManager.compileReportToStream(JasperCompileManager.java:471)  
8:       at com.alexnederlof.jasperreport.CompileTask.call(CompileTask.java:65)  
9:       at com.alexnederlof.jasperreport.CompileTask.call(CompileTask.java:28)  
10:       at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)  
11:       at java.util.concurrent.FutureTask.run(FutureTask.java:166)  
12:       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)  
13:       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)  
14:       at java.lang.Thread.run(Thread.java:724)  

As I try to open this jrxml file via iReport Designer, it show the same error message


Root Cause
As I change iReport mode from Designer to XML, I figure out the root cause. This jrxml file had broken for unknown reason, it's an empty jrxml file so it cannot be compiled correctly.

THerefore, I need to revert this file to server version by svn client, and do it again.

[AngularJS] How do I conditionally apply CSS styles in AngularJS?

Requirement
If the amount value of the last column is less than zero, it should be showed by red color.

Solution
Step1. create a css file, and define a pair of key and value.


Step2. add ng-class in the input text, and give a condition: 
when the value of row.amtc are less than zero, then apply negative-number css; 
when the value of row.amtc are equal or greater than zero, then apply nothing.

1:  <td class="text-left border-right" style="width:150px">  
2:    <input id="amtc" name="amtc" size="30" maxlength="16"  
3:        data-ng-readonly="true" ng-class="row.amtc < 0 ? 'negative-number' : ''"  
4:        data-ng-model="row.amtc" style="width:150px;text-align:right"   
5:        title="{{row.amtcf}}"/>  
6:  </td>  

Demo

2014/06 花蓮

太魯閣砂卡礑步道

夢幻湖 雲山水

林田山林業文化園區

花蓮港-麗娜輪

[Oracle] Use to_char to format a number type column to currency format

Requirement

Before formatting.....
User hopes to add 1000 separator in amount-related columns, including AMT_A, AMT_B, and AMT_C

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD, --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM, --特別預算支出項目名稱\n
       F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP) AS AMT_A, --實支數\n
       F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP) AS AMT_B,
       --年度經費滕餘數\n
       F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP) AS AMT_C --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;

Solution
We can use Oracle build-in function, TO_CHAR, to do currency format.

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD,
       --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM,
       --特別預算支出項目名稱\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_A_F,
       --實支數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_B_F,
       --年度經費滕餘數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_C_F --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;

Parameter(s) [SPEC_FUND] are unresovlable in query string

Problem
Here has part of my SQL statement.
I faced a weird situation, this SQL statement can be executed successfully in SQL Developer, but fail to execute in Java program.

SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
       NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

As I executed this SQL statement in Java program, it throws exception : Parameter(s) [SPEC_FUND] are unresovlable in query string

Root Cause
This problem result from ":SPEC_FUND" in remarks. 

SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
                                          NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

Basically, program will ignore what I wrote in remarks. But I do not know why it will still interpret remarks in this case. Therefore, As I remove ":SPEC_FUND", this SQL statement will be working fine.

穩健投資三原則

最近閱讀了漫步華爾街這本書，內容提到穩健投資三原則，筆記一下

原則一、只買未來五年或五年以上，盈餘成長高於平均值的公司

原則二、絕不付出高過真實價值的股價

原則三、尋找有題材的成長股，讓投資人建造空中樓閣

其中一跟三，其實都不容易去做預測，比較能掌握的就是第二個原則了，文中用本益比來做為判斷的原則，只要買進本益比低，就算成長未實現且盈餘下跌，損失也有限；但是如果預測成真，就可以賺錢，藉此增加勝算