The main idea is than I want to implement pivot function without know columns in advance. I found script for that here but it is for one column and i want to pivot two columns.
I have function Pivot than return pipelined result using PivotImpl:
create or replace function Pivot(p_stmt in varchar2,
p_aggr_function in varchar2 := 'max')
return anydataset
pipelined using PivotImpl;
Type PivotImpl is implementation of ODCITable(more here).
CREATE OR REPLACE TYPE PivotImpl as object
(
ret_type anytype, -- The return type of the table function
stmt varchar2(32767),
aggr_function varchar2(32767),
cur integer,
static function ODCITableDescribe(rtype out anytype, p_stmt in varchar2)
return number,
static function ODCITablePrepare(sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_aggr_function in varchar2 := 'max')
return number,
static function ODCITableStart(sctx in out PivotImpl, p_stmt in varchar2)
return number,
member function ODCITableFetch(self in out PivotImpl, nrows in number, outset out anydataset) return number,
member function ODCITableClose(self in PivotImpl) return number
)
My ODCITablePrepare is like:
static function ODCITablePrepare(sctx out PivotImpl,
ti in sys.ODCITabFuncInfo,
p_stmt in varchar2,
p_aggr_function in varchar2 := 'max')
return number is
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
elem_typ anytype;
aname varchar2(30);
tc pls_integer;
begin
tc := ti.RetType.GetAttrElemInfo(1,
prec,
scale,
len,
csid,
csfrm,
elem_typ,
aname);
--
dbms_output.put_line('Prepare: ' || p_stmt); -- !!!HERE IS MY DEBUG FOR p_stmt VALUES
sctx := PivotImpl(elem_typ,
p_stmp,
p_aggr_function,
null);
return odciconst.success;
end;
In ODCITableClose I invalidate type every time and that force ODCITablePrepare to be invoced every time then execute my SELECT.
Implementation of ODCITableClose function is:
member function ODCITableClose(self in PivotImpl) return number is
c integer;
t_id number;
begin
c := self.cur;
dbms_sql.close_cursor(c);
select object_id
into t_id
from user_objects
where object_name = $$PLSQL_UNIT -- name of your type!
and object_type = 'TYPE BODY';
-- invalidating of the type body forces that ODCITableDescribe is executed for every call to the pivot function
-- and we do need that to make sure that any new columns are picked up (= new values for the pivoting column)
dbms_utility.invalidate( t_id );
return odciconst.success;
end;
I have two select almost equal SELECTs that work different.
In first SELECT in output I can see that value of p_stmt is empty(from my debug in ODCITablePrepare). First select:
select *
from table(Pivot(p_stmt => 'select account_number, branch_code, field_name, field_value,field_value_descr
from account_map')) ;
I second case I use view:
create or replace view v_account_map as
select *
from table(Pivot('select account_number, branch_code, field_name, field_value,field_value_descr
from account_map'));
And select:
select *
from v_account_map t
In second select value of p_stmt is a value that I passed to Pivot function.
Another strange think that I can't understand is why when open **v_account_map** implementation my
SELECT *
is replaced with real colomn's names
SELECT "ACCOUNT_NUMBER","BRANCH_CODE","A1","A2","A3"
And when I add filter to select from view that have to return result, the result is empty. Example:
select *
from table(Pivot(p_stmt => 'select account_number, branch_code, field_name, field_value,field_value_descr
from account_map'))
where account_number = '42342325345452'; --> RETURN 1 row;
select *
from V_ACCOUNT_MAP t
where account_number = '42342325345452'; --> RETURN 0 rows
Without filter a two selects return same result.
My first question is Why in first case ODCITablePrepare isn't called corect and in second is corect?
My second question is Why I have problem to filter result from ODCITable? Actually problem is not only with filtering. Group by is very strange too. For example result have to be *10,20,30,40*. In first select result is *40,40,40,40* and in second select(from a view) result is only *40*.
Maybe I missed something in my implementation, but I can see what is it.
Another info:
I'm not sure is that my Oracle version, but:
OCI: version 11.1
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
And don't know is it necessary but this is a rest of ODCITable functions implementation:
static function ODCITableDescribe(rtype out anytype, p_stmt in varchar2)
return number is
atyp anytype;
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
rc sys_refcursor;
t_c2 varchar2(32767);
t_fmt varchar2(1000):= '@p@';
v_stmp varchar2(32767) := 'select account_number, branch_code, field_name, field_value,field_value_descr
from dropme_account_map_eli';
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, /*p_stmt*/ v_stmp, dbms_sql.native); -- !!!Нямам никаква идея защо p_stmt е празно
dbms_sql.describe_columns2(cur, numcols, desc_tab);
dbms_sql.close_cursor(cur);
--
anytype.begincreate(dbms_types.typecode_object, atyp);
for i in 1 .. numcols - 3 -- атрибутите за group by
loop
atyp.addattr(desc_tab(i).col_name,
case desc_tab(i).col_type
when 1 then dbms_types.typecode_varchar2
when 2 then dbms_types.typecode_number
when 9 then dbms_types.typecode_varchar2
when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
when 12 then dbms_types.typecode_date
when 208 then dbms_types.typecode_varchar2 -- show urowid as varchar2
when 96 then dbms_types.typecode_char
when 180 then dbms_types.typecode_timestamp
when 181 then dbms_types.typecode_timestamp_tz
when 231 then dbms_types.typecode_timestamp_ltz
when 182 then dbms_types.typecode_interval_ym
when 183 then dbms_types.typecode_interval_ds
end,
desc_tab(i).col_precision,
desc_tab(i).col_scale,
case desc_tab(i).col_type
when 11 then
18 -- for rowid col_max_len = 16, and 18 characters are shown
else
desc_tab(i).col_max_len
end,
desc_tab(i).col_charsetid,
desc_tab(i).col_charsetform);
end loop;
-- Останлите атрибути
open rc for replace('select distinct ' || t_fmt || '
from( ' || v_stmp /*p_stmt*/ -- !!!Нямам никаква идея защо p_stmt е празно
||
' )
order by ' || t_fmt,
'@p@'
,'"' || desc_tab(numcols - 2).col_name || '"');
loop
fetch rc
into t_c2;
exit when rc%notfound;
-- Актрибутите на UDF-ите
atyp.addattr(t_c2,
case desc_tab(numcols - 1).col_type
when 1 then dbms_types.typecode_varchar2
when 2 then dbms_types.typecode_number
when 9 then dbms_types.typecode_varchar2
when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
when 12 then dbms_types.typecode_date
when 208 then dbms_types.typecode_urowid
when 96 then dbms_types.typecode_char
when 180 then dbms_types.typecode_timestamp
when 181 then dbms_types.typecode_timestamp_tz
when 231 then dbms_types.typecode_timestamp_ltz
when 182 then dbms_types.typecode_interval_ym
when 183 then dbms_types.typecode_interval_ds end
,
desc_tab(numcols - 1).col_precision,
desc_tab(numcols - 1).col_scale,
case desc_tab(numcols - 1).col_type when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
else desc_tab(numcols - 1).col_max_len end
,
desc_tab(numcols - 1).col_charsetid,
desc_tab(numcols - 1).col_charsetform);
-- Актрибутите на описанито на UDF-ите
atyp.addattr(t_c2 || '_desc',
dbms_types.typecode_varchar2,
desc_tab(numcols).col_precision,
desc_tab(numcols).col_scale,
case desc_tab(numcols).col_type when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
else desc_tab(numcols).col_max_len end
,
desc_tab(numcols).col_charsetid,
desc_tab(numcols).col_charsetform);
end loop;
close rc;
atyp.endcreate;
anytype.begincreate(dbms_types.typecode_table, rtype);
rtype.SetInfo(null, null, null, null, null, atyp, dbms_types.typecode_object, 0);
rtype.endcreate();
return odciconst.success;
exception
when others then
anytype.begincreate(dbms_types.typecode_object, atyp);
atyp.addattr('ERROR', dbms_types.typecode_varchar2, null, null, 4000, null, null, null);
atyp.endcreate;
anytype.begincreate(dbms_types.typecode_table, rtype);
rtype.SetInfo(null, null, null, null, null, atyp, dbms_types.typecode_object, 0);
rtype.endcreate();
return odciconst.success;
end;
static function ODCITableStart(sctx in out PivotImpl, p_stmt in varchar2)
return number is
cur integer;
numcols number; -- броя на колонките от заявката(p_stmt)
desc_tab dbms_sql.desc_tab2; -- описание на колонките от заявката(p_stmt)
t_stmt varchar2(32767); -- новата заявка
t_stmt_group varchar2(32767); -- group by клаузата
type_code pls_integer; -- описание деклариран тип в ODCITableDescribe
-- Атрибути, които се взимат от типа
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
schema_name varchar2(30);
type_name varchar2(30);
version varchar2(30);
attr_count pls_integer; --само това се използва, за да итерирате през всички декларирани атрибути
attr_type anytype;
attr_name varchar2(100);
dummy2 integer; -- май е, за да се види дали проблема е във заявката
begin
-- Инциализиране на курсора на подадения низ като заявка
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, p_stmt, dbms_sql.native);
dbms_sql.describe_columns2(cur, numcols, desc_tab);
dbms_sql.close_cursor(cur);
--
for i in 1 .. numcols - 3 --колонките от group by
loop
t_stmt := t_stmt || ', "' || desc_tab(i).col_name || '"' || chr(10);
if i = 1 then
t_stmt_group := ' group by "' || desc_tab(i).col_name || '"';
else
t_stmt_group := t_stmt_group || ', "' || desc_tab(i).col_name || '"';
end if;
end loop;
--
type_code := sctx.ret_type.getinfo(prec,
scale,
len,
csid,
csfrm,
schema_name,
type_name,
version,
attr_count -- брой атрибути(от декларираните чрез atyp.addattr в ODCITableDescribe)
);
for i in numcols - 2 .. attr_count loop
type_code := sctx.ret_type.getattreleminfo(i,
prec,
scale,
len,
csid,
csfrm,
attr_type,
attr_name);
-- в атрубутите с наставка "_desc", наставката трябва да се премахне от името, за да е правилен decode-а
if REGEXP_LIKE(attr_name, '_desc$') then
t_stmt := t_stmt || ', ' || sctx.aggr_function || '( decode( ' ||
'"' || desc_tab(numcols - 2).col_name || '"' || ', ';
t_stmt := t_stmt || '''' || REGEXP_REPLACE(attr_name, '_desc$') ||
''', "' || desc_tab(numcols).col_name || '" ) )' ||
chr(10);
else
t_stmt := t_stmt || ', ' || sctx.aggr_function || '( decode( ' ||
'"' || desc_tab(numcols - 2).col_name || '"' || ', ';
t_stmt := t_stmt || '''' || attr_name || ''', "' || desc_tab(numcols - 1)
.col_name || '" ) )' || chr(10);
end if;
end loop;
t_stmt := 'select ' || substr(t_stmt, 2) || ' from ( ' || sctx.stmt || ' )';
t_stmt := t_stmt || t_stmt_group;
t_stmt := t_stmt || ' order by 1 nulls first';
dbms_output.put_line(t_stmt); -- преглед на генерираната заявка
--
sctx.cur := dbms_sql.open_cursor;
dbms_sql.parse(sctx.cur, t_stmt, dbms_sql.native);
for i in 1 .. attr_count loop
type_code := sctx.ret_type.getattreleminfo(i,
prec,
scale,
len,
csid,
csfrm,
attr_type,
attr_name);
case type_code
when dbms_types.typecode_char then
dbms_sql.define_column(sctx.cur, i, 'x', 32767);
when dbms_types.typecode_varchar2 then
dbms_sql.define_column(sctx.cur, i, 'x', 32767);
when dbms_types.typecode_number then
dbms_sql.define_column(sctx.cur, i, cast(null as number));
when dbms_types.typecode_date then
dbms_sql.define_column(sctx.cur, i, cast(null as date));
when dbms_types.typecode_urowid then
dbms_sql.define_column(sctx.cur, i, cast(null as urowid));
when dbms_types.typecode_timestamp then
dbms_sql.define_column(sctx.cur, i, cast(null as timestamp));
when dbms_types.typecode_timestamp_tz then
dbms_sql.define_column(sctx.cur, i, cast(null as timestamp with time zone));
when dbms_types.typecode_timestamp_ltz then
dbms_sql.define_column(sctx.cur, i, cast(null as timestamp with local time zone));
when dbms_types.typecode_interval_ym then
dbms_sql.define_column(sctx.cur, i, cast(null as interval year to month));
when dbms_types.typecode_interval_ds then
dbms_sql.define_column(sctx.cur, i, cast(null as interval day to second));
end case;
end loop;
dummy2 := dbms_sql.execute(sctx.cur);
return odciconst.success;
exception
when others then
sctx.cur := dbms_sql.open_cursor;
dbms_sql.parse(sctx.cur,
'select :msg from dual union all select :err from dual union all select :stm from dual',
dbms_sql.native);
dbms_sql.bind_variable(sctx.cur, 'msg', 'Oops, not a valid query?');
dbms_sql.bind_variable(sctx.cur,
'err',
dbms_utility.format_error_stack);
dbms_sql.bind_variable(sctx.cur, 'stm', t_stmt);
dbms_sql.define_column(sctx.cur, 1, 'x', 32767);
dummy2 := dbms_sql.execute(sctx.cur);
return odciconst.success;
end;
--
-- Връщане на set от данни
member function ODCITableFetch(self in out PivotImpl, nrows in number, outset out anydataset) return number is
type_code pls_integer;
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
schema_name varchar2(30);
type_name varchar2(30);
version varchar2(30);
attr_count pls_integer;
attr_type anytype;
attr_name varchar2(100);
v1 varchar2(32767);
n1 number;
d1 date;
ur1 urowid;
ids1 interval day to second;
iym1 interval year to month;
ts1 timestamp;
tstz1 timestamp with time zone;
tsltz1 timestamp with local time zone;
begin
outset := null;
if nrows < 1 then
-- is this possible???
return odciconst.success;
end if;
--
if dbms_sql.fetch_rows(self.cur) = 0 then
return odciconst.success;
end if;
--
type_code := self.ret_type.getinfo(prec,
scale,
len,
csid,
csfrm,
schema_name,
type_name,
version,
attr_count);
anydataset.begincreate(dbms_types.typecode_object,
self.ret_type,
outset);
outset.addinstance;
outset.piecewise();
for i in 1 .. attr_count loop
type_code := self.ret_type.getattreleminfo(i,
prec,
scale,
len,
csid,
csfrm,
attr_type,
attr_name);
case type_code
when dbms_types.typecode_char then
dbms_sql.column_value(self.cur, i, v1);
outset.setchar(v1);
when dbms_types.typecode_varchar2 then
dbms_sql.column_value(self.cur, i, v1);
outset.setvarchar2(v1);
when dbms_types.typecode_number then
dbms_sql.column_value(self.cur, i, n1);
outset.setnumber(n1);
when dbms_types.typecode_date then
dbms_sql.column_value(self.cur, i, d1);
outset.setdate(d1);
when dbms_types.typecode_urowid then
dbms_sql.column_value(self.cur, i, ur1);
outset.seturowid(ur1);
when dbms_types.typecode_interval_ds then
dbms_sql.column_value(self.cur, i, ids1);
outset.setintervalds(ids1);
when dbms_types.typecode_interval_ym then
dbms_sql.column_value(self.cur, i, iym1);
outset.setintervalym(iym1);
when dbms_types.typecode_timestamp then
dbms_sql.column_value(self.cur, i, ts1);
outset.settimestamp(ts1);
when dbms_types.typecode_timestamp_tz then
dbms_sql.column_value(self.cur, i, tstz1);
outset.settimestamptz(tstz1);
when dbms_types.typecode_timestamp_ltz then
dbms_sql.column_value(self.cur, i, tsltz1);
outset.settimestampltz(tsltz1);
end case;
end loop;
outset.endcreate;
return odciconst.success;
end;