intermedia¿Í long type °Ë»öÇϱâ
ÀÛ¼ºÀÚ °ü¸®ÀÚ ÀÛ¼º½Ã°£ 2003-08-21 20:40:20
 

----------------------------------------------------------------------
---------- -----
table ¸ð¾ç
----------------------------------------------------------------------
---- -----------
create table b(
a long row
);




--------------------------------------------------------------- -----------------------
insert ºÎºÐ
----------------------------------------------------------------------
---- ------------
//DB Connetion ºÎºÐÀº ¼³¸í »ý·«
private DBConnectionManager connMgr;
Connection con ;
connMgr = DBConnectionManager.getInstance();

try{
String szCode = "ÇѱÛÀÌ µé¾î ÀÖ´Â 2000byte ÀÌ»óÀÇ string";

Reader abc = new StringReader(szCode);
PreparedStatement ps = con.prepareStatement("insert into b (a) values (?)");

//binaryÇüÅ·ΠDB¿¡ insert
ps.setCharacterStream(1, abc, szCode.length());

ps.executeUpdate()
ps.close
}
catch( SQLException e )
{
out.println("´ÙÀ½ ÀÌÀ¯·Î ÀÔ·ÂÀÌ Ãë¼Ò µÇ¾ú½À´Ï´Ù.\n"+e.toString());
}

connMgr.freeConnection("oracle",con);




----------------------------------------------------------------------
------- -----------
fetch ºÎºÐ
----------------------------------------------------------------------
---- --------------
import javax.servlet.*;
import javax.servlet.http.*;
import java.lang.*;
import neo.db.*;
import java.sql.*;
import java.util.*;
import java.io.*;

public class test extends HttpServlet
{

Connection con ;
private DBConnectionManager connMgr;

public void init(ServletConfig conf) throws ServletException {
super.init(conf);
}

public void doGet ( HttpServletRequest req , HttpServletResponse res) throws IOException
{

connMgr = DBConnectionManager.getInstance();

res.setContentType("text/html; charset=ksc5601");
PrintWriter out = res.getWriter();


con = connMgr.getConnection("oracle");
Statement stmt = null;
ResultSet rs = null;
try{
stmt = con.createStatement();
rs = stmt.executeQuery("select a from b ");
rs.next();

// byte ÇüÅ·ΠDBÀÇ data¸¦ ¹Þ¾Æ StringÀ¸·Î º¯È¯
String x= new String(rs.getBytes(1),0);

//stringÀ» Çѱ۷ΠÄÚµù
out.println(han(x.toString()));
stmt.close();
rs.close();
}catch ( SQLException e )
{
out.println(e);
}

connMgr.freeConnection("oracle",con);
}

public String han(String Unicodestr) throws UnsupportedEncodingException
{
if( Unicodestr == null)
return null;
return new String(Unicodestr.getBytes("8859_1"),"KSC5601");
}
}


8.0 ¹öÀü¿¡¼­´Â context optionÀ»
8i ¹öÀü¿¡¼­´Â InterMedia Text ¿É¼ÇÀ» ¼³Á¤À» ÇØÁÖ¾î¾ß ÇÕ´Ï´Ù.


ÀÌ ÀÚ·á´Â context option¼³Ä¡ ÈÄ »ç¿ëÀ» À§ÇØ ÇÊ¿äÇÑ »çÇ×°ú, ½ÇÁ¦ »ç¿ë¹æ¹ý¿¡ ´ëÇÑ ±âº»ÀûÀÎ »çÇ×µéÀ» ±â¼úÇÏ¿´´Ù. ±×¸®°í º°Ã·¿¡´Â °¢ versionº° ÃʱâÈ­ ¹æ¹ýÀ» Á¤¸®ÇÏ¿´´Ù.

1. parameter ¼³Á¤

(1) $ORACLE_HOME/dbs directory³»ÀÇ init.ora file³»¿¡

TEXT_ENABLE = TRUE·Î ÁöÁ¤µÇ¾î¾ß ÇÑ´Ù.

À̶§, ´Â ȯ°æº¯¼ö¿¡ ÁöÁ¤µÈ ORACLE_SID¿¡ ÇØ´çÇÏ´Â
°ªÀ¸·Î, ORA7ÀÎ °æ¿ì°¡ ¸¹´Ù.

(2) open_cursors¸¦ ¾à 2000Á¤µµ·Î Å©°Ô ÁöÁ¤ÇÑ´Ù.

2. one step query¿Í two step query»ç¿ë¹æ¹ý

~ (5)Àº one step query¿Í two step query¸ðµÎ µ¿ÀÏÇÏ´Ù.

(1) ctxsrv -user ctxsys/ctxsys -log ctx.log &

ÀÌ context server process´Â Çϳª¸¸ ½ÇÇà½ÃÅ°±â º¸´Ù´Â context optionÀ» »ç¿ëÇÏ°Ô µÇ´Â user¼ö¸¸Å­ ¶ç¿ì´Â °ÍÀÌ performance¿¡ ÁÁ´Ù.

¶Ç´Â ctxctlÀ» ¼öÇà½ÃŲ ÈÄ start n (¿©±â¿¡¼­ nÀº context serverÀÇ °¹¼ö)À¸·Î ½ÇÇàÇÏ¿©µµ µÈ´Ù.

(2) context optionÀ» »ç¿ëÇÒ »ç¿ëÀÚ¿¡°Ô ´ÙÀ½°ú °°Àº role¿¡ ´ëÇÑ ±ÇÇÑÀ» ºÎ¿©ÇÑ´Ù.

sqlplus ctxsys/ctxsys
sql> grant ctxapp to username;

3) pl/sql¿¡¼­ ¶§¶§·Î roleÀ» ÅëÇØ ºÎ¿©¹ÞÀº ±ÇÇÑÀ» ÀνÄÇÏÁö ¸øÇÏ´Â °æ¿ì°¡ ÀÖÀ¸¹Ç·Î ´ÙÀ½°ú °°ÀÌ ¸í½ÃÀûÀ¸·Î ±ÇÇÑÀ» ºÎ¿©ÇÑ´Ù.

sqlplus ctxsys/ctxsys
sql> grant execute on ctx_query to username;
sql> grant execute on ctx_ddl to username;

(4) sqlplus username/password
sql> exec ctx_ddl.create_policy('policy_name', 'table_name.column_name',
lexer_pref=>'CTXSYS.KOREAN');

* À̶§ policy_nameÀº ÀÓÀÇÀÇ À̸§À» ÁöÁ¤ÇÏ¸é µÈ´Ù.
** primary key°¡ µÎ°³ ÀÌ»óÀÇ columnÀ¸·Î ±¸¼ºµÇ¾î ÀÖ´Ù¸é,
textkey=>¡¯pk1,...,pkn¡¯À¸·Î ÁöÁ¤ÇÑ´Ù.
Áï pk1, pk2 columnÀ¸·Î primary key°¡ ±¸¼ºµÇ¾ú´Ù¸é
exec ctx_ddl.create_policy('policy_name', 'table_name.column_name', textkey=>¡®pk1,pk2¡¯, lexer_pref=>'CTXSYS.KOREAN');
*** composite textkeyÀÎ °æ¿ì tableÀÇ primary key¸¦ ±¸¼ºÇÏ´Â columnÀÌ 16°³±îÁö¸¸ °¡´ÉÇÏ´Ù.

(5) exec ctx_ddl.create_index('policy_name');
¿©±â¿¡¼­ policy_nameÀº create_plicy¿¡¼­ »ý¼ºÇÑ policy_nameÀ» ÁöÁ¤ÇÑ´Ù.

(6)-1 1 step query¹æ¹ý(stored procedure³»¿¡ »ç¿ëÇÏ´Â °ÍÀº ºÒ°¡´É)

´ÙÀ½°ú °°ÀÌ whereÀý¿¡ contains¶ó´Â »õ·Î¿î ¿¬»êÀÚ¸¦ ÀÌ¿ëÇÏ¿© queryÇÑ´Ù.

¿¹¸¦ µé¾î,
select * from table_name
where contains(column_name, 'ã°íÀÚ_ÇÏ´Â_ÇüżÒ') > 0;

(6)-2 2 step query¹æ¹ý

a. ¸ÕÀú ´ÙÀ½°ú °°Àº result tableÀ» ¸¸µç´Ù.

create table ctx_result
(textkey varchar2(64),
textkey2 varchar2(64),
...
textkeyn varchar2(64),
score number,
conid number);


* À̶§, primary key¸¦ ±¸¼ºÇÏ´Â columnÀÇ °¹¼ö¸¸Å­ textkey°¡ ÇÊ¿äÇϹǷΠprimary key°¡ ÇϳªÀÇ columnÀ¸·Î ±¸¼ºµÇ¾î ÀÖ´Ù¸é À§ÀÇ ctx_result tableÀÇ ±¸¼º Áß textkey2 ~ textkeynÀº »©°í textkey, score, conid, 3°³ÀÇ columnÀ¸·Î result tableÀ» ¸¸µé¸é µÈ´Ù.
** primary key columnÀÇ data typeÀÌ varchar2°¡ ¾Æ´Ï°í number¶ó ÇÏ´õ¶óµµ textkey´Â ¹«Á¶°Ç varchar2(64)·Î Àâµµ·Ï ÇÑ´Ù.

*** conid´Â ÇϳªÀÇ result tableÀ» ¿©·¯ query°¡ °øÀ¯ÇÏ´Â °æ¿ì, query¸¦ ±¸º°ÇÏ¿© ÁÖ±â À§ÇÑ °ÍÀÌ´Ù.

b. À§¿¡¼­ ¸¸µç result tableÀ» ÀÌ¿ëÇÏ¿© ´ÙÀ½°ú °°ÀÌ µÎ ´Ü°èÀÇ query¸¦ ¼öÇàÇÑ´Ù.

exec ctx_query.contains('policy_name', 'ã°íÀÚ_ÇÏ´Â_ÇüżÒ', 'ctx_result');

select score, primary_key, column_name
from table_name, result_name
where table_name.pk = ctx_result.textkey
and table_name.pk2 = ctx_result.textkey2
...
and table_name.pkn = ctx_result.textkeyn
order by score desc;

À§ÀÇ query´Â primary key¸¦ ±¸¼ºÇÏ´Â columnÀÌ ¶ó°í °¡Á¤ÇÒ °æ¿ìÀÇ query¹®À̸ç, primary key°¡ ÇϳªÀÇ columnÀ¸·Î ÀÌ·ç¾îÁ³´Ù¸é
and table_name.pk2 = ctx_result.textkey2
...
and table_name.pkn = ctx_result.textkeyn
ºÎºÐÀº ºÒÇÊ¿äÇÏ´Ù.

º°Ã·------------------------------------------------------------------
------ -
ConText ÃʱâÈ­ ¹æ¹ý

context option»ç¿ë Áß ±âÁ¸ »ç¿ëÁßÀÎ policy, indexµîÀ» ¸ðµÎ ¾ø¾Ö°í »õ·Î ÃʱâÈ­ÇؾßÇÏ´Â ÇÊ¿ä°¡ »ý±æ ¼ö ÀÖ´Ù. ¹öÁ¯º°·Î ÃʱâÈ­ ¹æ¹ýÀº Á¶±Ý¾¿ ´Ù¸£¸ç ´ÙÀ½°ú °°´Ù.
1. ConText 2.0.4, 2.0.6 ÀÇ ÃʱâÈ­(Oracle 7.3.3, 8.0.3)
$(SQLPLUS) ctxsys/ctxsys @dr0drp
$(SQLPLUS) sys/$(SYSPW) @drdpsyst
$(SQLPLUS) sys/$(SYSPW) @drcrsyst CTXSYS CTXSYS
(CTX_TABLESPACE) (CTX_TEMP_TABLESPACE)
$(SQLPLUS) CTXSYS/CTXSYS @dr0inst.sql
$(SQLPLUS) sys/$(SYSPW) @drsys.sql

2. ConText 2.3.4 ÀÇ ÃʱâÈ­(Oralce 7.3.4, 8.0.4)
$(SQLPLUS) ctxsys/ctxsys @dr0drop
$(SQLPLUS) sys/$(SYSPW) @drdpsyst
$(SQLPLUS) sys/$(SYSPW) @drcrsyst CTXSYS CTXSYS
(CTX_TABLESPACE)
$(CTX_TEMP_TABLESPACE)
$(SQLPLUS) CTXSYS/CTXSYS @dr0inst.sql
$(SQLPLUS) CTXSYS/CTXSYS @drgus.sql

3. ConText 2.4.5 ÀÇ ÃʱâÈ­(Oracle 8.1)
$(SQLPLUS) ctxsys/ctxsys @dr0drop
$(SQLPLUS) sys/$(SYSPW) @drdpsyst
$(SQLPLUS) sys/$(SYSPW) @drcrsyst CTXSYS CTXSYS
(CTX_TABLESPACE)
$(CTX_TEMP_TABLESPACE)
$(SQLPLUS) CTXSYS/CTXSYS @dr0inst.sql
$(SQLPLUS) CTXSYS/CTXSYS @drgus.sql
$(SQLPLUS) CTXSYS/CTXSYS @drlngset.sql .


Oracle8i InterMedia Text ÃʱâÈ­ ¹æ¹ý
====================================


InterMedia Text´Â Oracle8iÀÇ Universal Installer¿¡ ÀÇÇØ ÀÚµ¿À¸·Î
¼³Ä¡µÇ±â´Â ÇÏÁö¸¸ ¿©·¯ °¡Áö ÀÌÀ¯·Î ManualÇÏ°Ô ´Ù½Ã ¼³Ä¡ÇÒ ¼ö ÀÖ´Ù.
Installer¿¡ ÀÇÇØ ÀÚµ¿À¸·Î ¼³Ä¡µÈ °æ¿ì¿¡, InterMedia Text¸¦ °ü¸®ÇÏ´Â
À¯ÀúÀÎ ctxsys user´Â default·Î system tablespace·Î »ý¼ºµÇ´Â µ¥,
manualÇÑ ¼³Ä¡ °úÁ¤À» ÅëÇؼ­ ´Ù¸¥ tablespace¸¦ ¼³Á¤ÇÒ ¼ö ÀÖ´Ù.

InterMedia Text´Â Oracle8iÀÇ External Procedure±â´ÉÀ» ÀÌ¿ëÇÏ¿©
µ¥ÀÌÅͺ£À̽º Ä¿³Î°ú Á¢¸ñµÇ¾ú´Ù. ±×·¡¼­ InterMedia Text¸¦ Àß ½ÇÇàÇϱâ
À§Çؼ­´Â Net8 configurationÀ» ¼³Á¤ÇØ¾ß ÇÑ´Ù.


* InterMedia Text ÃʱâÈ­ ¹æ¹ý

1. ±âÁ¸ÀÇ ctxsys user¸¦ dropÇÑ´Ù.
$ cd $ORACLE_HOME/ctx/admin
$ sqlplus ctxsys/ctxsys @dr0drop.sql
$ sqlplus sys/sys_password @dr0dsys.sql

2. »õ·Î ctxsys user¸¦ »ý¼ºÇÑ´Ù.
Oracle8i ÀÌÀü¿¡´Â °ü¸®ÀÚ À¯Àú¸¦ ctxsys ¿Ü¿¡ À¯Àú°¡ ¿øÇÏ´Â À̸§À¸·Î
»ý¼ºÀÌ °¡´ÉÇßÁö¸¸, Oracle8iºÎÅÍ´Â ctxsys user·Î¸¸ °¡´ÉÇÏ´Ù.
ÀÌ script µÚ¿¡ ¿À´Â ù¹ø° ÀÔ·Â °ªÀº ctxsys userÀÇ passwordÀÌ°í,
µÎ¹ø°´Â ctxsys userÀÇ default tablespace, ¼¼¹ø°´Â temporary
tablespaceÀÌ´Ù. ctxsys userÀÇ default tablespaceÀÇ Å©±â´Â ¿øÈ°ÇÑ
¼öÇàÀ» À§ÇØ ¾à 30MÀÌ»óÀ» ±ÇÀåÇÑ´Ù.

$ sqlplus sys/sys_password @dr0csys.sql ctxsys context temp

3. Default object »ý¼º

$ sqlplus ctxsys/ctxsys @dr0inst.sql

/home/app/oracle/product/8.1.5/ctx/lib/libctxx8.so
-------------------------------------------------
À§¿Í °°ÀÌ $ORACLE_HOME/ctx/lib/libctxx8.so¿¡ ÇØ´çÇÏ´Â Àý´ë path¸¦
ÁöÁ¤ÇØ¾ß ÇÑ´Ù.

* on HP

HPÀÇ °æ¿ì libctxx8.so °¡ ¾Æ´Ï°í libctxx8.sl ·Î ÁöÁ¤ÇØ¾ß ÇÑ´Ù.

* on NT

sqlplus ¸¦ ctxsys user·Î Á¢¼ÓÇÑ ÈÄ ´ÙÀ½°ú °°ÀÌ ½ÇÇàÇÑ´Ù.

@dr0inst.sql Driver:oracle_home\bin\oractxx8.dll
(oractxx8.dll fileÀ̸§Àº Àý´ë path·Î ÀÔ·ÂÇÑ´Ù.)


$ sqlplus ctxsys/ctxsys @defaults/drdefko.sql

drdefko.sql fileÀº default·Î ÇÑ±Û lexer¸¦ »ý¼ºÇϱâ À§Çؼ­ ½ÇÇàÇÑ´Ù.


* Net*8 ¼³Á¤¹æ¹ý

InterMedia Text¸¦ »ç¿ëÇϱâ À§Çؼ­´Â Net8ÀÌ ¼³Á¤µÇ¾î ÀÖ¾î¾ß Çϸç,
À̸¦ À§Çؼ­ listener.ora¿Í tnsnames.ora fileÀÇ ³»¿ëÀ» ¼öÁ¤ÇØ¾ß ÇÑ´Ù.

1. IPC listener address¼³Á¤
listener.ora file¿¡ External procedure¸¦ À§ÇÑ listener¸¦ ¼³Á¤ÇÑ´Ù.

- ¼³Á¤ ¾ÈµÈ °æ¿ì

LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL=tcp)(HOST=Krhp1)(PORT=1521)
)
)

- ¼³Á¤ ÇÑ °æ¿ì

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=tcp)(HOST=Krhp1)(PORT=1521))
)
)
)

EXTPROC0ÀÇ ¸¶Áö¸· °ªÀº zeroÀ̸ç ÀÌ´Â default°ªÀÌ´Ù. ¸¸ÀÏ ¼­¹ö¿¡
¿©·¯ °³ÀÇ database°¡ ¼³Ä¡µÇ¾î ÀÖ´Ù¸é ipcÀÇ key°ªÀ» ´Ù¸£°Ô ÁÖ¾î¾ß ÇÑ´Ù.

2. SID(System Identifier)¼³Á¤
External procedureÀÇ »ç¿ëÀ» À§ÇÑ SID¼³Á¤À» À§ÇØ listener.ora fileÀÇ
SID_LIST_LISTENERÀ» ¼öÁ¤ÇÑ´Ù.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
....
....
(SID_DESC =
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/hom1/app/oracle/product/8.1.5)
(PROGRAM=extproc)
)
)

3. Service Name Description¼³Á¤
tnsnames.ora file¿¡ service nameÀ» ´ÙÀ½°ú °°ÀÌ Ãß°¡ÇÑ´Ù.

extproc_connection_data =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC0))
)
(CONNECT_DATA =
(SID=PLSExtProc)
(PRESENTATION =RO)
)
)

4. Listener ¼³Á¤ÀÇ È®ÀÎ

$ lsnrctl serv

LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 04-OCT-99
16:35:24

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
Services Summary...
PLSExtProc has 1 service handler(s)
DEDICATED SERVER established:0 refused:0
LOCAL SERVER
ORA815 has 1 service handler(s)
DEDICATED SERVER established:0 refused:0
LOCAL SERVER
The command completed successfully


* »ç¿ë¹æ¹ý

1. InterMedia Text »ç¿ëÀÚ »ý¼º

$ sqlplus system/manager
SQL> create user ctxdemo identified by ctxdemo
default tablespace users
temporary tablespace temp;
SQL> grant connect, resouce, ctxapp to ctxdemo;

2. InterMedia TextÀÇ À妽º »ý¼º°ú »ç¿ë

SQL> create table documents
( docu_id number primary key, text varchar2(1000) );
SQL> insert into documents values(1,'DB¸¶ÄÉÆà °í°´¸¸Á· À̲ö´Ù');
SQL> insert into documents values(2,'¸®´ª½º½ÃÀå Æǵµº¯È­ Á¶Áü');
SQL> commit;
SQL> create index docu_text on documents(text)
indextype is ctxsys.context;

-> index»ý¼º½Ã¿¡ ORA-28575 ERROR°¡ ¹ß»ýÇϸé Net8 ¼³Á¤ÀÇ ¹®Á¦ÀÌ´Ù.


SQL> select * from documents where contains(text,'º¯È­') > 0;

DOCU_ID TEXT
--------- -----------------------------------
2 ¸®´ª½º½ÃÀå Æǵµº¯È­ Á¶Áü

SQL> select * from documents where contains(text,'DB') > 0

DOCU_ID TEXT
--------- -----------------------------------
1 DB¸¶ÄÉÆà °í°´¸¸Á· À̲ö´Ù



Çѱ¹ Oracle¿¡¼­ ¹ÞÀº ÀÚ·áÀÔ´Ï´Ù.


¸ñ·Ï | ÀÔ·Â | ¼öÁ¤ | ´äº¯ | »èÁ¦