Category: Oracle

  • คำสั่ง Update โดยใช้ข้อมูลจากอีกตาราง [Oracle]

    worapot.k

    ในการสั่ง update ข้อมูล ในบางครั้งเราอาจจะเจอโจทย์ว่า ต้องการ update ข้อมูลในตารางหนึ่งโดยใช้ข้อมูลจากอีกตารางหนึ่ง ซึ่งลักษณะของข้อมูลทั้งสองตารางนี้ จะต้องมีฟีลด์ข้อมูลที่สามารถเชื่อมโยงความสัมพันธ์ของข้อมูลแต่ละเร็คคอร์ดได้

    ตัวอย่างข้อมูล

    1. มีข้อมูลสองตาราง

    ตารางที่ 1 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาไทย [ชื่อตาราง : DAY_OF_WEEK_THAI]

    โครงสร้างตารางประกอบด้วย

    • ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
    • ฟีลด์ DAY_FULL เก็บข้อมูลวันแบบเต็มภาษาไทย
    • ฟีดล์ DAY_SHORT เก็บข้อมูลวันแบบย่อภาษาไทย

    ข้อมูลในตารางเป็นดังนี้

    ตารางที่ 2 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาอังกฤษ [ชื่อตาราง : DAY_OF_WEEK]

    โครงสร้างตารางประกอบด้วย

    • ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
    • ฟีลด์ DAY_ENG เก็บข้อมูลวันแบบเต็มภาษาอังกฤษ
    • ฟีดล์ DAY_ENG_SHORT เก็บข้อมูลวันแบบย่อภาษาอังกฤษ

    ข้อมูลในตารางเป็นดังนี้

    2. ฟีลด์ที่เชื่อมโยงความสัมพันธ์ของทั้งสองตารางคือฟีลด์ ID

    3. ต่อมามีการปรับแก้โครงสร้างตาราง DAY_OF_WEEK โดยเพิ่มฟีลด์ 2 ฟีลด์ ดังนี้

    ฟีลด์ DAY_THA สำหรับเก็บข้อมูลวันแบบเต็มภาษาไทย

    ฟีลด์ DAY_THA_SHORT สำหรับเก็บข้อมูลวันแบบย่อภาษาไทย

    4. เนื่องจากมีข้อมูลวันภาษาไทยในตาราง DAY_OF_WEEK_THAI อยู่แล้ว และสามารถเชื่อมโยงข้อมูลกันได้ด้วยฟีลด์ ID ดังนั้นความต้องการคือ การใช้ข้อมูลจากตาราง DAY_OF_WEEK_THAI มา update ให้กับตาราง DAY_OF_WEEK

    5. รายละเอียดการ update

    นำข้อมูลวันแบบเต็มภาษาไทยในฟีลด์ DAY_FULL ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA ของตาราง DAY_OF_WEEK

    นำข้อมูลวันแบบย่อภาษาไทยในฟีลด์ DAY_SHORT ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA_SHORT ของตาราง DAY_OF_WEEK

    รูปแบบคำสั่ง SQL

    แบบที่ 1

    update table1 t1 set

    table1.data1= (select table2.data1 from table2 where table2.key = t1.key),

    table1.data2 = (select table2.data2 from table2 where table2.key = t1.key);

    แบบที่ 2

    update table1 t1 set

    (table1.data1, table1.data2) =

    (select table2.data1, table2.data2 from table2 where table2.key = t1.key);

    อธิบายคำสั่ง

    table1 คือ ตารางที่ต้องการ update

    t1 คือ ชื่ออ้างอิงแทนตาราง table1 ซึ่ง t1 จะถูกใช้ในคำสั่ง select

    table1.data1 และ table1.data2 คือ ฟีลด์ข้อมูลที่ต้องการ update

    table2 คือ ตารางที่จะนำข้อมูลมาใช้เพื่อ update ให้กับ table1

    table2.data1 และ table2.data2 คือ ฟีลด์ข้อมูลที่จะนำมาใช้เพื่อ update ให้กับ table1.data1 และ table1.data2 ตามลำดับ

    ตัวอย่างการนำไปใช้งาน

    แบบที่ 1

    update day_of_week t1 set
    day_tha = (select day_full from day_of_week_thai where day_of_week_thai.id = t1.id),
    day_tha_short = (select day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

    แบบที่ 2

    update day_of_week t1 set
    (day_tha, day_tha_short) =
    (select day_full, day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

    ผลลัพธ์การรันคำสั่งทั้งสองรูปแบบ จะได้ผลลัพธ์เช่นเดียวกัน ดังรูป

    ข้อมูลก่อน update

    ข้อมูลหลัง update

    สรุป

    1. สามารถใช้คำสั่ง update ได้ทั้งสองรูปแบบซึ่งจะได้ผลลัพธ์เหมือนกัน
    2. แบบที่ 2 จะเป็นการใช้คำสั่งที่สั้นและกระชับกว่าแบบที่ 1 มาก กรณีที่ข้อมูลที่ต้องการ update มีมากกว่า 1 ฟีลด์
    3. ข้อควรระวังคือ ความสัมพันธ์ระหว่างสองตารางต้องเป็น 1 ต่อ 1 จึงจะสามารถใช้คำสั่งรูปแบบนี้ได้

  • Change BYTE to CHAR for all columns, all tables

    grianggrai.n

    เมื่อมีการสร้างฟิลด์ให้มีชนิดเป็น char หรือ varchar2 จริงๆ แล้ว มันมีหน่วยย่อยไปอีกว่าจะให้เป็น Byte หรือ Char ซึ่งโดยปกติจะเป็น Byte สามารถเปลี่ยนค่า default นี้ได้ด้วยการเปลี่ยนตัวแปรของระบบตัวแปรชื่อ nls_length_semantics

    https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams127.htm#REFRN10124

    ดูรายละเอียดเพิ่มเติม

    ทีนี้ Byte กับ Char มันสำคัญอย่างไร…สำคัญเรื่องการแปลง Character Set เช่น จาก TH8ASCII เป็น AL32UTF8 จากข้อมูลเดิม 1 ตัวอักษรเท่ากับ 1Byte สำหรับ TH8ASCII และ 3Byte สำหรับ AL32UTF8 ทำให้เมื่อเราสร้างฟิลด์ ตอนมี Character set บน TH8ASCII ชนิดของข้อมูลเป็น varchar2 มีขนาด 3Byte ถ้าจะนำข้อมูลเข้าอีกฐานข้อมูลที่มี Character Set เป็น AL32UTF8 จะนำเข้าไม่ได้เพราะขนาดเกินจำนวน Byte ที่สร้างคือกลายเป็น 9Byte ต้องกลับไปเปลี่ยนต้นทางจากหน่วย Byte เป็น Char คือเก็บเป็นตัวอักษรแทน

    ทีนี้เราจากสร้างสคริปต์เพื่อ สร้างสคริปต์ อีกที… ด้วยคำสั่ง (สคริปต์ในตัวอย่างนี้จะเป็นการสร้างจาก Username SCOTT)

    SELECT 'alter table '||owner||'.'|| table_name ||
       ' modify '||column_name||' varchar2('|| 
       data_length ||' CHAR); '
  FROM all_tab_columns
 WHERE data_type = 'VARCHAR2'
   AND char_used = 'B'
   and owner in ('SCOTT')
;

    เมื่อสั่งสคริปต์ทำงานจะได้ผลลัพธ์ประมาณว่า

    alter table

    ทีนี้จากสคริปต์ข้างต้น จะดึงข้อมูลของ Table และ View มาทั้งหมดทำให้เมื่อได้สคริปต์ไปแล้วสั่งทำงานจะมี error ต้องกรองเอา view ออก

    error

    เปลี่ยนสคริปต์ใหม่เป็น

    select 'alter table '||all_objects.OWNER||'.'||'"'||all_objects.OBJECT_NAME ||'"'||' modify '||'("'||column_name||'"'||' varchar2('|| ALL_TAB_COLUMNS.DATA_LENGTH ||' CHAR)); '
from   all_objects,all_tab_columns
where  all_objects.object_type in ('TABLE')
and all_tab_columns.data_type = 'VARCHAR2'
AND all_tab_columns.char_used = 'B'
and object_name not in (select all_objects.object_name from all_objects where all_objects.object_type = 'VIEW')
and all_objects.object_name=all_tab_columns.table_name
and all_objects.owner in ('SCOTT');

    เมื่อสั่งรันสคริปต์จะได้ผลลัพธ์

    alter script

    ก็จะสามารถนำสคริปต์ที่ได้ไปสั่งรันได้ปกติ

    Complete

    หลังจากเปลี่ยนข้อมูล TH8ASCII จาก Byte เป็น Char แล้วสามารถนำเข้าใน AL32UTF8 ได้เลย

    จบขอให้สนุก

    ต้นฉบับ

    http://webgeest.blogspot.com/2014/12/change-byte-to-char-for-all-columns-all.html

  • How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

    grianggrai.n

    อยากฆ่าคน เอ้ย session บน oracle แต่ไม่มีสิทธิ์ (Privilege) ไปขอสิทธิ์ DBA แล้ว DBA บอกว่าไม่มีสิทธิ์ kill session จะแจกให้!!! ทำไงดี

    เข้าระบบฐานข้อมูล Oracle ด้วยผู้ใช้ system หรือ sys แล้วสร้าง Procedure ชื่อว่า sp_kill_dev_session ด้วยคำสั่ง

    CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

    จาก code ข้างต้น ให้เปลี่ยน SCOTT เป็น username ที่เป็นเจ้าของ session เช่น 

    IF v_user IN ('HIRXPXT','PHU_XDMIHHION') THEN --the list can be extended

    เป็นต้น จากตัวอย่าง จะมี 2 user คือ HIRXPXT และ PHU_XDMIHHION

    แล้ว grant สิทธิ์ ให้สามารถ exec กับ user ที่ต้องการฆ่า session เหล่านั้นด้วยคำสั่ง ตัวอย่างจะให้ user ชื่อว่า HIRXPXT มีสิทธิ์ exec

    GRANT EXECUTE ON sp_kill_dev_session TO HIRXPXT;

    แล้วจะทำให้ user ที่ชื่อ HIRXPXT สามารถฆ่า (kill) session ของ user ได้ 2 คนคือ HIRXPXT และ PHU_XDMIHHION

    วิธีใช้งาน เข้าระบบด้วย user ที่ชื่อ HIRXPXT แล้วเรียกใช้คำสั่ง

    exec sys.sp_kill_dev_session(sid,serial num);

    เช่น

    Session Properties 
    exec sys.sp_kill_dev_session(154,42802);

    โดย 14 คือ sid และ 26043 คือ serial ซึ่งสองค่านี้สามารถเรียกดูได้จากคำสั่ง

    SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = upper('<username>');

    เปลี่ยน <username> เป็น username ที่ต้องการ แต่การที่จะดูค่าเหล่านี้ได้ต้องมีสิทธิ์ จากตัวอย่าง user ที่ชื่อ HIRXPXT สามารถ select view และ table เหล่านี้ได้

    SYS.V_$PROCESS
SYS.V_$SESSION
SYS.V_$TRANSACTION
SYS.V_$SESSION_LONGOPS
SYS.V_$LOCK
SYS.V_$SESSTAT
SYS.V_$STATNAME
SYS.V_$ACCESS
SYS.V_$ROLLNAME
SYS.V_$SQLTEXT
SYS.V_$SQLTEXT_WITH_NEWLINES
SYS.V_$SQL
SYS.V_$OPEN_CURSOR
SYS.V_$SESSION_WAIT
SYS.V_$SESSION_EVENT
SYS.V_$SESSION_CONNECT_INFO
SYS.V_$EVENT_NAME
SYS.V_$SESS_IO
SYS.V_$EVENTMETRIC
SYS.DBA_ROLLBACK_SEGS

    เท่านี้ก็เพียงพอที่จะให้ทีมพัฒนาสามารถฆ่า (kill) session ของตัวเองที่ค้างอยู่บน server ได้โดยไม่ต้องมาจิกหัว DBA กรั่กๆ

    ต้นฉบับ

    https://sqlpatterns.wordpress.com/2015/03/02/how-to-safely-grant-alter-system-kill-session-privilege-to-non-dba-users/

    จบ…. ขอให้สนุก

  • การเชื่อมต่อฐานข้อมูล Oracle ผ่าน Access office 365

    benjawan.n

    สำหรับหลาย ๆ ท่านนอกเหนือจากที่ต้องมีหน้าที่ในการพัฒนาโปรแกรมเป็นหลักแล้ว อาจจะต้องมีหน้าที่ในการนำเข้าข้อมูลด้วย ตัวเองก็เช่นกันต้องทำหน้าที่ในการนำเข้าข้อมูลเข้าฐานข้อมูล Oracle อยู่เป็นประจำ จริงๆ มีหลายเครื่องมือในการนำเข้าข้อมูล เช่น SQL Developer , Toad for Oracle เป็นต้น แต่บางครั้งก็ไม่สะดวกเพราะบางข้อมูลต้องผ่านการจัดการก่อนถึงจะสามารถถ่ายโอนได้ ตัวเองก็มีอีกวิธีคือ ถ่ายโอนผ่าน Access office 365

    สำหรับบทความในครั้งนี้ ขอนำเสนอวิธีการเชื่อมต่อฐานข้อมูล Oracle ผ่าน Access office 365 เผื่อใครจะใช้เป็นทางเลือกในการจัดการข้อมูลของ Oracle กันค่ะ

    โดยทำตามขั้นตอนดังต่อไปนี้

    STEP 1

    • ติดตั้ง  Oracle Database 12c Release 2 Client for Microsoft Windows (32–bit)
    • ตั้งค่า TNS Service name ในการเข้าถึงฐานข้อมูล Oracle ผ่าน Oracle Net Manager โดยมีขั้นตอนดังนี้
      • เปิดโปรแกรม Net Manager
      • คลิกที่ Local -> Service Naming คลิกเครื่องหมาย +
      • ระบบแสดงหน้าต่าง Net Service Name Wizard
      • ขั้นตอนที่ 1 ตรงช่อง Net Service Name ป้อนชื่อที่ต้องการ เช่น TEST_DB คลิกปุ่ม ถัดไป
      • ขั้นนตอนที่ 2 คลิกเลือก TCP/IP (Internet Protocol)  คลิกปุ่ม ถัดไป
      • ขั้นตอนที่ 3 ตรงช่อง Host Name: ป้อนชื่อฐานข้อมูล Oracle ที่ต้องการ เช่น TEST.PSU.AC.TH  ระบุ Port Number: 1521
      • ขั้นตอนที่ 4 ตรงช่อง Service Name ให้ใส่ชื่อ Service Name ของ Database ที่ระบุในขั้นตอนที่ 3 คลิกปุ่ม ถัดไป
      • ขั้นตอนที่ 5 ซึ่งเป็นขั้นตอนสุดท้าย เราสามารถคลิกปุ่ม Test เพื่อทดสอบการ connect กับฐานข้อมูล และคลิกปุ่ม เสร็จสิ้น เพื่อสิ้นสุดการการสร้าง Service Naming
    ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 1
    ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 2
    ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 3
    ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 4
    ภาพแสดงการตั้งค่า TNS Service name ขั้นตอนที่ 5

    STEP 2

    • เปิดโปรแกรม ODBC Data Sources (32-bit)
      • คลิกเลือกแท็บ System DSN คลิกปุ่ม Add…
      • คลิกเลือก driver : Oracle in OraClient 12Home1_32bit ในหน้าต่าง Create New Data Source คลิกปุ่ม Finish
      • ระบบแสดงหน้าต่าง Oracle ODBC Driver Configuration
        • กำหนด Data Source Name และ Description
        • เลือก TNS Service Name จากที่สร้างไว้ในขั้นตอนการตั้งค่า TNS Service name ขั้นตอนที่ 2 (กรณีไม่มีให้เลือกสามารถพิมพ์เองได้)
        • ตรงช่อง User ID ระบุ user / schema  เพื่อ connect เข้าฐานข้อมูล Oracle
        • คลิกปุ่ม OK
    ภาพแสดงหน้าต่างการเลือก driver ของ data source
    ภาพแสดงหน้าต่าง Oracle ODBC Driver Configuration

    STEP 3

    • เปิดโปรแกรม Access office 365
      • คลิกเลือกเมนู External Data -> New Data Source -> From Other Sources -> ODBC Database
      • ระบบแสดงหน้าต่าง Get External Data – ODBC Database
      • คลิกเลือก Link to data source by creating a linked table.
      • คลิกปุ่ม OK
      • ระบบแสดงหน้าต่าง Select Data Source คลิกแท็บ Machine Data Source
      • คลิกเลือก Data Source Name ที่เราได้สร้างไว้แล้ว คลิกปุ่ม OK
      • ระบบแสดงหน้าต่าง Oracle ODBC Driver Connect
      • กรอก Password เพื่อ connect เข้าฐานข้อมูล Oracle คลิกปุ่ม OK
      • ระบบแสดง Link Tables คลิกเลือกตารางที่ต้องการ คลิกปุ่ม OK
    ภาพแสดงหน้าจอหลักของ Access
    ภาพแสดงหน้้าต่าง Get External Data – ODBC Database
    ภาพแสดงหน้าต่าง Select Data Source
    ภาพแสดงหน้าต่าง Oracle ODBC Driver Connect
    ภาพแสดง Link Tables

    จากนั้นตารางที่เราเลือกก็จะมาแสดงให้เราเห็น เราก็สามารถจัดการข้อมูลดังกล่าวผ่าน Access office 365 ได้แล้ว คราวนี้ถ้าต้องการนำเข้าข้อมูลเราก็สามารถใช้ความสามารถของ Access ได้ผ่านตัว Append Query ได้หรือแม้แต่จะปรับปรุงข้อมูลก็สามารถทำได้ผ่านตัว Update Query เป็นต้น

    ภาพแสดงตัวอย่าง Linked Table ของฐานข้อมูล PSU_ADMISSION

  • วิธีการแสดงผลวันที่เป็นภาษาที่ต้องการด้วยฟังก์ชัน TO_CHAR

    benjawan.n

    หลาย ๆ คนคงเคยเจอปัญหาว่าฐานข้อมูลของเรากำหนด default การจัดเก็บข้อมูลวันที่ไว้เป็นปี ค.ศ. แต่ระบบที่เราพัฒนาต้องแสดงผลเป็นปี พ.ศ. เราต้องทำอย่างไร วันนี้จึงขอนำเสนอวิธีการแสดงผลวันที่เป็นภาษาที่เราต้องการด้วยฟังก์ชัน TO_CHAR กันค่ะ

    โดยที่ฟังก์ชัน TO_CHAR ทำหน้าที่ในการแปลงข้อมูลตัวเลขและข้อมูลวันที่เป็นตัวอักษร ซึ่งมีรูปแบบ Syntax ดังนี้

    TO_CHAR( input_value, [format_mask], [nls_parameter] )

    พารามิเตอร์ของฟังก์ชัน TO_CHAR คือ

    • input_value (จำเป็น): ค่าของวันที่ที่ต้องการจะแปลงค่า
    • format_mask (ทางเลือก): เป็นรูปแบบที่ต้องการให้แสดง หากไม่ได้ระบุไว้จะแสดงตามค่า default ที่กำหนดไว้
    • nls_parameter (ทางเลือก): เป็น nls language ที่จะใช้ในการแปลงค่า

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

    format_mask คำอธิบาย
    YEAR อ่านค่าปีเป็นตัวอักษร
    YYYY, YYY, YY, Y แสดงจำนวนหลักสุดท้ายของปีตามที่ระบุ
    MM แสดงเดือน (01-12, JAN = 01)
    MON แสดงชื่อเดือนแบบย่อ
    MONTH แสดงชื่อเดือนแบบเต็ม
    D แสดงวันในสัปดาห์
    DAY แสดงชื่อของวัน
    DD แสดงวันของเดือน (1-31)
    DDD แสดงวันของปี (1-366)
    DY แสดงชื่อย่อของวัน
    HH แสดงชั่วโมงของวัน (1-12)
    HH12 แสดงชั่วโมงของวัน (1-12)
    HH24 แสดงชั่วโมงของวัน (0-23)
    MI แสดงนาที (0-59)
    SS แสดงวินาที (0-59)

    คราวนี้ลองมาเขียน query เพื่อดูตัวอย่างค่าของ format_mask แต่ละตัวกันว่าจะแสดงผลออกมาเป็นอย่างไร

    รูปแสดง query และผลลัธพ์ที่ได้จากการแสดงผลตาม format_mask

    จะเห็นว่าค่าวันที่ที่แสดง แสดงเป็นปี ค.ศ. ตาม default ของฐานข้อมูล สังเกตุว่าข้อมูลบาง format_mask จะมีค่าศูนย์นำหน้า และมีช่องว่างตามหลัง ดังนั้นถ้าต้องการตัดเลขศูนย์และช่องว่างเราสามารถใช้ “FM” นำหน้า format_mask ตามตัวอย่างต่อไปนี้

    รูปแสดง query และผลลัพธ์ที่ได้จากการใช้ “FM” นำหน้า format_mask

    คราวนี้ถ้าเราต้องการที่จะให้การแสดงผลวันที่เป็นวัน เดือน ปี ไทย เราสามารถใช้ nls_parameter เข้ามาช่วยได้โดยสามารถกำหนดได้ตามตัวอย่างข้างล่างนี้

    รูปแสดงตัวอย่างวิธการกำหนด nls_parameter และผลลัพธ์ที่ได้

    ตัวแปรที่สำคัญคือ 
    – NLS_CALENDAR ตัวนี้แหละที่ทำให้ พ.ศ. แสดงเป็น พุทธศักราช 2562 (THAI BUDDHA)
    – NLS_DATE_LANGUAGE  ตัวนี้กำหนดให้แสดงเดือนเป็นภาษาไทย (THAI)

    ด้วยตัวพารามิเตอร์ NLS_CALENDAR และ NLS_DATE_LANGUAGE เราก็สามารถแสดงผลเป็น ภาษาอะไรก็ได้ตามที่เราต้องการ

  • วิธีการคำนวณหาอายุด้วย Oracle Datetime Functions

    benjawan.n

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

    Oracle Datetime Functions ที่จะแนะนำในวันนี้คือ ฟังก์ชัน TRUNC (date), ADD_MONTHS และฟังก์ชัน MONTHS_BETWEEN โดยมีรายละเอียดของฟังก์ชันดังนี้

    ฟังก์ชันคำอธิบาย
    ADD_MONTHSเพิ่มจำนวนเดือนลงในวันที่ที่ระบุ
    MONTHS_BETWEEN หาจำนวนเดือนระหว่าง 2 วันที่ที่ระบุ
    TRUNC (date)คืนค่าวันที่พร้อมส่วนเวลาของวันที่ตัดให้เป็นหน่วยที่ระบุ

    ตัวอย่างการใช้งานฟังก์ชัน ADD_MONTHS
    Syntax :
    ADD_MONTHS(date, integer)

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน ADD_MONTHS

    ตัวอย่างการใช้งานฟังก์ชัน MONTHS_BETWEEN
    Syntax :
    MONTHS_BETWEEN(date1, date2)

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน MONTHS_BETWEEN

    ตัวอย่างการใช้งานฟังก์ชัน TRUNC (date)
    Syntax :
    TRUNC ( date [, format ] )

    โดยที่ format ไม่ต้องระบุก็ได้ กรณีไม่ระบุวันที่จะถูกตัดเป็นวันที่ใกล้ที่สุด สามารถแสดงตัวอย่างของ format ได้ดังต่อไปนี้

    รูปแบบ Formatหน่วยปัดเศษ
    SYYYY, YYYY, YEAR. SYEAR, YYY, YY, Y ปี
    MONTH, MON, MM, RM เดือน
    DDD, DD, J วัน
    DAY, DY, D วันเริ่มต้นของสัปดาห์
    HH, HH12, HH24 ชั่วโมง
    MIนาที

    รูปแสดงตัวอย่างการเขียน query และผลลัพธ์ที่ได้ของฟังก์ชัน TRUNC (date)

    จากตัวอย่าง query การใช้งานทั้ง 3 ฟังก์ชันข้างต้น เราสามารถประยุกต์ใช้เพื่อคำนวณหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุได้ดังนี้

    รูปแสดงตัวอย่าง query การหาจำนวนปี จำนวนเดือน และจำนวนวันของอายุ

    คิดว่านอกเหนือจากการคำนวณหาอายุแล้วก็ยังไปประยุกต์ใช้สำหรับการหาช่วงเวลาอื่นๆ ได้อีกเช่น ระยะเวลาในการปฏิบัติงานเป็นต้น

  • SQL Expression สำหรับหาช่วงเวลาที่ต้องการ

    benjawan.n

    ใครที่เคยสร้าง Materialized View จะทราบว่าต้องมีการกำหนดช่วงเวลาเพื่อให้ Materialized View นั้นทำการ Refresh ข้อมูลเพื่อให้ได้ข้อมูลที่เป็นปัจจุบัน หรือแม้แต่ Job ก็เช่นกันต้องกำหนดช่วงเวลาเพื่อให้ Job ทำงานตามช่วงเวลาที่กำหนด บางคนอาจจะเจอปัญหาว่าไม่รู้ว่าต้องกำหนดอย่างไร เช่น อยากให้ทำงานทุก 8 โมงเช้า หรืออยากให้ทำงานทุกเที่ยงคืน เป็นต้น เพื่อให้ปัญหานั้นหมดไป เรามาทำความเข้าใจเกี่ยวกับช่วงเวลาบน Oracle กันดีกว่า

    จากที่เราทราบกันว่า 1 วันมี 24 ชั่วโมง 1 ชั่วโมงมี 60 นาที และ 1 นาทีมี 60 วินาที บน Oracle เราสามารถเขียนเป็น Expression ได้ตามตัวอย่างข้างล่างนี้

    ช่วงเวลา : PeriodExpression 1Expression 2
    1 Day11
    1 Hour1/241/24
    1 Minute1/(24*60)1/1440
    1 Second1/(25*60*60)1/86400

    ดังนั้นจาก Expression ข้างต้นเราก็สามารถเขียนช่วงเวลา ด้วย SQL Expression ดังตัวอย่างต่อไปนี้

    ช่วงเวลาที่ต้องการSQL Expression
    Nowsysdate
    Tomorrowsysdate+1
    Yesterdaysysdate-1
    One hour from nowsysdate + 1/24
    Ten minutes from nowsysdate + 10/1440
    Thirty seconds from nowsysdate + 30/86400
    Tomorrow at 12 Midnighttrunc(sysdate+1)
    Tomorrow at 8 AMtrunc(sysdate+1) + 8/24
    Yesterday at 10 AMtrunc(sysdate-1) + 10/24
    Next Monday at 12 Noonnext_day(trunc(sysdate),’MONDAY’) + 12/24

    คราวนี้เราลองมาเขียนคำสั่งในการ select ข้อมูลตามช่วงเวลาที่ต้องการกันดีกว่าค่ะ

    ตัวอย่างที่ 1 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, หนึ่งชั่วโมงถัดไปจากปัจจุบัน, พรุ่งนี้ตอนเที่ยงคืน และพรุ่งนี้ตอน 8 โมงเช้า สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

    ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 1

    ตัวอย่างที่ 2 ต้องการ query ข้อมูลเพื่อหาข้อมูลช่วงเวลาดังนี้ วันเวลาปัจจุบัน, สิบนาทีถัดไปจากปัจจุบัน, 30 วินาทีถัดไปจากปัจจุบัน และเวลาเที่ยงของวันศุกร์ถัดไป สามารถเขียน query เพื่อให้ได้ช่วงเวลาที่ต้องการได้ดังนี้ค่ะ

    ผลลัพธ์ที่ได้จาก query ตัวอย่างที่ 2

    จาก 2 ตัวอย่างข้างต้นคิดว่าก็พอจะเป็นแนวทางให้กับทุกท่านได้สามารถกำหนดช่วงเวลาที่ต้องการได้ไม่มากก็น้อย

  • การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติ

    sirapat.s

    การเขียน SQL เพื่อเลื่อนลำดับขึ้นลงอัตโนมัติของ column ที่ระบุลำดับเป็นตัวเลข

    รูปที่ 1 ตัวอย่างรูปแบบตาราง
    (more…)

  • ELK #08 Oracle Audit Trail

    kanakorn.h

    ต่อจา ELK #07 – Logstash คราวนี้ มาใช้งานจริง โดยใช้ ELK เพื่อเก็บ Log ของ Oracle Audit Trail

    1. Oracle Audit Trail บน Database Server เก็บ Log ในรูปแบบ XML โดยแต่ละ Event จะมี tag <AuditRecord> … </AuditRecord> คุมอยู่ ที่แตกต่างจาก Log ทั่วไปคือ ในแต่ละ Event จะมีเครื่องหมาย CRLF (การขึ้นบรรทัดใหม่) เป็นระยะ ๆ
    2. ออกแบบให้ Logstash รับข้อมูล (Input Plugin) จาก TCP Port 5515 ซึ่งต้องใช้ Codec ในการรวบ Multiline ในแต่ละ Event เข้าด้วยกัน โดยหา pattern “<AuditRecord>” เป็นจุดเริ่มต้น ส่วนบรรทัดที่ไม่เจอ Pattern ดังกล่าวนั้นการตั้งค่า negate => “true” เป็นการบอกว่า “ให้ดำเนินการต่อไป” โดยจะเอาบรรทัดที่ตามมาจากนี้ ต่อท้าย ด้วยการตั้งค่า what=> “previous”
    3. ในส่วนของ Filter Plugin จะอ่านค่าจาก “message” และ ส่งสิ่งที่ถอดจาก XML ได้ ไปยัง “doc”
    4. ในส่วของ Output Plugin จะส่งออกไปยัง ElasticSearch ที่ TCP port 9200

    ดัง Configuration ต่อไปนี้

    input {
   syslog {
      port => 5515
      codec => multiline {
           pattern => "<AuditRecord>"
           negate  => "true"
           what    => "previous"
      }
   }
}
filter {
   xml {
      source => "message"
      target => "doc"
   }
}
output {
  elasticsearch {
     hosts => ["elk.server:9200"]
  }
}

    จากนั้น ทาง Oracle Database Server ทำการเปิด Audit Trail แล้วเขียน Log ลงไฟล์ แล้วเขียน Cron เพื่อ Netcat ไฟล์ส่งมาให้ Lostash ที่เปิด Port TCP 5515 ไว้รอรับ

    ผลที่ได้คือ

    โดยวิธีนี้ จะเป็นการนำ Log ซึ่งจากเดิมเป็น Text Format นำมาเป็น NoSQL ได้ ซึ่งจะสามารถ Query ข้อมูลได้ง่ายยิ่งขึ้น

    หวังว่าจะเป็นประโยชน์ครับ