Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Re: TRIGGERS

david wendelken

2005-05-18

Replies:
>If you don't mind to correct , where i have gone wrong

I already gave you code that will work as long as you get the next value from the sequence, or make sure ahead of time that the value is not already used.

The only time you need to worry about it is if the id field fills up (numbers are up to 38 digits, not too likely a problem for most systems), or if you create the data in a test database and import it into production database (or vice-versa).  Then you have to increment the sequences to equal the highest value in the id field. That's a pretty simple pl/sql script to write, and not something you need to do everyday. Given a reminder in a week or so, I would even such a script and share it with you. Or, someone may already have one handy and beat me to it!


If you take the trouble to supply the insert statement with a specified id value and that value is already taken, the database SHOULD raise an error. It can't do what you've EXPLICTLY told it to do. If you don't care what the id value is, then you should let the trigger do the work for you.

It's bad design to do otherwise.  Several people have already told you this. Telling the database to explicitly use one value for the id and having it change that value without your knowledge is an absolutely horrible idea.  To put it bluntly, it is highly likely to make application maintenance a living hell, not to mention corrupt one heck of a lot of data if you use this technique for all your tables.  

I can't fix what you've written simply because the approach you are taking won't work reliably.
Period. Ever. If you go down the path you are taking, you won't be able to use this form of the insert statement which allows you to insert more than one row at a time:

INSERT INTO the_table (col1, col2, col3) SELECT aa1, bb2, cc3 FROM some_other_table;

That is a mighty handy form of the insert statement and I wouldn't give it up lightly.

To understand why the approach you are taking will never work reliably, you need to do some research on mutating table errors. I've already told you that. It's not hard to find out about it, you just do a google search on "mutating table errors" and glance at several entries until you find a winner.

The way around the mutating table error in this case is through the use of autonomous transactions. You will need to read up on them, too.

Since you are new to pl/sql and Oracle, you need to prioritize what you learn.

Rather than learning about mutating table errors and autonomous transactions to implement a bad design, you need to focus on understanding the database transaction design using commit and rollback, and how the exception handlers work.


-----Original Message-----
From: Kean Jacinta <jacintakean@(protected)>
Sent: May 18, 2005 6:04 PM
To: davewendelken@(protected)
Subject: Re: TRIGGERS

If you don't mind to correct , where i have gone wrong
?

JK

--- david wendelken <davewendelken@(protected)>
wrote:
> That's very interesting.
>
> I tried this, and it let me query the myclass table
> from a myclass row-level trigger.
> I even tried some variations on the select statement
> and it didn't give a mutating table error as
> expected.
>
> Hmmm. Not at all the behaviour I was expecting.
>
> Did some more tests, and the mutating table error
> showed up when I did a multi-record insert in a
> single statement. Don't remember Oracle being that
> smart back when I first learned about mutating table
> errors!
> Learn something every day.
>
> As I've written several times before, you are
> following a flawed approach - because you can still
> get the mutating table error if you continue down
> the path you are on.
>
> Do what I suggested, which is either use the code we
> gave you, or learn how to use autonomous
> transactions to avoid the mutating table error.
>
>
> As for what else wrong with the code below, your
> looping approach is implemented in a bass-ackwards
> way - you've got several really bad logic errors in
> what you've done.
>
> Is this a homework assignment?
>
>
> -----Original Message-----
>
> the error i get :
>
> ORA-0001: unique constraint violated
>
> Here is the entire create trigger
>
> CREATE OR REPLACE TRIGGER "TESTDB"."MYCLASS_TR"
> BEFORE
> INSERT
> OR UPDATE ON "MYCLASS" FOR EACH ROW DECLARE
>  REFCOUNT        NUMBER;
>  DUP_VAL_ON_INDEX   EXCEPTION;
>  TEMP           NUMBER;
>
> BEGIN
>   IF INSERTING THEN
>    
>    
>  LOOP    
>     SELECT COUNT(*) INTO REFCOUNT
>     FROM MYCLASS
>     WHERE :NEW.ID = ID;
>            
>     IF REFCOUNT > 0 THEN --RECORD EXISTED IN
> DATABASE
>     SELECT MYCLASS_SEQ.NEXTVAL INTO TEMP FROM
> DUAL;
>     END IF;  
>     EXIT;
>    --ELSIF REFCOUNT < 0 THEN
>    -- EXIT;
>    -- END IF;
>    
>          
>    
>  END LOOP;  
>    
>     IF :NEW.ID IS NULL THEN
>    
>        
>     SELECT MYCLASS_SEQ.NEXTVAL
>     INTO :NEW.ID
>     FROM DUAL;
>     END IF;
>      
>   END IF; --MAIN IF
>
>
> END;
>
> --
> http://www.freelists.org/webpage/oracle-l
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l