declare
--找到8000 USER 的出貨組織,並同時轉換成新 user_id ,io , role_id
cursor cur is select * from
( select XXCONVERT_8000_ID.USER_ID(USER_ID) USER_ID,
XXCONVERT_8000_ID.IO_ID(ORGANIZATION_ID) ORGANIZATION_ID,
XXCONVERT_8000_ID.WSH_ROLE_ID(ROLE_ID) ROLE_ID
from wsh_grants_v@prod8000
where END_DATE is null and user_id<>4501) where ROLE_ID is not null and USER_ID is not null
group by USER_ID,ORGANIZATION_ID,ROLE_ID;
cursor cur is select * from
( select XXCONVERT_8000_ID.USER_ID(USER_ID) USER_ID,
XXCONVERT_8000_ID.IO_ID(ORGANIZATION_ID) ORGANIZATION_ID,
XXCONVERT_8000_ID.WSH_ROLE_ID(ROLE_ID) ROLE_ID
from wsh_grants_v@prod8000
where END_DATE is null and user_id<>4501) where ROLE_ID is not null and USER_ID is not null
group by USER_ID,ORGANIZATION_ID,ROLE_ID;
--非公開wsh grant API
rec_grant WSH_RU_GRANTS_PVT.Grant_Type;
rec_init WSH_RU_GRANTS_PVT.Grant_Type;
p_rowid varchar2(50);
p_grant_id varchar2(50);
p_return_status varchar2(50);
p_user_id number:=1090;
begin
for rec in cur loop
rec_init WSH_RU_GRANTS_PVT.Grant_Type;
p_rowid varchar2(50);
p_grant_id varchar2(50);
p_return_status varchar2(50);
p_user_id number:=1090;
begin
for rec in cur loop
--初始清空
rec_grant := rec_init;
rec_grant.GRANT_ID :=null;
rec_grant.USER_ID :=rec.USER_ID;
rec_grant.ROLE_ID :=rec.ROLE_ID;
rec_grant.ORGANIZATION_ID :=rec.ORGANIZATION_ID;
rec_grant.START_DATE :=sysdate;
rec_grant.END_DATE :=null;
rec_grant.CREATED_BY :=p_user_id;
rec_grant.CREATION_DATE :=sysdate;
rec_grant.LAST_UPDATED_BY :=p_user_id;
rec_grant.LAST_UPDATE_DATE :=sysdate;
rec_grant.LAST_UPDATE_LOGIN :=null;
rec_grant := rec_init;
rec_grant.GRANT_ID :=null;
rec_grant.USER_ID :=rec.USER_ID;
rec_grant.ROLE_ID :=rec.ROLE_ID;
rec_grant.ORGANIZATION_ID :=rec.ORGANIZATION_ID;
rec_grant.START_DATE :=sysdate;
rec_grant.END_DATE :=null;
rec_grant.CREATED_BY :=p_user_id;
rec_grant.CREATION_DATE :=sysdate;
rec_grant.LAST_UPDATED_BY :=p_user_id;
rec_grant.LAST_UPDATE_DATE :=sysdate;
rec_grant.LAST_UPDATE_LOGIN :=null;
--呼叫API建立grant
begin
WSH_RU_GRANTS_PVT.insert_Row(
p_grant_record =>rec_grant,
x_rowid =>p_rowid,
x_grant_id =>p_grant_id,
x_return_status =>p_return_status) ;
commit;
-- DBMS_OUTPUT.put_line(p_return_status);
begin
WSH_RU_GRANTS_PVT.insert_Row(
p_grant_record =>rec_grant,
x_rowid =>p_rowid,
x_grant_id =>p_grant_id,
x_return_status =>p_return_status) ;
commit;
-- DBMS_OUTPUT.put_line(p_return_status);
exception
when others then
DBMS_OUTPUT.put_line(p_return_status);
rollback;
end;
end loop;
end;
when others then
DBMS_OUTPUT.put_line(p_return_status);
rollback;
end;
end loop;
end;
全站熱搜
留言列表
Oracle (6)
