Re: 40 Error Text = PLS-00201: identifier 'TABLE_NAME' must be declared

From: Dan White <gndiving_at_newsguy.com>
Date: 2000/09/20
Message-ID: <8qak4i01sa0@edrn.newsguy.com>#1/1

Your assumption that you are declaring "table_name" at the top is false, Reason being is that Oracle needs to know the table_name at compile time. You will need to use dynamic SQL to accomplish your task.

procedure create_trigger
 ( table_name IN varchar2 )
IS

max_id_num number;
cur_handle PLS_INTEGER := dbms_sql.open_cursor;
sql_stmt varchar2(200);

result PLS_INTEGER;
BEGIN
sql_stmt := 'select max(id) into max_id_num from :tab_name'; DBMS_SQL.PARSE(CUR_HANDLE,SQL_STMT,DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(CUR_HANDLE,TAB_NAME,TABLE_NAME); result := DBMS_SQL.EXECUTE(cur_handle);
end;
/

In article <8pmfr5$riq$1_at_nnrp1.deja.com>, 1517westmore_at_my-deja.com says...
>
>ok im a newbie here so bear with me :)
>
>im trying to create a procedure to automate trigger/sequence creation.
>so far here is my code
>----
>procedure create_trigger
> ( table_name IN varchar2 )
>IS
>max_id_num number;
>begin
> dbms_output.put_line('*** begining trigger creation');
> select max(id) into max_id_num from table_name;
>/* max_id_num := select max(id) from table_name; */
>end;
>
>---
>
>error that oracle spits back at me
>
>Line # = 6 Column # = 40 Error Text = PLS-00201: identifier 'TABLE_NAME'
>must be declared
>Line # = 6 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
>
>im doing it exactly like pl/sql book says and i thought i _was_
>declaring it at the top.
>
>any light that anyone could shed would be very helpful.
>
>thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Dan White
programmer/analyst Received on Wed Sep 20 2000 - 00:00:00 CDT