May 18
CREATE OR REPLACE function WLJ.row_to_col_func(tabname in varchar2,
  group_col in varchar2,
  column_col in varchar2,
  value_col in varchar2,
  Aggregate_func in varchar2 default 'max',
  colorder in varchar2 default null,
  roworder in varchar2 default null,
  when_value_null in varchar2 default null
  )return sys_refcursor
Authid Current_User
as
  sqlstr varchar2(2000):='select '||group_col||' ';
  c1 sys_refcursor;
  v1 varchar2(100);
  cur sys_refcursor;
begin
  open c1 for 'select distinct '||column_col||' from '||tabname||case when colorder is not null then ' order by '||colorder end;
  loop
  fetch c1 into v1;
  exit when c1%notfound;
  sqlstr:=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||
  Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||
  case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';
  end loop;
  close c1;
  open cur for sqlstr||' from '||tabname||' group by '||group_col|| case when roworder is not null then ' order by '||roworder end;
  return cur;
end row_to_col_func;
/
May 18

--行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;

--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code,
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) > 0) t
ORDER BY 1, 2;


-- 实现一条记录根据条件多表插入
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT 'a'||ROWNUM c1, 'b'||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;
DROP TABLE t_ia_dest_1;
CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_2;
CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10));
DROP TABLE t_ia_dest_3;
CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));

SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_1;
SELECT * FROM t_ia_dest_2;
SELECT * FROM t_ia_dest_3;

INSERT ALL
WHEN (c1 IN ('a1','a3')) THEN
INTO t_ia_dest_1(flag,c) VALUES(flag1,c2)
WHEN (c1 IN ('a2','a4')) THEN
INTO t_ia_dest_2(flag,c) VALUES(flag2,c2)
ELSE
INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2)
SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;

-- 如果存在就更新,不存在就插入用一个语句实现
DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

SELECT * FROM t_mg;

MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b
ON (a.code = b.code)
WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME
WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);

-- 抽取/删除重复记录
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=10;
INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=2;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);

SELECT b.code, b.NAME
FROM (SELECT a.code,
a.NAME,
row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b
WHERE b.rn > 1;

-- IN/EXISTS的不同适用环境
-- t_orders.customer_id有索引
SELECT a.*
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

SELECT a.*
FROM t_employees a
WHERE EXISTS (SELECT 1
FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);

-- t_employees.department_id有索引
SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);

-- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual
CONNECT BY ROWNUM <=10;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;

-- 不建议使用
SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';

-- LOOP中的COMMIT/ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

SELECT * FROM t_loop;

-- 逐行提交
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
COMMIT;
END LOOP;
END;

-- 模拟批量提交
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count := v_count + 1;
IF v_count >= 100 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;

-- 真正的批量提交
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 100;
-- forall 实现批量
FORALL i IN 1 .. recs.COUNT
INSERT INTO t_loop VALUES recs (i);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;

-- 悲观锁定/乐观锁定
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

SELECT * FROM t_lock;

-- 常见的实现逻辑,隐含bug
DECLARE
v_cnt NUMBER;
BEGIN
-- 这里有并发性的bug
SELECT MAX(ID) INTO v_cnt FROM t_lock;

-- here for other operation
v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT;
END;

-- 高并发环境下,安全的实现逻辑
DECLARE
v_cnt NUMBER;
BEGIN
-- 对指定的行取得lock
SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;
-- 在有lock的情况下继续下面的操作
SELECT MAX(ID) INTO v_cnt FROM t_lock;

-- here for other operation
v_cnt := v_cnt + 1;
INSERT INTO t_lock (ID) VALUES (v_cnt);
COMMIT; --提交并且释放lock
END;

-- 硬解析/软解析
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);

SELECT * FROM t_hard;

DECLARE
sql_1 VARCHAR2(200);
BEGIN
-- hard parse
-- java中的同等语句是 Statement.execute()
FOR i IN 1 .. 1000 LOOP
sql_1 := 'insert into t_hard(id) values(' || i || ')';
EXECUTE IMMEDIATE sql_1;
END LOOP;
COMMIT;

-- soft parse
--java中的同等语句是 PreparedStatement.execute()
sql_1 := 'insert into t_hard(id) values(:id)';
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1
USING i;
END LOOP;
COMMIT;
END;



-- 正确的分页算法
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500)
WHERE rn > 480 ;

-- 分页算法(why not this one)
SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500 AND ROWNUM > 480;

-- 分页算法(why not this one)
SELECT b.*
FROM (SELECT a.*, ROWNUM rn
FROM t_employees a
WHERE ROWNUM < = 500
ORDER BY first_name) b
WHERE b.rn > 480;

-- OLAP
-- 小计合计
SELECT CASE
WHEN a.deptno IS NULL THEN
'合计'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
'小计'
ELSE
'' || a.deptno
END deptno,
a.empno,
a.ename,
SUM(a.sal) total_sal
FROM scott.emp a
GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

-- 分组排序
SELECT a.deptno,
a.empno,
a.ename,
a.sal,
-- 可跳跃的rank
rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
-- 密集型rank
dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
-- 不分组排序
rank() over(ORDER BY sal DESC) r3
FROM scott.emp a
ORDER BY a.deptno,a.sal DESC;

-- 当前行数据和前/后n行的数据比较
SELECT a.empno,
a.ename,
a.sal,
-- 上面一行
lag(a.sal) over(ORDER BY a.sal DESC) lag_1,
-- 下面三行
lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;
Apr 9
    Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件,imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库,一个用来测试,一个用来正式使用。

执行环境:可以在SQLPLUS.EXE或者DOS(命令行)中执行,
DOS中可以执行时由于 在oracle 8i 中  å®‰è£…目录\ora81\BIN被设置为全局路径,
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写,SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类,完成导入导出功能。

下面介绍的是导入导出的实例。
数据导出:
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
   exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
   exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
    exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"
  
  ä¸Šé¢æ˜¯å¸¸ç”¨çš„导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
  ä¹Ÿå¯ä»¥åœ¨ä¸Šé¢å‘½ä»¤åŽé¢ 加上 compress=y 来实现。

数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
   imp system/manager@TEST  file=d:\daochu.dmp
   imp aichannel/aichannel@HUST full=y  file=file= d:\data\newsmgnt.dmp ignore=y
   上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
   在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除,然后导入。

注意:
操作者要有足够的权限,权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一:
给用户增加导入数据权限的操作
第一,启动sql*puls
第二,以system/manager登陆
第三,create user 用户名 IDENTIFIED BY 密码 (如果已经创建过用户,这步可以省略)
第四,GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
      DBA,CONNECT,RESOURCE,CREATE SESSION  TO 用户名字
第五, 运行-cmd-进入dmp文件所在的目录,
      imp userid=system/manager full=y file=*.dmp
      æˆ–者 imp userid=system/manager full=y file=filename.dmp

执行示例:
F:\Work\Oracle_Data\backup>imp userid=test/test full=y file=inner_notify.dmp

屏幕显示
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation.  All rights reserved.

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表                  "INNER_NOTIFY"          4行被导入
准备启用约束条件...
成功终止导入,但出现警告。


附录二:
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.
  å…ˆå»ºç«‹import9.par,
  ç„¶åŽï¼Œä½¿ç”¨æ—¶å‘½ä»¤å¦‚下:imp parfile=/filepath/import9.par
  ä¾‹ import9.par 内容如下:
        FROMUSER=TGPMS        
        TOUSER=TGPMS2     (注:把表的拥有者由FROMUSER改为TOUSER,FROMUSERå’ŒTOUSER的用户可以不同)          
        ROWS=Y
        INDEXES=Y
        GRANTS=Y
        CONSTRAINTS=Y
        BUFFER=409600
        file==/backup/ctgpc_20030623.dmp
        log==/backup/import_20030623.log
Feb 3
最近发现程序在开发环境运行都很正常,但一移至生产环境有些功能老出异常:ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值



15:59:46,953 ERROR [JDBCExceptionReporter] Could not execute JDBC batch update
java.sql.BatchUpdateException: ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10656)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeBatch(DelegatingPreparedStatement.java:231)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:59)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:56)
at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:109)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:460)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:442)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2418)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2371)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:386)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:314)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:189)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:134)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:148)
at $Proxy121.saveUserAuth(Unknown Source)

    å¦‚果把insert或者update语句直接在PL/SQL里面跑,或者拼成sql语句在程序里执行,都是没问题的,但是如果是用ojdbc驱动的方法(包括hibernate),就会出错。此外,在另外一台Linux的机器上的Oracle9.2.0.8就没问题(生产机是Windows系统,Oracle9.2.0.1)。

    ç»è¿‡google和多个交叉测试,排除了操作系统、中间件平台(Weblogic)的问题。在红伟的指点下,找到这么一个网页,把这个问题的原因说得好像比较清楚(wordpress的,需要翻墙):http://vsadilovskiy.wordpress.com/2007/10/19/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column/

    é‡Œé¢æåˆ°çš„解决办法是,用新版的ojdbc驱动,然后把OracleDatasourceçš„oracle.jdbc.RetainV9LongBindBehavior属性设为true,经测试,并不生效。测试了从Oracle网站下载的不同版本(9i,10g各小版本)的ojdbc.jar,情况依旧。

      æœ€åŽï¼ŒæŠŠç”Ÿäº§æœºçš„Oracle升级到9.2.0.8,问题解决。

------------------------------------------------------------

附:上面提到的网页的文字拷贝

ORA-01461: can bind a LONG value only for insert into a LONG column

October 19, 2007 — Vlad Sadilovskiy

Just one another issue with JDBC I think is worth mentioning. You perhaps wonder why so obvious error message could be troublesome to comprehend. Unfortunately, it speaks of things that a developer never meant to happen. It doesn’t have to be a LONG value. This error could appear when ASCII strings are bound for VARCHAR2 columns. In this case it happens when JDBC v. 10.1/2 is used with a Oracle Server 9i database configured for multi-byte character set. Before going any further, please note that according to support matrix Oracle JDBC 10g and 9i are cross supported with Oracle Server 9i and 10g.

For the testing I used two databases Oracle 10g and Oracle 9i with database character set UTF8 and JDBC 10.2.0.3.

Some of the Asian language characters can consume up to 3 bytes in UTF8 encoding. But can you expect that a string composed of 1334 ASCII characters would not bind for VARCHAR2(4000)? However, it is possible. Let’s dig up little more details.

Here is how the string of 4000 “a” characters would appear to the Oracle 9iwhen bound from JDBC 10g with default properties. This bind is not spooled into the 10046 trace file. Instead it can be captured by enabling stacktrace dump on 1461 event.

bind 19: dty=1 mxl=4001(12000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
   bfp=ffffffff7cd7f060 bln=4000 avl=00flg=05

Oracle Server 10g. Note, that when you test this scenario, there are no errors – Oracle Server 10g  is perfectly fine with the 12000 bytes in the private buffer.

Bind#19
oacdty=01 mxl=4001(12000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
  kxsbbbfp=ffffffff7b55a0c0  bln=4000  avl=4000  flg=05
  value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa....

According to Metalink Note: 3756847.8 this problem was introduced in some 10.1 versions of JDBC, but was fixed in 10.2. However, it is not enabled by default. Following JDBC connection property enables the fix.

java.util.Properties props = new java.util.Properties();
props.put("oracle.jdbc.RetainV9LongBindBehavior","true");
ods.setConnectionProperties(props);
ods.setURL(url);

And here is how the bind looks like after the fix is enabled.

Oracle Server 9i:

bind 19: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
   bfp=ffffffff7cd56088 bln=4000 avl=4000 flg=05
   value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

Oracle Server 10g:

Bind#19
oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
  kxsbbbfp=ffffffff7b56f060  bln=4000 avl=4000flg=05
  value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

There is no need in enabling this fix for 10g/10g setup. 10g version of the trace is shown just for comparison purposes.

In conclusion I want to clarify that this article describes the unexpected behavior with legitimate length values. However, there are other cases when apparently correct values fail to insert with same message. I.e. already mentioned strings that, when using UTF8 for database character set, can expand from 4000 Asian language characters to up to 12000 byte values. In these cases, Notes: 241358.1, 370438.1 recommend different workarounds. Note: 445072.1  has decent example of getting the length of encoded string and suggests converting VARCHAR2 columns into CLOB if encoded string length exceeds maximum length of VARCHAR2 type.




1  æŸ¥çœ‹oracle的版本信息

(1)用客户端连接到数据库,执行select * from v$instance
            æŸ¥çœ‹version项

(2)select * from product_component_version

(3)或查询V$VERSION查看组件级信息
Jan 28
做个记录:


SELECT LENGTHB(TRANSLATE('1,2,34,5',',12345',',')) FROM DUAL;
SELECT LENGTHB('1,2,34,5')-LENGTHB(REPLACE('1,2,34,5',',','')) FROM DUAL;
Pages: 5/8 First page Previous page 1 2 3 4 5 6 7 8 Next page Final page [ View by Articles | List ]