DBMS_SQL script not working

Hi,

I am running an Oracle 8.0.5.0.0

I want to run the following script but it does not work. I used DBMS_OUTPUT statements to check that the DML strings are being created properly and the username I input does exists, and the cursor is returning a value. If I place a DBMS_OUTPUT string after the first PARSE call nothing is displayed not even hard coded text could anybody tell me why.

Yes serveroutput has been set and I have tested that I can get other DBMS_OUTPUT messages out prior to the first PARSE statement. The only thing I have noticed is that the PARSE is for V7 but I have not any documentation to tell me otherwise. I have also replaced V7 with V8 but this does not work.

Anybody any ideas to get this script to execute ?

DECLARE drop_str VARCHAR2(2000);
v_dbms_sql NUMBER;
v_result INTEGER;

v_admin VARCHAR2(6) := '_ALL';
v_public VARCHAR2(7) := '_PUBLIC';

cursor c_users is
select username
from all_users
where username like '&USERNAME%';

BEGIN  v_dbms_sql := dbms_sql.OPEN_CURSOR;

 FOR c_users_rec IN c_users LOOP

• Drop site

  drop_str := 'drop user '||c_users_rec.username||' casade';

  DBMS_SQL.PARSE(v_dbms_sql,drop_str,DBMS_SQL.V7);

  v_result := DBMS_SQL.EXECUTE(v_dbms_sql);

• Drop admin user

  drop_str := 'drop user '||c_users_rec.username||v_admin||' casade';   DBMS_SQL.PARSE(v_dbms_sql,drop_str,DBMS_SQL.V7);   v_result := DBMS_SQL.EXECUTE(v_dbms_sql);

• Drop public user

  drop_str := 'drop user '||c_users_rec.username||v_public||' casade';   DBMS_SQL.PARSE(v_dbms_sql,drop_str,DBMS_SQL.V7);   v_result := DBMS_SQL.EXECUTE(v_dbms_sql);

• Drop site from wwv_modules$ table

  drop_str := 'delete from wwv_modules$ where name = '||c_users_rec.username;
  DBMS_SQL.PARSE(v_dbms_sql,drop_str,DBMS_SQL.V7);   v_result := DBMS_SQL.EXECUTE(v_dbms_sql);

END LOOP;   DBMS_SQl.CLOSE_CURSOR(v_dbms_sql);

EXCEPTION
  WHEN OTHERS THEN
     DBMS_SQl.CLOSE_CURSOR(v_dbms_sql);
END;
/

--
Regards,

SDG