Category: Oracle

  • สร้าง script comments column จาก table ไป view อัตโนมัติใน oracle

    การเขียน comment ของ column view อัตโนมัติผ่านการสร้าง sql script ใน oracle โดยการนำ comment ของ column table มา generate comments ของ column view นั้น เพื่อประหยัดเวลาและไม่ต้องเขียน comment ซ้ำอีกรอบในการสร้าง view

    จะยกตัวอย่างการใช้งาน โดยเลือก view V_MAJOR_FOR_API ที่ไม่มี comments โดยที่ view V_MAJOR_FOR_API มีการเรียกใช้ตาราง MAJOR, ตาราง MAJOR_TYPE และ ตาราง DEPT ที่มีการเขียน comments ของ column table อยู่แล้ว

    ตาราง MAJOR ที่มี comments
    ตาราง MAJOR_TYPE ที่มี comments
    ตาราง DEPT ที่มี comments
    view V_MAJOR_FOR_API ที่ไม่มี comments

    ขั้นตอนที่1 จัดทำ sql script เพื่อสร้าง comments ของ column view

    SELECT ud.name                                view_name,
       ud.referenced_name                     based_table,
       vcols.column_name,
        NVL (tcom.comments, vcom.comments)     comments
   FROM all_dependencies  ud,
       all_tab_columns   vcols,
       all_tab_columns   tcols,
       all_col_comments  tcom,
       all_col_comments  vcom
 WHERE     ud.referenced_name = tcom.table_name
       AND ud.name = vcom.table_name
       AND vcols.table_name = ud.name
       AND vcols.owner = ud.owner
       AND vcols.column_name = vcom.column_name
       AND vcols.column_name = tcols.column_name
       AND tcols.owner = ud.owner
       AND tcols.table_name = ud.referenced_name
       AND tcols.column_name = tcom.column_name
       AND vcom.owner = ud.owner
       AND tcom.owner = ud.owner
       AND ud.name = 'V_MAJOR_FOR_API'
       AND ud.TYPE = 'VIEW'
       AND ud.referenced_type IN ('TABLE', 'VIEW')
       AND tcom.comments IS NOT NULL;
    ผลลัพธ์จาก sql query ข้างบน

    ขั้นตอนที่ 2 จัดทำ format sql script เพื่อสร้าง comment ของ column view ตามรูปแบบ “COMMENT ON COLUMN V_MAJOR_FOR_API .MAJOR_TYPE IS ‘รหัสประเภท’;“ โดยเขียน sql script จากข้างบนมาจัดการต่อ

    SELECT    'COMMENT ON COLUMN '
       || ud.name
       || '.'
       || vcols.column_name
       || ' IS '''
       || NVL (tcom.comments, vcom.comments)
       || ''';'    comments
  FROM all_dependencies  ud,
       all_tab_columns   vcols,
       all_tab_columns   tcols,
       all_col_comments  tcom,
       all_col_comments  vcom
 WHERE     ud.referenced_name = tcom.table_name
       AND ud.name = vcom.table_name
       AND vcols.table_name = ud.name
       AND vcols.owner = ud.owner
       AND vcols.column_name = vcom.column_name
       AND vcols.column_name = tcols.column_name
       AND tcols.owner = ud.owner
       AND tcols.table_name = ud.referenced_name
       AND tcols.column_name = tcom.column_name
       AND vcom.owner = ud.owner
       AND tcom.owner = ud.owner
       AND ud.name = 'V_MAJOR_FOR_API'
       AND ud.TYPE = 'VIEW'
       AND ud.referenced_type IN ('TABLE', 'VIEW')
       AND tcom.comments IS NOT NULL;
    ผลลัพธ์จาก sql query ข้างบน

    ขั้นตอนที่ 3 จากนั้นก็ Copy script จากขั้นตอนที่ 2 ไป execute script และจะได้ผลลัพธ์ตามภาพด้านล่าง

    view V_MAJOR_FOR_API ที่มี comments จากการสร้าง script
    • ข้อเสีย จะเห็นว่า MASTER_MAJOR_NAME_THAI และ MASTER_MAJOR_NAME_ENG ใช้ไม่ได้เนื่องจาก ชื่อ column ไม่ตรงกับตารางที่เรียก

    หวังว่า km จะมีประโยชน์ไม่มากก็น้อยและสามารถช่วยลดระยะเวลาในการทำ document นะคะ

  • หาข้อมูลที่ซ้ำซ้อนในตารางด้วย LAG Function

    LAG Function
    เป็นฟังก์ชันที่ให้เราสามารถเข้าถึงแถวข้อมูลก่อนหน้าของตารางได้โดยที่ไม่ต้องทำการ self-join

    รูปแบบการใช้งาน
    LAG ( expression [, offset [, default] ] )
    OVER ( [ query_partition_clause ] order_by_clause )

    โดยที่

    • expression : ฟิลด์ข้อมูลที่ต้องการแสดง
    • offset : ระบุว่าต้องการแถวข้อมูลลำดับที่เท่าไหร่ก่อนแถวปัจจุบัน เช่น 2 คือต้องการข้อมูลลำดับที่สองก่อนหน้าแถวปัจจุบัน กรณีไม่ระบุ จะหมายถึงข้อมูลแถวที่1
    • query_partition_clause : ใช้สำหรับการจัดกลุ่มข้อมูลเพื่อให้ได้ผลลัพธ์ตามกลุ่มที่ต้องการ
    • order_by_clause : ใช้เพื่อจัดลำดับข้อมูลภายในแต่ละ partition

    ตัวอย่าง : การใช้งานฟังก์ชัน LAG

    Query ข้างต้นเป็นการหาผลการเรียนของภาคการศึกษาก่อนหน้าของรายวิชาที่ลงทะเบียนเรียน โดยที่

    • expression : ข้อมูลผลการเรียนคือฟิลด์ GRADE
    • offset : กรณีไม่ระบุหมายถึงลำดับที่หนึ่งก่อนแถวปัจจุบัน และ ตัวเลข 2 หมายถึง ลำดับที่สองก่อนแถวปัจจุบัน
    • query_partition_clause : จัดกลุ่มข้อมูลตามฟิลด์ SUBJECT_ID
    • order_by_clause : จัดเรียงข้อมูลภายใน SUBJECT_ID ด้วยฟิลด์ EDU_YEAR แล้วตามด้วยฟิลด์ EDU_TERM

    ผลลัพธ์จาก Query

    ✔ ฟิลด์ GRADE_BEFORE_1 = ผลการเรียนภาคการศึกษาก่อนหน้า
    ✔ ฟิลด์ GRADE_BEFORE_2 = ผลการเรียนของ 2 ภาคการศึกษาก่อนหน้า
    กรณีรายวิชาไหนมีค่าเป็น NULL หมายความว่านักศึกษาลงทะเบียนเรียนเพียงครั้งเดียว ไม่เคยลงทะเบียนเรียนซ้ำ
    รายวิชาในกรอบสีแดงคือรายวิชาที่นักศึกษามีการลงทะเบียนเรียนซ้ำ ตัวอย่างเช่น
    🎯 รายวิชา 0006343 มีการลงทะเบียนเรียน 2 ครั้ง
    🎯 รายวิชา 0007615 มีการลงทะเบียนเรียน 5 ครั้ง
    สามารถดูข้อมูลผลการเรียนของภาคการศึกษาก่อนหน้าได้ที่ฟิลด์ GRADE_BEFORE_1 และ GRADE_BEFORE_2

    คราวนี้เรามาประยุกต์ใช้งานฟังก์ชัน LAG กันค่ะ

    ตัวอย่าง : การประยุกต์ใช้ฟังก์ชัน LAG เพื่อหาข้อมูลที่ซ้ำซ้อนในตาราง
    เรามีตารางข้อมูลชื่อ V3_PLAN_SEC2_1_PLO ซึ่งประกอบด้วยฟิลด์
    PLAN_SEC2_1_PLO_ID (🔑) , PLAN_REPORT_ID และ PLO_DESC
    โดยเงื่อนไขของระบบกำหนดไว้ว่าจะต้องมี PLAN_REPORT_ID และ PLO_DESC เพียงรายการเดียวเท่านั้น

    📌จากข้อมูลข้างต้นจะเห็นว่ามีข้อมูลที่มีรายการซ้ำซ้อนของข้อมูล PLAN_REPORT_ID และ PLO_DESC
    เช่นที่ PLAN_REPORT_ID = C2933F24A525 และที่ PLAN_REPORT_ID = C29482CB6553 เป็นต้น📌

    วิธีการหาข้อมูลที่ซ้ำซ้อนเราสามารถหาได้หลายวิธีแต่วันนี้จะขอนำเสนอการประยุกต์ใช้ฟังก์ชัน LAG เพื่อหาข้อมูลที่ซ้ำซ้อนกันค่ะ
    ตามคำอธิบายข้างต้น LAG Function เป็นฟังก์ชันที่ให้เราสามารถเข้าถึงแถวข้อมูลก่อนแถวปัจจุบันได้โดยที่ไม่ต้องทำการ self-join
    ดังนั้นเราก็จะประยุกต์ใช้ความสามารถนี้ในการหาข้อมูลที่ซ้ำซ้อนก้นโดยมีขั้นตอนด้งนี้

    • ใช้ฟังก์ชัน LAG ในการดึงค่าข้อมูล PLO_DESC ก่อนหน้า ของ PLAN_REPORT_ID ปัจจุบัน
    • ทำการเปรียบเทียบค่า PLO_DESC กับ PLO_DESC ก่อนหน้า
    • กรณี PLO_DESC มีค่าเท่ากับ PLO_DESC ก่อนหน้า แสดงว่าข้อมูลรายการปัจจุบันมีความซ้ำซ้อนกับข้อมูลก่อนหน้า

    สามารถเขียนเป็น Query ได้ดังนี้

    ผลลัพธ์จาก Query

    😊😊เราได้ข้อมูลรายการที่ซ้ำซ้อนกันเรียบร้อยแล้วค่ะ ก็ไม่ยากแล้วใช่มั้ยคะที่จะลบรายการที่ซ้ำซ้อนออกจากตารางข้อมูลต่อไป
    ^_^ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • กู้คืนข้อมูลที่ถูกลบด้วย Oracle Flashback Query

    เคยเจอเหตุการณ์ที่ใช้คำสั่งผิดพลาดหรือเผลอลบข้อมูลไปโดยที่ไม่ได้ตั้งใจ และได้ทำการ commit ไปเรียบร้อยแล้วไม่สามารถ rollback กลับได้มั้ย 😊

    สำหรับคำถามข้างต้นคิดว่าคงจะมีบ้างแหละที่จะพลาดกันบ้างใช่มั้ยคะ แต่ไม่เป็นไรค่ะ เรามีวิธีการแก้ปัญหาที่เกิดจากความผิดพลาดที่ไม่ได้ตั้งใจของเรา วันนี้จึงขอนำเสนอวิธีการที่จะกู้คืนข้อมูลได้ด้วยตัวเอง โดยที่ไม่ต้องไปร้องขอให้ DBA กู้คืนข้อมูลให้ด้วย Flashback Query กันค่ะ

    Flashback Query เป็นฟีเจอร์ที่ช่วยให้เราสามารถเรียกดูข้อมูลย้อนหลัง ณ เวลา TIMESTAMP ที่ต้องการ ได้โดยใช้ AS OF clause การใช้งาน Flashback Query ก็ไม่ยาก ตามไปดูตัวอย่างการใช้งานกันค่ะ

    ตัวอย่าง

    Step1 : วันที่ 22 สิงหาคม 2565 เวลา 10:55:25 เรามีตารางข้อมูล TEST_NEW_STUDENT จำนวน 5 รายการ

    Step2 : เวลา 11:01:18 คงเบรอ ๆ นิดหน่อย ทำการลบข้อมูลตาราง TEST_NEW_STUDENT และทำการ COMMIT ไปเรียบร้อย นั่งไปสักพักเอะเมื่อกี้เราทำอะไรไป พลาดไปแล้ว ข้อมูลหายหมดแล้ว ทำไงดี

    Step3 : ใช่ Oracle มีฟีเจอร์ Flashback Query งั้นขอเรียกดูข้อมูลย้อนหลัง ณ เวลา 10:55:25 เพราะจำได้ว่าตอนนั้นข้อมูลยังมีอยู่ครบถ้วนสมบูรณ์ ก่อนที่จะพลาดลบไป 😂

    🎈TO_TIMESTAMP (‘2022-08-02 10:55:25 ‘, ‘YYYY-MM-DD HH24:MI:SS’) คือการทำการแปลงให้เป็น TIMESTAMP

    Step4 : นี่ไงข้อมูลที่เราลบไป รออะไรหละ ดำเนินการกู้คืนข้อมูลกันเลยค่ะ

    ไชโย😍 ข้อมูลกลับมาเรียบร้อยแล้ว Flashback Query ช่วยชีวิตเราได้จริง ๆ👍👍👍 หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

    หมายเหตุ : ถ้าเราใช้วิธีการ Truncate จะไม่สามารถกู้คืนข้อมูลกลับได้ด้วย Flashback Query น้า

  • การประยุกต์ใข้  Sequence เพื่อสร้างตัวเลขอัตโนมัติให้กับ Table ใน Oracle Database

    ก่อนหน้านี้เคยได้รับความต้องการจากระบบหนึ่งซึ่งเป็นระบบที่รับสมัครนักเรียนเพื่อเข้าศึกษาต่อในมหาวิทยาลัยโดยมีความต้องการข้อหนึ่งว่า “ในส่วนการจัดเก็บข้อมูลเลขที่สมัคร ให้รันข้อมูลเลขที่ผู้สมัครตามโครงการที่เปิดรับในแต่ละปีการศึกษา”

    เนื่องในการพัฒนาระบบนี้ มีการใช้ Oracle Database ในการจัดการฐานข้อมูลอยู่แล้ว เพื่อน ๆ ที่เคยทำงานกับ Oracle Database ก็จะทราบว่าเราสามารถใช้ Oracle Sequence  เพื่อสร้างตัวเลขอัตโนมัติได้ ดังนั้นเราจึงสามารถที่จะใช้ Oracle Sequence มาประยุกต์ใช้เพื่อที่จะรันเลขที่ผู้สมัครได้ โดยสามารถมั่นใจได้ว่าจะไม่เกิดปัญหาผู้สมัครได้ข้อมูลเลขที่สมัครเดียวกัน กรณีที่ทำการสมัครในโครงการเดียวกันเรียนพร้อม ๆ กันอย่างแน่นอน

    วิธีการดำเนินการก็ไม่ยุ่งยากแค่สร้าง sequence ตามโครงการและปีที่เปิดรับทั้งหมดไว้ให้ก่อนให้เรียบร้อยและเมื่อนักศึกษามาสมัครก็สามารถเรียกใช้ได้เลย

    ตามวิธีการข้างต้นก็ไม่น่าจะมีปัญหาอะไร แต่ก็เริ่มมีคำถามในใจ ถ้าโครงการนั้นไม่มีผู้สมัครเลย ถ้าดำเนินการสร้างไว้ก่อนก็เปลืองเปล่า ๆ หรือถ้าลืมสร้างของบางโครงการ ระบบต้องเกิดข้อผิดพลาดแน่นอน จึงเกิดแนวคิดใหม่แทนที่จะสร้างไว้ก่อน เปลี่ยนเป็นสร้าง sequence ตอนที่นักเรียนสมัครในโครงการนั้น ๆ ดีกว่า โครงการไหนไม่มีการสมัครก็ไม่ต้องสร้าง และไม่เกิดปัญหาสร้าง sequence ไม่ครบในทุกโครงการแน่นอน

    คราวนี้ถึงเวลาที่เราจะมาดำเนินการกันแล้วค่ะ โดยมีขั้นตอนคร่าว ๆ ดังนี้คือ ตรวจสอบก่อนว่ามีการสร้าง sequence ของโครงการนั้น ๆ หรือยังถ้ายังไม่มีการสร้างก็ให้ทำการสร้าง และเมื่อถึงเวลาที่มีการสมัครก็สามารถเรียกใช้ sequence เพื่อออกเลขที่ผู้สมัครได้เลยค่ะ

    ขั้นตอนที่ 1

    ตรวจสอบว่ามีสร้าง sequence หรือยัง โดยเราสามารถตรวจสอบได้จาก object : user_sequences ตามตัวอย่าง Oracle Function ดังต่อไปนี้

        FUNCTION CountSequence (var_seq_name IN VARCHAR2)

            RETURN NUMBER

        IS

            var_seq_count   NUMBER := 0;

            var_seq_query   VARCHAR2 (1000);

        BEGIN

            var_seq_query :=

                   ‘SELECT COUNT (*) FROM user_sequences WHERE sequence_name = ”’

                || var_seq_name

                || ””;

            EXECUTE IMMEDIATE var_seq_query

                INTO var_seq_count;

            RETURN var_seq_count;

        END;

    ขั้นตอนที่ 2

    ทำการสร้าง sequence ตามหลักการการตั้งชื่อ ตามตัวอย่าง Oracle Procedure ดังต่อไปนี้

    PROCEDURE CreateSequence (var_seq_name IN VARCHAR2)

        IS

            var_seq_count   NUMBER := 0;

        BEGIN

            var_seq_count := CountSequence (var_seq_name);

            IF var_seq_count = 0

            THEN

                EXECUTE IMMEDIATE   ‘CREATE SEQUENCE ‘

                                 || var_seq_name

                                 || ‘ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999            

                                 NOCACHE NOCYCLE’;

            END IF;

        END;

    ขั้นตอนที่ 3

    เมื่อทำการสร้าง sequence เสร็จเรียบร้อย เราสามารถดึงค่าถัดไปของลำดับด้วยคำสั่ง nextval

    การออกเลขที่ผู้สมัครให้กับนักเรียน ซึ่งจะถูกเก็บไว้ที่ตัวแปร var_app_no โดยมีความยาวขนาด 5 หลัก เริ่มต้นจาก 00001,00002,……  ตามตัวอย่าง

    หมายเหตุ : หลักการในการตั้งชื่อ sequence คือ  ‘SEQ_APP_NO_’  + รหัสโครงการ + ปีการศึกษา

    เราสามารถออกเลขที่ผู้สมัครได้ตามตัวอย่าง Oracle Procedure ดังต่อไปนี้

        PROCEDURE ToGetApplicationNO (var_seq_name   IN     VARCHAR2,

                                      var_app_no        OUT VARCHAR2)

        IS

        BEGIN

            CreateSequence (var_seq_name);

            EXECUTE IMMEDIATE   ‘SELECT LPAD (‘

                             || var_seq_name

                             || ‘.NEXTVAL, 5, ‘

                             || ”’0”)’

                             || ‘ FROM DUAL’

                INTO var_app_no;

        END;

    จากขั้นตอนที่ 1-3 เราก็สามารถที่จะออกเลขที่ผู้สมัคร โดยรันข้อมูลเลขที่ผู้สมัครตามโครงการที่เปิดรับในแต่ละปีการศึกษา ได้ตามความต้องการแล้วค่ะ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • ว่าด้วยเรื่องการคำนวณตัวเลขตรวจสอบ (Check Digit)

    ได้รับมอบหมายจากทีมในการสร้าง Oracle Function เพื่อคำนวณตัวเลขตรวจสอบ (check digit) ของการชำระเงินค่าสมัครผ่านช่องทางการชำระเงินช่องทางหนึ่ง โดยได้รับ requirement มาดังภาพข้างล่างนี้

    จากภาพข้างต้นจะมีข้อมูลสำหรับการนำเข้า 4 ชุดซึ่งประกอบด้วย

    • ชุดที่ 1 : Customer No.1/Ref.1 
    • ชุดที่ 2 : Due Date (DDMMYY : พ.ศ.)  
    • ชุดที่ 3 : Customer No.2/Ref.2
    • ชุดที่ 4 : จำนวนเงินที่ต้องชำระ

    พร้อมด้วยขั้นตอนวิธีในการคำนวณตัวเลขตรวจสอบ (check digit) ดังนี้

    มาทำความเข้าใจกับวิธีคำนวณกันก่อนที่จะเริ่มต้นสร้าง Oracle Function

    ถ้ามาดูรายละเอียดของวิธีการคำนวณในข้อที่ 1 ซึ่งเป็นการหาค่าประจำหลักของข้อมูลนำเข้า โดยวิธีการคือ นำข้อมูลแต่ละหลักคูณค่าคงที่ คือ 6, 4, 5, 8, 7 ไปเรื่อย ๆ ตามลำดับ

    การหาค่าประจำหลักของข้อมูลนำเข้า จะสังเกตุว่าถ้านำข้อมูลนำเข้าทั้ง 4 ชุดข้อมูลมาเรียงต่อกันจะมีความยาวเท่ากับ 35 โดยที่

    • ลำดับที่ 1, 6, 11, 16, 21, 26, 31 นำค่าข้อมูลคูณด้วย 6
    • ลำดับที่ 2, 7, 12, 17, 22, 27, 32 นำค่าข้อมูลคูณด้วย 4
    • ลำดับที่ 3, 8, 13, 18, 23, 28, 33 นำค่าข้อมูลคูณด้วย 5
    • ลำดับที่ 4, 9, 14, 19, 24, 29, 34 นำค่าข้อมูลคูณด้วย 8
    • ลำดับที่ 5,10, 15, 20, 25, 30, 35 นำค่าข้อมูลคูณด้วย 7

    กรณีถ้าไม่ต้องคิดให้ซับซ้อนเราก็บอกว่า fix ค่าไปเลยตามเงื่อนไขข้างต้น ก็สามารถจะหาค่าประจำหลักของข้อมูลนำเข้าแต่ละตัวได้ แต่ถ้าจะยืดหยุ่นกว่านั้นก็สามารถมองได้ว่า

    • ลำดับที่ 1, 6, 11, 16, 21, 26, 31 เมื่อ mod ด้วย 5 จะได้ค่ากับ 1
    • ลำดับที่ 2, 7, 12, 17, 22, 27, 32 เมื่อ mod ด้วย 5 จะได้ค่ากับ 2
    • ลำดับที่ 3, 8, 13, 18, 23, 28, 33 เมื่อ mod ด้วย 5 จะได้ค่ากับ 3
    • ลำดับที่ 4, 9, 14, 19, 24, 29, 34 เมื่อ mod ด้วย 5 จะได้ค่ากับ 4
    • ลำดับที่ 5,10, 15, 20, 25, 30, 35 เมื่อ mod ด้วย 5 จะได้ค่ากับ 0

    ดังนั้นเราก็สามารถค่าประจำหลักของข้อมูลนำเข้าได้ดังต่อไปนี้

    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 1 ให้นำค่าข้อมูลคูณด้วย 6
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 2 ให้นำค่าข้อมูลคูณด้วย 4
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 3 ให้นำค่าข้อมูลคูณด้วย 5
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 4 ให้นำค่าข้อมูลคูณด้วย 8
    • ค่าลำดับที่ mod ด้วย 5 ได้เท่ากับ 0 ให้นำค่าข้อมูลคูณด้วย 7

    จากแนวคิดข้างต้นและขั้นตอนวิธีการคำนวณตาม requirement ที่กำหนดให้สามารถเขียนเป็น Oracle Function เพื่อจะคำนวณหาค่าตัวเลขตรวจสอบ (check digit) ได้ดังนี้

        FUNCTION GetPaymentCheckDigit (var_reference_1   IN VARCHAR2,

                                       var_reference_2   IN VARCHAR2,

                                       var_amount        IN VARCHAR2,

                                       var_due_date      IN VARCHAR2)

            RETURN VARCHAR2

        IS

            var_text_concat   VARCHAR2 (50);

            var_total_sum     NUMBER (5);

            var_x             VARCHAR2 (1);

            var_out_result    VARCHAR2 (2);

            var_text_length   NUMBER (2);

        BEGIN

            var_total_sum := 0;

            var_text_concat :=

                var_reference_1 || var_due_date || var_reference_2 || var_amount;

            var_text_length := LENGTH (var_text_concat);

            FOR i IN 1 .. var_text_length

            LOOP

                var_x := TO_NUMBER (SUBSTR (var_text_concat, i, 1));

                CASE

                    WHEN MOD (i, 5) = 1

                    THEN

                        var_total_sum := var_total_sum + var_x * 6;

                    WHEN MOD (i, 5) = 2

                    THEN

                        var_total_sum := var_total_sum + var_x * 4;

                    WHEN MOD (i, 5) = 3

                    THEN

                        var_total_sum := var_total_sum + var_x * 5;

                    WHEN MOD (i, 5) = 4

                    THEN

                        var_total_sum := var_total_sum + var_x * 8;

                    WHEN MOD (i, 5) = 0

                    THEN

                        var_total_sum := var_total_sum + var_x * 7;

                END CASE;

            END LOOP;

            var_out_result := TRIM (TO_CHAR (MOD (var_total_sum * 3, 100), ’00’));

            RETURN var_out_result;    

    END;

    หลังจากที่ได้เขียน Oracle Function : GetPaymentCheckDigit ตามเงื่อนไขวิธีการคำนวณเรียบร้อยแล้ว ได้ทำการทดลอง Exec Oracle Function : GetPaymentCheckDigit โดยใช้ข้อมูลทดสอบตามไฟล์ตัวอย่างข้างต้นโดยที่

    • Customer No.1/Ref.1  = 6400000327
    • Due Date (DDMMYY : พ.ศ.)  = 311264
    • Customer No.2/Ref.2  = 649002300013
    • จำนวนเงิน = 0050000
    • ตัวเลขตรวจสอบ (check digit) = 87

    ตัวเลขลขตรวจสอบ (check digit) จากไฟล์ตัวอย่างมีค่าเท่ากับ 87 และผลจากการรัน Oracle Function : GetPaymentCheckDigit ผลลัพธ์ที่ได้คือ 87 เท่ากัน

    อันนี้เป็นตัวอย่างวิธีหนึ่งในการคำนวณ กรณีเพื่อน ๆ มีวิธีการอย่างอื่นก็สามารถแนะนำหรือนำเสนอกันได้นะคะ หวังว่าโพสนี้จะเป็นประโยชน์ต่อผู้อ่านไม่มากก็น้อยนะคะ แล้วพบกันใหม่ค่ะ

  • Oracle: แปลงข้อมูล JSON เป็น Table หรือ View

    ในการส่งข้อมูลระหว่างแอพพลิเคชัน ในปัจจุบันปฏิเสธไม่ได้ว่ารูปแบบ JSON ได้รับความนิยมสูงมาก วันนี้มีโจทย์ในการพัฒนาระบบอย่างนึงที่ผมเจอคือ ได้ข้อมูลจาก log มาจัดเก็บในฐานข้อมูลซึ่งอยู่ในรูปแบบ JSON แต่ต้องการแยกเก็บเป็นฟิลด์เพื่อนำไปประมวลผล และวิเคราะห์ต่อ

    วิธีการแปลงข้อมูลที่ผมเลือกใช้ คือใช้ฟังก์ชัน JSON_TABLE โดยตัวอย่างข้อมูล JSON มีรูปแบบดังนี้

    {
  "latencies": {
    "request": 232,
    "kong": 12,
    "proxy": 220
  },
  "service": {
    "host": "alist-demo.psu.ac.th",
    "created_at": 1641960693,
    "connect_timeout": 60000,
    "id": "c00d9805-c281-4283-ab68-54e99719634a",
    "protocol": "http",
    "name": "test",
    "read_timeout": 60000,
    "port": 80,
    "path": "/services/commonservice.asmx/GetSite",
    "updated_at": 1646117362,
    "ws_id": "4920834d-f36d-41f6-8e98-959b4ec18b1b",
    "tags": {},
    "retries": 5,
    "write_timeout": 60000
  },
  "request": {
    "querystring": {},
    "size": 226,
    "uri": "/alist/getsite",
    "url": "http://api-gateway.psu.ac.th:8000/alist/getsite",
    "headers": {
      "host": "api-gateway.psu.ac.th:8000",
      "postman-token": "c7ee397e-1570-4e5d-9930-eac24fd2ecbc",
      "user-agent": "PostmanRuntime/7.29.0",
      "accept": "*/*",
      "connection": "keep-alive",
      "accept-encoding": "gzip, deflate, br"
    },
    "method": "GET"
  },
  "client_ip": "192.168.3.174",
  "tries": [
    {
      "balancer_latency": 0,
      "port": 80,
      "balancer_start": 1648009515575,
      "ip": "192.168.100.177"
    }
  ],
  "upstream_uri": "/services/commonservice.asmx/GetSite",
  "response": {
    "headers": {
      "content-type": "text/xml; charset=utf-8",
      "x-powered-by": "ASP.NET",
      "x-kong-proxy-latency": "12",
      "cache-control": "private, max-age=0",
      "x-ratelimit-remaining-minute": "9",
      "date": "Wed, 23 Mar 2022 04:25:15 GMT",
      "via": "kong/2.2.2",
      "x-aspnet-version": "4.0.30319",
      "x-frame-options": "SAMEORIGIN",
      "ratelimit-reset": "45",
      "content-length": "2622",
      "x-ratelimit-limit-minute": "10",
      "server": "Microsoft-IIS/10.0",
      "ratelimit-remaining": "9",
      "ratelimit-limit": "10",
      "x-kong-upstream-latency": "220",
      "connection": "close"
    },
    "status": 200,
    "size": 3109
  },
  "route": {
    "id": "db8cc7a7-77bb-4a3a-a059-06b8de3aa549",
    "paths": [
      "/alist/getsite"
    ],
    "protocols": [
      "http",
      "https"
    ],
    "strip_path": true,
    "created_at": 1641960726,
    "ws_id": "4920834d-f36d-41f6-8e98-959b4ec18b1b",
    "request_buffering": true,
    "name": "test-getsite",
    "updated_at": 1647326716,
    "preserve_host": false,
    "regex_priority": 0,
    "response_buffering": true,
    "https_redirect_status_code": 426,
    "path_handling": "v1",
    "service": {
      "id": "c00d9805-c281-4283-ab68-54e99719634a"
    }
  },
  "started_at": 1648009515563
}

    จะเห็นได้ว่ามีจำนวน Key และ Value จำนวนมากและอยู่ในรูปแบบ Parent Chid ด้วยแต่สามารถสร้าง View หรือ Table จากข้อมูลดังกล่าวด้วยคำสั่งเดียวดังนี้

      create or replace view v_log_traffic as 
  select t.*
  from log_traffic l,
    json_table(l.detail, '$'
    columns
      service_name path '$.service.name',
      service_path path '$.service.path',
      request_url path '$.request.url',
      client_ip path '$.client_ip',
      response_status path '$.response.status',
      response_size path '$.response.size',
      latencies_request path '$.latencies.request',
      latencies_kong path '$.latencies.kong',
      latencies_proxy path '$.latencies.proxy') t;

    สังเกตุว่า จุดสำคัญคือคำสั่ง json_table ที่เราจะต้องระบุฟิลด์ที่เก็บข้อมูล JSON ของเราดังในตัวอย่างคือ l.detail ที่เดิมเก็บอยู่ในตาราง log_traffic โดยใช้ path ย่อเป็น ‘$’ จากนั้นระบุ Keyword columns และตั้งชื่อ columns สำหรับ view ที่เราจะสร้างเช่น service_name ซึ่งใช้เก็บข้อมูลที่อยู่ใน path ดังนี้ ‘$.service.name’ เป็นต้น ซึ่งเมื่อรันคำสั่งดังกล่าวจะได้ view ดังรูป

    ในเรื่องของประสิทธิภาพพบว่าประมวลผลจำนวน 4000+ รายการใช้เวลาประมาณ 0.0008 วินาที ซึ่งถือว่ารวดเร็ว แต่ถ้าจำนวนข้อมูลมากขึ้น และประมวลผลช้าลงอาจปรับเป็น Materialize View หรือวิธีการอื่นๆ ที่ใช้ในการประมวลผลเฉพาะรายการที่เพิ่มใหม่ หรือประมวลผลตามรอบเวลา (ไม่เรียลไทม์) โดยใช้คำสั่งดังกล่าวช่วยก็ได้เช่นกัน หวังว่าจะเป็นประโยชน์สำหรับท่านที่มีโจทย์ประมาณนี้นะครับ

  • สร้าง ER Diagram ง่ายๆ ด้วย Toad for Oracle

    สวัสดีผู้อ่านทุกท่าน Blog ที่ 2 ของ TOR รอบนี้จะขอว่าด้วยเรื่องของ ER Diagram กันค่ะ การจัดทำ ER Diagram โดยปกติเราสามารถทำได้หลากหลายวิธี ใช้งานได้มากมายหลากหลายเครื่องมือ ก็แล้วแต่แหละเนอะ ว่าใครถนัดแบบไหน ใช้เครื่องมือใด

    สำหรับทางผู้เขียนจะคลุกคลีตีโมงอยู่กับ Toad for Oracle เป็นหลัก ครั้งนี้เลยจะมาขอแชร์วิธีการเล็กๆ น้อยๆ ซึ่งมีความสะดวกสบายในการสร้าง ER Diagram จากข้อมูลตารางที่อยู่ในฐานข้อมูลของเราโดยอัตโนมัตินั่นเอ๊งงงง !!!

    ปะ …. เรามาเริ่มกันเลยละกัน

    1. เมื่อเราเปิด Toad for Oracle และ Connect เข้า Database ที่เราต้องการเรียบร้อยแล้ว เราก็จะได้หน้าจอตามรูป
      ปล…หน้าตาอาจจะต่างกันเล็กน้อยแล้วแต่ version ของแต่ละคนที่ใช้งาน

    2. ให้คลิกเลือกเมนู “Database” —> จากนั้นเลือกเมนู “Report” —> เลือกเมนู “ER Diagram” เมื่อเรียบร้อยแล้ว เราก็จะได้พื้นที่ Workspace ของเราขึ้นมา ตัวอย่างดังรูปด้านล่างเลยจ๊ะ

    3. จากนั้นคลิกเลือก Add Objects ตรงสัญลักษณ์เครื่องหมาย + สีเขียวๆฟ้าๆ จากนั้นโปรแกรมจะแสดงหน้าต่างให้เราเลือก Table หรือ View จาก Schema ที่เราต้องการ เพื่อ “Add to ER Diagram“

    ปล … ทั้งนี้หากเราไม่เลือกที่ละรายการ ก็สามารถเลือกได้ว่า Select All , Deselect All หรือ Invert selection ผ่าน เครื่องมือที่มีให้ได้เช่นเดียวกัน

    4. เราลองมาเลือก Table ข้อมูลที่เราต้องการนำมาสร้าง ER Diagram กันเลย และเมื่อเลือกเรียบร้อยแล้วก็ให้คลิกปุ่ม “OK” ได้เลยนะ จากนั้นก็จะได้หน้าตา ER Diagram ที่โยงความสัมพันธ์ของข้อมูลให้แล้ว ดังรูปเลยทุกคน !!

    5. และหากเราต้องการให้ใน ER Diagram ของเราแสดงเพียงแค่ชื่อ Column Name เท่านั้น ไม่แสดงรายละเอียดอื่นๆ ก็แนะนำให้เลือกตรงข้อความ All Columns จากนั้นเลือกแสดงแบบ “Column Names only“

    6. เมื่อเลือกแสดงแบบ Column Names only ก็จะได้หน้าตาดังรูปด้านล่างนะ

    เป็นยังไงกันบ้างเอ่ย Blog นี้พอจะช่วยให้การสร้าง ER Diagram สำหรับผู้อ่านง่ายขึ้นบ้างมั้ย ?

    ทั้งนี้ …. ทางผู้เขียนก็ขอออกตัวก่อนเลย มันมีมากมายหลายวิธีจริงๆ ในการสร้าง ER Diagram ทั้งอาจจะง่ายกว่าวิธีนี้ หรือยุ่งยากกว่าวิธีนี้ก็เป็นได้ ผู้เขียนจึงอยากจะขอแชร์วิธีที่ผู้เขียนเลือกใช้เพื่อช่วยในการทำงานของทางผู้เขียนเองเท่านั้น และก็ยังคงหวังเป็นอย่างยิ่งว่า Blog นี้จะมีประโยชน์กับผู้อ่านทุกท่าน ไม่มากก็น้อย ตามคติที่ว่า “รู้ไว้ใช่ว่าใส่บ่าแบกหามมมมม” นั้นเอง 555+

    Special Thanks : Supervisor Regist Team สำหรับคำแนะนำในการใช้งานแง๊บบบบ 🙂

  • เชื่อมต่อจากฐานข้อมูล Oracle ไปยังฐานข้อมูลอื่นด้วย Oracle Database Gateway

    โดยปกติหากต้องการเชื่อมต่อฐานข้อมูลสองฐานข้อมูลสำหรับ Oracle สามารถทำได้โดยการสร้าง database link แต่กรณีที่ต้องการสร้างการเชื่อมต่อจากฐานข้อมูล Oracle ไปยังฐานข้อมูลที่สร้างโดยระบบจัดการฐานข้อมูลอื่นที่ไม่ใช่ Oracle เช่น MS SQL Server, MySQL สามารถทำได้โดยใช้เครื่องมือที่เรียกว่า Oracle Database Gateway นั่นเอง

    ตัวอย่างการใช้ Oracle Database Gateway เชื่อมต่อไปยัง MS SQL Server

    เพื่อให้เห็นภาพการใช้งานจะยกตัวอย่างการใช้งานกับ MS SQL Sever ดังนี้

    หมายเหตุ รุ่นของ Software ที่ใช้ในตัวอย่างนี้คือ

    • Oracle Database 12c R2 (12.2.0)
    • Oracle Database Gateways 12c (12.2.0)
    1. เตรียมข้อมูลที่จำเป็นก่อนการติดตั้ง

    ข้อมูลของ SQL Server ที่เราต้องการเชื่อมต่อ

    • ชื่อเครื่องหรือ IP Address ของเครื่องที่ SQL Server ติดตั้งอยู่
    • หมายเลข port ของ SQL Server ที่เราต้องการเชื่อมต่อ
    • ชื่อฐานข้อมูล SQL Server ที่เราต้องการเชื่อมต่อ

    2. ขั้นตอนการติดตั้ง Oracle Database Gateway

    เมื่อดาวน์โหลดตัวติดตั้งจากเว็บไซต์ของ Oracle แล้ว ดำเนินการติดตั้งจากตัวติดตั้งที่ดาวน์โหลด ดังตัวอย่างต่อไปนี้

    ขั้นตอนนี้จะเป็นการใส่ข้อมูลของฐานข้อมูล MS SQL Server ที่ต้องการเชื่อมต่อ

    หลังจากนั้นทำการติดตั้งไปตามขั้นตอนตามลำดับ จนถึงขั้นตอนการสร้าง listener

    หลังจากติดตั้ง listener สำเร็จแล้ว ก็เป็นอันเสร็จสิ้นการติดตั้ง Oracle Database Gateway

    *** หลังจากการติดตั้ง หากเรียกดู Service จะเห็นว่ามี service ของ listener ปรากฎขึ้นมา

    3. การตั้งค่าหลังจากติดตั้ง

    • ไปยังโฟลเดอร์ที่ติดตั้ง Oracle Database Gateway เช่น C:\app\tg\Administrator\product\12.2.0\tghome_1\dg4msql\admin
    • เปิดไฟล์ initdg4msql.ora
    • ตรวจสอบการตั้งค่าตรง  HS_FDS_CONNECT_INFO

    HS_FDS_CONNECT_INFO=[192.168.100.184]/1433/MSSQLSERVER

    กรณีมีแค่ instance เดียว  อาจจะละชื่อ instance ไว้ และระบุชื่อฐานข้อมูลได้เลย ดังตัวอย่าง

    HS_FDS_CONNECT_INFO=[192.168.100.184]:1433//FILMSDSQL

    4. สร้าง database link

    เมื่อติดตั้ง Oracle Database Gateway เสร็จแล้ว ขั้นตอนต่อมาคือการสร้าง database link บนฐานข้อมูล Oracle เพื่อทำการเรียกดูข้อมูลบน MS SQL Server ผ่านทาง database link ที่สร้างขึ้นมา

    • รูปแบบคำสั่งในการสร้าง Database Link เป็นดังนี้

    CREATE PUBLIC DATABASE LINK DBLinkName

    CONNECT TO sa IDENTIFIED BY password

    USING ‘dg4msql’;

    • DbLinkName คือ ชื่อของ database link
    • sa คือชื่อของ user บน MS SQL Server (อาจจะเป็น user อื่นก็ได้)
    • password คือ รหัสผ่านสำหรับ user บนฐานข้อมูล MS SQL Server

    5. การเรียกดูข้อมูล สามารถเรียกดูข้อมูลผ่าน database link ที่สร้างขึ้นโดยใช้คำสั่ง sql เช่น

    select * from table@DBLinkName;

    เมื่อ table คือ ชื่อตารางที่อยู่บน MS SQL Server

  • การใช้ grant และ grant with grant option ใน Oracle

    การใช้คำสั่ง grant

    โดยทั่วไปเมื่อต้องการให้สิทธิ์สำหรับดำเนินการกับ object ใด ๆ ของ user หนึ่งให้กับอีก user หนึ่ง จะใช้คำสั่งคือ grant ตัวอย่าง เช่น

    • user1 เป็นเจ้าของตารางชื่อ table01
    • ในฐานข้อมูลมี user ชื่อ user2 อยู่ในฐานข้อมูลเดียวกับ user1
    • ต้องการให้ user2 สามารถใช้คำสั่ง select ข้อมูลจากตาราง table01 ของ user1
    • บน user1 ดำเนินการโดยใช้คำสั่งในการให้สิทธิ์คือ

    grant select on user1.table01 to user2;

    • หลังจากให้สิทธิ์แล้ว บน user2 สามารถใช้คำสั่งในการ select ข้อมูลจากตาราง table01 ของ user1 ได้ ตังตัวอย่างดังนี้

    select * from user1.table01; //ต้องระบุ user คั่นด้วยจุด และตามด้วยชื่อตาราง

    • การให้สิทธิ์ นอกจาก select แล้ว ยังมีสิทธิ์อื่น ๆ อีก เช่น insert, update, delete, execute ฯลฯ
    • ตัวอย่างการให้สิทธิ์มากกว่าหนึ่งสิทธิ์

    grant select, insert, update, delete on user1.table01 to user2;

    การใช้คำสั่ง grant with grant option

    รูปแบบการใช้คำสั่งคือ หลังคำสั่ง grant ปกติ ตามด้วย with grant option

    เช่น grant select on user1.table01 to user2 with grant option;

    ผลจากการใช้คำสั่ง with grant option จะมีผลให้ user ที่ได้รับสิทธิ์ สามารถให้สิทธิ์ที่ได้รับมา grant ต่อไปให้กับ user อื่นต่อได้นั่นเอง

    • ต่อเนื่องจากตัวอย่างแรก มี user3 ในฐานข้อมูลเดียวกันกับ user1 และ user2
    • เมื่อ user1 ใช้คำสั่ง

    grant select on user1.table01 to user2 with grant option;

    • มีผลทำให้ user2 สามารถ grant สิทธิ์ในการ select ให้กับ user อื่นต่อได้ เช่น

    grant select on user1.table01 to user3;

    user ที่ได้รับสิทธิ์ grant with option สามารถ ใช้ grant with option ต่อให้กับ user อื่นได้หรือไม่ ???

    • user ที่ได้รับการ grant ด้วยคำสั่ง with grant option สามารถ grant ให้ user อื่นด้วยคำสั่ง with grant option ด้วยเช่นกัน
    • ตัวอย่าง เช่น มี user4 ในฐานข้อมูลเดียวกับ user1, user2, user3
    • ต่อเนื่องจากตัวอย่างก่อนหน้านี้ user3 สามารถใช้คำสั่ง with grant option ต่อให้กับ user4 ได้ หาก user2 ได้ grant ให้ user3 ด้วยคำสั่ง with grant option

    ข้อควรระวัง

    • จะเห็นว่าในการใช้คำสั่ง grant with grant option นั้น จะทำให้ user ที่ได้รับสิทธิ์นี้ สามารถ grant สิทธิ์ต่อ ๆ กันไปให้กับ user อื่น ๆ เป็นทอด ๆ ด้วยคำสั่ง with grant option เช่นกัน
    • เมื่อ user อื่นที่ไม่ใช่เจ้าของ object ที่ถูก grant ได้รับสิทธิ์ในการ grant with option จะเห็นว่าจะทำให้การควบคุมการให้สิทธิ์ทำได้ยาก หรืออาจจะเกิดความไม่ปลอดภัยเกิดขึ้น ดังนั้น ถ้าไม่จำเป็นจึงไม่ควรให้สิทธิ์แบบ with grant option นั่นเอง

    การยกเลิกการให้สิทธิ์

    เมื่อต้องการยกเลิกการให้สิทธิ์ สามารถทำได้โดยใช้คำสั่ง revoke ตัวอย่างเช่น

    revoke select, insert, update, delete on user1.table01 from user2;

    จะเป็นการยกเลิกการให้สิทธิ์ในการ select, insert, update, delete บนตาราง table01 ของ user1 ที่เคยให้กับ user2

    ตรวจสอบได้อย่างไร ว่าเคย grant อะไรไปบ้าง

    มี view ของระบบที่ช่วยให้สามารถตรวจสอบได้ว่า object ใด ถูก grant อะไรไปบ้าง ซึ่ง view นี้เป็นของ user sys

    • view ที่เก็บข้อมูลการ grant ของ object ต่าง ๆ ชื่อ dba_tab_privs
    • มีฟีลด์ที่น่าสนใจคือ
      • grantee คือ user ที่ได้รับสิทธิ์
      • owner คือ user ที่เป็นเจ้าของ object
      • table_name คือ ชื่อ object (ชื่อของ table, view ฯลฯ)
      • grantor คือ user ที่เป็นผู้ให้สิทธิ์ (อาจจะไม่ใช่เจ้าของ object ก็ได้)
      • privilege คือ สิทธิ์ที่ได้รับ
      • grantable คือ user ที่เป็น grantee สามารถ grant สิทธิ์ต่อได้หรือไม่