EXECUTE IMMEDIATE with USING clause giving errors

Question

All,

I am very new to stored procedures in general but I am struggling especially with those in Oracle. I have created a very simple example of what I am trying to accomplish and I am still getting the same error with this simplified version.

The example stored procedure is as follows:

CREATE OR REPLACE PROCEDURE ashish_test
AUTHID CURRENT_USER IS
BEGIN
     DECLARE
          v_tab     VARCHAR2(50);
          v_strSQL  VARCHAR2(50);
     BEGIN
          v_strSQL := 'SELECT * FROM :1';
          v_tab    := 'ex.emp';
          EXECUTE IMMEDIATE v_strSQL USING v_tab;
     END;
END;

When I call the above stored procedure using CALL ashish_test(), I get :

Error Message http://web1.twitpic.com/img/12831839-06a3ea536df5d5a0a839eb83d9e59d25.4a3936b8-scaled.jpg

Based on this article (Look for Example 7-1), USING keyword should replace the numbered placeholder (:1) within v_strSQL with the value stored in v_tab. However, I keep getting invalid table error. I am guessing it's because EXECUTE IMMEDIATE is unable to replace the placeholder with the value for some reason but I am not sure why that is. Does anyone know if I am doing something stupid here?

I am running this on Oracle 10g database & using PL/SQL Developer.

Answer 1

The USING clause is only for bind variables (i.e. where you would use column names in a select statement), not table names. Typical usage would look like this:

Select col1 from table1 where col2 = :a

If you want to use variable table names use something like this:

         v_tab    := 'ex.emp';
         v_strSQL := 'SELECT * FROM ' || v_tab;
         EXECUTE IMMEDIATE v_strSQL;