使用 PL/SQL 连接 CLOB 行
我有一个表，其中有一个 id 和一个 clob 内容，例如：
Create Table v_example_l (
nip number,
xmlcontent clob
);
我们插入数据：
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>delta</name><content>548484646846484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>omega</name><content>545648468484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>gamma</name><content>54564846qsdqsdqsdqsd8484</content></block></section>');
我正在尝试执行一个函数来连接作为选择结果的 clob 行，我的意思是不必给出有关表名称等的多个参数，我应该只在这里给出包含 clob 的列，它应该处理其余的。
CREATE OR REPLACE function assemble_clob(q varchar2)
return clob
is
v_clob clob;
tmp_lob clob;
hold VARCHAR2(4000);
--cursor c2 is select xmlcontent from V_EXAMPLE_L where id=17852
cur sys_refcursor;
begin
OPEN cur FOR q;
LOOP
FETCH cur INTO tmp_lob;
EXIT WHEN cur%NOTFOUND;
--v_clob := v_clob || XMLTYPE.getClobVal(tmp_lob.xmlcontent);
v_clob := v_clob || tmp_lob;
END LOOP;
return (v_clob);
--return (dbms_xmlquery.getXml( dbms_xmlquery.set_context("Select 1 from dual")) )
end assemble_clob;
该功能已损坏...（如果有人可以给我帮助，非常感谢，而且我在 sql 方面很菜鸟......）。谢谢！
I've got a table which has an id and a clob content like:
Create Table v_example_l (
nip number,
xmlcontent clob
);
We insert our data:
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>delta</name><content>548484646846484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>omega</name><content>545648468484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT)
Values (17852,'<section><block><name>gamma</name><content>54564846qsdqsdqsdqsd8484</content></block></section>');
I'm trying to do a function that concatenates the rows of the clob that gone be the result of a select, i mean without having to give multiple parameter about the name of table or such, i should only give here the column that contain the clobs, and it should handle the rest.
CREATE OR REPLACE function assemble_clob(q varchar2)
return clob
is
v_clob clob;
tmp_lob clob;
hold VARCHAR2(4000);
--cursor c2 is select xmlcontent from V_EXAMPLE_L where id=17852
cur sys_refcursor;
begin
OPEN cur FOR q;
LOOP
FETCH cur INTO tmp_lob;
EXIT WHEN cur%NOTFOUND;
--v_clob := v_clob || XMLTYPE.getClobVal(tmp_lob.xmlcontent);
v_clob := v_clob || tmp_lob;
END LOOP;
return (v_clob);
--return (dbms_xmlquery.getXml( dbms_xmlquery.set_context("Select 1 from dual")) )
end assemble_clob;
The function is broken... (if anybody could give me a help, thanks a lot, and i'm noob in sql so ....). Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您并没有真正说明为什么它被破坏，但 DBMS_LOB 包有一个 APPEND 函数，这可能就是您正在寻找的。
You don't really say why it's broken but the DBMS_LOB package has an APPEND function that might be what you're looking for.
您必须解释正在发生的事情不是您所期望的。你的例子对我来说效果很好。在 SQLPlus 中，请注意需要将 SET LONG 设置为足够大的值以获取整个 CLOB 内容。
You'll have to explain what is happening that is not what you expect. Your example works fine for me. In SQLPlus, note the need to SET LONG to a large enough value to fetch the entire CLOB contents.
尝试使用
try using