Oracle 函数结果的表示，它是通过sql developer中的DUAL中的SELECT <func> 自定义类型的表。

嗨，汤姆，

我创建了一个函数，该函数向给定用户授予对给定架构中表和视图的访问权限。
结果，函数返回一个自己类型的表，其中包含准备好的语句和异常消息 (如果抛出)。

1.创建类型:

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.函数本身:

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;
复制

函数正确构建。

之后，在SQL developer中使用standart sql sheet，我正在执行以下查询:

select GRANT_ACCESS_TO_SCHEMA_RES('', '', 'SELECT', 0) from dual;
复制

之后的查询结果会显示该表的声明，而不是其中的数据...

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

我做错了什么？

您的函数返回STATEMENT_RESULTS类型的对象。这就是你在结果中看到的。

如果要将其视为 “适当的” 行和列，请在表运算符中调用该函数。这将为您进行转换:

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   
                                  
复制