Passing table name in dynamic SQL

From: <mikep_at_quebec.net>
Date: 1997/11/19
Message-ID: <879890531.25781@dejanews.com>#1/1

I'm trying to pass a Table name in a dynamic SQL call through PL/SQL. But I keep getting the error message Invalid Table name.

Here's the snippet:

procedure add_page_form (TABLE_IN IN VARCHAR2) IS

      u_cur    INTEGER;
      rows_processed INTEGER;
      unum NUMBER;
      uname VARCHAR2(32);

BEGIN

      u_cur := dbms_sql.open_cursor;
      dbms_sql.parse(u_cur,
         'SELECT num, name FROM :my_table',
          dbms_sql.v7);

      dbms_sql.bind_variable(u_cur, ':my_table', TABLE_IN );

      dbms_sql.define_column(u_cur, 1, unum);
      dbms_sql.define_column(u_cur, 2, uname, 32);



      rows_processed := dbms_sql.execute(u_cur);
      LOOP
         IF dbms_sql.fetch_rows(u_cur) = 0
         THEN
            EXIT;
         ELSE
            dbms_sql.column_value(u_cur, 1, unum);
            dbms_sql.column_value(u_cur, 2, uname);

            htp.print('<OPTION VALUE="'||unum||'">'||uname);
         END IF;
      END LOOP;

      dbms_sql.close_cursor(u_cur);


    EXCEPTION
       WHEN OTHERS THEN dbms_sql.close_cursor(u_cur);
       htp.print('Raised exception');

END ADD_PAGE_FORM;

And here's the error I receive:

Tue Nov 18 16:43:02 1997

OWS-05101: Agent : execution failed due to Oracle error 903
ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "COMMON.PAG", line 20
ORA-06512: at line 1

  OWA SERVICE: COMMON
  PROCEDURE: common.pag.add_page_form
  PARAMETERS:

TABLE_IN:
INTDEV.USER_LEVEL

INTDEV.USER_LEVEL is a valid table and I can staticly execute this no problem.

If anyone can shine some light on why this isn't working I would really appreciate it.

I'm using WG73.

Please respond via e-mail since my newsfeed is somewhat flaky.

mikep_at_quebec.net

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Wed Nov 19 1997 - 00:00:00 CST