Re: Requirements to create stored procedures, part II

In <DEAvBs.91v_at_cs.dal.ca> ae873_at_ccn.cs.dal.ca (Murray Kaiser) writes:
>
>Stefano UNTERNAEHRER (unter_at_mecati.mecasoft.ch) wrote:
><snip>

>: insert into sys.duc$ (owner, pack, proc, operation, seq, com)
>: *
>: ERROR at line 1:
>: ORA-00001: unique constraint (SYS.I_DUC) violated
>
>
You've tried to do an insert with the same unique value entered twice (ie, the same primary key or column with a unique index set up). One of your columns will not allow duplicates. Which one is a unique identifier? That's probably your boy.

Constraint information is stored in the data dictionary under these tables:

    USER_CONSTRAINTS     CONSTRAINT_DEFS     CONSTRAINT_COLUMNS You can run a SQL query that looks something like this:

    SQL> select owner, constraint_name

         from USER_CONSTRAINTS
         where table_name = 'sys.duc$';

That will tell you the names of the constraints. If there is a primary key constraint, it *may* begin with the letters 'pk_'. (That's a nice convention, but not everyone uses it.)

You can then look up the named constraint in the other tables. If you're not familiar with them, use: DESC CONSTRAINT_DEFS and DESC CONSTRAINT_COLUMNS to find out what you're dealing with, column-wise.

Q: Are they important errors?

Yup.

> You can tell more by typing SHOW ERRORS after compiling a procedure.

 Good advice. Type:

    SQL> SHOW ERRORS PROCEDURE your_procedure_name

 Other places to look when a procedure doesn't work:

    USER_DEPENDENCIES -- for dependent objects in the procedure

    USER_OBJECT_SIZE -- for sizing information

    USER_OBJECTS -- check the STATUS column for VALID or INVALID state

    USER_SOURCE -- not for debugging, but it contains the source code

(My students will recognize these from our PL/SQL class....)

There are many other tricks you can do, but time and space limit a more detailed reply here. You're still early in the learning curve, but mistakes will help you in the long run. (If you never did anything wrong, you'd never learn what *can* go wrong!) Think how much wiser you are already!!

Write and let us know how things turn out!

Best of luck to you!

Jennifer Blair, President
Blair Technical Training
Alexandria, VA
Specializing in Oracle and Unix Training email: BTT_at_ix.netcom.com    Received on Tue Sep 05 1995 - 00:00:00 CEST