CREATE OR REPLACE procedure sp_process_terminated_users(p_user in varchar2,p_pwd varchar2,p_db_name varchar2)
as
TYPE RefCurTyp1 IS REF CURSOR;
cv1 RefCurTyp1;

TYPE RefCurTyp2 IS REF CURSOR;
cv2 RefCurTyp2;

v_database_name db_names.database_name%type;

v_sql varchar2(1000);
v_sql1 varchar2(1000);

v_db_user all_users.username%type;
v_terminated_user terminated_users.user_name%type;
v_drop_user terminated_users.user_name%type;

v_table_name varchar2(30);
v_table_name_1 varchar2(30);

v_export_file varchar2(200);
v_export_user varchar2(100);

v_cmd_string varchar2(1000);
v_count_objects number;



cursor db is
select database_name from db_names;

cursor tu is
select user_name from terminated_users;

begin

v_cmd_string:='sqlldr '||p_user||'/'||p_pwd||'@'||p_db_name ||' control=c:\\terminated_users.ctl';
Execute_cmd(v_cmd_string);

open tu;
loop
fetch tu into v_terminated_user;
exit when tu%notfound;
open db;
loop
fetch db into v_database_name;
exit when db%notfound;
v_table_name:='all_users@'||v_database_name;
v_sql:= 'select username from '||v_table_name||' where username ='''||v_terminated_user||'''';
OPEN cv1 FOR
v_sql;
fetch cv1 into v_db_user;

if v_db_user is null then
INSERT INTO TERMINATED_USERS_STATUS(USER_NAME, STATUS_COMMENT)
VALUES(v_terminated_user,'Not In '||v_database_name||' Database');
commit;
elsif (v_db_user is not null ) then

-- select count(*) into v_count_objects from all_objects where owner = v_db_user;
v_table_name_1:='all_objects@'||v_database_name;
v_sql1:= 'select count(*) from '||v_table_name_1 ||'where owner = '''||v_db_user||'''';
OPEN cv2 FOR v_sql1;
loop
fetch cv2 into v_count_objects;
exit when cv2%notfound;
if v_count_objects = 0 then
insert into TERMINATED_USERS_DETAILS (TERMINATED_USER_NAME,
DATABASE_NAME,
OWNED_OBJECTS_COUNT
)
values(v_db_user,
v_database_name,
v_count_objects);
commit;
elsif v_count_objects > 0 then
v_export_file:=v_db_user||'_'||v_database_name||'_'||to_char(sysdate,'mmddyyyy')||'.dmp';
v_export_user:= p_user||'/'||p_pwd||'@'||v_database_name;


if v_database_name='SRINIVAS' then
v_cmd_string:=('d:\\oracle\\ora81\\bin\\exp '||v_export_user ||' file=c:\\'||v_export_file ||' OWNER='||v_db_user||' log=c:\\'||v_db_user||'_'||v_database_name||'.log');

elsif v_database_name='KNOW9I' then
v_cmd_string:='d:\\oracle\\ora91\\bin\\exp '||v_export_user ||' file=c:\\'||v_export_file ||' OWNER='||v_db_user||' log=c:\\'||v_db_user||'_'||v_database_name||'.log';

end if;

Execute_cmd(v_cmd_string);
insert into TERMINATED_USERS_DETAILS (TERMINATED_USER_NAME,
DATABASE_NAME,
OWNED_OBJECTS_COUNT,
EXPORT_DEST_AND_FILE_NAME,
EXPORT_DATE
)
values (v_db_user,
v_database_name,
v_count_objects,
v_export_file,
sysdate
);
commit;

update TERMINATED_USERS_STATUS
set STATUS_COMMENT = 'User data Exported.Not Dropped'
where USER_NAME = v_db_user;
commit;
end if;
end loop;
close cv2;
end if;
end loop;
close db;
end loop;
close tu;

exception
when no_data_found then
dbms_output.put_line('No users in the database to drop');
end;
/

==============
scott@SRINIVAS >exec SP_PROCESS_TERMINATED_USERS('scott','tiger','srinivas');
I am done
BEGIN SP_PROCESS_TERMINATED_USERS('scott','tiger','srinivas'); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SCOTT.SP_PROCESS_TERMINATED_USERS", line 63
ORA-06512: at line 1
========================