Home » SQL & PL/SQL » SQL & PL/SQL » Secuence Help (Oracle 11g)
Secuence Help [message #560589] Sun, 15 July 2012 17:32 Go to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Hi Guys!
I'm using Oracle 11g and I'm trying to set the secuence nextval as default to Primary Key row, when I create the table, Oracle throws an exception that columns are not allowed. Here is an example.

CREATE TABLE EMP (
EMP_ID VARCHAR2(50) PRIMARY KEY DEFAULT MYSECUENCE.NEXTVAL)

Can someone help me out?

Thanks in advance!
Re: Secuence Help [message #560590 is a reply to message #560589] Sun, 15 July 2012 18:01 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Not that it matters in this case but it is bad coding to depend upon implicit type conversion.
SEQUENCE is type NUMBER should should not be assigned to VARCHAR2(50).

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Secuence Help [message #560591 is a reply to message #560589] Sun, 15 July 2012 18:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Not possible. You must create a before insert row level trigger which assigns MYSECUENCE.NEXTVAL to EMP_ID.

SY.
Re: Secuence Help [message #560592 is a reply to message #560589] Sun, 15 July 2012 19:32 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Guys! I can't get it done by myself.

Here is the example:

First I create the procedure:

CREATE OR REPLACE PROCEDURE TRIGGER_TEST(
P_TR_ID IN NUMBER,
P_FIRST_NAME IN VARCHAR2,
P_LAST_NAME IN VARCHAR2,
P_DDATE IN DATE,
P_EMAIL IN VARCHAR2) IS

BEGIN

INSERT INTO EMP_BKC
(TR_ID,FIRST_NAME, LAST_NAME, DDATE, EMAIL)
VALUES
(P_TR_ID, P_FIRST_NAME, P_LAST_NAME, P_DDATE, P_EMAIL);

END TRIGGER_TEST;


then I create the trigger:

CREATE OR REPLACE TRIGGER TR_employees_02
AFTER INSERT ON EMP_BKC
FOR EACH ROW

BEGIN
TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
END;

When I tried to insert a new record into emp_bkc as


INSERT INTO
EMP_BKC
(FIRST_NAME, LAST_NAME, DDATE, EMAIL)
VALUES
('TEST','TEST',SYSDATE,'TEST@TEST.COM')

I get the error


ORA-04091: table HR.EMP_BKC is mutating, trigger/function may not see it
ORA-06512: at "HR.TRIGGER_TEST", line 10
ORA-06512: at "HR.TR_EMPLOYEES_02", line 4
ORA-04088: error during execution of trigger 'HR.TR_EMPLOYEES_02'

And I do not know why!!! Please guys help me out!

Thanks
Re: Secuence Help [message #560593 is a reply to message #560592] Sun, 15 July 2012 19:40 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>>You must create a before insert row level trigger

You were told to do above then you do below

>CREATE OR REPLACE TRIGGER TR_employees_02 AFTER INSERT ON EMP_BKC

Please guess what the problem is.

[Updated on: Sun, 15 July 2012 19:41]

Report message to a moderator

Re: Secuence Help [message #560594 is a reply to message #560593] Sun, 15 July 2012 19:46 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Thanks a lot, I replaced the procedure and now I got the Error

ORA-00036:
maximum number of recursive SQL levels (string) exceeded
Cause: An attempt was made to go more than the specified number of recursive SQL levels.
Action: Remove the recursive SQL, possibly a recursive trigger.

I think there's something wrong with my code, I am not whether the trigger would be "for each row" or now. I do have experience on Oracle but i created a trigger once or twice during the process of certification!

Thanks again in advance!
Re: Secuence Help [message #560595 is a reply to message #560594] Sun, 15 July 2012 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>I think there's something wrong with my code
Why YES!
The trigger contains following line:
>TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
which contains INSERT into EMP_BKC & fires the trigger that contains the following line:
>TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
which contains INSERT into EMP_BKC & fires the trigger that contains the following line:
>TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
which contains INSERT into EMP_BKC & fires the trigger that contains the following line:
>TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
which contains INSERT into EMP_BKC & fires the trigger that contains the following line:

so where does this endless LOOP end?
Re: Secuence Help [message #560596 is a reply to message #560595] Sun, 15 July 2012 20:12 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
yes!!!!!!!!!!!! it worked so sweet!!!!!!

Thank you all guys!!
Re: Secuence Help [message #560597 is a reply to message #560596] Sun, 15 July 2012 20:13 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Secuence Help [message #560599 is a reply to message #560597] Sun, 15 July 2012 20:41 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Here is the solution and the working trigger.

Creating the procedure

CREATE OR REPLACE PROCEDURE HR.TRIGGER_TEST(
                            P_TR_ID       IN  NUMBER, 
                            P_FIRST_NAME  IN  VARCHAR2,
                            P_LAST_NAME   IN  VARCHAR2,
                            P_DDATE       IN  DATE,
                            P_EMAIL       IN  VARCHAR2) IS
                            
BEGIN

INSERT INTO EMP_BKC
(TR_ID,FIRST_NAME, LAST_NAME, DDATE, EMAIL)
VALUES
(P_TR_ID, P_FIRST_NAME, P_LAST_NAME, P_DDATE, P_EMAIL);

END TRIGGER_TEST;
/


Step 2:

Creating the trigger:

CREATE OR REPLACE TRIGGER HR.TR_employees_17
BEFORE INSERT ON HR.EMP_BKC1 FOR EACH ROW
BEGIN
TRIGGER_TEST(EMPLOYEES_SEQ.NEXTVAL, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.DDATE, :NEW.EMAIL);
END;
/


Inserting some values
INSERT INTO EMP_BKC1
(FIRST_NAME, LAST_NAME, DDATE,EMAIL)
VALUES
('STEVE','XXXX',SYSDATE,'test@tets.com')


checking the EMP_BKC (where the trigger is supposed to insert the TR_ID)

411,STEVE,XXXX,15/07/2012 22:38:49,test@tets.com

[Updated on: Sun, 15 July 2012 20:45]

Report message to a moderator

Re: Secuence Help [message #560666 is a reply to message #560599] Mon, 16 July 2012 05:14 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Scrap your code. All you need is a simple trigger:

CREATE OR REPLACE
  TRIGGER TR_employees_17
    BEFORE INSERT
    ON EMP_BKC
    FOR EACH ROW
    BEGIN
        :NEW.TR_ID := EMPLOYEES_SEQ.NEXTVAL;
END;
/


For example:

SQL> DESC EMP_BKC
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TR_ID                                              NUMBER
 FIRST_NAME                                         VARCHAR2(10)
 LAST_NAME                                          VARCHAR2(10)
 DDATE                                              DATE
 EMAIL                                              VARCHAR2(20)

SQL> CREATE OR REPLACE
  2    TRIGGER TR_employees_17
  3      BEFORE INSERT
  4      ON EMP_BKC
  5      FOR EACH ROW
  6      BEGIN
  7          :NEW.TR_ID := EMPLOYEES_SEQ.NEXTVAL;
  8  END;
  9  /

Trigger created.

SQL> SELECT * FROM EMP_BKC
  2  /

no rows selected

SQL> INSERT INTO EMP_BKC
  2  (FIRST_NAME, LAST_NAME, DDATE,EMAIL)
  3  VALUES
  4  ('STEVE','XXXX',SYSDATE,'test@tets.com')
  5  /

1 row created.

SQL> INSERT INTO EMP_BKC
  2  (FIRST_NAME, LAST_NAME, DDATE,EMAIL)
  3  VALUES
  4  ('SAM','YYYY',SYSDATE,'sam@test.com')
  5  /

1 row created.

SQL> SELECT * FROM EMP_BKC
  2  /

     TR_ID FIRST_NAME LAST_NAME  DDATE     EMAIL
---------- ---------- ---------- --------- --------------------
         1 STEVE      XXXX       16-JUL-12 test@tets.com
         2 SAM        YYYY       16-JUL-12 sam@test.com

SQL> 


SY.
Previous Topic: Regexp
Next Topic: create user
Goto Forum:
  


Current Time: Wed Oct 01 04:25:06 CDT 2014

Total time taken to generate the page: 0.12858 seconds