Re: DBMS_SQL script not working

I tried a similar script and it worked.

The only suggestion that I can tell you are:

• check the case of usernames cursor c_users is select username from all_users where username like UPPER('&USERNAME')||'%'
• you don't need to drop _ALL and _PUBLIC because they belong to the answer set of cursor username% = (username, username_ALL, username_PUBLIC)

I hope that helps

--
be happy

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
Simong wrote in message <6vkb5.159$2I.112601_at_newsr2.u-net.net>...


>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

>

>