albert's blog

2020/10/02

[Oracle] [Stored Procedure] Cursor

Assume I have a table which name Product, the data look like:

SELECT ORDER_DATE, PRODUCT_ID, QTY
FROM PRODUCT
WHERE PRODUCT_ID = '1000'
ORDER BY ORDER_DATE;

If I would like to find data in stored procedure, and print data value for each row. We can make good use of CURSOR to fulfill this requirement:

CREATE OR REPLACE PACKAGE PG_PRODUCT AS
    PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2);
END PG_PRODUCT;
/
CREATE OR REPLACE PACKAGE BODY PG_PRODUCT AS
    PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2)
    AS
        CURSOR cur_product IS SELECT ORDER_DATE, PRODUCT_ID, QTY
                              FROM PRODUCT
                              WHERE PRODUCT_ID = i_product_id
                              ORDER BY ORDER_DATE;
        cur_product_rec cur_product%rowtype;
    BEGIN
        dbms_output.put_line('i_product_id = ' || i_product_id);
        FOR cur_product_rec IN cur_product
            LOOP
                dbms_output.put_line('ORDER_DATE = ' || cur_product_rec.ORDER_DATE ||
                                     ', PRODUCT_ID = ' || cur_product_rec.PRODUCT_ID ||
                                     ', QTY = ' || cur_product_rec.QTY);
            END LOOP;

    END SP_PRC_GET_PRODUCT;
END PG_PRODUCT;
/

Stored procedure execution log:

2020/10/01

[Oracle] ROW_NUMBER Function

Assume I have a table which name Product, the data looks like:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY
FROM PRODUCT
WHERE ORDER_DATE = '20200501'
ORDER BY ORDER_DATE, PRODUCT_ID;

We can make good use of row_number function to retrieve its row number:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY,
       row_number() over
           (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER
FROM PRODUCT
WHERE ORDER_DATE = '20200501'
ORDER BY ORDER_DATE, PRODUCT_ID;

If we would like get the second record, the SQL statement looks like:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY,
       ROW_NUMBER
FROM (
         SELECT ORDER_DATE,
                PRODUCT_ID,
                QTY,
                row_number() over
                    (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER
         FROM PRODUCT
         WHERE ORDER_DATE = '20200501'
         ORDER BY ORDER_DATE, PRODUCT_ID
     )
WHERE ROW_NUMBER = 2;

Reference

[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

2020/09/30

2020/09/29

2020/09/10

[Oracle] [Function] LEAD and LAG

I have a Product table:

If I would like to know PRODUCT_ID = 1000, and find its previous order date and next order date. We can make use of LEAD and LAG function to fulfill this requirement:

SELECT ORDER_DATE, PRODUCT_ID,
       LAG(ORDER_DATE) over (ORDER BY ORDER_DATE) AS PREVIOUS_RECORD,
       LEAD(ORDER_DATE) over (ORDER BY ORDER_DATE) AS NEXT_RECORD
FROM PRODUCT
WHERE PRODUCT_ID = 1000;

2020/09/09

[Oracle] [Function] LAST_DAY

If today is 2020/09/05, you can using the following SQL statement to get the end day of this month:

SELECT to_char(last_day(to_date('20200905', 'YYYYMMDD')), 'YYYYMMDD') as last_day
FROM DUAL
;

If I would like to get the days left from 2020/09/05 in this month:

SELECT (last_day(to_date('20200905', 'YYYYMMDD')) - to_date('20200905', 'YYYYMMDD')) as Days_Left
FROM DUAL
;

Reference

[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions072.htm

2020/09/08

[Google Sheet] How to download Google Sheet via command line as Excel file?

Requirement

How to download Google Sheet via command line as Excel file into specific directory?

How-To

In Windows:

powershell -command "& { (New-Object Net.WebClient).DownloadFile('https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx', 'src/main/resources/source/batchConfigFile.xlsx') }"

In Mac OS:

curl -L "https://docs.google.com/spreadsheets/d/xxxxx/export?format=xlsx" > src/main/resources/source/batchConfigFile.xlsx

2020/09/07

[Oracle] How to reset sequence value

How-To

Example

    PROCEDURE SP_RESET_SEQUENCE AS
        seq_exists VARCHAR2(1) := 'T';
    BEGIN
        SELECT CASE WHEN COUNT(*) > 0 THEN 'T' ELSE 'F' END
            INTO seq_exists
        FROM all_sequences
        WHERE sequence_name = 'SEQ_TEST' AND sequence_owner = 'TEST' ;

        IF seq_exists = 'T'
            THEN execute immediate 'DROP SEQUENCE TEST.SEQ_TEST';
        END IF;

        execute immediate 'create sequence TEST.SEQ_TEST start with 1 increment by 1 MAXVALUE 9999 cycle';

    END SP_RESET_SEQUENCE;

2020/09/06

[DataGrip] [Database] How to print DBMSUOUTPUT message into console

Enable DBMSUOUTPUT in the Output Panel:

2020/09/05

[Windows] 解決 Windows Server 2012 R2 輸入法預設中文問題

解決步驟

1. 到控制台新增語言：英文

2. 點選進階設定

3. 複寫預設輸入法為「英文」

4. 重開機後，打開命令提示字元，即可看到輸入法預設為英文

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

Total Pageviews

2020/10/02

2020/10/01

2020/09/30

2020/09/29

2020/09/10

2020/09/09

2020/09/08

2020/09/07

2020/09/06

2020/09/05

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