Re: Variable not declared for delete command

mikek_at_backweb.com (Mike) wrote in message news:<bf59f095.0111130618.4e96ae9a_at_posting.google.com>...
> look at this, from inside a stored procdure
>
> nCursor := dbms_sql.open_cursor;
> dbms_sql.parse(nCursor,'create table targets (tid
> int)',dbms_sql.native);
> rows_proc := dbms_sql.execute(nCursor);
> dbms_sql.close_cursor(nCursor);
> open cur;
> loop
> fetch cur into bw_content_id ,bw_profile_id ,bw_target_id1 ,
> bw_target_id2 ,bw_target_id3 ,bw_target_id4 ,bw_target_id5
> ,bw_target_id6 ,
> bw_target_id7 ,bw_target_id8 ,bw_target_id9 ,bw_target_id10
> ,bw_target_id11 ,
> bw_target_id12 ,bw_target_id13 ,bw_target_id14 ,bw_priority
> ,bw_content_version_cnt ,
> bw_expiry_date ,bw_infopak_id ,bw_last_modified_date ,bw_content_type
> ,
> bw_languages_flag,nrowid;
> EXIT WHEN cur%NOTFOUND;
> delete from targets;
>
> getting a must declare variable on targets error when compiling proc.
> any ideas please ?

Validation is done at compile-time, not at run time. If the table targets doesn't exist prior to the _compilation_ of your sp (and as you are creating this table in the sp this is quite likely) you have no other choice than to use dynamic sql to delete from the table also

Hth

Sybrand Bakker
Senior Oracle DBA Received on Wed Nov 14 2001 - 12:03:21 CET