-
pl sql error :ORA-00933: SQL command not properly ended
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
========================
sonofsita
http://www.ordba.net
-
And your question is?
Oh, btw, your procedure has an error - when it tries to execute line 63 it can't because your SQL command constructed in line 62 makes has semantic error.
v_sql1:= 'select count(*) from '||v_table_name_1 ||'where owner = '''||v_db_user||'''';
You need a space between the quotation mark and "where".....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
thanks a lot...I was missing that and there you have helped me.. Thanks again
sonofsita
http://www.ordba.net
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
|