Forum Stats

  • 3,874,265 Users
  • 2,266,717 Discussions
  • 7,911,796 Comments

Discussions

ODCITable Implementation doesn't call ODCITablePrepare correctly

3261255
3261255 Member Posts: 6
edited Aug 24, 2018 11:38AM in SQL & PL/SQL

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 isatyp     anytype;cur      integer;numcols  number;desc_tab dbms_sql.desc_tab2;  rc    sys_refcursor;t_c2  varchar2(32767);t_fmt varchar2(1000):= '@[email protected]';  v_stmp varchar2(32767) := 'select account_number, branch_code, field_name, field_value,field_value_descrfrom dropme_account_map_eli';  begincur := 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 byloop atyp.addattr(desc_tab(i).col_name,  case desc_tab(i).col_typewhen 1 then dbms_types.typecode_varchar2when 2 then dbms_types.typecode_numberwhen 9 then dbms_types.typecode_varchar2when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2when 12 then dbms_types.typecode_datewhen 208 then dbms_types.typecode_varchar2 -- show urowid as varchar2when 96 then dbms_types.typecode_charwhen 180 then dbms_types.typecode_timestampwhen 181 then dbms_types.typecode_timestamp_tzwhen 231 then dbms_types.typecode_timestamp_ltzwhen 182 then dbms_types.typecode_interval_ymwhen 183 then dbms_types.typecode_interval_ds  end,  desc_tab(i).col_precision,  desc_tab(i).col_scale,  case desc_tab(i).col_typewhen 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shownelse 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,'@[email protected]'  ,'"' || desc_tab(numcols - 2).col_name || '"');loop fetch rcinto 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 varchar2when 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 shownelse 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 shownelse 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;  exceptionwhen 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 iscur         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 byloop t_stmt  := t_stmt || ', "' || desc_tab(i).col_name || '"' || chr(10); if i = 1 thent_stmt_group := ' group by "' || desc_tab(i).col_name || '"'; elset_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 looptype_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_codewhen 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;  exceptionwhen 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 istype_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;  beginoutset := 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_codewhen 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;
Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,945 Red Diamond
    edited Aug 22, 2018 9:25AM

    Why do this using the Data Cartridge interface? It is complex, and has a lot of moving parts. Solid reasons are needed for going down this path.

    If you want to:

    a) supply a SQL (and parameters)

    b) use procedural logic to craft a pivot SQL

    c) open a cursor using the pivot SQL

    Then this can be done using a standard PL/SQL procedure or function, that accepts the applicable parameters, creates the pivot SQL dynamically, and then returns a ref cursor for the dynamic SQL.

    A client can use the standard OCI cursor interface to consume the cursor. Or for use in PL/SQL, convert the ref cursor handle into a DBMS_SQL cursor, in order to consume it.

  • Unknown
    edited Aug 22, 2018 11:52AM
    The main idea is than I want to implement pivot function without know columns in advance.

    Already done MANY times starting over a decade ago.

    Here is the one by Anton Scheffer that started it off.

    https://technology.amis.nl/2006/05/16/pivot-dynamic-data/

    And another one based on his

    https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

  • 3261255
    3261255 Member Posts: 6
    edited Aug 24, 2018 10:29AM

    Yes, I get idea from Anton's solution(there is link in question description) and my implementation is based on his. In my case I need pivot two columns. And my question is Why when I call pivot('some sql'), a function ODCITablePrepare doesn't pass parameters correctly.

  • 3261255
    3261255 Member Posts: 6
    edited Aug 24, 2018 10:45AM

    Yes, I can generate sql and return opened open ref cursor but I don't know what to do with it after that(I'm not a big oracle expert). I result have to be a VIEW and I don't know how to do this. I know how to do this with pipelined function but I need a TYPE that has been previously defined. Can you give me same example(or some hint for that) how from ref cursor to create a VIEW?

  • Unknown
    edited Aug 24, 2018 11:38AM
    Yes, I get idea from Anton's solution(there is link in question description) and my implementation is based on his.

    Sorry - but I find that hard to believe since a review of YOUR code and his shows MAJOR differences.

    Post results that show you have tried his EXACT example and code and that his example works for you. Then we can go from there.

    I result have to be a VIEW and I don't know how to do this. I know how to do this with pipelined function but I need a TYPE that has been previously defined. Can you give me same example(or some hint for that) how from ref cursor to create a VIEW?

    No - because you can't create a 'view' from a cursor. You can create it from a query - such as a query of a pipelined function - but not from a cursor.

    If you need a view it needs to be based on KNOWN columns and datatypes - there are NO workarounds.

This discussion has been closed.