|
-
It's because grant is data control language(not allowed in procedures) and not DML.
You have to use EXECUTE IMMEDIATE for 8 version or
use dbms_sql package for version bellow 8:
here a procedure for delete a oracle user (data definition language)
=====================================
PROCEDURE DeleteUser(user_id IN NUMBER,
errorcode OUT NUMBER)
IS
v_CreateCommand VARCHAR2(120);
v_Dummy INTEGER;
v_CursorID INTEGER;
ausername VARCHAR2(80);
BEGIN
errorcode :=0;
ausername:='';
SELECT login_id INTO ausername FROM broker_codes
WHERE brc_id = user_id;
UPDATE BROKER_CODES
SET login_id = ''
Where brc_id = user_id;
COMMIT;
v_CreateCommand := 'DROP USER ' || ausername ;
v_CursorID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID, v_CreateCommand, DBMS_SQL.NATIVE);
v_dummy := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
WHEN OTHERS THEN
errorcode:= SQLCODE;
End DeleteUser;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|