Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not getting the CLOB data (oracle9i)
Trigger not getting the CLOB data [message #354349] Fri, 17 October 2008 23:58 Go to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
Hi All,

I am having problem in After insert/update trigger. I have two tables having CLOB columns. I have written an after insert/update trigger on the first table in which I am taking all the :newrow values and inserting it into the second table.

But I am always receving NULL values for CLOB columns, while the othe datas are inserted/updated.


please suggest a solution for this.


Thanks in advance.

Re: Trigger not getting the CLOB data [message #354356 is a reply to message #354349] Sat, 18 October 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without your code the only thing we can say is: fix your code.

Before posting your code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Sat, 18 October 2008 02:27]

Report message to a moderator

Re: Trigger not getting the CLOB data [message #354357 is a reply to message #354349] Sat, 18 October 2008 00:48 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sreejithmenon,

Please post your trigger body statements. No one can guess what is it you have done in your trigger unless you show the trigger code.

Before posting the trigger code, please read Orafaq Forum Guide.

Regards,
Jo

[*** Oops. Didnt see Michel's Comment.]

[Updated on: Sat, 18 October 2008 00:48]

Report message to a moderator

Re: Trigger not getting the CLOB data [message #354366 is a reply to message #354356] Sat, 18 October 2008 02:38 Go to previous messageGo to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
Hi All,

I am having problem in After insert/update database trigger. I have two tables having CLOB columns, defaulted to empty_clob(). I have written an after insert/update trigger on the first table in which I am taking all the :newrow values and inserting it into the second table.

When I insert in the first table through back-end it works correctly and all the datas are inserted in the second table too.

But when i insert through Forms, I am always receving NULL values for CLOB column in the second table, while the other datas are inserted.

these are the tables

create table ABC 
   (I_NO Number(20), 
    Q_DOC Varchar2(10), 
    QSTN CLOB);
create table XYZ 
   (I_NO Number(20), 
    Q_DOC Varchar2(10), 
    QSTN CLOB, 
    ANSWER CLOB, 
    RESULT char(1));

trigger code is,

CREATE OR REPLACE TRIGGER A_Z 
   AFTER INSERT OR UPDATE ON ABC
   FOR EACH ROW
   BEGIN
      IF INSERTING THEN
           INSERT INTO Q_DYN (I_NO, Q_DOC, QSTN)
           VALUES (:NEW.I_NO, :NEW.Q_DOC, :NEW.QSTN);
      ELSIF UPDATING THEN
           UPDATE Q_DYN
           SET Q_DOC = :NEW.Q_DOC,
               QSTN= :NEW.QSTN
               WHERE I_NO = :OLD.I_NO;
        END IF;
    END;

please suggest a solution for this.

Thanks in advance.


please suggest a solution for this.

Thanks in advance.
  • Attachment: code.txt
    (Size: 0.64KB, Downloaded 132 times)
Re: Trigger not getting the CLOB data [message #354367 is a reply to message #354349] Sat, 18 October 2008 03:16 Go to previous messageGo to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
sreejithmenon wrote on Sat, 18 October 2008 10:28
Hi All,

I am having problem in After insert/update database trigger. I have two tables having CLOB columns, defaulted to empty_clob(). I have written an after insert/update trigger on the first table in which I am taking all the :newrow values and inserting it into the second table.

When I insert in the first table through back-end it works correctly and all the datas are inserted in the second table too.

But when i insert through Forms, I am always receving NULL values for CLOB column in the second table, while the other datas are inserted.

these are the tables

create table ABC 
   (I_NO Number(20), 
    Q_DOC Varchar2(10), 
    QSTN CLOB);
create table XYZ 
   (I_NO Number(20), 
    Q_DOC Varchar2(10), 
    QSTN CLOB, 
    ANSWER CLOB, 
    RESULT char(1));

trigger code is,

CREATE OR REPLACE TRIGGER A_Z 
   AFTER INSERT OR UPDATE ON ABC
   FOR EACH ROW
   BEGIN
      IF INSERTING THEN
           INSERT INTO Q_DYN (I_NO, Q_DOC, QSTN)
           VALUES (:NEW.I_NO, :NEW.Q_DOC, :NEW.QSTN);
      ELSIF UPDATING THEN
           UPDATE Q_DYN
           SET Q_DOC = :NEW.Q_DOC,
               QSTN= :NEW.QSTN
               WHERE I_NO = :OLD.I_NO;
        END IF;
    END;


please suggest a solution for this.

Thanks in advance.

Re: Trigger not getting the CLOB data [message #354368 is a reply to message #354367] Sat, 18 October 2008 03:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not even an hour on a Saturday...
Are we being impatient?

Why do you use this weird duplication of data? Why not a lookup from the table containing the answers to the table containing the questions, using I_NO as foreign key?

[Edit: rephrased answer]

[Updated on: Sat, 18 October 2008 03:45]

Report message to a moderator

Re: Trigger not getting the CLOB data [message #354372 is a reply to message #354368] Sat, 18 October 2008 03:59 Go to previous messageGo to next message
sreejithmenon
Messages: 15
Registered: October 2008
Junior Member
sorry sir
the second insertion of topic was by mistake.

sir, actually i've tried the way which you have said earlier itself. But the problems was, there are two more tables here, those are the details of table-abc and table-xyz. So in forms application(details are multi-record), based on table-xyz, when i fetch data from table-abc through cursors, some navigation problem occurs. Thats why I thought of this way.

If you can help me in transferring CLOB data, it'll be great help.

thanking you
Re: Trigger not getting the CLOB data [message #354386 is a reply to message #354372] Sat, 18 October 2008 06:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, you tried something, found you weren't able to make it work and came up with a lousy design.
Now using this design you run into other problems, so you decided to fix those.

Better fix the problems you ran into initially.
Re: Trigger not getting the CLOB data [message #354419 is a reply to message #354349] Sat, 18 October 2008 13:24 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The most obvious answer would be that there is a bug in the form. I presume you are talking Oracle Forms. Once the trigger gets the data, it should not matter where the data came from. To this end, once you show that the trigger works, it should always work if the data is infact inserted into the first table because once the trigger starts, it is an all database operation.

This then suggests that your forms do not handle clobs as your database needs them to. Maybe there is an incompatability between your version of forms and the database. Maybe there is a bug with the way lob identifiers are managed bewteen the two.

I would suggeset opening a TAR with oracle support.

Sorry, Kevin
Previous Topic: handling recursive call in pl/sql
Next Topic: SQL taking more time when selecting column which has length of Varchar2(4000)
Goto Forum:
  


Current Time: Thu Dec 08 00:13:43 CST 2016

Total time taken to generate the page: 0.11551 seconds