问题描述
出于调试目的,我希望能够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...)
希望有帮助
这篇关于Oracle PL/SQL TABLE 类型的 TO_CHAR的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!