Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not getting the CLOB data
Trigger not getting the CLOB data [message #125107] Thu, 23 June 2005 05:52 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #125267 is a reply to message #125259] Fri, 24 June 2005 02:32 Go to previous messageGo to next message
spsingh_mech
Messages: 4
Registered: May 2005
Location: India
Junior Member

Hi,

RETURNING "NOTE_MEMO" is misprint. It is MEMO.

I am using oracle 9i r2.
Also insertion is taking place from a vb executable, so I can only see the trace of the session. the applcation is inserting a rtf document in memo field.

I am doing this to check the clob data in trigger.

dbms_lob.getlength (:newrow.memo);

this always returns "0".

but when I query the table, data is there in table. some how it is not coming in trigger.

Is there a workaround for this?

thanks

Re: Trigger not getting the CLOB data [message #125277 is a reply to message #125267] Fri, 24 June 2005 03:11 Go to previous messageGo to next message
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.

Re: Trigger not getting the CLOB data [message #125281 is a reply to message #125277] Fri, 24 June 2005 03:29 Go to previous messageGo to next message
spsingh_mech
Messages: 4
Registered: May 2005
Location: India
Junior Member

Thanks for the nice explanation.

Is there a workaround to get clob values in trigger?

Re: Trigger not getting the CLOB data [message #125286 is a reply to message #125281] Fri, 24 June 2005 04:05 Go to previous message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
If the application populated clob data using streams but not
insert / update statements triggers can't help you.

Rgds.
Previous Topic: how to find median value for a date
Next Topic: varchar2 to date
Goto Forum:
  


Current Time: Wed May 15 14:47:52 CDT 2024