0

I am getting ORA-01410 and ORA-06512 for the following block of code in a trigger I'm firing for deletion of a record in my table.

Are the calls correct ?

getlong ('xxx', 'YYY', :OLD.ROWID),
getlong ('XXX', 'yyy', :NEW.ROWID)

There seems to be a problem in the way I'm passing OLD.ROWID and NEW.ROWID; there are OLD.ROWID and NEW.ROWID in the tables that I'm updating once the triggers are fired.

Here is the getlong stored procedure:

CREATE OR REPLACE FUNCTION getlong (p_tname    IN VARCHAR2, -- table name
                                    p_cname    IN VARCHAR2, -- column name
                                    p_rowid    IN ROWID)
   RETURN VARCHAR2
AS
   l_cursor     INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_n          NUMBER;
   l_long_val    VARCHAR2 (4000);
   l_long_len    NUMBER;
   l_buflen     NUMBER := 4000;
   l_curpos     NUMBER := 0;
BEGIN

   -- Usage:
   -- select getlong('TABLENAME', 'COLUMNNAME', rowid) from TABLENAME;

   DBMS_SQL.
   parse (l_cursor,
          'select ' || p_cname || ' from ' || p_tname || ' where rowid = :x',
          DBMS_SQL.native);
   DBMS_SQL.bind_variable (l_cursor, ':x', p_rowid);

   DBMS_SQL.define_column_long (l_cursor, 1);
   l_n := DBMS_SQL.execute (l_cursor);

   IF (DBMS_SQL.fetch_rows (l_cursor) > 0)
   THEN
      DBMS_SQL.column_value_long (l_cursor,
                                  1,
                                  l_buflen,
                                  l_curpos,
                                  l_long_val,
                                  l_long_len);
   END IF;

   DBMS_SQL.close_cursor (l_cursor);
   RETURN l_long_val;
END getlong;
/

Thanks for helping.

1
  • What exactly does your trigger look like? You're referencing the :old and :new pseudorecords so it must be a row-level trigger. Are you certain that the rowid values that you are getting from these psrudorecords are valid in the context of the function call? It looks like you're trying to work around a mutating table exception which causes you to look for a rowid that either does not yet exist in the context of the function call or that no longer exists in the context of the function call. Aug 11 '15 at 3:01
1

There seems to be a combination of more than one problem at work here.

First of all the pseudo-record :NEW is not available in a delete-trigger. You can only access the values of the deleted row via the pseudo-record :OLD. Hence the second call getlong ('XXX', 'yyy', :NEW.ROWID) will not make much sense.

Secondly you pass a row-ID to the procedure getlong which basically gets a value from an arbitrary table (here XXX), which is not necessarily the same table the deleted row came from. A row-ID by itself can only be used to retreive a row from the same table you got the row-ID from. For example

select some_column, ROWID from table_name where some_condition;

The row-ID from the statement above can only be used in a select-statement on the same table table_name, i.g.

select * from table_name where rowid = your_row_id_from_above;

If you try to pass a row-ID of a different table to a select-statement, you will also receive an ORA-01410 error.

A row-ID can be invalid out of multiple reasons

  1. The row-ID references a record which belongs to a different table than the statement accesses where that row-ID is used in.

  2. The row-ID does not match oracles specific format, e.g. when passed as a string.

In this case the first point might be the problem encoutered here. If the table XXX is not the table your delete-trigger works on, you will get an ORA-01410.

On the other hand, there is not much sense in calling getlong in the context of a trigger, if only a select on the same table makes sense, where the row-ID comes from, because you already have direct access to the value you are interested in via the pseudo-record :OLD.

0

ORA-06512 is a catch all for PL/SQL errors. The one you need to be concerned about is the ORA-01410 (more than likely a cause/effect here).
What is your DB version?
Run this:

select * from v$database_block_corruption

If this is a corporate database I highly recommend engaging your DBA immediately.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.