CODEWITHSUNDEEP
plsqloracle11gblob
Marcin Erbel
Marcin Erbel

Reputation: 1643

Return list/type/array of blobs in Oracle 11g

Is there any way to return in function a few Blobs? For instance in array? I need to return a few pictures which I have in database in StillImage format. But to get mapped well I need to convert them to blobs. But I didn't find any kind of method to return an array of blobs, cause types in Oracle doesn't support complex types.

Upvotes: 0

Views: 1807

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

I'm not sure that I understand what you mean when you say "types in Oracle don't support complex types". You can certainly define a collection of BLOBs 

SQL> create type blob_nt is table of blob;
  2  /

Type created.

and then write a function that returns this collection type

CREATE OR REPLACE FUNCTION get_blobs
  RETURN blob_nt
IS
BEGIN
  <<your logic>>
END;

Depending on the size of the BLOBs and what "few" means to you, however, it may make more sense to write a pipelined table function that pipes out individual BLOBs rather than loading multiple BLOBs into scarce PGA memory.

Upvotes: 1

Related Questions