Oracle PL/SQL TABLE类型的TO_CHAR [英] TO_CHAR of an Oracle PL/SQL TABLE type

问题描述

出于调试目的，我希望能够"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:

首先，我没有尝试创建任何数据/任何类型类型，而是尝试使用从游标中提取的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...)

希望有帮助

这篇关于Oracle PL/SQL TABLE类型的TO_CHAR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！