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

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进行简单树查询(递归查询)

DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (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