CODEWITHSUNDEEP
oracle-databaseplsql
GrzesiekO
GrzesiekO

Reputation: 1199

Get BLOB from stored procedure in Oracle

This is my stored procedure:

CREATE OR REPLACE
PROCEDURE prodecureName
  (
    firstArgument IN NUMBER,
    secondArgument OUT BLOB )
AS
BEGIN
  SELECT secondArgument 
  INTO prodecureName.secondArgument 
  FROM tableName
  WHERE firstArgument = prodecureName.firstArgument ;
END;

Data type of secondArgument column is valid (both are BLOB). During executing this procedure I get this error:

Wrong number or types of arguments in call to prodecureName

How I can get blob from stored procedure?

Upvotes: 0

Views: 6136

Answers (2)

tbone
tbone

Reputation: 15473

Try something like (using clob instead of blob, but same approach):

drop table tst_clob_tab;
create table tst_clob_tab
(
  id number,
  my_clob clob
);

-- Oracle will implicitly convert to clob (9i greater)
insert into tst_clob_tab(id,my_clob) values (1,'This is some large value...');
commit;

-- Create procedure
create or replace procedure tst_clob(p_1 in number, p_2 out clob) as
begin
  select my_clob
  into p_2
  from tst_clob_tab
  where id = p_1;
end;

-- Call procedure
declare
  l_clob clob;
begin
  tst_clob(1,l_clob);
  dbms_output.put_line('Clob: ' || l_clob);
end;

Upvotes: 0

Rene
Rene

Reputation: 10541

One way to call this procedure:

declare
  l_blob blob;
begin
  procedurename(1,l_blob);
end;

The error message indicates that you are calling this with the wrong type of parameters or the wrong number of parameters. First parameter must by of type number, second parameter of type blob.

Upvotes: 1

Related Questions