Hi TOM,

I've created a function, that granting access to tables and views in given schema to given user.
In result, function returns a own-type table, that contains prepared statement and exception message, if thrown.

1. Creating types:

CREATE OR REPLACE TYPE STATEMENT_RESULT AS OBJECT 
( sql_statement varchar2(2000),
  statement_result varchar(2000)
);

CREATE OR REPLACE TYPE STATEMENT_RESULTS
AS TABLE OF STATEMENT_RESULT;

2. Function itself:

create or replace function GRANT_ACCESS_TO_SCHEMA_RES
(
  RESOURCE_SCHEMA IN VARCHAR2 
, GRANTEE IN VARCHAR2
, ACCESS_TYPE IN VARCHAR2
, ALLOW_FURTHER_SHARING IN NUMBER DEFAULT 0 
) return statement_results is 
--DIMS
  pragma autonomous_transaction;
  sql_to_execute varchar2(2000);
  results statement_results;
--BODY
BEGIN
  results := statement_results();
  --MANAGE TABLES
  for tab in (select table_name from all_tables where owner = RESOURCE_SCHEMA)
  loop
    --ADD NEW GRANT
    sql_to_execute:= 'GRANT ' || ACCESS_TYPE || ' ON ' || RESOURCE_SCHEMA || '.'  || tab.table_name || ' TO ' || GRANTEE;
    if ALLOW_FURTHER_SHARING = 1 then
      sql_to_execute:= sql_to_execute || ' WITH GRANT OPTION';
    end if;
    BEGIN
      EXECUTE IMMEDIATE sql_to_execute;
    EXCEPTION
      WHEN others THEN
        results.extend;
        results(results.last) := statement_result(sql_to_execute, SQLERRM);
    END;
  end loop;
  
  
  --MANAGE VIEWS
  for tab in (select view_name from all_views where owner = RESOURCE_SCHEMA)
  loop
    --ADD NEW GRANT
    sql_to_execute:= 'GRANT ' || ACCESS_TYPE || ' ON ' || RESOURCE_SCHEMA || '.'  || tab.view_name || ' TO ' || GRANTEE;
    if ALLOW_FURTHER_SHARING = 1 then
      sql_to_execute:= sql_to_execute || ' WITH GRANT OPTION';
    end if;
    
    BEGIN
      EXECUTE IMMEDIATE sql_to_execute;
    EXCEPTION
      WHEN others THEN 
        results.extend;
        results(results.last) := leo_sysdba.statement_result(sql_to_execute, SQLERRM);
    END;
  end loop;
  return results;
END GRANT_ACCESS_TO_SCHEMA_RES;

Function builds correctly.

After that, using standart SQL sheet in sql developer, i'm executing following query:

select GRANT_ACCESS_TO_SCHEMA_RES('<RESURCE_SCHEMA>', '<GRANTEE>', 'SELECT', 0) from dual;

Query result after that presents something like declaration of that table, instead of data in it...

STATEMENT_RESULTS([STATEMENT_RESULT], [STATEMENT_RESULT], ... )

What am i doing wrong?

select * from table (
  GRANT_ACCESS_TO_SCHEMA_RES('CHRIS', 'HR', 'SELECT', 0) 
)

SQL_STATEMENT                       STATEMENT_RESULT                
GRANT SELECT ON CHRIS.ORDER TO HR   ORA-00903: invalid table name   
<null>                              <null>