Odd Oracle sqlplus command Error

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

Odd Oracle sqlplus command Error

Postposted on Wed May 11, 2011 3:27 pm

We're running a script with sqlplus taking a .sql file to execute to anonymize some data in tables that have the column 'name'. The code works for certain schemas while others fail with the following cryptic message:

Code: Select all

declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 8



This is the code we're executing (we pass in the schema name just fine):

Code: Select all
1 set serveroutput on
  2 DEFINE SCHEMA=&1;
  3 declare
  4 cursor c1 is select owner, table_name from dba_tab_columns c where c.column_name='NAME' and c.owner='&SCHEMA' and c.table_name in (select distinct table_name from dba_tables where owner=c.owner and table_name=c.table_name) order by 1;
  5 v_string varchar2 (255);
  6    begin
  7       for vctr1 in c1 loop
  8          v_string :='update '||vctr1.owner||'.'||vctr1.table_name||' set NAME=null;';
  9          --dbms_output.put_line(v_string);
 10          execute immediate v_string;
 11       end loop;
 12    end;
 13 /


Any ideas?

Edit: got it -- trailing semi-colon in the string. Thanks everyone for your time.
Image
Nitrodist
Grand Gerbil Poohbah
 
Posts: 3280
Joined: Wed Jul 19, 2006 1:51 am
Location: Minnesota

Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 1 guest