dblink 定时器 触发器 存储过程中 CLOB字段异常的解决方案

create PUBLIC database link bjlink

CONNECT TO username IDENTIFIED BY password

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = xxx)

)

)'

update student

set student.name = student1.name,

student.id = student1.id

(select student1.name,student1.id

from student,student1

where student.int_id = student1.int_id);

update student set (name,id )=

(select name ,id from (select student.rowid rd,student1.name,student1.id from student1,student where student1.int_id =student.int_id) tmp

where student.rowid=tmp.rd);

commit;

SQL：

update inventory set prod_id=

(

select prod_id from inventory_temp where inventory_temp.lot_no=inventory.lot_no

)

where exists

(

select lot_no from inventory_temp where inventory_temp.lot_no=inventory.lot_no

)

体会：

用一个表去更新另一个表

上边写法可以简写为：

update a

set a.name=(select b.name from a.id = b.id)

where exsits (select 1 from a.id = b.id);

如果更新的是多个字段：

UPDATE employees a

SET department_id =

(SELECT department_id

FROM departments

WHERE location_id = '2100'),

(salary, commission_pct) =

(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)

FROM employees b

WHERE a.department_id = b.department_id)

WHERE department_id IN

(SELECT department_id

FROM departments

WHERE location_id = 2900

OR location_id = 2700);

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

另外一个: http://blog.csdn.net/Bobwu/archive/2009/01/13/3768636.aspx

1.

declare

cursor t1 is select * from tablename;

begin

for rec in t1 loop

update tablename t set t.detail=rec.jieshao where t.objectid=rec.objid;

end loop;

end;

2.

update student set (name,id )=

(select name ,id from (select student.rowid rd,student1.name,student1.id from student1,student where student1.int_id =student.int_id) tmp

where student.rowid=tmp.rd);

commit;

3.

update test_a a set (a.name,a.age)=

(select b.name,b.age from test_b b where a.id = b.id) where exists

(select * from test_b c where c.id=a.id)

4.

UPDATE t_A SET Djrq=

(

SELECT djrq FROM t_B WHERE t_A.ID = T_B.ID

WHERE ROWNUM = 1

)

WHERE t_A.ID IN

(

SELECT ID FROM t_B WHERE jwh='XX村'

)

5.

update tbl1 a

set (a.col1, a.col2) = (select b.col1, b.col2

from tbl2 b

where a.key = b.key)

where a.key in(select key from tbl2)

保存备用

create or replace procedure sync_out_test_proc IS

--

CURSOR cursor_test IS

SELECT * FROM TEMP@REPL_23_DBLINK tabl WHERE tabl.CHANGETYPE = 'C';

--

v_currrow TEMP@REPL_23_DBLINK%ROWTYPE;

BEGIN

--*************************************************--

-- Author : hmily_telli

-- Created : 2010-3-2 10:40:11

-- Purpose : 测试将外网临时库中指定的数据同步到中心数据库

--*************************************************--

--增加。标志位为'C'的数据

INSERT INTO TEMP

(ID, NAME,T_CLOB)

SELECT TEST.ID, TEST.NAME,T_CLOB

FROM TEMP@REPL_23_DBLINK TEST;

--修改。标志位为'U'的数据

update student set (name,id )=

(select name ,id from (select student.rowid rd,student1.name,student1.id

from student1,student where student1.int_id =student.int_id) tmp

where student.rowid=tmp.rd);

--删除。标志位为'D'的数据

游标！！1

游标名字test Test表名%rowtype（定义test的没一行的类型）

定义游标 cursor test is select *from test;

For test in(select *　from test )

open cursor_test;

loop

fetch cursor_test into v_currrow;

exit when cursor_test%notfound;

insert INTO temp

(ID, NAME, t_clob, creattime, modifytime, changetype, EXCHANGE)

VALUES

(v_currrow.ID,v_currrow.NAME,v_currrow.t_clob,v_currrow.creattime,v_currrow.modifytime,v_currrow.changetype,v_currrow.EXCHANGE);

end loop;

close cursor_test;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');

end sync_out_test_proc

一个完整的存储过程

create or replace procedure SYNC_OUT_YWPZ_PROC IS

--*************************************************--

-- Author : zj

-- Created : 2010-3-3 10:40:11

-- Purpose : 测试将外网临时库中指定的数据同步到中心数据库

--*************************************************--

--定义row变量

v_currrow t_ins_ywpz%ROWTYPE;

--异常代号及内容

v_errorcode number;

v_errortext varchar2(400);

--临时表字符

--g_temp_table varchar2(1000);

BEGIN

/****************************/

/*含有CLOB等特殊字段解决方案

/****************************/

--创建事务级临时表

-- g_temp_table:='create global temporary table g_clob_temp as select * from t_ins_ywpz ON COMMIT DELETE ROWS';

-- EXECUTE IMMEDIATE g_temp_tableone;

--利用database link把远程数据先插入到临时表中

--(注意:INSERT后先不要commit,否则commit后临时表中数据就会丢失)

insert into g_clob_ywpz

select *

from t_ins_ywpz@REPL_163_DBLINK tabl

WHERE tabl.CHANGETYPE = 'C'

AND tabl.EXCHANGE = '-1';

-- COMMIT;

--新增。标志位为'C'的数据

--定义新增游标

for v_currrow in (select * from g_clob_ywpz) loop

begin

insert into t_ins_ywpz

(id,

ywbh,

type,

dataxml,

createtime,

modifytime,

changetype,

exchange)

values

(v_currrow.id,

v_currrow.ywbh,

v_currrow.type,

v_currrow.dataxml,

v_currrow.createtime,

v_currrow.modifytime,

v_currrow.changetype,

v_currrow.exchange);

--插入操作成功后，修改标识符1

UPDATE t_ins_ywpz SET EXCHANGE = '1' WHERE ID = v_currrow.ID;

exception

when OTHERS THEN

--执行异常插入操作

v_errorcode := SQLCODE;

v_errortext := SUBSTR(SQLERRM, 1, 400);

--插入异常记录

insert INTO t_sync_excep_log

(t_name,

t_excep_content,

t_ctime,

t_sync_dir,

t_excep_code,

t_unique_flag,

t_oper_type)

VALUES

('t_ins_ywpz',

v_errortext,

sysdate,

'website@163->website@163',

v_errorcode,

v_currrow.ID,

v_currrow.changetype);

--插入操作失败，修改标识符0

UPDATE t_ins_ywpz SET EXCHANGE = '0' WHERE ID = v_currrow.ID;

end;

end loop;

dbms_output.put_line('-->>cursor_c done!');

--利用database link把远程数据先插入到临时表中

--(注意:INSERT后先不要commit,否则commit后临时表中数据就会丢失)

--insert into g_clob_temp select * from TEMP@REPL_163_DBLINK tabl

-- WHERE (tabl.CHANGETYPE = 'U' OR tabl.CHANGETYPE = 'D')

-- AND tabl.EXCHANGE = '-1';

-- COMMIT;

--修改、删除。标志位为'U'或'D'的数据

--删除操作执行逻辑删除

--定义修改、删除游标

for v_currrow in (select *

from t_ins_ywpz tabl

WHERE tabl.CHANGETYPE = 'C'

AND tabl.EXCHANGE = '-1') loop

begin

--执行修改、删除操作

update t_ins_ywpz@REPL_163_DBLINK

set id = v_currrow.id,

ywbh = v_currrow.ywbh,

type = v_currrow.type,

dataxml = v_currrow.dataxml,

createtime = v_currrow.createtime,

modifytime = v_currrow.modifytime,

changetype = v_currrow.changetype,

exchange = v_currrow.exchange

where id = v_currrow.id;

--更新操作成功后，修改标识符1

UPDATE t_ins_ywpz SET EXCHANGE = '1' WHERE ID = v_currrow.ID;

exception

when OTHERS THEN

--执行异常插入操作

v_errorcode := SQLCODE;

v_errortext := SUBSTR(SQLERRM, 1, 400);

--插入异常记录

insert INTO t_sync_excep_log

(t_name,

t_excep_content,

t_ctime,

t_sync_dir,

t_excep_code,

t_unique_flag,

t_oper_type)

VALUES

('t_ins_ywpz',

v_errortext,

sysdate,

'website@163->website@163',

v_errorcode,

v_currrow.ID,

v_currrow.changetype);

--更新操作失败，修改标识符0

UPDATE t_ins_ywpz SET EXCHANGE = '0' WHERE ID = v_currrow.ID;

end;

end loop;

dbms_output.put_line('-->>cursor_ud done!');

end SYNC_OUT_YWPZ_PROC;

创建测试表

SQL> create table a(a date);

表已创建。

创建一个自定义过程

SQL> create or replace procedure test as

2 begin

3 insert into a values(sysdate);

4 end;

5 /

过程已创建。

创建JOB

variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,'your_procedure;',trunc(sysdate)+9/24,'trunc(sysdate)+1');
commit;

这是每天9点执行！

variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,'your_procedure;', sysdate,'sysdate+1/1440');
commit;

此是每分钟执行一次！

variable v_jobnum number;
exec dbms_job.submit(:v_jobnum,' pck_swap.p_clear_swapdata;',trunc(sysdate)+1/24,'trunc(sysdate)+1');
commit;

删除JOB

SQL> begin

2 dbms_job.remove(:job1);

3 end;

4 /

PL/SQL 过程已成功完成。

ID NVARCHAR2(20) not null,

NAME NVARCHAR2(100),

T_CLOB CLOB,

CREATTIME DATE default sysdate,

MODIFYTIME DATE default sysdate,

CHANGETYPE VARCHAR2(2 CHAR) default 'C',

EXCHANGE VARCHAR2(2 CHAR) default '-1'

物级零时表的创建方法

--创建事务级临时表

--g_temp_table:='create global temporary table g_clob_temp as select * from TEMP ON COMMIT DELETE ROWS'; TEMP 是 基表

EXECUTE IMMEDIATE g_temp_table;

Create global temporary table

(

字段1 varchar2()

…….

……

) ON COMMIT DELETE(trazication ) ROWS

create global temporary table临时表名 on commit preserve|delete rows

用preserve时就是SESSION级的临时表，用delete就是TRANSACTION级的临时表

汪雄才 14:23:29

创建SESSION级的临时表

解决clob字段

create global temporary table photo_temp as select * from photo

create global temporary table photo_temp as select * from photo ;

2.用database link导入远程数据到临时表
Sql代码

insert into photo_temp select * from photo@photo_link;--不要commit;否则临时表中数据消失 insert into photo_temp select * from photo@photo_link;--不要commit;否则临时表中数据消失

3.把临时表数据插入到永久表中：
Sql代码 insert into photo select * from photo_temp; commit;

更新 表ietm 的OK 列使其 所有值为哈哈！

如果想把这列都赋值为‘哈哈’那就用：update item set ok='哈哈'如果想在这列后追加一个‘哈哈’那就用：update item set ok=ok+'哈哈'

游标 使用和定义

CURSOR business_c_curIS
SELECT *
FROM t_webhall_business_info@REPL_241_DBLINK tabl
WHERE tabl.changetype = 'C'
AND tabl.exchange = '-1';

OPEN business_c_cur;
LOOP
FETCH business_c_cur
INTO v_currrow;
EXIT WHEN business_c_cur%NOTFOUND;
BEGIN

Oracle 授权语句

　--select * from dba_users; 查询数据库中的所有用户

　　--alter user TEST_SELECT account lock; 锁住用户

　　--alter user TEST_SELECT account unlock; 给用户解锁

　　--create user xujin identified by xujin; 建立用户

　　--grant create tablespace to xujin; 授权

　　--grant select on tabel1 to xujin; 授权查询

　　--grant update on table1 to xujin;

　　--grant execute on procedure1 to xujin 授权存储过程

　　--grant update on table1 to xujin with grant option; 授权更新权限转移给xujin用户，许进用户可以继续授权；

　　--收回权限

　　--revoke select on table1 from xujin1; 收回查询select表的权限；

　　--revoke all on table1 from xujin;

　　/*grant connect to xujin;

　　revoke connect from xujin

　　grant select on xezf.cfg_alarm to xujin;

　　revoke select on xezf.cfg_alarm from xujin;*/

　　--select table_name,privilege from dba_tab_privs where grantee='xujin' 查询一个用户拥有的对象权限

　　--select * from dba_sys_privs where grantee='xujin' 查询一个用户拥有的系统权限

　　--select * from session_privs --当钱会话有效的系统权限

　　--角色

　　--create role xujin1;--建立xujin1角色

　　--grant insert on xezf.cfg_alarm to xujin1; 将插入表的信息

　　--revoke insert on xezf.cfg_alarm from xujin1; 收回xujin1角色的权限

　　--grant xujin1 to xujin ; 将角色的权限授权给xujin;

　　-- create role xujin2;

　　--grant xujin1 to xujin2; 将角色xujin1授权给xujin2;

　　--alter user xujin default xujin1,xujin2; 修改用户默认角色

　　-- DROP ROLE xujin1;删除角色1；

　　--select * from role_sys_privs where role=xujin1;

　　--查看许进1角色下有什么系统权限；

　　--select granted_role,admin_option from role_role_privs where role='xujin2';

　　--查看xujin1角色下面有什么角色权限

　　--select * from role_sys_privs where role='xujin2';

　　--select table_name,privilege from role_tab_privs where role='xujin1';

　　--select * from dba_role_privs where grantee='xujin' --查看用户下面有多少个角色；

1楼cjr152336611431小时前

你对dblink的研究还是挺深入的啊