albert's blog

2020/09/04

[Oracle] [DataGrip] How to connect to Oracle as SYSDBA

Problem

When I try to connect to Oracle using sys, I got the following error:

How-To

Using sys as sysdba as username is working fine.

2020/09/03

[Oracle] ORA-06592 - CASE not found while executing CASE statement

Problem

When I try to execute the following function, I got ORA-06592 - CASE not found while executing CASE statement error:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

How-To

The error result from missing else clause, the function should be modified as bellows:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
            ELSE o_result := '';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

2020/09/02

[Google Sheet] How to join double quote

How-To

Step 1. Check double quote value in ASCII Codes Table, i.e. char(34)

Step 2. Then you can using char(34) as bellows:

=concatenate(char(34), A1, char(34))

Reference

[1] https://infoinspired.com/google-docs/spreadsheet/join-double-quotes-with-text-in-google-sheets/

2020/09/01

[SchemaSpy] How to customize css in SchemaSpy Document ?

Requirement
If I would like to customize css file in SchemaSpy, how to edit and assign new css file when I re-generate schema document?

How-To
1. Generate schema document firstly and find out the schemaSpy.css file.

2. Edit schemaSpy.css, ex. update background color in odd tr

tr.odd td.detail {
  background-color: #eaeaea;
}

3. Place schemaSpy.css file into specific directory, then assign it into -css parameter:

java -jar "C:\schemaspy\schemaSpy_5.0.0.jar" ^
     -dp "C:\schemaspy\ojdbc6.jar" ^
     -t orathin ^
     -db TEST_DB ^
     -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" ^

2020/08/31

[Travel] 202008 苗栗

舊山線鐵道自行車

龍騰斷橋

蓬萊溪護魚步道

2020/08/10

[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" ^

2020/08/09

[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

2020/08/08

[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

2020/08/07

[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

2020/08/06

[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

2020/08/05

[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

2020/08/04

[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

2020/08/03

[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

2020/08/02

[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

2020/08/01

[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

2020/07/31

[Travel] 東北角

南子吝山

2020/07/30

[Travel] 台北書畫院與琴道館

台北書畫院

台北琴道館

2020/07/10

[Oracle] PLS-00215: String length constraints must be in range

Problem
When I tried to create a stored procedure package as bellows:

CREATE OR REPLACE PACKAGE PG_TEST_001 AS
    v_last2Month     VARCHAR2 := '';

    PROCEDURE SP_Get_LAST2MONTH(i_dataId IN VARCHAR2,
                                o_last2Month OUT VARCHAR2);

END PG_TEST_001;

/

I got this error message from SQL Developer:

PLS-00215: String length constraints must be in range

How-To
This error message resulted from the missing length in variable declaration, it should be fixed as following:

CREATE OR REPLACE PACKAGE PG_TEST_001 AS
    v_last2Month     VARCHAR2(8) := '';

    PROCEDURE SP_Get_LAST2MONTH(i_dataId IN VARCHAR2,
                                o_last2Month OUT VARCHAR2);

END PG_TEST_001;

/

2020/07/09

[Java] How to read file with encoding Cp937?

Problem
How to read file with encoding Cp937?

How-To
Here has code snippet:

import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.stream.Stream;

....

    pubilc void readAndPrintContent(String file) throws IOException {
        try (Stream<String> stream = Files.lines(Paths.get(file), Charset.forName("Cp937"))) {
            stream.forEach(log::debug);
        } catch (IOException e) {
            throw new IOException("fail to read file, error : " + e.getMessage(), e);
        }
    }

2020/07/08

[Oracle] Retrieving JSON in Oracle

Problem
Assume I would like to select a CLOB column with JSON value, it looks like:

{
 "test_no":"9999",
 "year":"0108",
 "registerDate":"20191031"
}

How to select test_no value (i.e. "9999") in Oracle?

How-To
Using JSON_VALUE to fulfill this requirement:

SELECT JSON_VALUE(JSON_COLUMN, '$.test_no' RETURNING VARCHAR2) 
FROM test_table

Reference
https://www.red-gate.com/simple-talk/sql/oracle/json-for-absolute-beginners-part-4-retrieving-json-in-oracle/

Total Pageviews

2020/09/04

2020/09/03

2020/09/02

2020/09/01

2020/08/31

2020/08/10

2020/08/09

2020/08/08

2020/08/07

2020/08/06

2020/08/05

2020/08/04

2020/08/03

2020/08/02

2020/08/01

2020/07/31

2020/07/30

2020/07/10

2020/07/09

2020/07/08