Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: PL/SQL Question, Execute Immediate

    I'm trying to write an SQL procedure to query a table, whose name will be supplied from another query elsewhere. Anyway, I have a little test case I'm working with now. I just can't seem to provide the table name as a bind variable. I keep getting "Invalid Table Name", but it looks ok when I examine the variable.

    Code:
    PROCEDURE TESTIMM
    IS
       f1        VARCHAR2 (100);
       f2        VARCHAR2 (100);
       myKey     NUMBER (3)     := 999;
       mytable   VARCHAR2 (100);
       
    -- Declare program variables as shown above
    BEGIN
    
       mytable := 'fooTable';
    
       EXECUTE IMMEDIATE 'SELECT a, b from :1 where fooID = :2 '
                    INTO f1, f2
                   USING mytable, myKey;
    
       DBMS_OUTPUT.put_line ('F1 = ' || f1 || ' F2 = ' || f2);
    
    END;
    Error -903: ORA-00903: invalid table name
    12:34:15 Execution failed: ORA-00903: invalid table name
    12:34:15 ORA-06512: at line 7

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest that you build the desired SQL statement completely in a VARCHAR2
    prior to getting close to invoking the EXECUTE IMMEDIATE.
    Output the variable using DBMS_OUTPUT & use SQL*PLUS to verify it is OK.
    In your case the SQL is not valid.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2004
    Posts
    14
    Ok, thanks! This seems to work. Guess I can't use a bind variable as the table name.

    Code:
    PROCEDURE TESTIMM
    IS
       f1        VARCHAR2 (100);
       f2        VARCHAR2 (100);
       myKey     NUMBER (3)     := 999;
       mytable   VARCHAR2 (100);
       mySQL     varchar2(200);
       
    -- Declare program variables as shown above
    BEGIN
    
       mytable := 'fooTable';
    
       mySQL := 'SELECT a,b from ' || mytable || ' WHERE fooID = ' || myKey ;
       EXECUTE IMMEDIATE mySQL
                    INTO f1, f2;
    
    
       DBMS_OUTPUT.put_line ('F1 = ' || f1 || ' F2 = ' || f2);
    
    END;

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Yes, for sure. You can only bind a variable value to the variable in where clause or "in" and "return" variables if you execute a function or procedure dynamically. You should bind whenever you can though.
    Last edited by Nocopy; 05-03-04 at 17:19.
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •