从 select 语句调用时返回空字符串的函数

Question

CREATE OR REPLACE FUNCTION abc (
p_table_name       IN   VARCHAR2
)
RETURN VARCHAR2
IS
v_var varchar(200);
v_data   VARCHAR2 (4000);

CURSOR cur_column_list
IS
   SELECT column_name AS col_name
    FROM all_tab_cols
   WHERE table_name = p_table_name;
BEGIN
open cur_column_list;
 loop
fetch cur_column_list into v_var;
exit when cur_column_list%notfound;

  v_data := v_data || ' -- ' || v_var;

END LOOP;

RETURN v_data;
EXCEPTION
 WHEN OTHERS
 THEN
 dbms_output.put_line(sqlerrm);
END;
/

当从 select 语句调用时，

select abc('pqr') FROM DUAL ;

abc('PQR')                                                         
1 row selected.

不会检索任何输出，并且 pqr 中有 40 列。

Answer 1

首先，您究竟要传递给该函数什么？您的 SELECT 语句显示您正在传递小写字符串“pqr”。但在粗体中，您传递的是大写字符串“PQR”。由于表名以大写形式存储在数据字典中（除非您碰巧使用了带引号的标识符并指定了小写表名），并且由于您没有在您的数据字典中执行 UPPER查询，这是一个重要的区别。

其次，异常处理程序的目的是什么？捕获无法处理的异常并仅调用客户端可能已或可能未启用的 dbms_output 以及可能或可能未从缓冲区读取 dbms_output 的异常是没有意义的> 写入。删除异常处理程序并查看是否抛出错误。

第三，在定义者权限存储过程中运行的代码无权访问通过角色授予的权限。它只能访问直接授予过程所有者的权限。如果过程的所有者已通过角色被授予访问相关表的权限，则您可以直接在 SQL*Plus 会话中查询 ALL_TAB_COLS 并查看表，但如果查询位于定义者的内部，则无法查看表。权限存储过程。在 SQL*Plus 中，您可以禁用角色来模拟您在定义者权限存储过程中有权访问的权限

SQL> set role none

，然后重试该操作。如果您在 ALL_TAB_COLS 中无法再看到所需的数据，则需要授予过程所有者直接访问表的权限，而不是通过角色。或者，您可以通过 SELECT ANY DICTIONARY 权限授予用户对 DBA_TAB_COLS 视图的访问权限，并将代码更改为使用 DBA_TAB_COLS。