Using sequences as primary key

Question

I have one table called INTER and I want to transfer some data from INTER to FINAL (a new table which is already created and empty) using a procedure.

I have to make use of sequence as a primary key for the FINAL table. INTER is the parent table and it has all columns present in FINAL.

I have created a sequence M_SQ. Here is what I tried:

create or replace 
PROCEDURE STAGING_TO_CUSTOMER
    AS
     BEGIN
         INSERT INTO FINAL (C_ID,C_NAME,C_PHONE,C_ADDRESS)
         SELECT M_SQ.NEXTVAL,C_NAME,C_PHONE,C_ADDRESS FROM INTER;
         COMMIT;
         EXCEPTION
         WHEN OTHERS THEN
         ROLLBACK;
     END;

This procedure gets compiled but no records are transferred to FINAL.

Can somebody help? I am using sqldeveloper 11g.

get rid of the "EXCEPTION WHEN OTHERS". that is just serving to mask any errors. Once removed then rerun the procedure. you may then see an error raised. — DazzaL, Apr 12 '13 at 22:37

score 0 · Answer 1 · answered Apr 12 '13 at 21:20

0

You do not need a stored proc - you can just run the command as is:

INSERT INTO FINAL (C_ID,C_NAME,C_PHONE,C_ADDRESS) SELECT M_SQ.NEXTVAL,C_NAME,C_PHONE,C_ADDRESS FROM INTER; 
COMMIT;

answered Apr 12 '13 at 21:20

Fergus

220
1
7

I know i can run the command but its a part of requirement and hence I have to make use of procedure. It gets compiled and it runs succesfully when I call it. However, the table FINAL is still empty. – Nik_stack Apr 12 '13 at 23:30

Sebas · Accepted Answer · 2013-04-12T23:31:18.353

0

you actually have to run the procedure for it to do what is inside it:

BEGIN
    STAGING_TO_CUSTOMER;
END;

Also, your procedure might as well look like:

CREATE OR REPLACE PROCEDURE STAGING_TO_CUSTOMER AS
BEGIN
     INSERT INTO FINAL (C_ID,C_NAME,C_PHONE,C_ADDRESS)
     SELECT M_SQ.NEXTVAL,C_NAME,C_PHONE,C_ADDRESS FROM INTER;
     COMMIT;
     EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
         RAISE;
END;

So you know if something happens.

edited Apr 12 '13 at 23:31

answered Apr 12 '13 at 22:06

Sebas

21,192
9
55
109

I know..I tried running the procedure. As I told, the procedure gets compiled and runs successfully but; when I check the FINAL table, it is still empty – Nik_stack Apr 12 '13 at 23:28
I got the following error:- Error report: ORA-01722: invalid number ORA-06512: at "SYSTEM.STAGING_TO_CUSTOMER", line 11 ORA-06512: at line 2 01722. 00000 - "invalid number" *Cause: *Action: what does it mean? – Nik_stack Apr 13 '13 at 00:00
oh, is there any NUMBER type column in the FINAL table? (except the id column) – Sebas Apr 13 '13 at 00:01
Ya. The C_PHONE column is NUMBER – Nik_stack Apr 13 '13 at 00:25
ok, so the value `01722. 00000` is not a number, and probably fails to insert into it. Check the original table (`INTER`) to see what is the column type for the `C_PHONE` field. – Sebas Apr 13 '13 at 00:26
I changed the datatype. It worked. Thanks. What if I want only DISTINCT records from INTER? I means I cant say DISTINCT in SELECT statement as MIGR_SQ cant be distinct. – Nik_stack Apr 13 '13 at 00:36
Use this select query instead: `SELECT M_SQ.NEXTVAL, C_NAME, C_PHONE, C_ADDRESS FROM (SELECT DISTINCT C_NAME, C_PHONE, C_ADDRESS FROM INTER);` – Sebas Apr 13 '13 at 00:46
Thanks. It worked. Also, the NEXTVAL of sequences always refers to the next number from previously used values. Can I set it to 1 every time I run the procedure? – Nik_stack Apr 13 '13 at 01:13
yes, but this is tricky http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle – Sebas Apr 13 '13 at 01:15

Using sequences as primary key

2 Answers2