Oracle grant select from One schema to other schema

set heading off pagesize 0 feedback off echo off verify off linesize 200

ACCEPT schema_name CHAR prompt 'SCHEMA_NAME refers to the schema to duplicate privileges from :'
ACCEPT grantee_name CHAR prompt 'GRANTEE_NAME refers to the SCHEMA/role to grant privileges to :'

spool new_&&grantee_name&&schema_name..sql
spool &&grantee_name&&schema_name..sql

select (CASE
WHEN
object_type in ('VIEW','MATERIALIZED VIEW') THEN 'GRANT SELECT on '
WHEN
OBJECT_TYPE IN ('SEQUENCE') THEN 'GRANT SELECT ON '
WHEN
OBJECT_TYPE LIKE 'DIRECTORY' THEN 'GRANT READ ON DIRECTORY '
ELSE 'NOT AN APPROPRIATE OBJECT'END ) ||OWNER||'."'||OBJECT_NAME||'" TO &&grantee_name ;'
from dba_objects a
where owner='&&schema_name'
AND OBJECT_TYPE IN ('DIRECTORY','VIEW','MATERIALIZED VIEW','SEQUENCE')
and not exists (select 1 from dba_tab_privs b where a.object_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');



-- Now we do tables...we did not do tables from all objects because we need to

-- limit it to tables and not external tables or nested tables



select 'grant select on '||a.owner||'."'||a.table_name||'" to &&grantee_name ;' from dba_tables a
where owner='&&schema_name' and a.table_name not in
(select axt.table_name from dba_external_tables axt
where axt.owner=a.owner)
and a.table_name not in
(select nst.table_name from dba_nested_tables nst
where nst.owner=a.owner)
and not exists (select 1 from dba_tab_privs b where
a.table_name=b.table_name
and b.owner=a.owner
and b.grantee='&&grantee_name');

-- Now we do the external tables

select 'grant select on '||owner||'."'||table_name||'" to &&grantee_name ;' from dba_external_tables a
where owner='&&schema_name'
and not exists (select 1 from dba_tab_privs b where a.table_name=b.table_name
and b.owner=a.owner and b.grantee='&&grantee_name');

-- Now we tackle objects not owned by the schema_name
-- exclude directories because they require a DIRECTORY key word in grant

select 'grant select on ' || owner||'."'||table_name||'" to &&grantee_name ;'
from dba_tab_privs a
where grantee='&&schema_name' and not exists (select 1 from dba_tab_privs b
where a.privilege=b.privilege and a.privilege='SELECT'
and a.table_name=b.table_name and b.grantee='&&grantee_name')
and not exists (select 1 from dba_directories c
where c.directory_name=a.table_name
and a.owner=c.owner)
;



spool off;
set heading on verify on feedback on echo on
prompt check script &&grantee_name&&schema_name..sql
undef schema_name
undef grantee_name

Oracle Plsql REF CURSOR

Working with REF CURSOR
ref cursor example



ref cursor are used only for select



from scott

set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname user_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select table_name from user_tables ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_tabname;

open c_ref2 for 'select count(1) from "'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/



Table_name Row_Counts
========== =========
DEPT 4
BONUS 0
SALGRADE 5
MYEMP 2
EMP 14
ANUJ_BIG_TABLE 9999
ANUJ_BIG_TABLE1 9999
AA 14
ANUJ10 0
MYEMP_WORK 0
MYEMP_WORK 0
.
============end==================

********************************************************************************


from sys



set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname dba_tables.table_name%TYPE;
v_owner dba_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select owner,table_name from dba_tables where owner='SCOTT' ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_owner,v_tabname;

open c_ref2 for 'select count(1) from "'||v_owner||'"."'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/



===================================

for testing


begin
dbms_output.put_line('select count(1) from "'||'v_owner'||'"."'||'v_tabname'||'"') ;
end;
/