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.