[B! tips][Oracle] amnmaã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

amnma id:amnma

tipsã¨Oracleã«é–¢ã™ã‚‹amnmaã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (9)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

NLS_LANGã®è¨å®š
<IT æŠ€è¡“ã®å‡¦æ–¹ç®‹ï¼šNLS_LANGã®è¨å®š> ã‚ªãƒ©ã‚¯ãƒ«ã§ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆå´ã‹ã‚‰æŽ¥ç¶šã™ã‚‹ã¨ãã¯ã€ NLS_LANG ã®è¨å®šãŒå¿…è¦ã«ãªã‚Šã¾ã™ã€‚ ä»¥ä¸‹ã€ï¼“ã¤ã®NLS_LANGã‚’ãŠã•ãˆã¦ãŠãã¾ã—ã‚‡ã†ã€‚ ä¾‹ï¼šLINUX ã®å ´åˆ â—UTF8 $export NLS_LANG=Japanese_Japan.AL32UTF8 â—SJIS $export NLS_LANG=Japanese_Japan.JA16SJIS â—EUC $export NLS_LANG=Japanese_Japan.JA16EUC â€»å‚è€ƒ ç’°å¢ƒå¤‰æ•°ã®è¨å®š http://www.searchman.info/tips/1530.html ã‚ªãƒ©ã‚¯ãƒ«ã®æ–‡å—ã‚³ãƒ¼ãƒ‰ã‚’èª¿ã¹ã‚‹ http://www.searchman.info/tips/1150.html
amnma 2011/05/11
database

oracle

tips
ãƒªãƒ³ã‚¯
SQL*Loaderãƒ¡ãƒ¢(Hishidama's sqlloader for Oracle9i Memo)
CSVãƒ•ã‚¡ã‚¤ãƒ«ã‹ã‚‰Oracleã®ãƒ†ãƒ¼ãƒ–ãƒ«ã¸ãƒ‡ãƒ¼ã‚¿ã‚’æµã—è¾¼ã‚€ãƒ„ãƒ¼ãƒ«ã€‚ å¤§é‡ã®insertæ–‡ã‚’ç™ºè¡Œã™ã‚‹ã‚ˆã‚Šã¯ã€æ–ç„¶é«˜é€Ÿã€‚ ãƒ‡ãƒ¼ã‚¿ã§ã‚ã‚‹CSVãƒ•ã‚¡ã‚¤ãƒ«ã‚„å›ºå®šé•·ãƒ•ã‚¡ã‚¤ãƒ«ã¨ã€ãƒãƒ¼ãƒ‰æ–¹æ³•ã‚’æŒ‡å®šã™ã‚‹ã‚³ãƒ³ãƒˆãƒãƒ¼ãƒ«ãƒ•ã‚¡ã‚¤ãƒ«ã‚’ç”¨æ„ ã—ã¦å®Ÿè¡Œã™ã‚‹ã€‚ ï¼ˆCSVãƒ•ã‚¡ã‚¤ãƒ«ã‹ã‚‰ã®ãƒãƒ¼ãƒ‰ã¯ã“ã®SQL*LoaderãŒä½¿ãˆã‚‹ãŒã€CSVå‡ºåŠ›ã«ã¯æ¨™æº–çš„ãªæ–¹æ³•ã¯ç„¡ã„ã‚‰ã—ãã¦ã€selectæ–‡ã§åŠ å·¥ã™ã‚‹æ–¹æ³•ãŒã‚ˆãä½¿ã‚ã‚Œã‚‹ã‚‰ã—ã„ã€‚ ã“ã®SQLæ–‡ã‚’ã„ã¡ã„ã¡æ›¸ãã®ã¯å°‘ã€…é¢å€’ãªã®ã§ã€SQLç”Ÿæˆç”¨Excelãƒžã‚¯ãƒã‚’ä½œã£ã¦ã¿ã¾ã—ãŸï¼‰ ã‚³ãƒ³ãƒˆãƒãƒ¼ãƒ«ãƒ•ã‚¡ã‚¤ãƒ« CSVãƒ•ã‚¡ã‚¤ãƒ«ã®å„é …ç›®ã¨ãƒ†ãƒ¼ãƒ–ãƒ«ã®é …ç›®ã¨ã®é–¢é€£ä»˜ã‘ç‰ã‚’æŒ‡å®šã™ã‚‹ã€‚ ï¼ˆã‚³ãƒ³ãƒˆãƒãƒ¼ãƒ«ãƒ•ã‚¡ã‚¤ãƒ«ã‚’ãƒ†ã‚ã‚¹ãƒˆã‚¨ãƒ‡ã‚£ã‚¿ã§æ›¸ãã®ã¯ã‘ã£ã“ã†é¢å€’ãªã®ã§ã€ã‚³ãƒ³ãƒˆãƒãƒ¼ãƒ«ãƒ•ã‚¡ã‚¤ãƒ«ä½œæˆç”¨Excelãƒžã‚¯ãƒã‚’ä½œã£ã¦ã¿ã¾ã—ãŸï¼ˆCSVãƒ•ã‚¡ã‚¤ãƒ«ç”¨ã€å›ºå®šé•·ãƒ•ã‚¡ã‚¤ãƒ«ç”¨ï¼‰ï¼‰ ä¾‹ï¼‰emp.ctlï¼š OPTIONS(LOAD=100,SK
amnma 2010/10/19
Oracle

database

db

sql*loader

TIPS
ãƒªãƒ³ã‚¯
Oracle 10g ã§ ORA-16038 ã‚¨ãƒ©ãƒ¼ãŒç™ºç”Ÿã—ãŸã¨ãã®å¯¾å‡¦æ–¹æ³•
ãƒ‡ã‚£ã‚¹ã‚¯å®¹é‡ã®å°‘ãªã„é–‹ç™ºã‚µãƒ¼ãƒã€‚å¤šç›®çš„ãªç”¨é€”ã§ã„ã‚ã„ã‚ãª CGI ã‚¢ãƒ—ãƒªã‚’å‹•ä½œã•ã›ã¦ã„ã‚‹ã®ã§ã€ã™ãã«ãƒ‡ã‚£ã‚¹ã‚¯å®¹é‡ãŒã„ã£ã±ã„ã«ãªã£ã¦ãã¾ã™ã€‚æœ¬æ—¥ã® Samba ã®é ˜åŸŸã®ä½œæ¥ãƒ•ã‚¡ã‚¤ãƒ«ã‚„ã‚‰ Oracle ã®ã‚¢ãƒ¼ã‚«ã‚¤ãƒ–ãƒã‚°ã‚„ã‚‰ãŒä¸€æ¯å¢—ãˆã¦ããŸã®ã§ã€Oracle ã®ã‚¢ãƒ¼ã‚«ã‚¤ãƒ–ãƒã‚°ã‚’ä¸€æ‹¬å‰Šé™¤ã—ã¾ã—ãŸã€‚ ã¯ã„ã€‚è³¢ã„æ–¹ã¯ã“ã®æ™‚ç‚¹ã§ç†è§£é ‚ã‘ã‚‹ã¨æ€ã„ã¾ã™ãŒã€Oracle ã‚’ shutdown ã›ãšã«ã‚¢ãƒ¼ã‚«ã‚¤ãƒ–ãƒã‚°ã‚’å‰Šé™¤ã—ãŸã®ã§ã€redo ãƒã‚°ã‚’ã‚¢ãƒ¼ã‚«ã‚¤ãƒ–ãƒã‚°ã«æ›¸ãå‡ºã—ä¸ã®çŠ¶æ…‹ã®ã¾ã¾ã§ ORA-16038 ã‚¨ãƒ©ãƒ¼ã‚’åã„ã¦ Oracle ãŒåœæ¢ã—ã¦ã—ã¾ã„ã¾ã—ãŸã€‚ ä½•ã¨ã‚‚ã€æ„šã‹ãªï½¥ï½¥ï½¥(ï¼›Â´Ð”ï½€)ï½³ï½³ï½¯â€¦ ã€‚ Oracle Master ã‚’å–å¾—ã—ã¦ã‹ã‚‰éšåˆ†ã¨ãªã‚‹ã®ã§ã€ã„ããªã‚Šé“ã®éšœå®³ãŒç™ºç”Ÿã™ã‚‹ã¨ç„¦ã‚Šã¾ã™ã€‚å¹¸ã„é–‹ç™ºã‚µãƒ¼ãƒã€‚æœ€æ‚ªã€æœ¬ç•ªã‹ã‚‰ export ã—ãŸãƒ€ãƒ³ãƒ—ãƒ•ã‚¡ã‚¤ãƒ«ã‚’å…ƒã«ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’å†æ§‹ç¯‰ã™ã‚Œã°ã‚ˆã„ã®ã§ã™ãŒã€ã
amnma 2010/04/15
Oracle

tips
ãƒªãƒ³ã‚¯
Oracle
Oracle DatabaseWorld ã®å…¨ã‚»ãƒƒã‚·ãƒ§ãƒ³å‹•ç”»ãŒæ—¥æœ¬èªžå—å¹•ä»˜ãã«ã¦è¦–è´ã„ãŸã ã‘ã¾ã™ã€‚è‡ªç„¶è¨€èªžã«ã‚ˆã‚‹å•åˆã›ã€AIãƒ™ã‚¯ãƒˆãƒ«æ¤œç´¢ã€True Cache, JSON Duality, OCI GoldenGate Stream, GoldenGate 23ai, SQL Firewall, 23aiã®é«˜å¯ç”¨æ€§ã€ãªã©æ§˜ã€…ãªãƒˆãƒ”ãƒƒã‚¯ã‚’æ‰±ã£ã¦ã„ã¾ã™ã€‚
amnma 2010/03/29
Oracle

DB

Tips
ãƒªãƒ³ã‚¯
SYS_CONTEXTã€USERENV - ã‚ªãƒ©ã‚¯ãƒ«ãƒ»Oracle SQL é–¢æ•°ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹
ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ äº‹å‰å®šç¾©ã•ã‚Œã¦ã„ã‚‹çµ„è¾¼ã¿ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹å USERENV ï¼ˆé–¢æ•°åã§ã¯ãªã„ï¼‰ ã¾ãŸã¯ã€ã€Œãƒ¦ãƒ¼ã‚¶ãƒ¼å®šç¾©ã®ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆãƒ»ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ã€ ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ã®ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆå USERENV ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ã®ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆå ã€ã¾ãŸã¯ã€ã€Œãƒ¦ãƒ¼ã‚¶ãƒ¼å®šç¾©ã®ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆãƒ»ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ã€ã®ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆå SYS_CONTEXT é–¢æ•°ã®å†…å®¹ çµ„ã¿è¾¼ã¿ã•ã‚ŒãŸ USERENV ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ã€ã¾ãŸã¯ã€ãƒ¦ãƒ¼ã‚¶ãƒ¼å®šç¾©ã® namespace ã«é–¢é€£ä»˜ã‘ã‚‰ã‚ŒãŸ ã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆå param ã®å€¤ã‚’ VARCHAR2 åž‹ã§æˆ»ã™ã€‚ æˆ»ã™æ–‡å—åˆ—ã®é•·ã•ãŒ max_len ã‚’è¶…ãˆã‚‹å ´åˆã«ã¯åˆ‡ã‚Šæ¨ã¦ã‚‰ã‚Œã‚‹ã€‚ ç¾è¡Œã®ã‚»ãƒƒã‚·ãƒ§ãƒ³ã®ãƒ¦ãƒ¼ã‚¶ãƒ¼æƒ…å ±ã‚’ã‚‚ã¤ USERENV ã¨ã„ã†çµ„è¾¼ã¿ãƒãƒ¼ãƒ ã‚¹ãƒšãƒ¼ã‚¹ãŒç”¨æ„ã•ã‚Œã¦ã„ã‚‹ã€‚ç›®çš„ã«å¿œã˜ãŸã‚³ãƒ³ãƒ†ã‚ã‚¹ãƒˆã‚’è‡ªå‰ã§ä½œæˆã™ã‚‹ã“ã¨ã‚‚å¯èƒ½ã€‚ USETENV é–¢æ•°ã®å†…å®¹ ä¸‹ä½äº’æ›ç”¨ã ãŒ SYS_CONTE
amnma 2008/03/10
IPã‚¢ãƒ‰ãƒ¬ã‚¹å–å¾—

Oracle

Tips
ãƒªãƒ³ã‚¯
Works - ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹æŽ¥ç¶šã‚ã‚Œã“ã‚Œ
ãƒ¯ãƒ¼ã‚¯ã‚¹INDEXã¸ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹æŽ¥ç¶šã‚ã‚Œã“ã‚Œ [Â æŽ¥ç¶šæ–‡å—åˆ—ä¸€è¦§Â ]Â Â [Â VBScriptÂ ]Â Â [Â JavaScriptÂ ]Â Â [Â VC++Â ]Â Â [Â JavaÂ ]Â Â Last Updated. 12-Nov-2006 â– ã¯ã˜ã‚ã« ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã‹ã‚‰ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«æŽ¥ç¶šã™ã‚‹ã‚µãƒ³ãƒ—ãƒ«ã‚½ãƒ¼ã‚¹é›†ã§ã™ã€‚ ä¾‹ã¨ã—ã¦æŒ™ã’ã¦ã„ã‚‹ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã¯ã€WSHï¼ˆWindows Script Hostï¼‰ã€VC++ã€Java ã§ã™ã€‚ ãƒŸãƒ‰ãƒ«ã‚¦ã‚§ã‚¢ã¯ODBCã¨ã€oo4oï¼ˆOracle Objects for OLEï¼‰ã‚’ä¸å¿ƒã«æ›¸ã„ã¦ã„ã¾ã™ã€‚ â– æŽ¥ç¶šæ–‡å—åˆ—ä¸€è¦§Â 2006/11/12Â Â NEWÂ ! OLE DBãƒ—ãƒãƒã‚¤ãƒ€ã€ODBCãƒ‰ãƒ©ã‚¤ãƒã®æŽ¥ç¶šè¨˜è¿°åã®æ›¸ãæ–¹ã€‚ å¯¾è±¡ã¯ã€ORACLEã€MS SQL Serverã€PostgreSQLã€MySQLã€ Accessã€Excelã€CSVã€Outlook
amnma 2008/03/06
DB

Oracle

Tips
ãƒªãƒ³ã‚¯
Oracleãƒ»Tipsé›†
â—è¡¨ã®ä¸€è¦§ SQL# select * from tab; â—è¡¨ã®é …ç›®ä¸€è¦§ SQL# desc è¡¨å;ï¼ˆ;ã¯çœç•¥å¯ï¼‰ â—ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®ä¸€è¦§ SQL# select * from user_ind_columns; â€»æŒ‡å®šã—ãŸè¡¨ã®ã‚¤ãƒ³ãƒ‡ãƒƒã‚¯ã‚¹ã®ã¿ã€è¡¨ç¤ºã™ã‚‹ã“ã¨ã‚‚ã§ãã¾ã™ã€‚ SQL# select column_name from user_ind_columns where table_name = 'è¡¨å'; â—ãƒ¦ãƒ¼ã‚¶ä¸€è¦§ SQL# select username from user_users; â—ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®æ–‡å—ã‚³ãƒ¼ãƒ‰ SQL# select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_NCHAR_CHARACTERSET'; è¡¨ã®ã‚³ãƒ”ãƒ¼æ–¹æ³• create tableã‚³ãƒžãƒ³ãƒ‰ã§è¡¨ã®ã‚³ãƒ”ãƒ¼ãŒã§ãã¾ã™ã€‚ ãŸã ã—ã€åˆ¶ç´„æ¡ä»¶ã¯ã€
amnma 2008/03/06
DB

Oracle

Tips
ãƒªãƒ³ã‚¯
DBMS_OUTPUT ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ - ã‚ªãƒ©ã‚¯ãƒ«ãƒ»Oracleã‚’ãƒžã‚¹ã‚¿ãƒ¼ã™ã‚‹ãŸã‚ã®åŸºæœ¬ã¨ä»•çµ„ã¿
DBMS_OUTPUT ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã®ä½¿ç”¨ PL/SQL ã«ãŠã„ã¦ DBMS_OUTPUT ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã¯ãƒ‡ãƒãƒƒã‚°ç›®çš„ãªã©ã§æ¨™æº–å‡ºåŠ›ã«ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã‚’è¡¨ç¤ºã™ã‚‹ãŸã‚ã®ãŠãã‚‰ãå”¯ä¸€ã®ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã§é–‹ç™ºä½œæ¥ä¸ã«ã¯åˆ©ç”¨çŽ‡ãƒŠãƒ³ãƒãƒ¼ãƒ¯ãƒ³ã®ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã§ã‚ã‚‹ã€‚ãã®å¤šãã¯é–‹ç™ºãƒ•ã‚§ãƒ¼ã‚ºãŒçµ‚äº†ã™ã‚‹ã¨å‘¼ã³å‡ºã—ã®ã‚ªãƒ¼ãƒãƒ˜ãƒƒãƒ‰ã‚’ãªãã™ç‚ºã«ã‚³ãƒ¡ãƒ³ãƒˆåŒ–ã•ã‚Œã‚‹ã‹ç„¡åŠ¹åŒ–ã•ã‚Œã‚‹ã€‚ ä½¿ç”¨æ–¹æ³•ã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ç°¡å˜ã§ã‚ã‚‹ã€‚ BEGIN FOR i in 1..7 LOOP FOR j IN 1..9 LOOP DBMS_OUTPUT.PUT('-'); END LOOP; DBMS_OUTPUT.PUT('+'); END LOOP; DBMS_OUTPUT.NEW_LINE; DBMS_OUTPUT.PUT_LINE('12345678901234567890'); END; / å®Ÿè¡Œçµæžœ ---------+---------+------
amnma 2008/02/19
[PL/SQL]ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã®ã™ã¹ã¦ã‚’ã‚ªãƒ©ã‚¯ãƒ«ã®ãƒ¡ãƒ¢ãƒªãƒãƒƒãƒ•ã‚¡ã«ä¸€æ™‚æ ¼ç´ã—ã¦ãŠããŸã‚ã€ç”¨é€”æ¬¡ç¬¬ã§ã¯ã€ã™ãã«ãƒãƒƒãƒ•ã‚¡ã‚µã‚¤ã‚ºã‚’è¶…éŽã—ã¦ ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes ã¨ã‚¨ãƒ©ãƒ¼ã¨ãªã‚‹

Oracle

Tips

DB
ãƒªãƒ³ã‚¯
æ¡ä»¶ä»˜ãã‚³ãƒ³ãƒ‘ã‚¤ãƒ«ã‚’ä½¿ã£ã¦Oracleã®é‹ç”¨ã‚³ãƒ¼ãƒ‰ã‚’åŠ¹çŽ‡åŒ–ã™ã‚‹ï¼šCodeZine
åŽ»ã‚‹8æœˆ30æ—¥ï¼ˆåœŸæ›œæ—¥ï¼‰ã€ãƒ©ã‚¤ãƒˆã‚¦ã‚§ã‚¤ãƒˆãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°è¨€èªžï¼ˆLightweight programming Languageï¼‰ã®ç·åˆã‚«ãƒ³ãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ã€ŒLL Futureã€ãŒã€ãªã‹ã®ZEROï¼ˆä¸é‡ŽåŒºã‚‚ã¿ã˜å±±æ–‡åŒ–ã‚»ãƒ³ã‚¿ãƒ¼ï¼‰å¤§ãƒ›ãƒ¼ãƒ«ã§é–‹å‚¬ã•ã‚ŒãŸã€‚å½“æ—¥ã¯å‰ç·šã®å½±éŸ¿ã§æ™‚ãŠã‚Šè±ªé›¨ã«è¦‹èˆžã‚ã‚Œã‚‹ä¸ã€ã•ã¾ã–ã¾ãªè¨€èªžã®ãƒ¦ãƒ¼ã‚¶ãƒ¼ã‚„é–‹ç™ºè€…ãªã©åƒäººè¿‘ããŒé›†ã¾ã£ãŸã€‚
amnma 2006/10/28
DB

Oracle

Programming

Tips
ãƒªãƒ³ã‚¯
1