ORA-01410 Error

oracleplsql

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.

Best Answer

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.

Related Question