Re: Passing table name in dynamic SQL

mikep_at_quebec.net wrote:
>
> 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
>
> <A HREF="mailto:mikep_at_quebec.net">E-Mail</A>
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Hi,

are you sure that the one executing this procedures has granted 'SELECT'-rights on 'INTDEV.USER_LEVEL'?

-- 
Regards

Matthias Gresz    :-)