0

I am trying to execute a PL/SQL procedure without success. It shows me "inexistent table". What can I do?

CREATE OR REPLACE PROCEDURE INTEGRATION(tableName varchar2) IS
BEGIN
     MERGE INTO tableName alert
     USING ...
Ciro
  • 253
  • 2
  • 5
  • 23
  • 5
    Possible duplicate of [UPSERT into table with dynamic table name](http://stackoverflow.com/questions/6762376/upsert-into-table-with-dynamic-table-name) – Tim Biegeleisen Nov 09 '16 at 09:53
  • Or this one: [Oracle stored function - pass table name as parameter](http://stackoverflow.com/questions/29958998/oracle-stored-function-pass-table-name-as-parameter). – William Robertson Nov 09 '16 at 10:23

1 Answers1

0

you can try with execute immediate statement and concatenate the tablename passed from procedure

see example from oracle site

CREATE OR REPLACE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition  IN VARCHAR2 DEFAULT NULL) AS
   where_clause  VARCHAR2(100) := ' WHERE ' || condition;
   v_table      VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
  SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name);
END;
/
BEGIN
  delete_rows('employees_temp', 'employee_id = 111');
END;
/

for other info see oracle site

execute immediate

Giuseppe
  • 36
  • 2