UPSERT into table with dynamic table name

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.

MERGE works perfectly fine with Native dynamic SQL (EXECUTE IMMEDIATE):

create table so_test(pk number not null primary key, value varchar2(20));

insert into so_test(pk, value) values(1, 'one');

declare
  l_SQL varchar2(4000);
  l_tablename varchar2(4000) default 'so_test';
begin
  l_SQL := 'merge into ' || l_tablename || ' target' ||
    ' using (select 1 pk, ''eins'' value from dual union all
             select 2 pk, ''zwei'' value from dual) source
      on (target.pk = source.pk)
      when matched then 
        update set target.value = source.value
      when not matched then
        insert values(source.pk, source.value)      
  ';
  dbms_output.put_line(l_sql);
  execute immediate l_SQL;
end; 

Could you please post the error message you get when using MERGE?

You should consider writing this to use Static SQL instead of passing the table name at Run Time. Is there a valid reason why you don't know what table you would be merging into till run-time?

As for debugging the issue...

How is the function FGETTABLENAME defined in your code? This is what I came up with which mimics that scenario. I would suggest Using %type (instead of RAW for Number Types) declarations and removing the Double Quotes from the procedure Names.

    create or replace function FGETTABLENAME(
        POBJECTID in user_objects.object_id%type
    ) return user_objects.object_name%type
    as
      v_object_name user_objects.object_name%type;
    begin
      select object_name
        into v_object_name
        from all_objects
        where object_id = pobjectid;
       return v_object_name;
    end;
    /

SQL> select object_id, object_name from user_objects;

 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------
     52641 TFIVE
     52644 SPINSERTDATA
     52643 PROCEDURE
     52645 FGETTABLENAME
     52554 GET_SAL_EMP
     52559 T1

SQL> select FGETTABLENAME(52641) from dual;

FGETTABLENAME(52641)
--------------------------------------------
TFIVE

You can add DBMS_OUTPUT.PUT_LINE statements to your code after

vTableName := FGETTABLENAME(POBJECTID => pObjectID); 

and 

vQueryUpdate := 'UPDATE '      || vTableName || ' SET "VALUE" = :1';
      vQueryInsert := 'INSERT INTO ' || vTableName || ' ("DTTIME", "VALUE") VALUES (:1, :2)';

or Trace your code to see the actual SQL statements being fired to your Database.

Firstly, you don't have a WHERE in your UPDATE so it will update every row of the table.

Secondly, have you used a mixed case table name. If you do a

CREATE TABLE "testOne" (ID NUMBER);

then the table name will be stored as testOne. But when you do an UPDATE testOne is will be treated as UPDATE TESTONE and you'll get a "no such table" error.

Avoid using mixed case table names. If you absolutely must, then you'll need to quote them in the dynamic SQL statement