INSERT INTO WORKGROUP_DEPARTMENTS(ID, DEPT_NAME, DEPT_TYPE)
VALUES (SEQ_WGDPT.NEXTVAL, 'Staff', 3);

INSERT INTO WORKGROUP_SECTIONS(ID, SECT_NAME, WGDPT_ID)
VALUES (SEQ_WGSEC.NEXTVAL, 'Staff 1',
        (SELECT ID FROM WORKGROUP_DEPARTMENTS
         WHERE DEPT_NAME = 'Staff'
         AND DEPT_TYPE = '3')
);

INSERT INTO WORKGROUPS(NAME, UNIT_TYPE, WGSEC_ID)
VALUES ('Staff Room', 3,
        (SELECT ID FROM WORKGROUP_SECTIONS
         WHERE SECT_NAME = 'Staff 1')
);

SELECT SEQ_WGDPT.CURRVAL INTO SeqVal FROM DUAL

SQL> create table t1(id number);

Table created.

SQL> create table t2(id number, fk number);

Table created.

SQL> create table t3(id number, fk number);

Table created.

SQL> create sequence t1_s;

Sequence created.

SQL> create sequence t2_s start with 100; -- I used a different number just to verify that we are getting the right values in the right places

Sequence created.

----------------------- Start of first script -----------------------------

SQL> var x1 number;

SQL> insert into t1 values(t1_s.nextval) returning id into :x1;

1 row created.

----------------------- End of first script -------------------------------

---------------------- Start of second script -----------------------------

SQL> var x2 number;

SQL> insert into t2 values (t2_s.nextval, :x1) returning id into :x2;

1 row created.

---------------------- End of second script -------------------------------


---------------------- Start of third script ------------------------------

SQL> insert into t3 values (1, :x2);

1 row created.

---------------------- End of third script --------------------------------

-- Validation that the process worked as expected

SQL> select *
  2  from   t1;

        ID
----------
         1

SQL> select * from t2;

        ID         FK
---------- ----------
       100          1

SQL> select * from t3;

        ID         FK
---------- ----------
         1        100

SQL>

SQL> CREATE TABLE testit (id INT);

Table created.

SQL> CREATE SEQUENCE testit_seq;

Sequence created.

SQL> var testit_id NUMBER
SQL> 
SQL> INSERT INTO testit VALUES (testit_seq.NEXTVAL)
  2  RETURNING id INTO :testit_id;

1 row created.

SQL> print :testit_id

 TESTIT_ID
----------
         1