Tag: 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 นะคะ
- ที่มา https://stackoverflow.com/questions/26716595/sql-table-column-comments-in-view-column-comments
January 25, 2023
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 หรือวิธีการอื่นๆ ที่ใช้ในการประมวลผลเฉพาะรายการที่เพิ่มใหม่ หรือประมวลผลตามรอบเวลา (ไม่เรียลไทม์) โดยใช้คำสั่งดังกล่าวช่วยก็ได้เช่นกัน หวังว่าจะเป็นประโยชน์สำหรับท่านที่มีโจทย์ประมาณนี้นะครับ
March 29, 2022
ว่าด้วยการนับแถวข้อมูลใน ORACLE

              การนับแถวข้อมูล (Row Count) ในตารางข้อมูล (Table) บน ORACLE จะใช้คำสั่ง SQL พื้นฐานคือ

                            SELECT COUNT(*) FROM table_name;

              แต่ในบางครั้งข้อมูลที่ไม่ปกติหรือการเพิ่มพารามิเตอร์ในคำสั่ง COUNT อาจทำให้ได้ผลลัพธ์ที่แตกต่างกันได้ดังตัวอย่าง

ภาพการสร้างตารางข้อมูล table1 และเพิ่มข้อมูล 7 แถว

              จากภาพเป็นการเพิ่มตารางข้อมูล และเพิ่มข้อมูลในลักษณะที่แตกต่างกันคือ เพิ่มข้อมูลที่ไม่ซ้ำกัน เพิ่มข้อมูลซ้ำกัน เพิ่มข้อมูลที่เป็น NULL ทั้งสิ้น 7 rows

              เมื่อใช้คำสั่งเรียกดูข้อมูลและนับจำนวนข้อมูลพบว่าข้อมูลถูกแสดงถูกต้อง และสามารถนับได้ 7 rows ถูกต้อง

ภาพการแสดงข้อมูลและการนับจำนวนแถวข้อมูลแบบพื้นฐาน

              เมื่อใช้พารามิเตอร์ ALL ในคำสั่ง COUNT จะพบว่าสามารถนับได้ 5 แถว ซึ่งจะหมายถึงการนับเฉพาะแถวที่มีค่าข้อมูล (ยกเว้นแถวที่มี F1 เป็น NULL)

              การทำงานโดยใช้คำสั่ง SELECT COUNT( ALL f1) FROM table1; จะให้ผลการทำงานเหมือนกับการนับโดยระบุเงื่อนไข SELECT COUNT(*) WHERE f1 IS NOT NULL; ดังรูป

ภาพการแสดงข้อมูล และการนับข้อมูลโดยใช้พารามิเตอร์ ALL และการระบุเงื่อนไข WHERE …. IS NOT NULL

              เมื่อใช้พารามิเตอร์ DISTINCT ภายในคำสั่ง COUNT จะพบว่าผลการนับจะแสดงค่าที่ไม่ซ้ำเท่ากับ 4 (ค่าที่นับได้คือ 1,2,3,4) ค่า NULL ใน Row=5,7 ไม่ถูกนับเนื่องจาก NULL ไม่มีค่า

ภาพแสดงการนับข้อมูลและการนับข้อมูลโดยระบุพารามิเตอร์ DISTINCT

              สรุปในเบื้องต้นการนับจำนวนแถวใน ORACLE โดยใช้คำสั่ง COUNT นอกจากจะนับจำนวนแบบง่ายด้วยคำสั่ง COUNT(*) แล้ว เราสามารถระบุพารามิเตอร์ให้มีค่าเป็น ALL หรือ DISTINCT ก็จะให้ผลลัพธ์ของการทำงานที่แตกต่างกันได้

May 16, 2021
การใช้งานหน่วยเวลาใน ORACLE ระดับมิลลิวินาที

              การใช้งานประเภทเวลาใน ORACLE ที่เราใช้งานปกติคือข้อมูลประเภท Date (Data Type=Date) ซึ่งจะเก็บข้อมูลที่มีหน่วยเล็กที่สุดคือ วินาที (second)

              การใช้งานระบบที่มีผู้ใช้จำนวนมากพร้อมๆกัน ในบางครั้งหน่วยวินาทีอาจไม่ละเอียดพอ จำเป็นต้องใช้หน่วยเวลาที่เล็กกว่าวินาทีคือมิลลิวินาที (1000 มิลลิวินาที = 1 วินาที) ซึ่งใน ORACLE ได้จัดเตรียมข้อมูลประเภทนี้ไว้ให้คือ Timestamp ซึ่งสามารถใช้งานได้ทั้งการสร้างเป็นคอลัมน์ในตารางข้อมูลหรือเป็นตัวแปรใน PL/SQL ดังตัวอย่าง

การใช้งาน Timestamp ใน SQL

รูปแสดงการสร้างตาราง

              จากรูปจะสร้างฟิลด์ประเภท NUMBER, DATE และ TIMESTAMP (ที่ระดับความละเอียด 6 digits) โดยฟิลด์ DATE กำหนด Default Value = SYSDATE และ TIMESTAMP กำหนด Default = SYSTIMESTAMP

              เมื่อเพิ่มข้อมูลโดยระบุค่าในฟิลด์ ID ค่าในฟิลด์ Date1, Date2 จะถูกป้อนค่าอัตโนมัติดังรูป โดยจะเห็นค่าความละเอียดของหน่วยเวลาที่แตกต่างกันของทั้งสองฟิลด์ดังรูป

รูปเมื่อเพิ่มข้อมูลรายการใหม่ และสืบค้นข้อมูล

              จากรูปจะพบว่าค่าในฟิลด์ Date1 มีค่าเวลาหน่วยวินาทีเท่ากับ 38 ส่วน Date2 มีค่าเวลาหน่วยวินาทีเป็น 38.779

              ทดสอบการเพิ่มข้อมูลเพื่อดูค่าเวลาในฟิลด์ Timestamp โดยเพิ่มข้อมูลและหยุด 100 มิลลิวินาที ก่อนเพิ่มรายการถัดไป

คำสั่งเพิ่มข้อมูล และพัก 0.1 วินาทีก่อนเพิ่มข้อมูลรายการถัดไป

การสืบค้นข้อมูลเพื่อดูค่า Timestamp ที่รายการ ID = 2 และ 3

              จากรูปจะพบว่าค่า Date1 ของรายการ 2,3 มีค่าเท่ากันในขณะที่ Date2 (Timestamp) มีค่าต่างกัน

การใช้งานตัวแปรประเภท Timestamp ใน PL/SQL

              นอกจาก Timestamp สามารถใช้งานในคำสั่ง SQL แล้ว ยังสามารถใช้งานได้ใน PL/SQL ได้อีกด้วยดังรูป

รูปแสดงการใช้งานตัวแปรประเภท Timestamp

              จากรูปเป็นการสร้างตัวแปรประเภท Timestamp และนำค่าข้อมูลในฟิลด์ข้อมูลมาจัดเก็บและแสดง

              จากบทความนี้สามารถสรุปได้ว่า สามารถใช้งาน Data Type ประเภท Timestamp บน ORACLE เพื่อจัดการกับข้อมูลที่มีผู้ใช้งานระบบหลายคนพร้อมๆกัน ซึ่งจำเป็นต้องใช้หน่วยเวลาที่มีความละเอียดกว่าวินาที

May 16, 2021