Hi Folks:
I am trying to run an insert statement inside of APEX that has several sub-queries.
The data brought back from the x4 queries is based on finding empty storage spots. After each insert the 'next' free storage spot is chosen.
The problem I'm running into is that it appears APEX is doing a commit at the end and so all of the inserted rows have the same location. Here is an example of the data:
What is should be (as an example) the second insert would be something like Column_of_Box = 2 (since the first position 1 is taken). Is there a way in Apex to get Apex to commit as it goes along instead of just at the end? Here is the insert code:
BEGIN
INSERT INTO GENOME.TISSUE_INVENTORY_TEST (SAMPLE_NUMBER,
SPECIES,
ANIMAL,
LOCATION,
TISSUE,
COLLECTION_DATE,
PROCESSING_DATE,
TOWER,
BOX,
ROW_OF_BOX,
COLUMN_OF_BOX,
ALMOST_GONE,
DEPLETED)
SELECT TISSUE_SAMPLE_NUMBERS_TEST.NEXTVAL,
t.species,
t.animal,
t.location,
t.tissue,
t.collection_date,
t.processing_date,
(SELECT LTRIM (SUBSTR (TOWER_BOX_ROW_COLUMN, 1, 4)) FROM GENOME.TISSUE_INVENTORY_NEXT_CELL) AS TOWER,
(SELECT LTRIM (SUBSTR (TOWER_BOX_ROW_COLUMN, 5, 1)) FROM GENOME.TISSUE_INVENTORY_NEXT_CELL) AS BOX,
(SELECT LTRIM (SUBSTR (TOWER_BOX_ROW_COLUMN, 6, 1)) FROM GENOME.TISSUE_INVENTORY_NEXT_CELL) AS ROW_OF_BOX,
(SELECT LTRIM (SUBSTR (TOWER_BOX_ROW_COLUMN, 7, 2)) FROM GENOME.TISSUE_INVENTORY_NEXT_CELL) AS COLUMN_OF_BOX,
'NO',
'NO'
FROM GENOME.TISSUE_INVENTORY_ANIMALS t,
GENOME.TISSUE_INVENTORY_NEXT_CELL,
GENOME.TOWER_BOX_COLUMNS C
WHERE C.COLUMN_of_BOX <= t.number_of_samples;
END;
I've tried adding a commit; statement before and after with no luck.
Thanks for any advice. The code for the table being inserted into is included as an attachment if needed.
Thank you,
Matthew
Tissue_inventory_test_Table_SQL.txt (902 Bytes)