Passing Data in Native SQL stored Procedures

[Former Member]

Hi all,

We are connecting to a stored procedure in Oracle from a user exit. We have managed to send through small character variables but would like to pass through a large complex structure which can be interpretted and used by the Oracle db.

What options do we have to send this large data object through.

Your help is appreciated.

[Former Member]

Hi all,

We are connecting to a stored procedure in Oracle from a user exit. We have managed to send through small character variables but would like to pass through a large complex structure which can be interpretted and used by the Oracle db.

What options do we have to send this large data object through.

Your help is appreciated.

[jayanthi_jayaraman]

Hi,

I am not sure.Just try the following and create a table in oracle(say zzz_table) similar to the one in SE11.

data wa type zzz_table.

select * from zzz_table into wa where regno = '33'.

exec sql.

create or replace procedure prod1 ( m in zzz_table ) is

begin

insert into student ( regno, name, percentage )

values ( &m-regno, &m-name, &m-percentage );

end;

execute procedure prod1 ( in :wa)

Kindly reward points by clicking the star on the left of reply,if it is helpful.

[Former Member]

Thanks for the posting - this does not really help me though. We need to send through a complex structure and I am not sure what data types are supported - and maybe this is the bottom line question. We need to send information through that will effectively map to an Oracle user defined type which is structured and nested etc.

I hope this is clearer.

Regards