[Travel] 202008 苗栗

 舊山線鐵道自行車

龍騰斷橋

蓬萊溪護魚步道

[Windows] How to specify multi-line command on command prompt?

Problem
Here is my original command to generate database schema document:

java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" -dp "C:\schemaspy\ojdbc6.jar" -t orathin -db TEST -s ap_tax -host localhost -port 1521 -u system -p password -schemas "ODS,TS" -charset UTF-8 -o "C:\schemaspy\schema_doc" -norows -hq -css "C:\schemaspy\schemaSpy.css" 

If I would like to break it into multiple line, how to do it?

How-To
Add ^ in the end of each line, the updated command is as following:

java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" ^
     -dp "C:\schemaspy\ojdbc6.jar" ^
     -t orathin ^
     -db TEST ^
     -s ap_tax ^
     -host localhost ^
     -port 1521 ^
     -u system ^
     -p password ^
     -schemas "ODS,TS" ^
     -charset UTF-8 ^
     -o "C:\schemaspy\schema_doc" ^
     -norows -hq -css "C:\schemaspy\schemaSpy.css" ^
     

[Oracle] CASE Statement example

Requirement

以下是用來將幣別轉換為代號的邏輯

case 幣別

  when 'NTD' then '01'

  when 'USD' then '51'

  when 'HKD' then '52'

  when 'JPY' then '53'

Example

以下是使用 CASE Statement 的範例

FUNCTION FN_GET_CCY_CODE (i_ccy IN VARCHAR2)
    RETURN VARCHAR2 IS o_ccy_code VARCHAR2(2) := '';
    BEGIN
        CASE i_ccy
            WHEN 'NTD' THEN o_ccy_code := '01';
            WHEN 'USD' THEN o_ccy_code := '51';
            WHEN 'HKD' THEN o_ccy_code := '52';
            WHEN 'JPY' THEN o_ccy_code := '53';
        END CASE;

        RETURN o_ccy_code;
    END FN_GET_CCY_CODE;

Reference

[1] https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems004.htm

[Java] [Apache Freemarker] 如何在 ftl 做設定，印製年、月、日，且年份採用民國年

Requirement
假設我希望在輸出結果檔，印製年、月、日，且年份採用民國年，該如何在 ftl 做設定？

How-To
ftl 的 code snippet 如下：

<#-- 取得現在時間 -->
<#assign aDateTime = .now>
<#-- 只保留日期 -->
<#assign aDate = aDateTime?date>
<#-- 取出日期中的年份、轉數字，減去 1911 得到民國年 -->
列印年：${aDate?string.YYYY?number - 1911}
<#-- 取出日期中的月份 -->
列印月：${aDate?string.MM}
<#-- 取出日期中的日期 -->
列印日：${aDate?string.dd}

Reference
[1] https://freemarker.apache.org/docs/ref_builtins_date.html

[Oracle] [SQL Developer] How to change language in Oracle SQLDeveloper?

Problem
How to change language from Transitional Chinese to English in Oracle SQL Developer?

How-To
1. Go to C:\<SQLDeveloper Path>\ide\bin, open ide.conf
2. Add AddVMOption -Duser.language=en in the end of ide.conf
3. Relaunch Oracle SQL Developer

Reference
[1] https://stackoverflow.com/a/30396125

[Oracle] ORA-02287: sequence number not allowed here

Problem
Assume I create a sequence as bellows:

create sequence seq_test
    start with 1
    increment by 1;

As I try to get the next sequence value in my select statement, I get ORA-02287 error

 select (select lpad(to_char(seq_test.NEXTVAL), 4, '0') from dual) as seq,
        .........
 from ............
 where .........

How-To
1. create a function to get the next sequence

    FUNCTION FN_GET_NEXT_SEQ_VAL RETURN VARCHAR2 IS v_next_seq_val VARCHAR2(4) := '';
    BEGIN
        select lpad(to_char(seq_test.NEXTVAL), 4, '0')
        into v_next_seq_val
        from dual;

        return v_next_seq_val;
    END FN_GET_NEXT_SEQ_VAL;

2. select value from function

    select FN_GET_NEXT_SEQ_VAL() as seq,
           .........
    from ............
    where .........

Reference
Uses and Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in the following places:

• VALUES clause of INSERT statements
• The SELECT list of a SELECT statement
• The SET clause of an UPDATE statement

CURRVAL and NEXTVAL cannot be used in these places:

• A subquery
• A view query or materialized view query
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint

[Java] [Apache Freemarker] How to compute total price in ftl ?

How-To
You can assign a variable, added value in the loop, then print the value in the end.

<#assign total = 0>
${"水果名稱"?right_pad(10)}${"單價"?right_pad(5)}${"訂購數量"?right_pad(10)}
${"-"?right_pad(25, "-")}
<#list rows as row>
<#assign total += row.price * row.quantity>
${row.name?right_pad(10)}${row.price?left_pad(5)}${row.quantity?left_pad(10)}
</#list>
${"-"?right_pad(25, "-")}
總筆數：${rows?size?left_pad(10)}
總金額：${total?left_pad(10)}

Result

水果名稱      單價   訂購數量      
-------------------------
apple        50        20
avocado      60         2
banana       70         1
cherry       80        10
coconut      90         1
durian      100         3
-------------------------
總筆數：         6
總金額：     2,380

[Java] [Apache Freemarker] How to count list size in ftl ?

How-To
You can make good use of size to count list size as bellowing:

<#assign total = 0>
${"水果名稱"?right_pad(10)}${"單價"?right_pad(5)}${"訂購數量"?right_pad(10)}
${"-"?right_pad(25, "-")}
<#list rows as row>
<#assign total += row.price * row.quantity>
${row.name?right_pad(10)}${row.price?left_pad(5)}${row.quantity?left_pad(10)}
</#list>
${"-"?right_pad(25, "-")}
總筆數：${rows?size?left_pad(10)}
總金額：${total?left_pad(10)}

Result

水果名稱      單價   訂購數量      
-------------------------
apple        50        20
avocado      60         2
banana       70         1
cherry       80        10
coconut      90         1
durian      100         3
-------------------------
總筆數：         6
總金額：     2,380

[Mac] [VMware Fusion] Keyboard 錯亂

Problem
由於我的 VM 有時候會接上 Windows OS，有時候會接上 Mac OS。
當作業系統轉換時，複製、貼上、全選的按鈕有時候會轉換失敗，例如在 Mac 系統，打開 Windows 10 VM，若要做全選，Mac 應該是 command + A：

但是不知道什麼原因，必須用 control + A：

操作的不一致性，讓使用上很困擾。


How-To
雖然不知道根本原因，透過以下步驟可以解決此問題，且不需重開虛擬機。
1. VMWare Fusion => Virtual Machine => Settings

2. 點選 Keyboard & Mouse

3. 點選 Profile => Make Default Profile

[Oracle] [SchemaSpy] Catalog (-cat) was not provided and unable to deduce catalog, wildcard catalog can be used -cat %

Problem
When I try to generate schema document via SchemaSpy, I got the following error message:

Catalog (-cat) was not provided and unable to deduce catalog, wildcard catalog can be used -cat %

I used schemaspy-6.1.0 and graphviz-2.38.


How-To
The root cause is uncertain, but this problem can be resolved by adding -cat "%"  in your command line.


Reference
[1] https://github.com/schemaspy/schemaspy/issues/616

[IntelliJ] Shortcut to upper case

Problem
Assume I have an insert sql statement as bellows:

insert into test (...)
values (...);

If I would like to convert SQL reserved words to upper case as bellows:

INSERT INTO test (...)
VALUES (...);

Any shortcut to fulfill this requirement in IntelliJ?


How-To