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

我的归纳
1、裸设备定义：
一块没有分区的硬盘，称为原始设备(RAW DEVICE)
或者是一个分区，但是没有用EXT3,OCFS等文件系统格式化,称为原始分区(RAW PARTITION)
以上两者都是裸设备

2、裸设备的绑定
有文件系统的分区是采用mount的方式挂载到某一个挂载点的(目录)
而裸设备不能mount，只能绑定到/dev/raw/下的某一个设备名
比如/dev/raw/raw1

3、裸设备的绑定方法
有两种方法，这里介绍一种，另一种可以google搜索到

修改/etc/sysconfig/rawdevices，添加以下内容,
这里sdd1和sdd2是原始分区名或者原始设备(硬盘)名，
raw1和raw2是/dev目录下的原始设备名，编号从raw1到raw255，也就是最多可以绑定255个裸设备
/dev/raw/raw1 /dev/sdd1
/dev/raw/raw2 /dev/sdd2

然后修改裸设备的属主和访问权限
chown oracle:dba /dev/raw/raw1
chown oracle:dba /dev/raw/raw2
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2

最后使得裸设备生效，并且在机器启动的时候就自动加载

执行 /etc/init.d/rawdevices restart 使裸设备生效

执行 /sbin/chkconfig rawdevices on 保证机器启动的时候裸设备能够加载，这一步很重要

4、裸设备的读写
不能用cp等命令操作，写入内容用dd命令，可以参阅相关资料

5、清空裸设备，相当于格式化啦bs是快的大小，block size
count是快的数量，这两者相乘大于裸设备的容量即可
dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=12800
dd if=/dev/zero of=/dev/raw/raw2 bs=8192 count=12800

-------

另外

rhel4使用udev来管理设备
手动修改/dev/raw/raw1 不能永久生效
要想使得权限持久生效
需要修改文件/etc/udev/permissions.d/50-udev.permissions 的第113行
raw/*:root:disk:0660
改成
raw/*:oracle:dba:0660
重启机器

如果/dev/下没有 /raw/ 目录，可以自己手工建立

http://defoliation.iblog.cn/post/3528/78361
http://www.*****.org/viewthread.php?tid=26708
这里的*号是(C和N和O和U和G)

By Alexander Barkov and Peter Gulutzan

MySQL version 5.1.5 has functions for searching and changing XML documents. This article has examples.

Let's make a database and put two XML documents in it.

CREATE TABLE x (doc VARCHAR(150));

INSERT INTO x VALUES
('
<book>
<title>A guide to the SQL standard</title>
<author>
<initial>CJ</initial>
<surname>Date</surname>
</author>
</book>
');

INSERT INTO x VALUES
('
<book>
<title>SQL:1999</title>
<author>
<initial>J</initial>
<surname>Melton</surname>
</author>
</book>
');
The doc columns have an internal hierarchical structure, with books containing titles and authors, and authors in turn containing initials and surnames. It's a popular way to format and store, and the "markup" -- words like "<book>" and </book>" -- makes it easy to see the hierarchy if you're careful about indentation.

ExtractValue()
Syntax
EXTRACTVALUE (XML_document, XPath_string);
1st Parameter
XML_document string formatted as in the example
2nd Parameter
XPath_string (XPath is a "sub-language")
Action
returns string containing a value from the document
Example #E1
mysql> SELECT EXTRACTVALUE(doc,'/book/author/initial') FROM x;
+------------------------------------------+
| EXTRACTVALUE(doc,'/book/author/initial') |
+------------------------------------------+
| CJ |
| J |
+------------------------------------------+
2 rows in set (0.01 sec)
What happened here? Books contain authors which contain initials. With EXTRACTVALUE() we navigated down through the hierarchy to get the values at the final node points: 'CJ' and 'J'. A basic extraction is just a matter of specifying the hierarchy in the XPath_string argument.

Example #E2
mysql> SELECT EXTRACTVALUE(doc,'/*/*/initial') FROM x;
+----------------------------------+
| EXTRACTVALUE(doc,'/*/*/initial') |
+----------------------------------+
| CJ |
| J |
+----------------------------------+
2 rows in set (0.01 sec)
You don't have to list the whole hierarchy. When part of a path is a wildcard, that means "any name will do".

Example #E3
mysql> SELECT extractValue(doc,'/book/child::*') FROM x;
+---------------------------------------------+
| extractValue(doc,'/book/child::*') |
+---------------------------------------------+
| A guide to the SQL standard |
| SQL:1999 |
+---------------------------------------------+
2 rows in set (0.00 sec)
With /book/child:: we find what's immediately below book, namely the title data. We could use a variety of operators here:
child ... what's immediately below
descendant ... what's below at all levels
parent ... what's immediately above
ancestor ... what's above at all levels
following-sibling ... what's next at same level
preceding-sibling ... what's before at same level
self ... not before, not after, same level

Example #E4
mysql> select
extractValue(doc,'/book/author/surname[self:text()="Date"]') from x;
+--------------------------------------------------------------+
| extractValue(doc,'/book/author/surname[self:text()="Date"]') |
+--------------------------------------------------------------+
| Date |
| |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
And here's one way to add a predicate (a conditional expression). By saying "in the text of self, that is, in the text of surname because the predicate immediately comes after surname, look for value = Date", we include book/author/surname=Date and we exclude book/author/surname=Melton. The Melton row is blank. Naturally = isn't the only operator we could use here; we could have self:text()>="Date", self:text()="Date" OR self:text()="Melton", and so on.

What you've seen is: an XPath expression can contain nodes separated by slashes (vaguely like a Unix path expression), and you can pick values from one or more nodes. Wildcards, navigation aids, and predicates are supported. Although the examples all used extractValue() in the SELECT list, it can be used in any statement wherever an expression is allowed. A good tip is to combine XML columns with fulltext indexing.

UpdateXML()
Now here's a new function for updating the structure.

Syntax
UPDATEXML (XML_document, XPath_string, new_value);
1st Parameter
XML_document string formatted as in the example
2nd Parameter
XPath_string (XPath is a "sub-language")
3rd Parameter
new_value to replace whatever is found
Action
changes string containing a value from the document
Example #U1
mysql> select UpdateXML(doc,'/book/author/initial','!!') from x;
+----------------------------------------------------------+
| UpdateXML(doc,'/book/author/initial','!!') |
+----------------------------------------------------------+
|
<book>
<title>A guide to the SQL standard</title>
<author>
!!
<surname>Date</surname>
</author>
</book> |
|
<book>
<title>SQL:1999</title>
<author>
!!
<surname>Melton</surname>
</author>
</book> |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
UpdateXML's first two arguments are the same as for ExtractValue because the first thing we want to do is navigate to the node. The third argument is a replacement string. So we change book/author/initial to !!. The return value is the complete new document. To replace the document permanently, you could say UPDATE x SET doc = UpdateXML(doc,'/book/author/initial','!!');

But this is probably a mistake! We didn't just change the text to !!. We changed <initial>CJ></initial> to !! So we changed the document structure. Normally, we only want to change the contents. For that, we should say: select UpdateXML(doc,'/book/author/initial','<initial>!!</initial>') from x;

Example #U2
mysql> select
extractvalue(
UpdateXML(doc,'/book/author/initial','<initial>!!</initial>'),'/book/author/
initial') from x;
+---------------------------------------------------------------------------
--------------------------+
|
extractvalue(
UpdateXML(doc,'/book/author/initial','<initial>!!</initial>'),'/book/author/
initial') |
+---------------------------------------------------------------------------
--------------------------+
| !!
|
| !!
|
+---------------------------------------------------------------------------
--------------------------+
2 rows in set (0.01 sec)

[PHP]

1、concat()函数
1.1 MySQL的concat函数可以连接一个或者多个字符串,如
mysql> select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)

mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

而Oracle的concat函数只能连接两个字符串
SQL> select concat('11','22') from dual;

1.2 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL
SQL> select concat('11',NULL) from dual;

CONCAT
--
11

2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接
如连接后以逗号分隔
mysql> select concat_ws(',','11','22','33');
+-------------------------------+
| concat_ws(',','11','22','33') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set (0.00 sec)

和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
mysql> select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)

3、group_concat()可用来行转列, Oracle没有这样的函数
完整的语法如下
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
如下例子
mysql> select * from aa;
+------+------+
| id | name |
+------+------+
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+------+------+
6 rows in set (0.00 sec)

3.1 以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id | group_concat(name) |
+------+--------------------+
| 1 | 10,20,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+--------------------+
3 rows in set (0.00 sec)

3.2 以id分组，把name字段的值打印在一行，分号分隔
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id | group_concat(name separator ';') |
+------+----------------------------------+
| 1 | 10;20;20 |
| 2 | 20 |
| 3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

3.3 以id分组，把去冗余的name字段的值打印在一行，逗号分隔
mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id | group_concat(distinct name) |
+------+-----------------------------+
| 1 | 10,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

3.4 以id分组，把name字段的值打印在一行，逗号分隔,以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id | group_concat(name order by name desc) |
+------+---------------------------------------+
| 1 | 20,20,10 |
| 2 | 20 |
| 3 | 500,200 |
+------+---------------------------------------+
3 rows in set (0.00 sec)

4、repeat()函数，用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数
mysql> select repeat('ab',2);
+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+
1 row in set (0.00 sec)

又如
mysql> select repeat('a',2);
+---------------+
| repeat('a',2) |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)

..

[/PHP]

open(F1,">test.dat");
open(F,"test.txt");
while(<F>)
{
/^(S{1})/i;
$hash_segment{$1}++;
print F1;
}
close(F);
close(F1);

open(F,"test.dat");
while(<F>)
{
/^(S{1})/i;
if ($hash_segment{$1}<=2)
{
if (!exists($hash_new{$1}))
{
$filename='valid'.$1.'.dat';
open (FO,">$filename");
}
$hash_new{$1}='';
print FO;
}
else
{
$hash_rest{$1}='';
}
}
close(F);

undef %hash_new;
undef %hash_segment;

CREATE UNDO TABLESPACE MY_UNDO
DATAFILE '/test/db/undo_01.dbf' SIZE 2048M,
'/test/db/undo_01.dbf' SIZE 2048M;

ALTER SYSTEM SET UNDO_TABLESPACE=MY_UNDO SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
ALTER TABLESPACE UNDOTBS1 OFFLINE;

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

CREATE TEMPORARY TABLESPACE MY_TEMP
TEMPFILE '/oradata/temp/tmp_01.dbf' SIZE 1024M
REUSE;

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

CREATE TABLESPACE MY_DATA
DATAFILE '/oradata/dat_01.dbf' SIZE 1024M,
'/oradata/dat_02.dbf' SIZE 1024M
NOLOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

一、Window
1、net stop mysql 停止mysql服务
2、执行mysqld-nt --skip-grant-tables
这样启动，不需要密码就可以进入MySQL了
3、此时打开一个新的命令窗口，执行mysql，进入MySQL
4、修改root密码
use mysql
update user set password=password('NEW PASSWORD') where user='root';
flush privileges;
5、在任务管理器里kill掉mysqld-nt进程
6、net start mysql 重启MySQL即可

二、linux
1、killall -TERM mysqld 停止MySQL服务
2、bin/safe_mysqld --skip-grant-tables &
这样启动，不需要密码就可以进入MySQL了
3、执行mysql进入mysql
4、use mysql
update user set password=password('NEW PASSWORD') where user='root';
flush privileges;
5、killall -TERM mysqld
6、bin/safe_mysqld 重启MySQL

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

public class zhu
{
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:gene","test","test");
OraclePreparedStatement cstmt = (OraclePreparedStatement)conn.prepareStatement("begin p2(:x); end;" );
int a[][]={{1,2},{3,4}};
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("ARR",conn);
ARRAY array_to_pass = new ARRAY(descriptor,conn,a);
cstmt.setARRAY(1, array_to_pass);
cstmt.execute();
}
}

create or replace type mtype is record
(id number,name number);
/

create or replace type acc_type as object
(id number,name number)
/

create or replace type arr as table of acc_type
/

create or replace type arr as table of number;

--´´½¨´æ´¢¹ý³Ì
create or replace procedure p(myArray arr)
as
begin
for i in myArray.first..myArray.last
loop
dbms_output.put_line(to_char(i));
end loop;
end;

create or replace procedure p2(myArray arr)
as
begin
forall i in myArray.first..myArray.last
insert into aa values(myArray(i,i));
COMMIT;
end;

--²âÊÔ
declare
myArray arr:=arr('1,2','3,4');
begin
p(myArray);
end;

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:712625135727

15、java读取Excel表
//Function Read data from Excel
//Author ATGC
//Date of compilation Oct 29,2004

import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
public class xls2table
{
public static String fileToBeRead="e://test.xls";
public static void main(String argv[])
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
HSSFSheet sheet = workbook.getSheet("Sheet1");
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.getRow(r);
if (row != null)
{
int cells = row.getPhysicalNumberOfCells();
String value = "";
System.out.println(cells);
for (short c = 0; c < cells; c++)
{
HSSFCell cell = row.getCell(c);
if (cell != null)
{
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
//strCell = String.valueOf(aCell.getNumericCellValue());
//returnstr+=strCell+" ";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value += (long)cell.getNumericCellValue()+"t";
break;
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue()+"t";
break;
case HSSFCell.CELL_TYPE_BLANK://blank
//strCell = aCell.getStringCellValue();
//returnstr+=strCell+" ";
break;

default:
value +="t";
}
}
}
//下面可以将查找到的行内容用SQL语句INSERT到oracle
System.out.println(value);
//
}
}
}catch(Exception e)
{System.out.println(e);}
}
}

#!/usr/bin/perl
#Author ATGC

use strict;
use Win32::OLE;
my $excel_file = 'c:/out.xls';
my ($row,@field,$c_times,$residual,$cols,$cell_end);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->add;
my $Sheet = $Book->Worksheets(1);
my @array_cols=("Z","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
unlink $excel_file if (-e $excel_file);
@field=("你好","他好","大家好","就我不好");

$cols=scalar(@field);
$c_times=sprintf "%.0f",$cols/26+0.5;
$residual=$cols%26;
$cell_end = ($cols<27) ? $array_cols[$cols] : $array_cols[$c_times-1].$array_cols[$residual];
$row++;
$Sheet->Range("A$row:$cell_end$row")->{Value} = [@field];
$Book->SaveAs($excel_file);
undef($Sheet);
undef($Book);
undef($Excel);

create or replace directory photo_dir as 'C:';
create table photos (photo BLOB);

declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('PHOTO_DIR','test.jpg');
begin
insert into photos values (empty_blob())
returning photo 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 过程已成功完成

验证是否存入了图片
select dbms_lob.getlength(photo) from photos;
DBMS_LOB.GETLENGTH(PHOTO)
-------------------------
608474

MySQL的批插入 BULK INSERT和load data的速度差不多，并且可靠。

语法如下

假设有表test (ID NUMBER,NAME VARCHAR(10))

insert into test values(1,'aa'),(2,'bb'),.....(100000,'bb');

也就是每个记录间用逗号隔开，最后用分号结束，插入10万条记录只需不到10秒

create or replace type acc_type as object
(acc varchar2(50))
/

create or replace type acc_table as table of acc_type
/

create or replace function str2table (acc_str in varchar2) return acc_table pipelined
is
v_str varchar2(30000) := acc_str;
v_acc varchar2(30);
v_acc_end pls_integer;
begin
loop
v_acc_end := instrb(v_str,',');
exit when (v_acc_end=0 or v_str is null);
v_acc := substrb(v_str,1,v_acc_end-1);
v_str := ltrim(v_str,v_acc);
v_str := ltrim(v_str,',');
pipe row(acc_type(rtrim(v_acc,';')));
end loop;
return;
end;
/

SQL> select * from table(str2table('TEST1,TEST1,12,123,55,99MOON,'));

ACC
--------------------------------------------------
TEST1
TEST1
12
123
55
99MOON

6 rows selected.

SQL>

以下内容是我在别的贴子上回答的,供参考
http://www.itpub.net/showthread.php...light=TRANSLATE
http://www.itpub.net/showthread.php...10&pagenumber=1

select translate('采sg大gd型矩gd阵356式服][12','0123456789'||'采sg大gd型矩gd阵356式服][12','0123456789') from dual;

TRANS
-----
35612


解释一下
translate(string,from_str,to_str)
执行时,translate依次检查string中的每个字符
然后查找这个字符是否在from_str中存在
如果不存在，那么这个string中的字符被保留，也就是被返回，
如果存在，那么，translate会记下这个字符在from_str中的位置，
然后用to_str的同样位置的字符代替string中的这个字符

from_str可以比to_str长，也就是from_str的字符数目可以比to_str多
在from_str中多出来的字符称为"额外字符"，也就是from_str中的位置在
to_str中找不到的，比如
from_str 'abc123'
to_str 'abc'
这里from_str中的1的位置是4，但是to_str的总长度是3，没有4，
所以在from_STR中的123都是额外字符
如果from_str中的额外字符在string中出现，那么string中这些字符将在返回时被删除

举例如下
SQL> select translate('ab12','ab2','00') from dual;

TRA
---
001
这里a和b都被替换成了00，因为a在from_str中的位置是1，那么被to_str中的位置1的字符替换
b也是一样的处理，被to_str中的位置2的字符替换，依然是0
string中的1在from_str没有找到，所以被保留下来了
string中的2在from_str中找到了，但是是from_str的额外字符，因为2在from_str中的位置是3
而to_str中是没有3这个位置的，所以string中的2虽然在from_str中找到了,且处于3的位置,
但是TO_STR的长度是2,没有3这个位置,所以2在返回时被删除
得到的是001

另外to_str不能是NULL或者''，否则会返回空值
translate也不能用于CLOB

再说得简单一点，也就是
1、如果string中的字符如果在from_string中没有，那么返回时被保留
2、如果string中的字符是from_string中的"额外字符"，那么返回时被删除
3、如果string中的字符在from_string中找到，且在to_string中有相应位置的字符，
那么返回时用to_string中的字符替换string中的字符