dbms_sqlparse problem

[mb]

Im having problems with this anoymous pl/sql block. Its probably something blindingly obvious but I keep getting this error message:

DECLARE
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 9



DECLARE
UserCount integer;
SchemaUserName dba_users.username%TYPE;
cid integer;
userCheckSql varchar2(800);
BEGIN
SchemaUserName := upper('girs');
userCheckSql := 'SELECT count(1) into UserCount FROM dba_users WHERE username = '' :s '' ';
EXECUTE IMMEDIATE userCheckSql INTO UserCount USING SchemaUserName;
IF (UserCount = 1)
THEN
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'DROP USER girs cascade' , dbms_sql.native);
dbms_sql.close_cursor(cid);
ELSE
dbms_output.put_line('No user girs to drop');
END IF;
END;
/

Has anyone got any ideas?

[ales]

userCheckSql := 'SELECT count(1) FROM dba_users WHERE username = :s';

You don't have to enclose character bind variable between single quote marks and you must not use SELECT ... INTO in the SQL for EXECUTE IMMEDIATE. That caused the error.
Ales



[Edited by ales on 04-12-2002 at 08:31 AM]

[mb]

Thanks that seems to be the problem