当前位置：文江博客话题详情

将文件加载到 clob 中

发布于 2024-09-01 08:33:55 字数 65 浏览 5 评论 0原文

给定一个指向网络服务器上托管的文件的 url，是否可以将该 url 的内容读取到 clob 中？如果是这样，怎么办？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独患者 2024-09-08 08:33:55

下面是一个获取 URL 并将其内容加载到表中的过程。

使用 UTL_HTTP.GET_PIECES() 检索 Web 文件。这将返回一个字符串数组。如果您在防火墙后面工作，则需要声明您的代理。了解有关 UTL_HTTP 的更多信息。

CLOB 争论使用 DBMS_LOB 功能的各个位。该过程声明一个临时 lob 变量，将 UTL_HTTP 数组的各个部分附加到其中，最后将其插入表中。了解有关 DBMS_LOB 的更多信息。

SQL> create or replace procedure pop_file_from_url
  2      (p_url in varchar2)
  3  is
  4      tc clob;
  5      lv_web_page utl_http.html_pieces;
  6  begin
  7
  8      dbms_lob.createtemporary(tc, true);
  9      dbms_lob.open(tc, dbms_lob.lob_readwrite);
 10
 11      lv_web_page := utl_http.request_pieces (p_url);
 12
 13      for i in 1..lv_web_page.count()
 14      loop
 15          dbms_lob.writeappend(tc, length(lv_web_page(i)) ,lv_web_page(i));
 16      end loop;
 17
 18      insert into t23 values (1, tc);
 19
 20      dbms_lob.close(tc);
 21      dbms_lob.freetemporary(tc);
 22
 23  end pop_file_from_url;
 24  /

Procedure created.

SQL>

如果，例如我，您使用的是 11g，您需要将 URL 添加到访问控制列表，否则请求将被阻止。了解有关 ACL 的更多信息。

SQL> exec pop_file_from_url('stackoverflow.com')
BEGIN pop_file_from_url('stackoverflow.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "APC.POP_FILE_FROM_URL", line 11
ORA-06512: at line 1


SQL>

将 StackOverflow 的 URL 添加到我的 ACL 后，我现在可以将该文件插入到我的表中：

SQL> exec pop_file_from_url('stackoverflow.com')

PL/SQL procedure successfully completed.

SQL> select id, dbms_lob.getlength(txt)
  2  from t23
  3  /

        ID DBMS_LOB.GETLENGTH(TXT)
---------- -----------------------
         1                  208226

SQL>

Here is a procedure which takes a URL and loads its contents into a table.

The web file is retrieved using UTL_HTTP.GET_PIECES(). This returns an array of strings. If you are working behind a firewall you will need to declare your proxy. Find out more about UTL_HTTP.

The CLOB wrangling uses various bits of DBMS_LOB functionality. The procedure declares a temporary lob variable, appends the pieces of the UTL_HTTP array to it and then finally inserts it into a table. Find out more about DBMS_LOB.

SQL> create or replace procedure pop_file_from_url
  2      (p_url in varchar2)
  3  is
  4      tc clob;
  5      lv_web_page utl_http.html_pieces;
  6  begin
  7
  8      dbms_lob.createtemporary(tc, true);
  9      dbms_lob.open(tc, dbms_lob.lob_readwrite);
 10
 11      lv_web_page := utl_http.request_pieces (p_url);
 12
 13      for i in 1..lv_web_page.count()
 14      loop
 15          dbms_lob.writeappend(tc, length(lv_web_page(i)) ,lv_web_page(i));
 16      end loop;
 17
 18      insert into t23 values (1, tc);
 19
 20      dbms_lob.close(tc);
 21      dbms_lob.freetemporary(tc);
 22
 23  end pop_file_from_url;
 24  /

Procedure created.

SQL>

If, like me, you are on 11g you will need to add the URL to an Access Control List, otherwise the request will be blocked. Find out more about ACLs.

SQL> exec pop_file_from_url('stackoverflow.com')
BEGIN pop_file_from_url('stackoverflow.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "APC.POP_FILE_FROM_URL", line 11
ORA-06512: at line 1


SQL>

After adding the URL for StackOverflow to my ACL I can now insert the file into my table:

SQL> exec pop_file_from_url('stackoverflow.com')

PL/SQL procedure successfully completed.

SQL> select id, dbms_lob.getlength(txt)
  2  from t23
  3  /

        ID DBMS_LOB.GETLENGTH(TXT)
---------- -----------------------
         1                  208226

SQL>

回复收藏 0 原文

~没有更多了~