oracle - How to fetch cursor values from pivot SQL to fixed variables - Error ORA-01007 -
i have sql statement pivot operation. maximum number of columns have in pivot 5, can have less, 4, 3, 2. how can read these columns in cursor , assign (fetch .. into...) fixed variables, without occurring error ora-01007.
... sql_stmt := 'select * (select codcoligada, idprd, codcfo, valnegociado tcitmorcamento codcoligada = ' || p_codcoligada || ' , codcotacao = ' || '''' || p_codcotacao || '''' || ') pivot ( sum(valnegociado) codcfo in (' || pivot_clause || ') )'; ret := t_tab_sesa_cotacao(); open vcursor sql_stmt; loop /* if cursor returns less 5 columns in pivot error occurs ora-01007 */ fetch vcursor vcodcoligada, vidproduto, vvalor01, vvalor02, vvalor03, vvalor04, vvalor05; exit when vcursor%notfound; ret.extend; ret(ret.count) := t_type_sesa_cotacao(vcodcoligada, vidproduto, vvalor01, vvalor02, vvalor03, vvalor04, vvalor05); end loop; close vcursor; ... if return less 5 colums, want fill in remainder of variables value of 0 or null.
the variables vcodcoligada , vidproduto identified, pivot columns can vary between 1 , 5 (vvalor1, vvalor2, vvalor3, vvalor4, vvalor5)
result pivot sql:
codcoligada idprd '000125' '002272' '002342' ----------------- ---------------- ---------------- ---------------- ---------------- 1 15464 45 300 30 1 18460 35 200 20 1 57492 20 100 10 -------- end of data -------- example: if cursor returns 3 values in pivot (above), variables vvalor01, vvalor02, vvalor03 filled in, , variables vvalor04, vvalor05 must 0 or null.
example:
codcoligada idprd valor01 valor02 valor03 valor04 valor05 ----------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- 1 15464 45 300 30 0 0 1 18460 35 200 20 0 0 1 57492 20 100 10 0 0 -------- end of data -------- as have 3 columns in pivot, , have 5 variables, ora-01007 error occurs in (fetch .. ...).
hope below snippet helps. basic understanding need add excess variable null or blank make work.
set serveroutput on; declare lv_pivot varchar2(100):='''y'',''n'''; type lv record ( flg_y varchar2(100), flg_n varchar2(100), flg_e varchar2(100)); type lv_tab table of lv; lv_num lv_tab; lv_check varchar2(1000); begin lv_check :=regexp_count(lv_pivot,',',1); if lv_check < 3 z in 1..(2-lv_check) loop lv_pivot:=lv_pivot||',null val'||z; end loop; else lv_pivot:=lv_pivot; end if; dbms_output.put_line(lv_pivot); execute immediate ' select * (select col1 <table> ) pivot ( count(1) col1 in ('||lv_pivot||'))' bulk collect lv_num; end; ---------------------------refactoring in function------------------------------ --create object type create or replace type lv_obj object ( flg_y varchar2(100), flg_n varchar2(100), flg_e varchar2(100) ); --create table type create or replace type lv_tab table of lv_obj; --create function create or replace function test_func return lv_tab lv_pivot varchar2(100):='''y'',''n'''; lv_num lv_tab; lv_check varchar2(1000); begin lv_check :=regexp_count(lv_pivot,',',1); if lv_check < 3 z in 1..(2-lv_check) loop lv_pivot:=lv_pivot||',null val'||z; end loop; else lv_pivot:=lv_pivot; end if; dbms_output.put_line(lv_pivot); execute immediate ' select * (select col1 <table> ) pivot ( count(1) col1 in ('||lv_pivot||'))' bulk collect lv_num; return lv_tab; end; -------------------------------------------------output----------------------------------------------- select * table(test_func); -------------------------------------------------------------------------------------------------------
Comments
Post a Comment