Home » SQL & PL/SQL » SQL & PL/SQL » ORA-03114 (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0)
ORA-03114 [message #543254] Mon, 13 February 2012 14:56 Go to next message
hnguyen
Messages: 5
Registered: February 2012
Junior Member
Hi,
Following is my trigger. Whenever I invoke this trigger the second time, it creates "ORA-03114" error message. The first time it's OK.
This is what I meant: I updated the record the first time, then I saved this trigger was invoked and it was OK. The I updated the same record the second time, then I saved this trigger was invoked again and it failed.

Here are the error messages:
"ORACLE Error: unable to update record."

"FRM-40655: SQL error forced rollback clear formand reenter
transaction."

"Error:ORA-03114: not connected to ORACLE
ORA-03114: not connected to ORACLE"

Please give me any pointer to solve this problem.

Thank you.
create or replace trigger "CDBT_CMPD_ASSET_LOCATION" 
FOR INSERT OR UPDATE OF LOCATION
ON synergen.SA_ASSET

COMPOUND TRIGGER
--
-- History:
-- 07/12/2011 by HN
-- Before a row is inserted or updated in SA_ASSET,
-- fire this trigger to verify that location is valid.

  -- Global declaration.
   e_error              EXCEPTION;
   v_message            VARCHAR2 (100);
   n_is_location_valid  number;
   tabLOCATION          tabtypeLOCATION  := tabtypeLOCATION ();
   v_plant              varchar2(3);
   v_location           varchar2(30);

  BEFORE STATEMENT IS
  BEGIN
    FOR rec IN (SELECT code , plant
                FROM sa_authority
                WHERE table_no = 231
                UNION
                SELECT UNIQUE t.location code , t.plant
                FROM sa_asset t, sa_authority a
                WHERE t.plant = a.plant(+)
                AND t.location = a.code(+)
                AND a.table_no(+) = 231
                order by 1)
    LOOP
      tabLOCATION.extend;
      tabLOCATION(tabLOCATION.count) := typeLOCATION(rec.code,
                                                     rec.plant);
    END LOOP;

  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
--dbms_output.put_line('BEFORE EACH ROW - :new.location = '||
:new.location||'; :old.location = '||:old.location);
    IF (:new.location is NOT NULL) AND
       (NVL(:new.location,' ') != NVL(:old.location,' ')) THEN
      BEGIN
        n_is_location_valid := 0;

        select count(*)
        INTO n_is_location_valid
        FROM  TABLE( cast(tabLOCATION AS tabtypeLOCATION) )
        where location = :new.location
          and plant    = :new.plant;

        -- Removed all data after finished.
        tabLOCATION.delete;

--dbms_output.put_line('BEFORE EACH ROW - n_is_location_valid = '||n_is_location_valid);

        IF n_is_location_valid = 0 THEN
          Raise e_error;
        END IF;

      EXCEPTION
        WHEN e_error then
          raise_application_error
          (-20501,
          'The selected location is invalid.  Please select a valid location.'
          );
        WHEN OTHERS THEN
          v_message := SUBSTR (v_message || ' ' || SQLERRM, 1, 100);
          raise_application_error (-20502, v_message);
      END;
    END IF;

  END BEFORE EACH ROW;
END CDBT_CMPD_ASSET_LOCATION;


[EDITED by LF: applied [code] tags]

[Updated on: Wed, 15 February 2012 00:55] by Moderator

Report message to a moderator

Re: ORA-03114 [message #543261 is a reply to message #543254] Mon, 13 February 2012 15:06 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
hnguyen wrote on Mon, 13 February 2012 15:56

This is what I meant: I updated the record the first time, then I saved this trigger was invoked and it was OK. The I updated the same record the second time, then I saved this trigger was invoked again and it failed.


I do not know what this means or what you did. You do not save a trigger after making data changes. You "create" a trigger and it executes based on a condition you set.
Re: ORA-03114 [message #543263 is a reply to message #543261] Mon, 13 February 2012 15:10 Go to previous messageGo to next message
hnguyen
Messages: 5
Registered: February 2012
Junior Member
I meant I save the transaction and at this time the trigger was invoked. I am sorry for the confusion.
Re: ORA-03114 [message #543264 is a reply to message #543254] Mon, 13 February 2012 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-03114: not connected to ORACLE


Either you did not connect either you disconnected either you have been disconnected.

Regards
Michel
Re: ORA-03114 [message #543296 is a reply to message #543264] Tue, 14 February 2012 01:04 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
This seems to be an error in Oracle Forms design side. Please refer the below and try to investigate further:

Quote:

Error Message: FRM-40655: SQL error forced rollback: clear form and re-enter transaction.
Error Cause:

A deadlock or some other error has caused the current transaction to fail. Your changes were rolled back.
Action:

Clear the form (or exit and re-enter the form) and re-enter the transaction. You might have to modify the form's design to prevent the error from recurring. Level: >25 Type: Error
Re: ORA-03114 [message #543328 is a reply to message #543296] Tue, 14 February 2012 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
The FRM error is probably just a knock-on from the other errors.
Re: ORA-03114 [message #543329 is a reply to message #543264] Tue, 14 February 2012 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Mon, 13 February 2012 21:14
Quote:
ORA-03114: not connected to ORACLE


Either you did not connect either you disconnected either you have been disconnected.

Regards
Michel


Been disconnected, by a bug I suspect, Forms doesn't normally disconnect.

@hnguyen - I suggest tracing the session to see at what point it dies.
Re: ORA-03114 [message #543336 is a reply to message #543329] Tue, 14 February 2012 03:37 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Perhaps the "WHEN OTHERS" part obfuscates the real error somehow, too?
Re: ORA-03114 [message #543467 is a reply to message #543336] Tue, 14 February 2012 23:48 Go to previous message
javed.khan
Messages: 291
Registered: November 2006
Location: Banglore
Senior Member

As per cookiemonster trace the session also if the process runs for a long time Perhaps there is a timeout value set in sqlnet.ora..just check that.

Also you crosscheck with alert_dbname.log lists a core dump access violation.
Previous Topic: Anyone know if Double Quotes in Column Name Can be replace with Alternate Character???
Next Topic: Rename Column Issue
Goto Forum:
  


Current Time: Mon Apr 21 04:36:11 CDT 2014

Total time taken to generate the page: 0.08790 seconds