Trigger not getting the CLOB data [message #125107] |
Thu, 23 June 2005 05:52 |
spsingh_mech
Messages: 4 Registered: May 2005 Location: India
|
Junior Member |
|
|
Hi All,
I am having problem in After insert trigger. I have a table having CLOB column. I am inserting a row in this table from VB application. I have written a after insert trigger on this table in which I am taking all the :newrow values and inserting it into a new table.
But I am always receving NULL values for CLOB column, while the actual data is getting inserted into original table.
I tried to find the reason behind this, and found that It may be because the vb is insertint CLOB using empty_lob().
Can you suggest any solution for this.
I can not change the VB program, also I need the CLOB values for further processing in trigger.
Thanks in advance.
Sumit
|
|
|
Re: Trigger not getting the CLOB data [message #125184 is a reply to message #125107] |
Thu, 23 June 2005 13:16 |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
It's not clear what is your doubt ?
If you use empty_clob() (or empty_blob()) you just initiate
a new pointer to lob data but not data themselfes.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions39a.htm#77386
Quote: |
Purpose
EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable or, in an INSERT or UPDATE statement, to initialize a LOB column or attribute to EMPTY. EMPTY means that the LOB is initialized, but not populated with data.
Restriction on LOB Locators
You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI.
|
So no wonder you don't have any data in you trigger:
SQL> create table clob_tab (id number, data clob);
Table created.
SQL> create or replace trigger tr_clob_01
2 after insert on clob_tab
3 for each row
4 declare
5 clb clob;
6 data_new varchar2(100);
7 amnt binary_integer := 100;
8 begin
9 clb := :new.data;
10 DBMS_LOB.READ (clb,amnt,1,data_new);
11 dbms_output.put_line(data_new);
12 exception
13 when no_data_found then
14 dbms_output.put_line('No data found');
15 end;
16 /
Trigger created.
SQL> insert into clob_tab values(1,'ABC');
ABC
1 row created.
SQL> insert into clob_tab values(2,empty_clob());
No data found
1 row created.
Rgds.
|
|
|
Re: Trigger not getting the CLOB data [message #125251 is a reply to message #125184] |
Fri, 24 June 2005 01:07 |
spsingh_mech
Messages: 4 Registered: May 2005 Location: India
|
Junior Member |
|
|
Thanks for the reply, Still I have the problem.
CREATE TABLE tbl_01(
CL_ID NUMBER (10) NOT NULL,
USER_CODE NUMBER (10),
MEMO CLOB);
A VB application is inserting data in this table. I have generated trace file for this operation. following is the insert statement coming in trace file.
INSERT INTO tbl_01 (CL_ID,USER_CODE, MEMO)
VALUES (137,1,:1) RETURNING "NOTE_MEMO" INTO :NOTE_MEMO
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 2 1 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 2 1 5 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 96
********************************************************************************
I want to write a trigger on this table (After Insert) and process the clob data in the trigger.
CREATE OR REPLACE TRIGGER AI_trg_01
AFTER insert ON tbl_01
REFERENCING NEW AS newrow
FOR EACH ROW
DECLARE
new_rec tbl_01e%ROWTYPE;
v_1 clob;
BEGIN
v_1 := :newrow.memo;
--Do some processing
END AI_trg_01;
In this trigger, I am getting all the values except :newrow.memo, which is always null.
But when I check in the table, the memo field is getting populated.
Can anybody tell me how to do this?
Thanks
|
|
|
Re: Trigger not getting the CLOB data [message #125259 is a reply to message #125251] |
Fri, 24 June 2005 02:07 |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
spsingh_mech wrote on Fri, 24 June 2005 10:07 | INSERT INTO tbl_01 (CL_ID,USER_CODE, MEMO)
VALUES (137,1,:1) RETURNING "NOTE_MEMO" INTO :NOTE_MEMO
...
In this trigger, I am getting all the values except :newrow.memo, which is always null.
But when I check in the table, the memo field is getting populated.
|
Why do you think clob is null ? What data and how
do you put into clob ? What Oracle release are you using ? And what RETURNING "NOTE_MEMO" means in your code - there is not the column in table with such name. Is it a misprint ?
SQL> create or replace trigger tr_clob_01
2 after insert on clob_tab
3 for each row
4 declare
5 clb clob;
6 begin
7 clb := :new.data;
8 if clb is null then
9 dbms_output.put_line('CLOB pointer is NULL');
10 else
11 dbms_output.put_line('CLOB pointer is NOT NULL');
12 end if;
13 end;
14 /
Trigger created.
SQL> insert into clob_tab values(1,'ABC');
CLOB pointer is NOT NULL
1 row created.
SQL> insert into clob_tab values(2,empty_clob());
CLOB pointer is NOT NULL
1 row created.
SQL> insert into clob_tab values(3,null);
CLOB pointer is NULL
1 row created.
Rgds.
|
|
|
|
Re: Trigger not getting the CLOB data [message #125277 is a reply to message #125267] |
Fri, 24 June 2005 03:11 |
dmitry.nikiforov
Messages: 723 Registered: March 2005
|
Senior Member |
|
|
dbms_lob.getlength(lob_locator) doesn't mean
lob_locator is null. It just means locator points on empty lob.
RETURNING ... INTO incline me to think that the application
populates your table after insertion using known CLOB pointer.
For example:
SQL> declare
2 clb clob;
3 new_data varchar2(10) := '0123456789';
4 begin
5 insert into clob_tab values(1,empty_clob()) returning data into clb;
6 dbms_lob.write(clb,10,1,new_data);
7 commit;
8 end;
9 /
CLOB length is 0
PL/SQL procedure successfully completed.
SQL> select * from clob_tab;
ID
----------
DATA
--------------------------------------------------------------------------------
1
0123456789
So in triger you always see what the clob is empty, while
your application populates the data using, for example,
streams opened for known clob pointer (I don't know
details for VB, but for Java it's the standard approach).
Rgds.
|
|
|
|
|