SQL & PL/SQL

I am trying to do an INSERT ALL INTO two different TABLEs at the same time. The reason is mainly because the same SEQUENCE number is required in both, due to a FOREIGN KEY.

During testing, a low number of records worked, but a higher number failed, due to the FK violation. Seemingly, Oracle is INSERTing into the second TABLE before the first.

In order to figure this problem out, i created a test case to find the boundaries.

The TABLEs used are A and B. A has a PRIMARY KEY and a CHAR. B has one COLUMN that REFERENCES A.

The CHAR is used for the padding. VARCHAR2 fails when the padding is provided.

SQL> CREATE TABLE A(A NUMBER(5) PRIMARY KEY, B CHAR(24));

Table created.

SQL> CREATE TABLE B(A NUMBER(5) CONSTRAINT B REFERENCES A);

Table created.

SQL> CREATE SEQUENCE C;

Sequence created.

SQL>
SQL> INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
2 SELECT * FROM Dual CONNECT BY Level < 10202;
INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
*
ERROR at line 1:
ORA-02291: integrity constraint (CHACHAM.B) violated - parent key not found

Knock down the amount of records by 1 and it works:

SQL> DROP TABLE B;

Table dropped.

SQL> DROP TABLE A;

Table dropped.

SQL> DROP SEQUENCE C;

Sequence dropped.

SQL>
SQL> CREATE TABLE A(A NUMBER(5) PRIMARY KEY, B CHAR(24));

Table created.

SQL> CREATE TABLE B(A NUMBER(5) CONSTRAINT B REFERENCES A);

Table created.

SQL> CREATE SEQUENCE C;

Sequence created.

SQL>
SQL> INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
2 SELECT * FROM Dual CONNECT BY Level < 10201;

20400 rows created.

If the SEQUENCE STARTs WITH 9758, only 256 records are reuired to fail:

SQL> CREATE TABLE A(A NUMBER(5) PRIMARY KEY, B CHAR(24));

Table created.

SQL> CREATE TABLE B(A NUMBER(5) CONSTRAINT B REFERENCES A);

Table created.

SQL> CREATE SEQUENCE C START WITH 9758;

Sequence created.

SQL>
SQL> INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
2 SELECT * FROM Dual CONNECT BY Level < 257;
INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
*
ERROR at line 1:
ORA-02291: integrity constraint (CHACHAM.B) violated - parent key not found

Or, change the CHAR to be 25, and it fails at 256 as well:

DROP TABLE B;
DROP TABLE A;
DROP SEQUENCE C;

CREATE TABLE A(A NUMBER(5) PRIMARY KEY, B CHAR(25));
CREATE TABLE B(A NUMBER(5) CONSTRAINT B REFERENCES A);
CREATE SEQUENCE C;

INSERT ALL INTO A (A,B) VALUES (C.NEXTVAL, ' ') INTO B VALUES (C.NEXTVAL)
SELECT * FROM Dual CONNECT BY Level < 257;

In summary:
--
A.B = 24
CREATE SEQUENCE C START WITH 1;
Source table breaks at 10202
--
A.B = 24
CREATE SEQUENCE C START WITH 9758;
Source table breaks at 257
--
A.B = 25
CREATE SEQUENCE C START WITH 1;
Source table breaks at 257
--

I have tested this in both 10g XE 10.2.0.1.0 and 9i EE 9.2.0.8.0.

Could someone verify and/or explain this?