Any better method to UPSERT into a table, provided :
- Data upsert at ~1 row/second
- Table Name is DYNAMIC, generated using ObjectID parameter passed to it
THE FOLLOWING PROCEDURE THROWS :
"ORA-00942: table or view does not exist"
CREATE OR REPLACE PROCEDURE
PROCEDURE "SPINSERTDATA"
(
pObjectID IN RAW,
pDateTime IN TIMESTAMP,
pValue IN BINARY_DOUBLE,
)
AS
BEGIN
Declare
vQueryInsert VARCHAR2(1000);
vQueryUpdate VARCHAR2(1000);
vTableName VARCHAR2(30);
Begin
vTableName := FGETTABLENAME(POBJECTID => pObjectID);
vQueryUpdate := 'UPDATE ' || vTableName || ' SET "VALUE" = :1';
vQueryInsert := 'INSERT INTO ' || vTableName || ' ("DTTIME", "VALUE") VALUES (:1, :2)';
EXECUTE IMMEDIATE vQueryInsert USING pDateTime, pValue;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
EXECUTE IMMEDIATE vQueryUpdate USING pValue;
End;
END "SPINSERTDATA";
- Apparently MERGE does not work as the TableName Cannot be dynamic
???
- I'm a newbie, my third month of coding, I scourged through
STACKOVERFLOW & Googled for 3 days now, trying all sorts of funny &
desperate solutions ... Even a very relevant link if you found one
would be honestly appreciated.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…