从Oracle SQL中的函数查询表数据类型

colleagues

I have a problem with table function in Oracle.

我在Oracle中遇到表函数问题。

More specifically, I have a function that converts BLOB into table of varchar2.

更具体地说,我有一个将BLOB转换为varchar2表的函数。

create type string_array is table of varchar2(255);

create or replace function blob_to_strings(p_blb in BLOB) return string_array as
begin
-- some processing here
end;

Also I have table containing BLOBS I need to work with.

我也有包含我需要使用的BLOBS的表。

create table my_blobs (id number, data blob)

Now, having id in my_blobs table, I want to query result of convert function. Something like

现在,在my_blobs表中有id,我想查询转换函数的结果。就像是

select t.* from table(blob_to_strings(b.data)) t, my_blobs b where b.id = 123;

(I know this is incorrect, just showing what I need) This query expectedly returns b.data: invalid identifier as you can't access other table columns inside from section.

(我知道这是不正确的,只显示我需要的内容)此查询预期会返回b.data:无效的标识符,因为您无法访问其中的其他表列。

I understand how to do it in PL/SQL running 2 queries, but really need to do it in SQL.

我理解如何在运行2个查询的PL / SQL中执行此操作,但实际上需要在SQL中执行此操作。

Can anybody help me? Thank you in advance.

有谁能够帮我?先感谢您。

UPD: I tried following:

UPD:我试过以下:

select * from table(select blob_to_strings(b.data) from my_blobs b where b.id = 123);

Result: ORA-00902: invalid datatype

结果:ORA-00902:数据类型无效

Any other ideas?

还有其他想法吗?

2 个解决方案

#1

Possibly the issue with your original query was that you had the table name coming after the attempt to select from the array (from table(blob_to_strings(b.data)) t, my_blobs b). In other words, you were trying to select from something that hadn't yet been declared. Switch the order of the items in the from clause, and it should work.

可能原始查询的问题在于您尝试从数组中选择后会有表名(来自表(blob_to_strings(b.data))t,my_blobs b)。换句话说,您试图从尚未声明的内容中进行选择。切换from子句中项目的顺序,它应该工作。

Here's a test case that I knocked up to demonstrate (I used CLOBs since we're apparently dealing with text; I'm not sure why you're using BLOBs?):

这是一个我试图演示的测试用例(我使用了CLOB,因为我们显然正在处理文本;我不确定你为什么要使用BLOB?):

create table t1 (id number,
                 clob_col clob);

insert into t1 values (1, 'abcd');
insert into t1 values (2, 'efg');

commit;

create type string_array is table of varchar2(255);

create or replace function clob_to_str_tab (p_clob in clob)
return string_array
is
  v_str_arr string_array := string_array();
begin
  for i in 1.. length(p_clob)
  loop
    v_str_arr.extend;
    v_str_arr(i) := substr(p_clob, i, 1);
  end loop;

  return v_str_arr;
end;
/

select t1.id,
       t2.column_value res
from   table(clob_to_str_tab(t1.clob_col)) t2,
       t1;

ORA-00904: "T1"."CLOB_COL": invalid identifier

select t1.id,
       t2.column_value res
from   t1,
       table(clob_to_str_tab(t1.clob_col)) t2;

        ID RES
---------- ---
         1 a  
         1 b  
         1 c  
         1 d  
         2 e  
         2 f  
         2 g

#2

-1

You can achieve this with Oracle's PIPE ROW statement

您可以使用Oracle的PIPE ROW语句实现此目的

See: Pipelined Table Functions

请参阅:流水线表功能

#1