0

Possible Duplicate:
UPSERT into table with dynamic table name

The following procedure is declared as so: 

CREATE OR REPLACE  
PROCEDURE STUFF(tableToQuery VARCHAR2) AS  
BEGIN  
    MERGE INTO myTable m  
    USING (select * from tableToQuery)  t  
    ON (m.id = t.id)  
...  --other stuff
END STUFF;

I receive an ORA-00903 error that states the table name is invalid. My question is how do I get the value that resides within tableToQuery to equate to a valid table name in the select statement? Assume that I do not know the table name ahead of time.

UPDATE

The function compiles now, however I currently receive the unknown keyword error at the end of my function.

Community
  • 1
  • 1
Woot4Moo
  • 23,987
  • 16
  • 94
  • 151
  • "unknown keyword" indicates a syntax error. Alas we are not telepathic. So we cannot spot syntax errors in code you haven't posted. If you're using the dynamic SQL solution you will need to post the SQL statement which your procedure generates. – APC Jul 25 '12 at 11:06

1 Answers1

3

You need to use dynamic SQL: ie construct your SQL statement in a string and then pass that string to Oracle to execute using the execute immediate statement.

Something like

CREATE OR REPLACE  
PROCEDURE STUFF(tableToQuery IN VARCHAR2) AS
  s varchar2(100);
BEGIN
  s := 'MERGE INTO myTable m'
    || ' USING (select * from ' || tableToQuery || ')  t'
    || ' ON (m.id = t.id)';
  EXECUTE IMMEDIATE s;
  --other stuff
END STUFF;

should do the trick for you.

NB don't pass unverified data from end users (especially ones on the web) in the parameter tableToQuery as then you will have a SQL injection vulnerability!

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51