Home » SQL & PL/SQL » SQL & PL/SQL » Error in BLOB array (10g2)
Error in BLOB array [message #285802] Wed, 05 December 2007 12:39 Go to next message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

Dear Experts,

Any help on following problem will be much appreciated.

Thanks,
Krishna

Purpose : pass a blob array from a procedure to a package and insert data into a table.

Error: Not able to complile the test procedure which calls the package. Error PLS-00306: wrong number or types of arguments in call to 'BLOB_ARRAY_TEST'

source and destination table structure

SQL> desc dept
Name Null? Type
------------------------- -------- -------

DEPTNO NOT NULL NUMBER
DEPTDATA BLOB

procedure
======================================
CREATE OR REPLACE procedure Array_Test
as
dest_deptdata blob;
type arraydeptno_t is table of dept.deptno%type index by binary_integer;
type arraydeptdata_t is table of dept.deptdata%type index by binary_integer;
v_arraydeptno arraydeptno_t;
v_arraydeptdata arraydeptdata_t;

cursor c_depts is select * from dept_s;

begin


open c_depts;
fetch c_depts bulk collect into v_arraydeptno,v_arraydeptdata;
close c_depts;

Blob_Array_Test_pkg.Blob_Array_Test(v_arraydeptno,v_arraydeptdata);

end Array_Test;
/

Package
===============

CREATE OR REPLACE package Blob_Array_Test_PKG
is

type arraydeptno_t is table of dept.deptno%type index by binary_integer;
type arraydeptdata_t is table of dept.deptdata%type index by binary_integer;

procedure Blob_Array_Test(
v_arraydeptno IN arraydeptno_t,
v_arraydeptdata IN arraydeptdata_t
);

end Blob_Array_Test_PKG;
/


CREATE OR REPLACE package body Blob_Array_Test_PKG
is

procedure Blob_Array_Test(
v_arraydeptno IN arraydeptno_t,
v_arraydeptdata IN arraydeptdata_t
) as

dest_deptdata blob;

begin


execute immediate 'truncate table dept';

for i in v_arraydeptno.first .. v_arraydeptno.last
loop

insert into dept values(i,empty_blob());

select deptdata into dest_deptdata
from dept
where deptno = i
for update;

dbms_lob.append(dest_deptdata, v_arraydeptdata(i));
commit;

end loop;

end Blob_Array_Test;

end Blob_Array_Test_PKG;
/





Re: Error in BLOB array [message #285804 is a reply to message #285802] Wed, 05 December 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In test procedure variables are of arraydeptno_t... types of this procedure.
In package procedure parameters are ot arraydeptno_t... types of this package.
So different datatypes.

Regards
Michel
Re: Error in BLOB array [message #285861 is a reply to message #285804] Wed, 05 December 2007 23:05 Go to previous messageGo to next message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

Both the variables are inherited from parent tables which is of same data type number and BLOB respectively.

Please correct me if i am wrong.

Regards
Krishna
Re: Error in BLOB array [message #285896 is a reply to message #285861] Thu, 06 December 2007 00:40 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
YOU define new types, there are different types.
It does not matter what's in the definition.
If you want same type, use only one definition and use it in both procedures.

Regards
Michel
Previous Topic: Duplicate Address
Next Topic: BULK COLLECT getting slower pga memory increasing
Goto Forum:
  


Current Time: Sat Dec 10 06:54:19 CST 2016

Total time taken to generate the page: 0.08138 seconds