Re: DBMS_SQL script not working

you have mistyped the keyword CASCADE (casade) in your generated SQL-statement. So oracle raises an exception and jumps to your exception-code at the end of the script. The statements after the parse-command will never reached in this case.

Hans Quick

Simong wrote:

> 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
Received on Thu Jul 13 2000 - 00:00:00 CDT