由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - 请教:trigger 里得不到blob值是怎么回事儿?
相关主题
Oracle / DB2 问题请教:PL/SQL
Database Interview Questionsoracle pl sql recursive function
新手求教,plsql collection 参数传入问题,非常感谢。SQL question...
Oracle questionvarchar (80) 比 varchar(800)的优势?
这几天Oracle 92I经常出现ORA-00600的错误HELP!!! PL/SQL write to a file
plsql 求救--速度太慢了, 咋办呢小问题
如何用SQL语句判断一个TABLE是否存在?初级问题
请教: SQL SUMSQL question HELP
相关话题的讨论汇总
话题: blob话题: trigger话题: problem话题: table1话题: lob
进入Database版参与讨论
1 (共1页)
c***c
发帖数: 6234
1
我有一table1,不知为什前面的人把一cloumn设成blob。我有新table叫problem_
history_summary,想得到table1的blob。
我运行
update problem_history_summary set (problem_history, problem_blob,
datestamp,type)= (select UTL_RAW.CAST_TO_VARCHAR2( t.description ), t.
description ,t.datestamp,t.type from table1 t where t.thenumber = '
001A1965');
没有问题。但我设了个trigger,每当table1里insert了new row,我就运行以上的sql
,当然稍微变下
update problem_history_summary set (problem_history, problem_blob,
datestamp,type)= (UTL_RAW.CAST_TO_VARCHAR2( :new.description ), :ne
c***c
发帖数: 6234
2
google到答案了,原来是不可能的。以前的家伙玩酷,害死我了最近。cloumn的名字净
是,id,type,number,description之类的。
You cannot write to a LOB (:old or :new value) in any kind of trigger.
In regular triggers, you can read the :old value but you cannot read the :
new value. In INSTEAD OF triggers, you can read the :old and the :new values.
You cannot specify LOB type columns in an OF clause, because BFILE types can
be updated without updating the underlying table on which the trigger is
defined.
Using OCI functions or the DBMS_LOB pac

【在 c***c 的大作中提到】
: 我有一table1,不知为什前面的人把一cloumn设成blob。我有新table叫problem_
: history_summary,想得到table1的blob。
: 我运行
: update problem_history_summary set (problem_history, problem_blob,
: datestamp,type)= (select UTL_RAW.CAST_TO_VARCHAR2( t.description ), t.
: description ,t.datestamp,t.type from table1 t where t.thenumber = '
: 001A1965');
: 没有问题。但我设了个trigger,每当table1里insert了new row,我就运行以上的sql
: ,当然稍微变下
: update problem_history_summary set (problem_history, problem_blob,

B*****g
发帖数: 34098
3
source?
just try code in oracle 9i, it works.

values.
can

【在 c***c 的大作中提到】
: google到答案了,原来是不可能的。以前的家伙玩酷,害死我了最近。cloumn的名字净
: 是,id,type,number,description之类的。
: You cannot write to a LOB (:old or :new value) in any kind of trigger.
: In regular triggers, you can read the :old value but you cannot read the :
: new value. In INSTEAD OF triggers, you can read the :old and the :new values.
: You cannot specify LOB type columns in an OF clause, because BFILE types can
: be updated without updating the underlying table on which the trigger is
: defined.
: Using OCI functions or the DBMS_LOB pac

B*****g
发帖数: 34098
4
I guess is because most of insert blob, it is insert an empty_blob() first,
then update the column to real value. so insert trigger fired when the value
is empty_blob(). But when you try
INSERT INTO table
SELECT *
FROM table
the trigger works.

【在 B*****g 的大作中提到】
: source?
: just try code in oracle 9i, it works.
:
: values.
: can

c***c
发帖数: 6234
5
http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10800/dcilobs.htm
我的oracle 10g。你是说trigger里:new.blob_field不是empty的?我们所有server都
是10g的client。

【在 B*****g 的大作中提到】
: source?
: just try code in oracle 9i, it works.
:
: values.
: can

B*****g
发帖数: 34098
6
not empty.
I will check tonight on 10g

【在 c***c 的大作中提到】
: http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10800/dcilobs.htm
: 我的oracle 10g。你是说trigger里:new.blob_field不是empty的?我们所有server都
: 是10g的client。

B*****g
发帖数: 34098
7
http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10795/adfns_tr.htm
Example: Modifying LOB Columns with a Trigger
You can treat LOB columns the same as other columns, using regular SQL and
PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with
BLOB columns:
drop table tab1;
create table tab1 (c1 clob);
insert into tab1 values ('

HTML Document Fragment

Some text.');
create or replace trigger trg1
before update on tab1
for each row
begin
dbms_output.put_line('Ol

c***c
发帖数: 6234
8
It is not working on 10g. I just tried. I used DBMS_LOB functions and get
empty anyway.

【在 B*****g 的大作中提到】
: http://www.acs.ilstu.edu/docs/oracle/appdev.101/b10795/adfns_tr.htm
: Example: Modifying LOB Columns with a Trigger
: You can treat LOB columns the same as other columns, using regular SQL and
: PL/SQL functions with CLOB columns, and calls to the DBMS_LOB package with
: BLOB columns:
: drop table tab1;
: create table tab1 (c1 clob);
: insert into tab1 values ('

HTML Document Fragment

Some text.');
: create or replace trigger trg1
: before update on tab1

B*****g
发帖数: 34098
9
下面这个work,没有用dbms_lob。我估计那个append在trigger之后。
CREATE TABLE PROBLEM_HISTORY_SUMMARY
(
PROBLEM_HISTORY VARCHAR2(4000 BYTE),
TYPE VARCHAR2(10 BYTE),
PROBLEM_BLOB BLOB,
DATESTAMP TIMESTAMP(6)
);
CREATE TABLE TABLE1
(
THENUMBER VARCHAR2(20 BYTE),
TYPE VARCHAR2(10 BYTE),
DESCRIPTION BLOB,
DATESTAMP TIMESTAMP(6)
)
;
CREATE OR REPLACE TRIGGER TRIGGER_TABLE1
BEFORE INSERT
ON TABLE1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
UPDATE

【在 c***c 的大作中提到】
: It is not working on 10g. I just tried. I used DBMS_LOB functions and get
: empty anyway.

c***c
发帖数: 6234
10
你是对的。:new.blob_field能得到value。我直接写query insert a row into table1
可以。但通过我们的application就不行。明明每次都看见新row,也得到除
description外所有数值,就是得不到description。哪怕我增加一个blob field 来存
desciption也没用。
多谢了

【在 B*****g 的大作中提到】
: 下面这个work,没有用dbms_lob。我估计那个append在trigger之后。
: CREATE TABLE PROBLEM_HISTORY_SUMMARY
: (
: PROBLEM_HISTORY VARCHAR2(4000 BYTE),
: TYPE VARCHAR2(10 BYTE),
: PROBLEM_BLOB BLOB,
: DATESTAMP TIMESTAMP(6)
: );
: CREATE TABLE TABLE1
: (

1 (共1页)
进入Database版参与讨论
相关主题
SQL question HELP这几天Oracle 92I经常出现ORA-00600的错误
Another oneplsql 求救--速度太慢了, 咋办呢
求救!!! 一个oracle的问题! 在procedure中所涉及的表名可以用变量从参数中传入吗如何用SQL语句判断一个TABLE是否存在?
求救!!! 一个oracl的问题淀请教: SQL SUM
Oracle / DB2 问题请教:PL/SQL
Database Interview Questionsoracle pl sql recursive function
新手求教,plsql collection 参数传入问题,非常感谢。SQL question...
Oracle questionvarchar (80) 比 varchar(800)的优势?
相关话题的讨论汇总
话题: blob话题: trigger话题: problem话题: table1话题: lob