Problem with DBMS_SQL.PARSE

[mb]

I am trying to use the following procedure for dropping users from the database, the procedure compiles ok but does not seem to work.
create or replace Procedure test (username IN varchar2)
is

cursor_id integer;
sql_string varchar2(200);
BEGIN
cursor_id := DBMS_SQL.OPEN_CURSOR;
sql_string := 'drop user ' | | username | | ' cascade' ;

dbms_output.put_line(sql_string);

DBMS_SQL.PARSE(cursor_id, sql_string , DBMS_SQL.NATIVE);

dbms_output.put_line('user '| | username | | 'dropped');
DBMS_SQL.CLOSE_CURSOR (cursor_id);

EXCEPTION
WHEN OTHERS
THEN
dbms_sql.CLOSE_CURSOR (cursor_id);
dbms_output.put_line('an error occured');
END ;
/
show errors
/

[nealh]

I don't use DBMS_SQL much, but I would have thought you need to call DBMS_SQL.EXECUTE to actually run the SQL. PARSE will just parse it.

Of course, if you've got Oracle 8i you can do the whole thing much more simply using native dynamic sql. You just need:

execute immediate 'drop user xxxx cascade';

[mb]

Using the native dynamic sql is it possible to pass a parameter into the string?

ie. execute immediate 'drop user $user cascade';

[Heath]

nealh, when using DBMS_SQL, a DDL statement will get executed at parse time. There is no need to call DBMS_SQL.EXECUTE for DDL.

Heath

[nealh]

As I said, I don't use dbms_sql much nowadays. It is possible to pass a parameter to the NDS SQL statement:

execute immediate
'drop user '||user_name||' cascade;'

[nealh]

The original procedure gave an "insufficient privileges" error when I tried to run it under the SYSTEM user. However, when I added an "authid current_user", it worked:

create or replace Procedure test (username IN varchar2) authid current_user
is
cursor_id integer;
sql_string varchar2(200);
BEGIN
cursor_id := DBMS_SQL.OPEN_CURSOR;
sql_string := 'drop user ' || username || ' cascade' ;
dbms_output.put_line(sql_string);
DBMS_SQL.PARSE(cursor_id, sql_string , DBMS_SQL.NATIVE);
dbms_output.put_line('user '|| username || 'dropped');
DBMS_SQL.CLOSE_CURSOR (cursor_id);
END ;

You may not have seen the error message if you hadn't done a "set serveroutput on" when it was run.