java执行数组类型的ORACLE 存储过程相关问题记录,oracle 全文索引

3925s9b1a7

java系统采用的SSH以及从C3p0连接池。数据库为oracle 10g
完成目标：
   通过java调用oracle存储过程。其中存储过程用到数组参数和事务及临时表。
碰到的问题：
   1 更倾向于用会话级临时表。但从一些资料上看，采用连接池的话会出现数据混乱问题。该删的未删。这个未验证。有谁做过，请指点。
   2  C3P0貌似不支持JDBC4，故不支持创建oracle数组类型，因而采用原生JDBC代替。基于第二点的话第一点看起来就不是问题了（如果是同一个存储过程的话）
第一步 建立了一个number类型的数组类型
  create or replace
type myvarray_list as varray(50) of number;
第二步 创建存储过程使用数组参数
   
create or replace
procedure test_proc
(                        
totalCount out NUMBER,
dataType in  myvarray_list
)
is
begin
select count(*) into totalCount  from test where typeid in
(select column_value from table(cast(dataType as myvarray_list)));
commit;
end test_proc;
  
java  JDBC执行此存储过程
  
public Long excuteProcByJDBC() throws SQLException {
CallableStatement cs = null;
Connection c=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
c = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.245:1521:orcl", "test", "test");
List<Object> list = new ArrayList<Object>();
list.add(1);
list.add(2);
Object[] data = list.toArray(new Object[list.size()]);
ArrayDescriptor arrDesc = ArrayDescriptor.createDescriptor("MYVARRAY_LIST", c);
ARRAY a = new ARRAY(arrDesc, c, data);
cs = c.prepareCall("call   SHOW_LIST(?,?)");
cs.registerOutParameter(1, OracleTypes.INTEGER);
cs.setArray(2, a);
cs.execute();
Long co = cs.getLong(1);
System.out.println(co);
return co;
} catch (Exception e) {
return 0L;
} finally {
if (cs!=null&&!cs.isClosed()) {
cs.close();
}
if (c!=null&&!c.isClosed()) {
c.close();
}
}
}


要下班了，有时间再写,继续
10g下手工安装全文检索组件(参考盖国强的文章和网上的一些资料)
1. 创建表空间
SQL> create tablespace oratext
2  datafile '+DISKGROUP/dbrac/datafile/oratext01.dbf' size 200m
3  extent management local uniform size 128k
4  ;      
Tablespace created.
2. 创建CTXSYS用户和CTXAPP角色
SQL> connect sys/<PASSWORD> as sysdba
Connected.
SQL> start ?/ctx/admin/catctx.sql ctxsys oratext temp unlock
...creating user CTXSYS
<省略>
Procedure created.

Grant succeeded.

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

3. 以CTXSYS用户登录执行drdefus.sql脚本
SQL> connect ctxsys/ctxsys;
Connected.
SQL> start ?/ctx/admin/defaults/drdefus.sql;
# 如果不执行这个脚本, 会出现如下错误: (10g中也一样)
# ERROR atline 1:
# ORA-29855: error occurred in the execution of ODCIINDEXCREATEroutine
# ORA-20000: Oracle Text error:
# DRG-10700: preference does notexist: CTXSYS.DEFAULT_LEXER
# ORA-06512: at "CTXSYS.DRUE", line 126
# ORA-06512:at "CTXSYS.TEXTINDEXMETHODS", line 54
# ORA-06512: at line 1
Creating lexer preference...
PL/SQL procedure successfully completed.
Creating wordlist preference...
PL/SQL procedure successfully completed.
Creating stoplist...
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
Creating default policy...
PL/SQL procedure successfully completed.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

4. 查询CTXSYS和CTXAPP的状态
[oracle@dbhost1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 18 15:35:39 2008
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select username,user_id,account_status,lock_date,expiry_date,created,profile from dba_users where username = 'CTXSYS';
USERNAME                          USER_ID ACCOUNT_STATUS     LOCK_DATE EXPIRY_DA CREATED   PROFILE
------------------------------ ---------- ----------------------------------------- --------- --------- ------------------------------
CTXSYS                                 72       OPEN                                                                 12-AUG-07 DEFAULT

SQL> select * from dba_roles where role = 'CTXAPP';
ROLE                           PASSWORD
------------------------------ --------
CTXAPP                         NO
SQL> quit                    

赋予需要使用全文索引的账号执行语句的权限
GRANT EXECUTE ON CTX_DDL TO test;

默认采用英文的那个分词需要使用中文分词的需要执行语句设置
EXEC CTX_DDL.create_preference('CN_LEXER','chinese_vgram_lexer');
创建全文索引
使用中文分词的
CREATE INDEX index1 ON tablename(columename) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER CN_LEXER');
使用英文分词的
create index index1 on tablename(columename)
indextype is ctxsys.context;
SELECT * FROM tablename WHERE CONTAINS(索引列,'索引条件')>0


PS 为了防止出现oracle 全文索引的 drg-50901 错误
可以这样使用语句
SELECT * FROM tablename WHERE CONTAINS(索引列,'/索引条件')>0
在索引条件前加上'/'

卸载Oracle text 组件
SQL> connect SYS/password as SYSDBA
SQL> spool text_deinstall.log
SQL> @?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL> spool off

ORACLE 递归查询
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME  VARCHAR2(100 BYTE),
PID   NUMBER                                  DEFAULT 0
);

插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
DEPTIDPAREDEPTIDNAME
NUMBERNUMBERCHAR (40 Byte)
部门id父部门id(所属部门id)部门名称
通过子节点向根节点追朔.
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid   
  
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid   

通过根节点遍历子节点.
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid   
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid   

可通过level 关键字查询所在层次.
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid   
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid   

再次复习一下:start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；
connect by prior 后面所放的字段是有关系的，它指明了查询的方向。
练习： 通过子节点获得顶节点
select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid