E:photo>dir
Volume in drive E is New Volume
Volume Serial Number is F816-C39F

Directory of E:photo

2007-10-31 11:00 <DIR> .
2007-10-31 11:00 <DIR> ..
2007-08-13 16:19 1,212,681 flower.jpg
1 File(s) 1,212,681 bytes
2 Dir(s) 40,406,614,016 bytes free

----------------------------------------------------
SQL> conn a/a
Connected.

grant create any directory to a;
grant create any library to a;
create or replace directory photo_dir as 'e:photo';
create table photos (blob_column blob);

declare
a_blob blob;
a_bfile bfile := bfilename('PHOTO_DIR','flower.jpg');
begin
insert into photos values (empty_blob()) returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob,a_bfile,dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(blob_column) from photos;

DBMS_LOB.GETLENGTH(BLOB_COLUMN)
-------------------------------
1212681

create table table_a(id number(5),name varchar2(10),score number(2));
insert into table_a values(1,'aa',10);
insert into table_a values(2,'bb',10);
insert into table_a values(3,'cc',10);
insert into table_a values(4,'dd',10);

create table table_b(id number(5) unique,name varchar2(10),score number(2),batch_id number(10));

insert into table_b values(1,'xx',90,1);
insert into table_b values(2,'yy',80,1);
insert into table_b values(3,'zz',60,2);
insert into table_b values(4,'mm',70,2);

commit;

create bitmap index idx_eyes1000 on table_b(batch_id) nologging tablespace users compute statistics;
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'table_b',cascade=>true);

alter table table_a add constraint pk_a primary key (id) using index tablespace users;
alter table table_b add constraint pk_b primary key (id) using index tablespace users;

declare
type t_type is table of number(10);
v_batch_id t_type;
begin
execute immediate 'select distinct t.batch_id from table_b t' bulk collect into v_batch_id;
if v_batch_id.count>0 then
for i in v_batch_id.first..v_batch_id.last loop
update (select /*+ ordered */
x.name source_name,x.score source_score,
y.name target_name,y.score target_score
from table_b x,table_a y
where x.batch_id=v_batch_id(i)
and x.id = y.id)
set target_name = source_name,
target_score = source_score;
commit;
end loop;
end if;
end;
/

写一函数，准确地判断字段是否含有汉字或者提取汉字等
从表里提取汉字, 需要考虑字符集, 不同的字符集汉字的编码有所不同
这里以GB2312为例, 写一函数准确地从表里提取简体汉字.

假设数据库字符集编码是GB2312, 环境变量(注册表或其它)的字符集也是GB2312编码
并且保存到表里的汉字也都是GB2312编码的

那么也就是汉字是双字节的，且简体汉字的编码范围是
B0A1 - F7FE
换算成10进制就是
B0 A1 F7 FE
176,161 - 247,254

我们先看一下asciistr函数的定义
Non-ASCII characters are converted to the form xxxx, where xxxx represents a UTF-16 code unit.
但是这并不表示以 "" 开始的字符就是汉字了

举例如下
SQL> select * from test;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123
★ABC

这里第5条记录有一个实心的五角星
然后用asciistr函数转换一下试试
SQL> select name,asciistr(name) from test;

NAME ASCIISTR(NAME)
-------------------- ----------------------
,啊OO10哈 ,554AOO1054C8
你好aa 4F60597Daa
大家好aa/ 59275BB6597Daa/
☆大海123 260659276D77123
★ABC 2605ABC

我们看到最后一条记录的实心五角星也是 ""开头的
此时我们就不能用asciistr(字段)是否存在 "" 来判断是否含有汉字了.

我的函数如下，基本思路是判断字符的编码是否在GB2312规定的汉字编码范围之内

create or replace function get_chinese(p_name in varchar2) return varchar2
as
v_code varchar2(30000) := '';
v_chinese varchar2(4000) := '';
v_comma pls_integer;
v_code_q pls_integer;
v_code_w pls_integer;
begin
if p_name is not null then
select replace(substrb(dump(p_name,1010),instrb(dump(p_name,1010),'ZHS16GBK:')),'ZHS16GBK: ','') into v_code from dual where rownum=1;
for i in 1..length(p_name) loop
if lengthb(substr(p_name,i,1))=2 then
v_comma := instrb(v_code,',');
v_code_q := to_number(substrb(v_code,1,v_comma-1));
v_code_w := to_number(substrb(v_code,v_comma+1,abs(instrb(v_code,',',1,2)-v_comma-1)));
if v_code_q>=176 and v_code_q<=247 and v_code_w>=161 and v_code_w<=254 then
v_chinese := v_chinese||substr(p_name,i,1);
end if;
v_code := ltrim(v_code,'1234567890');
v_code := ltrim(v_code,',');
end if;
v_code := ltrim(v_code,'1234567890');
v_code := ltrim(v_code,',');
end loop;
return v_chinese;
else
return '';
end if;
end;
/

好，现在来执行一些语句
SQL> select * from test;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123
★ABC

5 rows selected.

1. 列出有汉字的记录
SQL> select name from test where length(get_chinese(name))>0;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123

4 rows selected.

2. 列出有汉字的记录，并且只列出汉字

SQL> select get_chinese(name) from test where length(get_chinese(name))>0;

GET_CHINESE(NAME)
---------------------------------------------------------------------------
啊哈
你好
大家好
大海

4 rows selected.

需要说明的是GB2312共有6763个汉字，即72*94-5=6763
我这里是计算72*94，没有减去那5个，那五个是空的。等查到了再减去
============

改写这个函数，可以提取非汉字或者汉字
该函数有两个参数，第一个表示要提取的字符串，第二个是1，表示提取汉字，是非1，表示提取非汉字

create or replace function get_chinese
(
p_name in varchar2,
p_chinese in varchar2
) return varchar2
as
v_code varchar2(30000) := '';
v_chinese varchar2(4000) := '';
v_non_chinese varchar2(4000) := '';
v_comma pls_integer;
v_code_q pls_integer;
v_code_w pls_integer;
begin
if p_name is not null then
select replace(substrb(dump(p_name,1010),instrb(dump(p_name,1010),'ZHS16GBK:')),'ZHS16GBK: ','') into v_code from dual where rownum=1;
for i in 1..length(p_name) loop
if lengthb(substr(p_name,i,1))=2 then
v_comma := instrb(v_code,',');
v_code_q := to_number(substrb(v_code,1,v_comma-1));
v_code_w := to_number(substrb(v_code,v_comma+1,abs(instrb(v_code,',',1,2)-v_comma-1)));
if v_code_q>=176 and v_code_q<=247 and v_code_w>=161 and v_code_w<=254 then
v_chinese := v_chinese||substr(p_name,i,1);
else
v_non_chinese := v_non_chinese||substr(p_name,i,1);
end if;
v_code := ltrim(v_code,'1234567890');
v_code := ltrim(v_code,',');
else
v_non_chinese := v_non_chinese||substr(p_name,i,1);
end if;
v_code := ltrim(v_code,'1234567890');
v_code := ltrim(v_code,',');
end loop;
if p_chinese = '1' then
return v_chinese;
else
return v_non_chinese;
end if;
else
return '';
end if;
end;
/

.--------------------------------------------------------------------------------
SQL> select * from a;

NAME
--------------------
我们啊、
他（艾呀）是★们
他的啊@

SQL> select get_chinese(name,1) from a;

GET_CHINESE(NAME,1)
-----------------------------------------
我们啊
他艾呀是们
他的啊

SQL> select get_chinese(name,0) from a;

GET_CHINESE(NAME,0)
-----------------------------------------
、
（）★
@

SQL>

说明一点，带out参数的字定义函数不能用于 SQL语句，只能在PL/SQL程序里
如下例子，返回两个3个数字

set serveroutput on

declare
v_1 number;
v_2 number;
v_3 number;
function my_f(p_1 in number,p_2 out number,p_3 out number) return number
as
v_id number(10):=p_1;
begin
p_2 := 200;
p_3 := 300;
return v_id;
end;
begin
v_1:=my_f(100,v_2,v_3);
dbms_output.put_line('v_1 is '||v_1);
dbms_output.put_line('p_2 is '||v_2);
dbms_output.put_line('p_3 is '||v_3);
end;
/

v_1 is 100
p_2 is 200
p_3 is 300

PL/SQL procedure successfully completed.

或者
create or replace function my_f(p_1 in number,p_2 out number,p_3 out number) return number
as
v_id number(10):=p_1;
begin
p_2 := 200;
p_3 := 300;
return v_id;
end;
/

declare
v_1 number;
v_2 number;
v_3 number;
begin
v_1:=my_f(100,v_2,v_3);
dbms_output.put_line('v_1 is '||v_1);
dbms_output.put_line('p_2 is '||v_2);
dbms_output.put_line('p_3 is '||v_3);
end;
/

v_1 is 100
p_2 is 200
p_3 is 300

PL/SQL procedure successfully completed.

原始需求，有2表如下
SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS
06 DHL

6 rows selected.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

要求输出如下格式的报表
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 1 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1
即以senddt和toolid分组后的count数
每行显示一个senddt，但是列数是不确定的，根据表mas的toolid来定的

基本思路如下，如下的SQL 是动态构造的，其中的列数不确定，根据mas的toolid来定
select senddt,
max(toolid1) toolid1,max(sum1) sum1,
max(toolid2) toolid2,max(sum2) sum2,
max(toolid3) toolid3,max(sum3) sum3,
max(toolid4) toolid4,max(sum4) sum4,
max(toolid5) toolid5,max(sum5) sum5
from (select b.senddt,
'01' toolid1,
case when a.toolid=b.toolid and b.toolid='01' then b.sum_c else 0 end sum1,
'02' toolid2,
case when a.toolid=b.toolid and b.toolid='02' then b.sum_c else 0 end sum2,
'03' toolid3,
case when a.toolid=b.toolid and b.toolid='03' then b.sum_c else 0 end sum3,
'04' toolid4,
case when a.toolid=b.toolid and b.toolid='04' then b.sum_c else 0 end sum4,
'05' toolid5,
case when a.toolid=b.toolid and b.toolid='05' then b.sum_c else 0 end sum5
from mas a,
(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b)
group by senddt
order by senddt

测试用的存储过程
create or replace procedure test
as
v_column varchar2(5) := '';
v_sql_0 varchar2(10000) := '';
v_sql_1 varchar2(10000) := '';
p_fields varchar2(10000) := '';
begin
for t in (select toolid from mas order by toolid) loop
v_column := to_char(to_number(t.toolid));
v_sql_0 := v_sql_0||',max(toolid'||v_column||') toolid'||v_column||',max(sum'||v_column||') sum'||v_column;
v_sql_1 := v_sql_1||','''||t.toolid||''' toolid'||v_column||', case when a.toolid=b.toolid and b.toolid='''||t.toolid||''' then b.sum_c else 0 end sum'||v_column;
p_fields := p_fields||',toolid'||v_column||',sum'||v_column;
end loop;
v_sql_1 := 'select senddt'||v_sql_0||' from (select b.senddt'||v_sql_1||' from mas a,(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b) group by senddt order by senddt';
p_fields := ltrim(p_fields,',');
--open p_rs for v_sql_1;
dbms_output.put_line(v_sql_1);
end;
/

--------------
1. 建立package
--------------

create or replace package pkg_test
as
type cursor_fount100 is ref cursor;
procedure rs_fount100 (p_fields in out varchar2,p_rs in out cursor_fount100);
end;
/

create or replace package body pkg_test
as
procedure rs_fount100 (p_fields in out varchar2,p_rs in out cursor_fount100)
is
v_column varchar2(5) := '';
v_sql_0 varchar2(10000) := '';
v_sql_1 varchar2(10000) := '';
begin
for t in (select toolid from mas order by toolid) loop
v_column := to_char(to_number(t.toolid));
v_sql_0 := v_sql_0||',max(toolid'||v_column||') toolid'||v_column||',max(sum'||v_column||') sum'||v_column;
v_sql_1 := v_sql_1||','''||t.toolid||''' toolid'||v_column||', case when a.toolid=b.toolid and b.toolid='''||t.toolid||''' then b.sum_c else 0 end sum'||v_column;
p_fields := p_fields||',toolid'||v_column||',sum'||v_column;
end loop;
v_sql_1 := 'select senddt'||v_sql_0||' from (select b.senddt'||v_sql_1||' from mas a,(select senddt,toolid,count(*) sum_c from putdt group by senddt,toolid) b) group by senddt order by senddt';
p_fields := ltrim(p_fields,',');
open p_rs for v_sql_1;
end;
end;
/

----------------------------
2. JAVA程序 test.java 演示用
----------------------------

import java.io.*;
import java.text.*;
import java.lang.String;
import java.util.*;
import java.util.regex.*;
import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class test
{
public static void main(String[] args) throws Exception
{
String record_str="";
String field_list="";
String[] fields=null;
Pattern p = Pattern.compile(",");
Matcher m1;
OracleCallableStatement cstmt = null;
ResultSet rs = null;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:实例名","数据库用户","密码");
cstmt = (OracleCallableStatement)conn.prepareCall("begin pkg_test.rs_fount100(?,?); end;");
cstmt.registerOutParameter(1,OracleTypes.VARCHAR);
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
cstmt.execute();
field_list = cstmt.getString(1);
rs = (ResultSet)cstmt.getObject(2);
fields=p.split(field_list);
for(int i=0; i<fields.length; i++)
{
record_str=record_str+fields[i]+" ";
}
System.out.println("senddt "+record_str);
while (rs.next())
{
record_str="";
for(int f=1; f<=fields.length+1; f++)
{
record_str=record_str+rs.getString(f)+" ";
}
System.out.println(record_str);
}
rs.close();
cstmt.close();
conn.close();
}
}

---------------
3. 编译java程序
---------------
javac test.java

---------------
4. 演示1
---------------

SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566

6 rows selected.

E:>java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 1 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1

---------------
5. 演示2
---------------

SQL> insert into putdt values('20070101','01','james','12','8800');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

E:>java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5
20070101 01 2 02 0 03 0 04 1 05 1
20070201 01 0 02 0 03 0 04 1 05 0
20070304 01 0 02 0 03 0 04 1 05 1

---------------
6. 演示3
---------------
SQL> insert into mas values('06','DHL');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mas;

TO TOOLNAME
-- ----------
01 包裹
02 信函
03 挂号信
04 中国速递
05 EMS
06 DHL

6 rows selected.

SQL> select * from putdt;

SENDDT TO GNAME ST CITY
-------- -- ---------- -- ----
20070101 01 john 12 2566
20070101 04 david 16 3098
20070101 05 apple 18 2098
20070201 04 apple 16 3078
20070304 05 poly 13 1001
20070304 04 john 12 2566
20070101 01 james 12 8800

7 rows selected.

E:java test
senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum5 toolid6 sum6
20070101 01 2 02 0 03 0 04 1 05 1 06 0
20070201 01 0 02 0 03 0 04 1 05 0 06 0
20070304 01 0 02 0 03 0 04 1 05 1 06 0

import java.io.*;
import java.text.*;
import java.lang.String;
import java.util.*;
import java.lang.*;
import java.io.FileOutputStream;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class zhu
{
public static void main(String[] args) throws Exception
{
String sql = "select id from table where rownum<=20";
String fields = "";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@连接名","user","pass");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
fields = (rs.getString("id")!=null)? rs.getString("id") : "";
System.out.println(fields);
}
rs.close();
stmt.close();
conn.close();
}
}

and we said...

There are at least 2 ways to perform this sort of co-related update correctly.  I'll show 
my preferred method (update a join) and then another method that'll work if you cannot 
put a unique constraint on LOOKUP(keyname) (which is needed for the join update).


Here are the test tables:

scott@ORA734.WORLD> create table name
  2  ( keyname int,
  3    columnName varchar2(25)
  4  )
  5  /
Table created.

scott@ORA734.WORLD> create table lookup
  2  ( keyname int PRIMARY KEY,
  3    value varchar2(25),
  4    otherColumn int
  5  )
  6  /
Table created.

scott@ORA734.WORLD> insert into name values ( 100, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into name values ( 200, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into lookup values ( 100, 'New Data', 1 );
1 row created.

scott@ORA734.WORLD> commit;
Commit complete.


here is the "other_value" parameter you are using in the above update you 
attempted...

scott@ORA734.WORLD> variable other_value number
scott@ORA734.WORLD> exec :other_value := 1
PL/SQL procedure successfully completed.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data


Here we update a join.  We can only modify the columns in one of the tables and the 
other tables we are *NOT* modifying must be "key preserved" -- that is, we must be able 
to verify that at most one record will be returned when we join NAME to this other table. 
 In order to do that, keyname in LOOKUP must either be a primary key or have a unique 
constraint applied to it...

scott@ORA734.WORLD> update
  2    ( select columnName, value
  3        from name, lookup
  4       where name.keyname = lookup.keyname
  5         and lookup.otherColumn = :other_value )
  6     set columnName = value
  7  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data

See, the other data is untouched and only the rows we wanted are updated..

scott@ORA734.WORLD> rollback;
Rollback complete.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data


Now, this way will work with no constraints on anything -- you do not need the primary 
key/unique constraint on lookup (but you better be sure the subquery returns 0 or 1 
records!).

It is very much like your update, just has a where clause so that only rows that we find 
matches for are actually updated...

scott@ORA734.WORLD> update name
  2     set columnName = ( select value
  3                          from lookup
  4                         where lookup.keyname = name.keyname
  5                           and otherColumn = :other_value )
  6   where exists ( select value
  7                    from lookup
  8                   where lookup.keyname = name.keyname
  9                     and otherColumn = :other_value )
 10  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data
 

参考文档
http://download-west.oracle.com/doc...g.htm#NETAG0153


假设存在2 instance sales1 and sales2
数据库 sales

client 设置

sales.us.acme.com=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))



sales1 上 tnsnames.ora内容

listener_sales2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))

sales1 上spfile内容

REMOTE_LISTENER=listener_sales2
service_names=sales.us.acme.com
instance_name = sales1



这里使用到 tnsnames.ora 中的 listener_sales2 表示instance启动的时候pmon通过tnsnames中服务名连接注册到指定的listener


同理，在sales2上
tnsnames.ora

listener_sales1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)))


spfile

REMOTE_LISTENER=listener_sales1
service_names=sales.us.acme.com
instance_name = sales2







*****************************************************************************************************
几种client TAF 配置方式

Example: TAF with Connect-Time Failover and Client Load Balancing
Implement TAF with connect-time failover and client load balancing for multiple addresses.
In the following example, Oracle Net connects randomly to one of the protocol addresses
on sales1-server or sales2-server. If the instance fails after the connection,
the TAF application fails over to the other node's listener, reserving any SELECT statements in progress.

sales.us.acme.com=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales1-server)
(PORT=1521))
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales2-server)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

Example: TAF Retrying a Connection
TAF also provides the ability to automatically retry connecting if the first
connection attempt fails with the RETRIES and DELAY parameters.
In the following example, Oracle Net tries to reconnect to the listener on sales1-server.
If the failover connection fails, Oracle Net waits 15 seconds before trying to reconnect again.
Oracle Net attempts to reconnect up to 20 times.

sales.us.acme.com=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales1-server)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=20)
(DELAY=15))))

Example: TAF Pre-Establishing a Connection
A backup connection can be pre-established. The initial and backup connections must be explicitly specified.
In the following example, clients that use net service name sales1.us.acme.com to connect to the listener
on sales1-server are also preconnected to sales2-server. If sales1-server fails after the connection,
Oracle Net fails over to sales2-server, preserving any SELECT statements in progress. Likewise,
Oracle Net preconnects to sales1-server for those clients that use sales2.us.acme.
com to connect to the listener on sales2-server.

sales1.us.acme.com=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales1-server)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(INSTANCE_NAME=sales1)
(FAILOVER_MODE=
(BACKUP=sales2.us.acme.com)
(TYPE=select)
(METHOD=preconnect))))
sales2.us.acme.com=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales2-server)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(INSTANCE_NAME=sales2)
(FAILOVER_MODE=
(BACKUP=sales1.us.acme.com)
(TYPE=select)
(METHOD=preconnect))))





FAILOVER_MODE Parameters
The FAILOVER_MODE parameter must be included in the CONNECT_DATA section of a connect descriptor.
FAILOVER_MODE can contain the subparameters described in Table 15-6.

Table 15-6 Subparameters of the FAILOVER_MODE Parameter
FAILOVER_MODE Subparameter Description
BACKUP
Specify a different net service name for backup connections.
A backup should be specified when using preconnect to pre-establish connections.

TYPE
Specify the type of failover. Three types of Oracle Net failover functionality are available by
default to Oracle Call Interface (OCI) applications:

session: Set to failover the session. If a user's connection is lost,
a new session is automatically created for the user on the backup.
This type of failover does not attempt to recover selects.
select: Set to enable users with open cursors to continue fetching on them after failure.
However, this mode involves overhead on the client side in normal select operations.
none: This is the default. No failover functionality is used.
This can also be explicitly specified to prevent failover from happening.

METHOD
Determines how fast failover occurs from the primary node to the backup node:

basic: Set to establish connections at failover time.
This option requires almost no work on the backup server until failover time.
preconnect: Set to pre-established connections. This provides faster failover but requires that the
backup instance be able to support all connections from every supported instance.

RETRIES
Specify the number of times to attempt to connect after a failover.
If DELAY is specified, RETRIES defaults to five retry attempts.

Note: If a callback function is registered, then this subparameter is ignored.

DELAY
Specify the amount of time in seconds to wait between connect attempts.
If RETRIES is specified, DELAY defaults to one second.

Note: If a callback function is registered, then this subparameter is ignored.



--------------------------------------------------------------------------------
Note:
Oracle Net Manager does not provide support for TAF parameters. These parameters must be manually added.

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


TAF Implementation
Important:
Do not set the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora.
A statically configured global database name disables TAF.

MySQL版本5.0.13以上

create table aa (id int);

delimiter //

create procedure test(v_sql varchar(200))
begin
set @sqltext:=concat('insert into aa ',v_sql);
prepare stmt from @sqltext;
execute stmt;

DEALLOCATE PREPARE stmt;
end;
//

call test('values (1)')//

CC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.210.220.1_vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.210.220.2_vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cc)
)
)

翻遍了MYSQL手册都找不到说明哪个系统变量保存错误码
以下异常处理可以捕获异常,但是不知道是什么异常,但是可以在log里找到

当然,对于特定的SQL语句,也可以指定比如主键冲突,就rollback;
DECLARE exit HANDLER FOR SQLSTATE '23000'

delimiter //

CREATE PROCEDURE TEST()
BEGIN
DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
begin
rollback;
insert into bb values('error');
end;
START TRANSACTION;
INSERT INTO aa VALUES (1);
INSERT INTO aa VALUES (2);
COMMIT;
END;
//

CALL test()//

1. 先看一下这个目录
[oracle@ocean ~]$ pwd
/home/oracle
[oracle@ocean ~]$ ls -l
total 8
-rw-r--r-- 1 oracle orains 604 Dec 26 2006 bash

2. 连接到test用户
SQL> conn test/test
Connected.

3. 授于test用户文件读写和执行命令的权限
SQL>exec dbms_java.grant_permission('TEST','SYS:java.io.FilePermission','<<ALL FILES>>','read,write,execute,delete');
PL/SQL procedure successfully completed.

SQL>exec dbms_java.grant_permission('TEST','java.lang.RuntimePermission','*','writeFileDescriptor' );
PL/SQL procedure successfully completed.

4. 建立java存储过程,注意,这里的exp 路径和dmp文件的路径是数据库服务器的路径
create or replace and compile java source named "aa"
as
import java.io.*;
import java.lang.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;

public class aa
{
public static void invoke_exe() throws IOException
{
Process p=Runtime.getRuntime().exec("/oracle/product/9.2.4/bin/exp userid=test/test file=/home/oracle/test.dmp");
try
{
p.waitFor();
}catch(InterruptedException ie){System.out.println(ie);}
}
}
/

Java created.

Elapsed: 00:00:00.01

5. 建立调用java存储过程的存储过程call_aa
create or replace procedure call_aa
as language java
name 'aa.invoke_exe()';
/

6. 执行java存储过程
exec call_aa;

7. 检查一下dmp文件是否已经生成
[oracle@ocean ~]$ pwd
/home/oracle
[oracle@ocean ~]$ ls -l
total 28
-rw-r--r-- 1 oracle orains 604 Dec 26 2006 bash
-rw-r--r-- 1 oracle orains 16384 Jul 3 14:12 test.dmp

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

exec dbms_Java.Grant_Permission('TEST', 'java.io.FilePermission', '<<ALL FILE>>', 'read ,write, execute, delete');
exec dbms_Java.Grant_Permission('TEST', 'java.io.FilePermission', 'd:aa.bat', 'read ,write, execute, delete');
exec dbms_java.grant_permission('TEST','java.lang.RuntimePermission','*','writeFileDescriptor' );

SQL> desc aa
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(2)

SQL> select * from aa;

ID
--
1
2
3
4
5
6
7
8
9
10
11

11 rows selected.

---------------
建立PACKAGE
---------------
create or replace package pkg_test
as
type pagination is ref cursor;
procedure page_test
(
p_count in out pls_integer,
p_page in out pagination,
p_beg in pls_integer,
p_end in pls_integer,
p_id in varchar2
);
end;
/

create or replace package body pkg_test
as
procedure page_test
(
p_count in out pls_integer,
p_page in out pagination,
p_beg in pls_integer,
p_end in pls_integer,
p_id in varchar2
)
is
sql_count varchar2(1000) := '';
sql_page varchar2(30000) := '';
begin
sql_count := 'select count(*) from aa';
sql_page := 'select id from aa where rownum<=10';
execute immediate sql_count into p_count;
open p_page for sql_page;
end;
end;
/

---------
test.java
---------

import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

public class test
{
public static void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:数据库SID","用户名","密码");
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("begin pkg_test.page_test(?,?,?,?,?); end;");
cstmt.registerOutParameter(1,OracleTypes.VARCHAR); //返回记录数
cstmt.registerOutParameter(2,OracleTypes.CURSOR); //返回结果集
cstmt.setInt(3,1); //起始记录号
cstmt.setInt(4,10); //结束记录号
cstmt.setString(5,"10"); //WHERE 条件
cstmt.execute();
ResultSet rs = (ResultSet)cstmt.getObject(2);
while (rs.next())
{
System.out.println(rs.getString(1));
}
}
}

TEST.DAT内容如下
----------------
13436002514|CQ|D|Jun 28 2006 9:54:16:420AM||Jun 28 2006 9:54:16:420AM|0||0.00|
13436003898|CQ|D|May 17 2006 8:58:53:356PM||May 17 2006 8:58:53:356PM|0||0.00|
13436005288|CQ|D|Mar 20 2007 9:24:33:796AM||Mar 29 2007 1:51:51:153PM|0||0.00|
13436014844|CQ|U|May 2 2006 9:35:20:296AM||Sep 1 2006 9:12:51:830PM|0||0.00|

TEST.CTL内容如下
----------------
由于DATE型没有毫秒表示，所以你的文本里得毫秒被去掉了

LOAD DATA
INFILE 'TEST.DAT'
TRUNCATE
INTO TABLE USERS
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
MOBILENO,
BANKID,
AVLFLAG,
OPENTIME "DECODE(NULL,:OPENTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:OPENTIME,1,instrb(:OPENTIME,':',-1)-1)||' '||SUBSTRB(:OPENTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
STOPTIME "DECODE(NULL,:STOPTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:STOPTIME,1,instrb(:STOPTIME,':',-1)-1)||' '||SUBSTRB(:STOPTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
LASTTIME "DECODE(NULL,:LASTTIME,TO_DATE('','MM-DD-YYYY'),TO_DATE(SUBSTRB(:LASTTIME,1,instrb(:LASTTIME,':',-1)-1)||' '||SUBSTRB(:LASTTIME,-2),'Mon dd yyyy hh:mi:ss PM'))",
FLAG,
PASSWORD,
TRANSLIMIT,
RIGHTS
)

create table USERS
(
MOBILENO CHAR(11) not null,
BANKID VARCHAR2(16) not null,
AVLFLAG CHAR(1) not null,
OPENTIME DATE not null,
STOPTIME DATE,
LASTTIME DATE not null,
FLAG FLOAT,
PASSWORD VARCHAR2(8),
TRANSLIMIT NUMBER(10,2),
RIGHTS VARCHAR2(32)
);

..--------------------------------------------------------------------------------

SQL>select * from users;

no rows selected

C:>sqlldr userid=test/test@gene control=test.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Thu May 31 14:49:58 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 4

SQL> SELECT MOBILENO,
TO_CHAR(OPENTIME,'Mon dd yyyy hh12:mi:ss AM') OPENTIME,
TO_CHAR(STOPTIME,'Mon dd yyyy hh12:mi:ss AM') STOPTIME,
TO_CHAR(LASTTIME,'Mon dd yyyy hh12:mi:ss AM') LASTTIME
FROM USERS;

MOBILENO OPENTIME STOPTIME LASTTIME
----------- ----------------------- ----------------------- -----------------------
13436002514 Jun 28 2006 09:54:16 AM Jun 28 2006 09:54:16 AM
13436003898 May 17 2006 08:58:53 PM May 17 2006 08:58:53 PM
13436005288 Mar 20 2007 09:24:33 AM Mar 29 2007 01:51:51 PM
13436014844 May 02 2006 09:35:20 AM Sep 01 2006 09:12:51 PM