CLOB

2022. 12. 27. 15:46ยท Today I Learned/2022
๋ชฉ์ฐจ
  1. CLOB
  2. CLOB ์ปฌ๋Ÿผ Select ์‹œ ์ฃผ์˜ ์‚ฌํ•ญ
  3. ๐Ÿง DBMS_LOB
  4. ๐Ÿ‘ Reference
๋ฐ˜์‘ํ˜•

CLOB

  • ๋ฌธ์ž ๋Œ€ํ˜• ๋ฐ์ดํ„ฐ ๋ณด๊ด€์šฉ
  • ์˜ค๋ผํด์€ CLOB <-> VARCHAR2 ์‚ฌ์ด์— ์•”์‹œ์  ๋ณ€ํ™˜์„ ์ˆ˜ํ–‰ํ•ด์ค€๋‹ค.
  • ์ตœ๋Œ€ 4GB๊นŒ์ง€ ์ €์žฅ ๊ฐ€๋Šฅ
  • ๋”ฐ๋กœ SIZE ์ง€์ • ์•ˆํ•˜๊ณ  ์‚ฌ์šฉ

 

CLOB ์ปฌ๋Ÿผ Select ์‹œ ์ฃผ์˜ ์‚ฌํ•ญ

  • CLOB ์ปฌ๋Ÿผ์€ ์ตœ๋Œ€ 4GB์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ €์žฅํ•  ์ˆ˜ ์žˆ๊ธฐ ๋•Œ๋ฌธ์—, row ์ˆ˜๊ฐ€ ๋งŽ์•„์ง„๋‹ค๋ฉด ์„ฑ๋Šฅ๋ฉด์—์„œ ํฐ ๋ถ€๋‹ด์ด ๋  ์ˆ˜ ์žˆ๋‹ค.
  • ๊ทธ๋ ‡๊ธฐ ๋•Œ๋ฌธ์— CLOB ์ปฌ๋Ÿผ ์กฐํšŒ ์‹œ ์ „์ฒด ๋ฐ์ดํ„ฐ๊ฐ€ ์•„๋‹Œ ์ผ๋ถ€ ๋ฐ์ดํ„ฐ๋งŒ ๋ณด์—ฌ์ง€๊ฒŒ ๋œ๋‹ค. (๋ฐ์ดํ„ฐ๊ฐ€ ์ž˜๋ชป ๋“ค์–ด ๊ฐ„๊ฒŒ ์•„๋‹˜..!)
  • ์ „์ฒด ๊ฐ’์„ ์กฐํšŒํ•˜๊ณ  ์‹ถ๋‹ค๋ฉด dbms_lob.substr(์ปฌ๋Ÿผ๋ช…, dbms_lob.getlength(์ปฌ๋Ÿผ๋ช…)) ์„ ์ด์šฉํ•ด์„œ ์กฐํšŒํ•œ๋‹ค.
    • ๋‹จ, Orange ํˆด์„ ์‚ฌ์šฉํ•˜๊ณ  ์žˆ๋‹ค๋ฉด ์ „์ฒด ๋ฐ์ดํ„ฐ๋ฅผ ๋ณด์—ฌ์ค€๋‹ค๊ณ  ํ•˜๋‹ˆ ์ฐธ๊ณ !
      • [Tools] - [Orange Optionsโ€ฆ] - [Common]์—์„œ โ€˜Long Sizeโ€™๊ฐ’์˜ ํฌ๊ธฐ๋ฅผ ๋Š˜๋ ค์ฃผ๋Š” ๋งŒํผ CLOB ์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ๊ฐ€ ์กฐํšŒ
      • ์˜ค๋ Œ์ง€์—์„œ โ€˜Long Sizeโ€™ ์˜ต์…˜์˜ ์ตœ๋Œ€๊ฐ’์€ 32,000์œผ๋กœ ๊ถŒ์žฅ
      • 32,000๋ณด๋‹ค ํฐ ๊ฒฝ์šฐ -> SQL Tool - Grid Result์—์„œ CLOB ์ปฌ๋Ÿผ์„ ์„ ํƒํ•˜๊ณ  ๋งˆ์šฐ์Šค ์šฐํด๋ฆญ ํŒ์—… ๋ฉ”๋‰ด๋ฅผ ํ†ตํ•ด โ€˜Export LOBs,LONG,RAWsโ€™ ๋ฉ”๋‰ด๋ฅผ ์„ ํƒํ•˜๋ฉด CLOB์ปฌ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํŒŒ์ผ ๋ณ„๋กœ ์ž˜๋ฆผ ์—†์ด ๋‚ด๋ ค ๋ฐ›์„ ์ˆ˜ ์žˆ์Œ
    • Orange์—์„œ CLOB ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ ํŒŒ์ผ๋กœ uploadํ•˜๊ธฐ
      • CLOB์˜ ๊ฒฝ์šฐ ํ•ด๋‹น ๊ฐ’์„ Text ํŒŒ์ผ๋กœ ์ €์žฅ, Table Editor ์—์„œ Import LOBs,Long,RAWs ์ด์šฉ

 

๐Ÿง DBMS_LOB

  • BLOB ๋˜๋Š” CLOB ํƒ€์ž…์˜ ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ์—ฌ๋Ÿฌ ๊ฐ€์ง€ ์—ฐ์‚ฐ์„ ์ œ๊ณตํ•˜๋Š” ํŒจํ‚ค์ง€

 

๐Ÿ‘ Reference

๋”๋ณด๊ธฐ

https://prohannah.tistory.com/1

 

Oracle CLOB ์ปฌ๋Ÿผ Select ์‹œ ์ฃผ์˜์‚ฌํ•ญ (ORA-06502)

Oracle CLOB ์ปฌ๋Ÿผ์€ Oracle ๋ฒ„์ „ 10g ์ดํ›„๋กœ ์ง€์›๋˜๋Š” LOB ํƒ€์ž…์˜ ์ข…๋ฅ˜ ์ค‘ ํ•˜๋‚˜๋กœ, ๋ฌธ์ž์—ด์ด๋‚˜ ์ด๋ฏธ์ง€ ๋ฐ์ดํ„ฐ๋ฅผ 4GB๊นŒ์ง€ ์ €์žฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.์–ด๋А ๋‚  ํ™”๋ฉด์—์„œ ์ฒ˜๋ฆฌํ•œ ๋ฐ์ดํ„ฐ๊ฐ€ DB์— ์ •์ƒ์ ์œผ๋กœ ๋“ค์–ด๊ฐ€์žˆ

prohannah.tistory.com

https://ezbuilder.tistory.com/78

 

[Oracle] DBMS_LOB ๋ฐ์ดํ„ฐ ์กฐํšŒ ๋ฐฉ๋ฒ•

์˜ค๋ผํด์˜ LOB ๋ฐ์ดํ„ฐ ์กฐํšŒ 1. ์ฒซ๋ฒˆ์จฐ SELECT DBMS_LOB.INSTR(content, '์˜๋ฌธ', 1, 1) FROM tb_board_clob WHERE DBMS_LOB.INSTR(content, '์˜๋ฌธ', 1, 1) 0; 2. ๋‘๋ฒˆ์งธ SELECT DBMS_LOB.SUBSTR(CONTENTS_TXT, DBMS_LOB.GETLENGTH(CONTENTS_TXT), 1) FROM LO

ezbuilder.tistory.com

 

 

๋ฐ˜์‘ํ˜•
  1. CLOB
  2. CLOB ์ปฌ๋Ÿผ Select ์‹œ ์ฃผ์˜ ์‚ฌํ•ญ
  3. ๐Ÿง DBMS_LOB
  4. ๐Ÿ‘ Reference
'Today I Learned/2022' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€
  • Lombok @UtilityClass
  • LocalDate, LocalDateTime ์›”์˜ 1์ผ, ๋ง์ผ ๊ตฌํ•˜๊ธฐ ๋ฐ Min/Max ์‹œ๊ฐ„ ๊ตฌํ•˜๊ธฐ
  • Spring REST Docs ๊ฐœํ–‰
  • MyBatis parameterType, resultType์— Static Inner Class ์‚ฌ์šฉ
YURI๐Ÿ•๐Ÿ“๐Ÿถ
YURI๐Ÿ•๐Ÿ“๐Ÿถ
๐Ÿ•YURI๐Ÿ•๐Ÿ“๐Ÿถ ๋‹˜์˜ ๋ธ”๋กœ๊ทธ์ž…๋‹ˆ๋‹ค.
๋ฐ˜์‘ํ˜•
YURI๐Ÿ•๐Ÿ“๐Ÿถ
๐Ÿ•
YURI๐Ÿ•๐Ÿ“๐Ÿถ
์ „์ฒด
์˜ค๋Š˜
์–ด์ œ
  • ๋ถ„๋ฅ˜ ์ „์ฒด๋ณด๊ธฐ (96)
    • Today I Learned (0)
      • 2022 (16)
      • 2023 (6)
      • 2024 (0)
    • Project (0)
    • Study (41)
      • OOP (1)
      • Java (2)
      • Spring (22)
      • Kafka (3)
      • Web (1)
      • Network (4)
      • MSA (2)
      • ETC (6)
    • Algorithm (30)
      • ๐Ÿ‘ ๋ฌธ์ œ (30)
    • Book (1)
    • Daily Life (0)

์ธ๊ธฐ ๊ธ€

์ตœ๊ทผ ๋Œ“๊ธ€

์ตœ๊ทผ ๊ธ€

hELLO ยท Designed By ์ •์ƒ์šฐ.v4.2.0
YURI๐Ÿ•๐Ÿ“๐Ÿถ
CLOB
์ƒ๋‹จ์œผ๋กœ

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”

๊ฐœ์ธ์ •๋ณด

  • ํ‹ฐ์Šคํ† ๋ฆฌ ํ™ˆ
  • ํฌ๋Ÿผ
  • ๋กœ๊ทธ์ธ

๋‹จ์ถ•ํ‚ค

๋‚ด ๋ธ”๋กœ๊ทธ

๋‚ด ๋ธ”๋กœ๊ทธ - ๊ด€๋ฆฌ์ž ํ™ˆ ์ „ํ™˜
Q
Q
์ƒˆ ๊ธ€ ์“ฐ๊ธฐ
W
W

๋ธ”๋กœ๊ทธ ๊ฒŒ์‹œ๊ธ€

๊ธ€ ์ˆ˜์ • (๊ถŒํ•œ ์žˆ๋Š” ๊ฒฝ์šฐ)
E
E
๋Œ“๊ธ€ ์˜์—ญ์œผ๋กœ ์ด๋™
C
C

๋ชจ๋“  ์˜์—ญ

์ด ํŽ˜์ด์ง€์˜ URL ๋ณต์‚ฌ
S
S
๋งจ ์œ„๋กœ ์ด๋™
T
T
ํ‹ฐ์Šคํ† ๋ฆฌ ํ™ˆ ์ด๋™
H
H
๋‹จ์ถ•ํ‚ค ์•ˆ๋‚ด
Shift + /
โ‡ง + /

* ๋‹จ์ถ•ํ‚ค๋Š” ํ•œ๊ธ€/์˜๋ฌธ ๋Œ€์†Œ๋ฌธ์ž๋กœ ์ด์šฉ ๊ฐ€๋Šฅํ•˜๋ฉฐ, ํ‹ฐ์Šคํ† ๋ฆฌ ๊ธฐ๋ณธ ๋„๋ฉ”์ธ์—์„œ๋งŒ ๋™์ž‘ํ•ฉ๋‹ˆ๋‹ค.