CODEWITHSUNDEEP
sqloracle-databasefunctionplsqlblob
Viktor Molokanov
Viktor Molokanov

Reputation: 274

Query table datatype from function in Oracle SQL

colleagues

I have a problem with table function in Oracle.

More specifically, I have a function that converts BLOB into table of 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.

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

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.

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

Can anybody help me? Thank you in advance.

UPD: I tried following:

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

Result: ORA-00902: invalid datatype

Any other ideas?

Upvotes: 1

Views: 374

Answers (2)

Boneist
Boneist

Reputation: 23578

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.

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?):

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

Upvotes: 2

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

You can achieve this with Oracle's PIPE ROW statement

See: Pipelined Table Functions

Upvotes: -1

Related Questions