Tag: SQL

  • สร้าง 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 นะคะ

  • Change BYTE to CHAR for all columns, all tables

    เมื่อมีการสร้างฟิลด์ให้มีชนิดเป็น 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