Inserting Data into Corresponding Oracle Tables and Listing Identity Values
I have a table of intermediate data that is populated with procedural code.
TABLE BatchRecord{
BatchRecordID NUMBER PRIMARY KEY,
BatchID NUMBER, --Assigned by the procedural code
RecordID NUMBER, --Relative to the batch
FieldID NUMBER, --Assigned by the procedural code; FK to another table
Value VARCHAR2(MAX)
Instance NUMBER}
Each BatchRecord must then be converted to three related tables: Records, RecordFields, FieldValues.
TABLE Record{
RecordID NUMBER PRIMARY KEY,
BatchID NUMBER, --Same from BatchRecords}
TABLE RecordFields{
RecordFieldID NUMBER PRIMARY KEY,
RecordID NUMBER, --FK from Records
FieldID NUNBER --Same from BatchRecords}
TABLE FieldValues{
RecordFieldID NUMBER PRIMARY KEY,
Instance NUMBER PRIMARY KEY, --Same from BatchRecords
Value VARCHAR2(MAX) --Same from BatchRecords}
In SQL Server, I can accomplish this with a statement MERGE
and use OUTPUT
to grab the newly inserted keys:
CREATE TABLE #InsertedRecords(RecordID INT, NewRecordID INT);
MERGE INTO Records USING (
SELECT RecordID
FROM BatchRecords
WHERE BatchID = @BatchID
GROUP BY RecordID) AS BR ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (BatchID)
VALUES (@BatchID)
OUTPUT BR.RecordID, INSERTED.RecordID INTO #InsertedRecords;
A similar approach is used for inserting RecordFields, and the operator is INSERT...SELECT
used for inserting into FieldValues.
However, in Oracle it is not possible to use RETURNING
(equivalent OUTPUT
) in a statement MERGE
or even in a statement SELECT...INTO
.
Is there a way to accomplish the same task in Oracle and also avoid looping FORALL
as the total number of BatchRecords can exceed several million?
source to share
You can try using oracle PIPELINE function to select all records from BatchRecord and then rows of rows to different tables. I could show an example if necessary.
EDIT
create or replace package test_pkg AS
TYPE REP_CURS IS REF CURSOR;
TYPE output_REC IS RECORD(
RecordID_ number,
BatchID_ number);
TYPE output_TAB IS TABLE OF output_REC;
FUNCTION Get_Data RETURN output_TAB
PIPELINED;
END test_pkg;
CREATE OR REPLACE PACKAGE BODY test_pkg IS
FUNCTION Get_Data RETURN output_TAB
PIPELINED IS
output_REC_ output_REC;
rep_lines_ REP_CURS;
stmt_ VARCHAR2(5000);
table_rec_ BatchRecord%ROWTYPE;
begin
stmt_ := ' (select BatchRecordID,BatchID ....Instance from BatchRecord) ';
OPEN rep_lines_ FOR stmt_;
LOOP
FETCH rep_lines_
INTO table_rec_;
EXIT WHEN rep_lines_%NOTFOUND;
output_REC_.RecordID_ := <<whatever valu that you want>;
output_REC_.BatchID_ := table_rec_.BatchID;
PIPE ROW(output_REC_);
END LOOP;
CLOSE rep_lines_;
RETURN;
exception
when others then
DBMS_OUTPUT.put_line('Error:' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
DBMS_UTILITY.FORMAT_ERROR_STACK ||
DBMS_UTILITY.FORMAT_CALL_STACK);
END Get_Data;
END test_pkg;
I have shown an example for inserting into a record. You must also do this for the other two tables. Hope this helps!
source to share