ODCITableDescribe not picking up parameters

[matthew....@gmail.com]

I'm attempting to implement the ODCITable interface to build a interfaced pipeline function. When I go to call my code from a query, the parameters I am passing the pipelined function are all being passed into the ODCITable* functions as null values.

Essentially, what I am trying to do is be able to do this:
select *
from table(some_package.show(id1 = 12, id2 = 1234));
and have it return the resultset back as an abstract table. I have the code to make parse the query and build it, but my parameters are being pass as nulls.

I've attached a simpele type_test object to try and illustrate what I'm doing. Any help would be appreciated.

create or replace type type_test as object
(
-- Author : MATT_MACARI
-- Created : 4/3/2012 12:26:29 PM
-- Purpose : Tests the ODCI Interface


l_param number,

rtype sys.anytype,

rows_requested number,

static function ODCITableDescribe(rtype out anytype,
l_param in number default 1, row_param in number default 12) return number,

static function ODCITableStart(sctx in out type_test,
l_param in number, row_param in number) return number,

member function ODCITableFetch(self in out type_test,
nrows in number,
rws out anydataset) return number,

member function ODCITableClose(self in type_test) return number,

static function ODCITablePrepare(sctx out type_test,
tf_info sys.odcitabfuncinfo,
l_param in number, row_param in number) return number,

static function show(l_param in number default 12, row_param in number default 123) return anydataset pipelined using type_test

)/

create or replace type body type_test is


static function ODCITableDescribe(rtype out anytype,
l_param in number := 1,
row_param in number := 12) return number is
v_rtype sys.anytype;

v_param number;
begin

v_param := l_param;

anytype.BeginCreate(typecode => dbms_types.typecode_object, atype => v_rtype);

if v_param is not null or row_param is not null then
v_rtype.AddAttr('type_test_success_' || v_param, dbms_types.typecode_varchar2, null, null, 50, null, null);
dbms_output.put_line('success');
else
v_rtype.AddAttr('type_test_failure', dbms_types.typecode_varchar2, null, null, 50, null, null);
dbms_output.put_line('failure' || ' ' || l_param || ' ' || v_param);
end if;
v_rtype.endcreate;

anytype.begincreate(dbms_types.typecode_table, rtype);

rtype.setinfo(null, null, null, null, null, v_rtype, dbms_types.typecode_object, 0);

rtype.endcreate();

return odciconst.success;
end;


static function ODCITablePrepare(sctx out type_test,
tf_info sys.odcitabfuncinfo,
l_param in number,
row_param in number) return number is
elem_type sys.anytype;
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
tc pls_integer;
aname varchar2(30);
begin
tc := tf_info.rettype.GetAttrElemInfo(1, prec, scale, len, csid, csfrm, elem_type, aname);

sctx := type_test(l_param, elem_type, row_param);

return odciconst.success;

end;

static function ODCITableStart(sctx in out type_test,
l_param in number,
row_param in number) return number is
begin
return odciconst.success;
end;

member function ODCITableFetch(self in out type_test,
nrows in number,
rws out anydataset) return number is

begin

anydataset.begincreate(dbms_types.typecode_object, self.rtype, rws);

for i in 1 .. nvl(self.rows_requested, 23) loop
rws.addinstance;
rws.piecewise();
if l_param is not null then
rws.setvarchar2(to_char(i) || ' ' || l_param);
else
rws.setvarchar2(to_char(i) || ' Failure.');
end if;
end loop;
self.rows_requested := 0;
rws.endcreate;
return odciconst.success;
end;

member function ODCITableClose(self in type_test) return number is
t_id number;
begin
return odciconst.success;
select object_id
into t_id
from user_objects
where object_name = 'TYPE_TEST'
and object_type = 'TYPE BODY';
dbms_utility.invalidate(t_id);
end;


end;
/

[Michel Cadot]

<matthew....@gmail.com> a écrit dans le message de news: 10232227.2576.1333490469304.JavaMail.geo-discussion-forums@yncd8...

| I'm attempting to implement the ODCITable interface to build a interfaced pipeline function. When I go to call my code from a
query, the parameters I am passing the pipelined function are all being passed into the ODCITable* functions as null values.
|
| Essentially, what I am trying to do is be able to do this:
| select *
| from table(some_package.show(id1 = 12, id2 = 1234));
| and have it return the resultset back as an abstract table. I have the code to make parse the query and build it, but my
parameters are being pass as nulls.
|

| ...

SQL> select * from table(type_test.show(12,1234));
type_test_success_12
--------------------------------------------------
1 12
2 12
3 12
4 12
5 12
6 12
7 12
8 12
9 12
...
1229 12
1230 12
1231 12
1232 12
1233 12
1234 12

1234 rows selected.

Your code works for me.

Regards
Michel