Oracle PL/SQL TABLE 类型的 TO_CHAR

2021-12-24 00:00:00 tostring oracle plsql temp-tables

出于调试目的，我希望能够TO_CHAR"一个 Oracle PL/SQL 内存表.这是我想做的一个简化示例:

For debugging purposes, I'd like to be able to "TO_CHAR" an Oracle PL/SQL in-memory table. Here's a simplified example, of what I'd like to do:

DECLARE
  TYPE T IS TABLE OF MY_TABLE%ROWTYPE INDEX BY PLS_INTEGER;
  V T;

BEGIN
  -- ..

  -- Here, I'd like to dbms_output V's contents, which of course doesn't compile
  FOR i IN V.FIRST .. V.LAST LOOP
    dbms_output.put_line(V(i));
  END LOOP;

  -- I want to omit doing this:
  FOR i IN V.FIRST .. V.LAST LOOP
    dbms_output.put_line(V(i).ID || ',' || V(i).AMOUNT ...);
  END LOOP;

END;

这能实现吗，简单的?之所以这么问，是因为懒得一遍遍写这段调试代码，想用在任何表类型上.

Can this be achieved, simply? The reason I ask is because I'm too lazy to write this debugging code again and again, and I'd like to use it with any table type.

推荐答案

好的，抱歉这还没有完成，但是为了跟进@Lukas，这是我目前所拥有的:

ok, sorry this isn't complete, but to followup with @Lukas, here's what I have so far:

首先，我没有尝试创建 anydata/anytype 类型，而是尝试使用从游标中提取的 XML...很奇怪，但它是通用的:

First, instead of trying to create anydata/anytype types, I tried using XML extracted from a cursor...weird, but its generic:

CREATE OR REPLACE procedure printCur(in_cursor IN sys_refcursor) IS
begin

    FOR c IN (SELECT ROWNUM rn,
                    t2.COLUMN_VALUE.getrootelement () NAME,
                    EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE
               FROM TABLE (XMLSEQUENCE (in_cursor)) t,
                    TABLE (XMLSEQUENCE (EXTRACT (COLUMN_VALUE, '/ROW/node()'))) t2
               order by 1)

   LOOP
      DBMS_OUTPUT.put_line (c.NAME || ': ' || c.VALUE);
   END LOOP;

exception
    when others then raise;
end;
/

现在，要调用它，您需要一个游标，所以我尝试在 pl/sql 中转换为游标，例如:

Now, to call it, you need a cursor, so I tried casting to cursor in pl/sql, something like:

open v_cur for select * from table(cast(v_tab as tab_type));

但是根据 v_tab 的定义方式，这可能会或可能不会导致 pl/sql 转换问题(在嵌套表 def 中使用 %rowtype 似乎会产生问题).

But depending on how v_tab is defined, this may or may not cause issues in pl/sql cast (using %rowtype in nested table def seems to give issues).

无论如何，您可以在此基础上进行构建或根据需要对其进行改进.(并可能使用 xmltable...)

Anyway, you can build on this or refine it as you like. (and possibly use xmltable...)

希望有帮助

相关文章