Home » SQL & PL/SQL » SQL & PL/SQL » transposing of data in oracle (oracle 12 c)
| transposing of data in oracle [message #653167] |
Tue, 28 June 2016 09:28  |
 |
tolupuluri
Messages: 21
Registered: May 2016
|
Junior Member |
|
|
Hi all,
I am new to this forum.
I am looking for the solution on transposing data from rows to columns, but with some special requirement.
I managed to search the forums and i have tried the same, but that will not fit for my requirement.
Attached is the sample data that i am working on and the desired output that i am looking for.
Please let me know some hints to achieve this.
thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653189 is a reply to message #653181] |
Tue, 28 June 2016 12:31  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Bill B wrote on Tue, 28 June 2016 11:14The number of rows has nothing to do with pivoting.
It does if you are using pivoting to transpose. Many people confuse one for the other. Transposing is pivot where pivoting column is ROWID (in real life we create row enumeration and use it as pivoting column). Therefore, you can't transpose table that has 1,000+ rows.
SY.
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653215 is a reply to message #653167] |
Wed, 29 June 2016 04:53 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have provided a few pivot methods for selecting the data. It is up to you to use them to create a second table or insert into a second table or whatever you plan to do with the results. The last two methods are dynamic and don't require hard-cording the number of columns.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM table1
2 /
STDNO SUBJ MARKS
---------- ----- ----------
1 A 50
1 B 100
1 C 25
1 D 50
1 E 100
1 F 20
1 G 40
1 H 60
1 I 80
1 J 100
1 K 100
11 rows selected.
-- older pivot method:
SCOTT@orcl_12.1.0.2.0> SELECT stdno,
2 MAX (DECODE (rn, 1, subj)) subj1,
3 SUM (DECODE (rn, 1, marks)) marks1,
4 MAX (DECODE (rn, 2, subj)) subj2,
5 SUM (DECODE (rn, 2, marks)) marks2,
6 MAX (DECODE (rn, 3, subj)) subj3,
7 SUM (DECODE (rn, 3, marks)) marks3,
8 MAX (DECODE (rn, 4, subj)) subj4,
9 SUM (DECODE (rn, 4, marks)) marks4,
10 MAX (DECODE (rn, 5, subj)) subj5,
11 SUM (DECODE (rn, 5, marks)) marks5
12 FROM (SELECT stdno, subj, marks, groups,
13 ROW_NUMBER () OVER
14 (PARTITION BY stdno, groups ORDER BY subj) rn
15 FROM (SELECT stdno, subj, marks,
16 SUM (DECODE (lag_marks, 100, 1, 0)) OVER
17 (PARTITION BY stdno ORDER BY subj) groups
18 FROM (SELECT stdno, subj, marks,
19 LAG (marks) OVER (PARTITION BY stdno ORDER BY subj) lag_marks
20 FROM table1)))
21 GROUP BY stdno, groups
22 ORDER BY stdno, subj1
23 /
STDNO SUBJ1 MARKS1 SUBJ2 MARKS2 SUBJ3 MARKS3 SUBJ4 MARKS4 SUBJ5 MARKS5
---------- ----- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ----------
1 A 50 B 100
1 C 25 D 50 E 100
1 F 20 G 40 H 60 I 80 J 100
1 K 100
4 rows selected.
-- newer pivot method:
SCOTT@orcl_12.1.0.2.0> SELECT *
2 FROM (SELECT stdno, subj, marks, groups,
3 ROW_NUMBER () OVER
4 (PARTITION BY stdno, groups ORDER BY subj) rn
5 FROM (SELECT stdno, subj, marks,
6 SUM (DECODE (lag_marks, 100, 1, 0)) OVER
7 (PARTITION BY stdno ORDER BY subj) groups
8 FROM (SELECT stdno, subj, marks,
9 NVL (LAG (marks) OVER (PARTITION BY stdno ORDER BY subj), 100) lag_marks
10 FROM table1)))
11 PIVOT (MAX (subj) AS subj, SUM (marks) AS marks
12 FOR rn IN (1, 2, 3, 4, 5))
13 ORDER BY stdno, groups
14 /
STDNO GROUPS 1_SUB 1_MARKS 2_SUB 2_MARKS 3_SUB 3_MARKS 4_SUB 4_MARKS 5_SUB 5_MARKS
---------- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ----------
1 1 A 50 B 100
1 2 C 25 D 50 E 100
1 3 F 20 G 40 H 60 I 80 J 100
1 4 K 100
4 rows selected.
-- dynammic generation and execution of above method:
SCOTT@orcl_12.1.0.2.0> VARIABLE g_ref REFCURSOR
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_sql CLOB;
3 BEGIN
4 v_sql :=
5 'SELECT *
6 FROM (SELECT stdno, subj, marks, groups,
7 ROW_NUMBER () OVER
8 (PARTITION BY stdno, groups ORDER BY subj) rn
9 FROM (SELECT stdno, subj, marks,
10 SUM (DECODE (lag_marks, 100, 1, 0)) OVER
11 (PARTITION BY stdno ORDER BY subj) groups
12 FROM (SELECT stdno, subj, marks,
13 NVL (LAG (marks) OVER (PARTITION BY stdno ORDER BY subj), 100) lag_marks
14 FROM table1)))
15 PIVOT (MAX (subj) AS subj, SUM (marks) AS marks
16 FOR rn IN (';
17 FOR r IN
18 (SELECT DISTINCT (ROW_NUMBER () OVER (PARTITION BY stdno, groups ORDER BY subj)) rn
19 FROM (SELECT stdno, subj, marks,
20 SUM (DECODE (lag_marks, 100, 1, 0)) OVER
21 (PARTITION BY stdno ORDER BY subj) groups
22 FROM (SELECT stdno, subj, marks,
23 NVL (LAG (marks) OVER (PARTITION BY stdno ORDER BY subj), 100) lag_marks
24 FROM table1))
25 ORDER BY rn)
26 LOOP
27 v_sql := v_sql || r.rn || ',';
28 END LOOP;
29 v_sql := RTRIM (v_sql, ',') || ')) ORDER BY stdno, groups';
30 --DBMS_OUTPUT.PUT_LINE (v_sql);
31 OPEN :g_ref FOR v_sql;
32 END;
33 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> PRINT g_ref
STDNO GROUPS 1_SUB 1_MARKS 2_SUB 2_MARKS 3_SUB 3_MARKS 4_SUB 4_MARKS 5_SUB 5_MARKS
---------- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ----------
1 1 A 50 B 100
1 2 C 25 D 50 E 100
1 3 F 20 G 40 H 60 I 80 J 100
1 4 K 100
4 rows selected.
-- dynamic pivot method:
-- Download Anton Scheffer's dynamic pivot function from
-- https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip
-- Unzip it and run pivotfun.sql to create the funciton.
-- I edited the function to change the name from pivot to pivotfun to avoid conflict with an existing Oracle name
-- and commented out the dbms_output lines from the object before running it.
-- When you use it, it requires that you provide an alias for the aggregate in the query that you pass to it.
SCOTT@orcl_12.1.0.2.0> SELECT *
2 FROM TABLE (pivotfun (
3 'SELECT stdno, groups, rn, MAX (subjmarks) subjmarks
4 FROM (SELECT stdno, subj || ''='' || marks subjmarks, groups,
5 ''subj=marks'' || ROW_NUMBER () OVER
6 (PARTITION BY stdno, groups ORDER BY subj) rn
7 FROM (SELECT stdno, subj, marks,
8 SUM (DECODE (lag_marks, 100, 1, 0)) OVER
9 (PARTITION BY stdno ORDER BY subj) groups
10 FROM (SELECT stdno, subj, marks,
11 NVL (LAG (marks) OVER (PARTITION BY stdno ORDER BY subj), 100) lag_marks
12 FROM table1)))
13 GROUP BY stdno, groups, rn' ) )
14 ORDER BY stdno, groups
15 /
STDNO GROUPS SUBJ=MARKS1 SUBJ=MARKS2 SUBJ=MARKS3 SUBJ=MARKS4 SUBJ=MARKS5
---------- ---------- ----------- ----------- ----------- ----------- -----------
1 1 A=50 B=100
1 2 C=25 D=50 E=100
1 3 F=20 G=40 H=60 I=80 J=100
1 4 K=100
4 rows selected.
|
|
|
|
| Re: transposing of data in oracle [message #653230 is a reply to message #653215] |
Wed, 29 June 2016 08:41 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara,
ODCI table interface method ODCITableDescribe is called at HARD parse time only. And because it is called at parse time we can't pass expressions as ODCITableDescribe parameters. Only literals will be passed and expressions will be substituted with NULL. Anton knows that, however he doesn't explain it in his examples (and I believe in article itself). Look at pivot.sql script in that zip file you referenced:
select * from table( pivot( 'select deptno, job, decode (count(1),0,null, count(1)) count_job from emp group by deptno, job' ) )
/
select * from table( pivot( 'select deptno, job, decode (count(1),0,null, count(1+0)) count_job from emp group by deptno, job' ) )
Notice first call to pivot p has count(1) in select passed to pivot as parameter and second call has count(1+0)? That's the "trick" Anton uses to get correct results after he added new job QUIZMSTER to emp table. Look what happens if we pass exactly same query:
SQL> select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job'))
2 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30 1 1 4
20 2 2 1
10 1 1 1
SQL> insert into emp
2 ( empno, deptno, job, ename)
3 values
4 ( 1112, 30, 'QUIZMSTER', 'ALEX')
5 /
1 row created.
SQL> select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job'))
2 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30 1 1 4
20 2 2 1
10 1 1 1
SQL>
No QUIZMSTER. Why? First time we issued that statement Oracle checked shared pool and didn't find it. So hard parse was done and ODCITableDescribe was called which determined there are 5 distinct jobs in emp table and therefore defined 5 columns. Then we added new job QUIZMSTER and issued same select. Oracle again checked shared pool and this time it found same statement and therefore did soft parse which means ODCITableDescribe was not called and therefore same 5 columns were reused. By changing statement (even by one character, e.g. number of spaces between tokens) we are forcing (subject to CURSOR_SHARING) Oracle into treating logically same statement as new one thus performing hard parse which will call ODCITableDescribe and number of distinct jobs in emp table and therefore number of columns will be recalculated. I will simply add space in front of select:
SQL> column quizmster format a10
SQL> select * from table(pivot(' select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job'))
2 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ----------
30 1 1 1 4
20 2 2 1
10 1 1 1
SQL>
As you can see, we need to generate new statement (text-wise) each time to guarantee right results. In SQL*PLus it can be done via substitution variables. For example:
SQL> create sequence force_hard_parse
2 /
Sequence created.
SQL> column force_hard_parse new_value force_hard_parse noprint
SQL> select force_hard_parse.nextval force_hard_parse
2 from dual
3 /
SQL> select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* &force_hard_parse */'))
2 /
old 1: select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* &for
new 1: select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* 1
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
30 1 1 4
20 2 2 1
10 1 1 1
SQL> insert into emp
2 ( empno, deptno, job, ename)
3 values
4 ( 1112, 30, 'QUIZMSTER', 'ALEX')
5 /
1 row created.
SQL> select force_hard_parse.nextval force_hard_parse
2 from dual
3 /
SQL> select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* &force_hard_parse */'))
2 /
old 1: select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* &for
new 1: select * from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job /* 2
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ----------
30 1 1 1 4
20 2 2 1
10 1 1 1
SQL>
OP will have to implement something like that otherwise your solution using Anton's pivotfun will not produce right results. And price of dynamic pivoting via ODCI table interface is forcing hard parse each time dynamic pivot is used and what can be even more costly littering shared pool with all these versions of same statement thus affecting parse time of all statements issued against the database.
SY.
[Updated on: Wed, 29 June 2016 08:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653235 is a reply to message #653234] |
Wed, 29 June 2016 16:32 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Why can't you just purge the one sql from the shared pool. This should work in version 10.2 and above. put the code at the very end of the pivotfun procedure.
DECLARE
X_KILL VARCHAR2(100);
BEGIN
SELECT B.ADDRESS || ' , '|| B.HASH_VALUE
INTO X_KILL
FROM v$session a,v$sqlarea b
WHERE a.SID = userenv('SID')
AND UPPER(b.SQL_TEXT) LIKE '%PIVOTFUN%'
AND a.sql_id = b.sql_id;
DBMS_SHARED_POOL.PURGE (X_KILL, 'C');
END;
[Updated on: Wed, 29 June 2016 16:33] Report message to a moderator |
|
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653276 is a reply to message #653257] |
Thu, 30 June 2016 14:21 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Bill,
It sounds like a good idea. I had to remove the spaces from around the concatenated comma. If I run it anywhere between queries, then I get correct results, as shown below. However, you said to add it to the pivotfun procedure. Pivotfun is not a procedure; It is a pipelined function that uses an object type named pivotimpl. I can't figure out where inside the function to add the code you provided. You can't just add it to the end of the code inside such a function. Could you please advise where to put it?
Thanks,
Barbara
SCOTT@orcl_12.1.0.2.0> drop function pivotfun
2 /
Function dropped.
SCOTT@orcl_12.1.0.2.0> drop type pivotimpl
2 /
Type dropped.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE
2 type PivotImpl as object
3 (
4 ret_type anytype, -- The return type of the table function
5 stmt varchar2(32767),
6 fmt varchar2(32767),
7 cur integer,
8 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
9 return number,
10 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
11 return number,
12 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
13 return number,
14 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
15 return number,
16 member function ODCITableClose( self in PivotImpl )
17 return number
18 )
19 /
Type created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> create or replace type body PivotImpl as
2 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
3 return number
4 is
5 atyp anytype;
6 cur integer;
7 numcols number;
8 desc_tab dbms_sql.desc_tab2;
9 rc sys_refcursor;
10 t_c2 varchar2(32767);
11 t_fmt varchar2(1000);
12 begin
13 cur := dbms_sql.open_cursor;
14 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
15 dbms_sql.describe_columns2( cur, numcols, desc_tab );
16 dbms_sql.close_cursor( cur );
17 --
18 anytype.begincreate( dbms_types.typecode_object, atyp );
19 for i in 1 .. numcols - 2
20 loop
21 atyp.addattr( desc_tab( i ).col_name
22 , case desc_tab( i ).col_type
23 when 1 then dbms_types.typecode_varchar2
24 when 2 then dbms_types.typecode_number
25 when 9 then dbms_types.typecode_varchar2
26 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
27 when 12 then dbms_types.typecode_date
28 when 208 then dbms_types.typecode_varchar2 -- show urowid as varchar2
29 when 96 then dbms_types.typecode_char
30 when 180 then dbms_types.typecode_timestamp
31 when 181 then dbms_types.typecode_timestamp_tz
32 when 231 then dbms_types.typecode_timestamp_ltz
33 when 182 then dbms_types.typecode_interval_ym
34 when 183 then dbms_types.typecode_interval_ds
35 end
36 , desc_tab( i ).col_precision
37 , desc_tab( i ).col_scale
38 , case desc_tab( i ).col_type
39 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
40 else desc_tab( i ).col_max_len
41 end
42 , desc_tab( i ).col_charsetid
43 , desc_tab( i ).col_charsetform
44 );
45 end loop;
46 if instr( p_fmt, '@p@' ) > 0
47 then
48 t_fmt := p_fmt;
49 else
50 t_fmt := '@p@';
51 end if;
52 open rc for replace( 'select distinct ' || t_fmt || '
53 from( ' || p_stmt || ' )
54 order by ' || t_fmt
55 , '@p@'
56 , desc_tab( numcols - 1 ).col_name
57 );
58 loop
59 fetch rc into t_c2;
60 exit when rc%notfound;
61 atyp.addattr( t_c2
62 , case desc_tab( numcols ).col_type
63 when 1 then dbms_types.typecode_varchar2
64 when 2 then dbms_types.typecode_number
65 when 9 then dbms_types.typecode_varchar2
66 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
67 when 12 then dbms_types.typecode_date
68 when 208 then dbms_types.typecode_urowid
69 when 96 then dbms_types.typecode_char
70 when 180 then dbms_types.typecode_timestamp
71 when 181 then dbms_types.typecode_timestamp_tz
72 when 231 then dbms_types.typecode_timestamp_ltz
73 when 182 then dbms_types.typecode_interval_ym
74 when 183 then dbms_types.typecode_interval_ds
75 end
76 , desc_tab( numcols ).col_precision
77 , desc_tab( numcols ).col_scale
78 , case desc_tab( numcols ).col_type
79 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
80 else desc_tab( numcols ).col_max_len
81 end
82 , desc_tab( numcols ).col_charsetid
83 , desc_tab( numcols ).col_charsetform
84 );
85 end loop;
86 close rc;
87 atyp.endcreate;
88 anytype.begincreate( dbms_types.typecode_table, rtype );
89 rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
90 rtype.endcreate();
91 return odciconst.success;
92 exception
93 when others then
94 return odciconst.error;
95 end;
96 --
97 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
98 return number
99 is
100 prec pls_integer;
101 scale pls_integer;
102 len pls_integer;
103 csid pls_integer;
104 csfrm pls_integer;
105 elem_typ anytype;
106 aname varchar2(30);
107 tc pls_integer;
108 begin
109 tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
110 --
111 if instr( p_fmt, '@p@' ) > 0
112 then
113 sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
114 else
115 sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
116 end if;
117 return odciconst.success;
118 end;
119 --
120 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
121 return number
122 is
123 cur integer;
124 numcols number;
125 desc_tab dbms_sql.desc_tab2;
126 t_stmt varchar2(32767);
127 type_code pls_integer;
128 prec pls_integer;
129 scale pls_integer;
130 len pls_integer;
131 csid pls_integer;
132 csfrm pls_integer;
133 schema_name varchar2(30);
134 type_name varchar2(30);
135 version varchar2(30);
136 attr_count pls_integer;
137 attr_type anytype;
138 attr_name varchar2(100);
139 dummy2 integer;
140 begin
141 cur := dbms_sql.open_cursor;
142 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
143 dbms_sql.describe_columns2( cur, numcols, desc_tab );
144 dbms_sql.close_cursor( cur );
145 --
146 for i in 1 .. numcols - 2
147 loop
148 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
149 end loop;
150 --
151 type_code := sctx.ret_type.getinfo( prec
152 , scale
153 , len
154 , csid
155 , csfrm
156 , schema_name
157 , type_name
158 , version
159 , attr_count
160 );
161 for i in numcols - 1 .. attr_count
162 loop
163 type_code := sctx.ret_type.getattreleminfo( i
164 , prec
165 , scale
166 , len
167 , csid
168 , csfrm
169 , attr_type
170 , attr_name
171 );
172 t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
173 , '@p@'
174 , desc_tab( numcols - 1 ).col_name
175 );
176 end loop;
177 t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
178 for i in 1 .. numcols - 2
179 loop
180 if i = 1
181 then
182 t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
183 else
184 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
185 end if;
186 end loop;
187 --
188 --dbms_output.put_line( t_stmt );
189 sctx.cur := dbms_sql.open_cursor;
190 dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
191 for i in 1 .. attr_count
192 loop
193 type_code := sctx.ret_type.getattreleminfo( i
194 , prec
195 , scale
196 , len
197 , csid
198 , csfrm
199 , attr_type
200 , attr_name
201 );
202 case type_code
203 when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
204 when dbms_types.typecode_varchar2 then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
205 when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
206 when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
207 when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
208 when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
209 when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
210 when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
211 when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
212 when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
213 end case;
214 end loop;
215 dummy2 := dbms_sql.execute( sctx.cur );
216 return odciconst.success;
217 end;
218 --
219 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
220 return number
221 is
222 c1_col_type pls_integer;
223 type_code pls_integer;
224 prec pls_integer;
225 scale pls_integer;
226 len pls_integer;
227 csid pls_integer;
228 csfrm pls_integer;
229 schema_name varchar2(30);
230 type_name varchar2(30);
231 version varchar2(30);
232 attr_count pls_integer;
233 attr_type anytype;
234 attr_name varchar2(100);
235 v1 varchar2(32767);
236 n1 number;
237 d1 date;
238 ur1 urowid;
239 ids1 interval day to second;
240 iym1 interval year to month;
241 ts1 timestamp;
242 tstz1 timestamp with time zone;
243 tsltz1 timestamp with local time zone;
244 begin
245 outset := null;
246 if nrows < 1
247 then
248 -- is this possible???
249 return odciconst.success;
250 end if;
251 --
252 --dbms_output.put_line( 'fetch' );
253 if dbms_sql.fetch_rows( self.cur ) = 0
254 then
255 return odciconst.success;
256 end if;
257 --
258 --dbms_output.put_line( 'done' );
259 type_code := self.ret_type.getinfo( prec
260 , scale
261 , len
262 , csid
263 , csfrm
264 , schema_name
265 , type_name
266 , version
267 , attr_count
268 );
269 anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
270 outset.addinstance;
271 outset.piecewise();
272 for i in 1 .. attr_count
273 loop
274 type_code := self.ret_type.getattreleminfo( i
275 , prec
276 , scale
277 , len
278 , csid
279 , csfrm
280 , attr_type
281 , attr_name
282 );
283 --dbms_output.put_line( attr_name );
284 case type_code
285 when dbms_types.typecode_char then
286 dbms_sql.column_value( self.cur, i, v1 );
287 outset.setchar( v1 );
288 when dbms_types.typecode_varchar2 then
289 dbms_sql.column_value( self.cur, i, v1 );
290 outset.setvarchar2( v1 );
291 when dbms_types.typecode_number then
292 dbms_sql.column_value( self.cur, i, n1 );
293 outset.setnumber( n1 );
294 when dbms_types.typecode_date then
295 dbms_sql.column_value( self.cur, i, d1 );
296 outset.setdate( d1 );
297 when dbms_types.typecode_urowid then
298 dbms_sql.column_value( self.cur, i, ur1 );
299 outset.seturowid( ur1 );
300 when dbms_types.typecode_interval_ds then
301 dbms_sql.column_value( self.cur, i, ids1 );
302
303 outset.setintervalds( ids1 );
304 when dbms_types.typecode_interval_ym then
305 dbms_sql.column_value( self.cur, i, iym1 );
306 outset.setintervalym( iym1 );
307 when dbms_types.typecode_timestamp then
308 dbms_sql.column_value( self.cur, i, ts1 );
309 outset.settimestamp( ts1 );
310 when dbms_types.typecode_timestamp_tz then
311 dbms_sql.column_value( self.cur, i, tstz1 );
312 outset.settimestamptz( tstz1 );
313 when dbms_types.typecode_timestamp_ltz then
314 dbms_sql.column_value( self.cur, i, tsltz1 );
315 outset.settimestampltz( tsltz1 );
316 end case;
317 end loop;
318 outset.endcreate;
319 return odciconst.success;
320 end;
321 --
322 member function ODCITableClose( self in PivotImpl )
323 return number
324 is
325 c integer;
326 begin
327 c := self.cur;
328 dbms_sql.close_cursor( c );
329 return odciconst.success;
330 end;
331 end;
332 /
Type body created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> create or replace function pivotfun
2 (p_stmt in varchar2,
3 p_fmt in varchar2 := 'upper(@p@)',
4 dummy in number := 0)
5 return anydataset pipelined using PivotImpl;
6 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> select *
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from emp
5 group by deptno, job'))
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1
20 2 2 1
30 1 1 4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> DECLARE
2 X_KILL VARCHAR2(100);
3 BEGIN
4 SELECT B.ADDRESS || ',' || B.HASH_VALUE
5 INTO X_KILL
6 FROM v$session a,v$sqlarea b
7 WHERE a.SID = userenv('SID')
8 AND UPPER(b.SQL_TEXT) LIKE '%PIVOTFUN%'
9 AND a.sql_id = b.sql_id;
10 DBMS_OUTPUT.PUT_LINE ('X_KILL: ' || X_KILL);
11 SYS.DBMS_SHARED_POOL.PURGE (X_KILL, 'C');
12 END;
13 /
X_KILL: 00007FFB12618228,58510759
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> insert into emp
2 ( empno, deptno, job, ename)
3 values
4 ( 1112, 30, 'QUIZMSTER', 'ALEX')
5 /
1 row created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> select *
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from emp
5 group by deptno, job'))
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1
20 2 2 1
30 1 1 1 4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from emp where empno = 1112
2 /
1 row deleted.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
|
|
|
|
| Re: transposing of data in oracle [message #653278 is a reply to message #653276] |
Thu, 30 June 2016 16:03 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
The ODCITableClose is the last function called during the call and is used for cleanup. If you put the code just after the
dbms_sql.close_cursor( c );
The code should work. Also if the PivotImpl type nd type body is placed in system you only have to make the pivotfun procedure executable by public and make a public synonym and since it is being run from system it should be able to run the dbms_shared_pool without granting it to anyone else since the AUTHID is definer.
[Updated on: Thu, 30 June 2016 16:06] Report message to a moderator |
|
|
|
| Re: transposing of data in oracle [message #653282 is a reply to message #653278] |
Thu, 30 June 2016 16:45 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Bill,
That works. You should tell Anton. I think a lot of people might want to use it with this problem solved. Oracle might even want to include it in a future version.
Barbara
SYS@orcl_12.1.0.2.0> -- by Anton Scheffer
SYS@orcl_12.1.0.2.0> -- downloaded https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip
SYS@orcl_12.1.0.2.0> -- unzipped
SYS@orcl_12.1.0.2.0> -- edited pivotfun.sql:
SYS@orcl_12.1.0.2.0> -- changed function name from pivot to pivotfun to avoid conflict with existing Oracle name
SYS@orcl_12.1.0.2.0> -- commented out dbms_output lines in pivotimpl
SYS@orcl_12.1.0.2.0> -- added section to purge from shared pool as suggested by Bill B at:
SYS@orcl_12.1.0.2.0> -- http://www.orafaq.com/forum/mv/msg/201346/653235/#msg_653235
SYS@orcl_12.1.0.2.0> CREATE OR REPLACE
2 type PivotImpl as object
3 (
4 ret_type anytype, -- The return type of the table function
5 stmt varchar2(32767),
6 fmt varchar2(32767),
7 cur integer,
8 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
9 return number,
10 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
11 return number,
12 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
13 return number,
14 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
15 return number,
16 member function ODCITableClose( self in PivotImpl )
17 return number
18 )
19 /
Type created.
SYS@orcl_12.1.0.2.0> show errors
No errors.
SYS@orcl_12.1.0.2.0> create or replace type body PivotImpl as
2 static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
3 return number
4 is
5 atyp anytype;
6 cur integer;
7 numcols number;
8 desc_tab dbms_sql.desc_tab2;
9 rc sys_refcursor;
10 t_c2 varchar2(32767);
11 t_fmt varchar2(1000);
12 begin
13 cur := dbms_sql.open_cursor;
14 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
15 dbms_sql.describe_columns2( cur, numcols, desc_tab );
16 dbms_sql.close_cursor( cur );
17 --
18 anytype.begincreate( dbms_types.typecode_object, atyp );
19 for i in 1 .. numcols - 2
20 loop
21 atyp.addattr( desc_tab( i ).col_name
22 , case desc_tab( i ).col_type
23 when 1 then dbms_types.typecode_varchar2
24 when 2 then dbms_types.typecode_number
25 when 9 then dbms_types.typecode_varchar2
26 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
27 when 12 then dbms_types.typecode_date
28 when 208 then dbms_types.typecode_varchar2 -- show urowid as varchar2
29 when 96 then dbms_types.typecode_char
30 when 180 then dbms_types.typecode_timestamp
31 when 181 then dbms_types.typecode_timestamp_tz
32 when 231 then dbms_types.typecode_timestamp_ltz
33 when 182 then dbms_types.typecode_interval_ym
34 when 183 then dbms_types.typecode_interval_ds
35 end
36 , desc_tab( i ).col_precision
37 , desc_tab( i ).col_scale
38 , case desc_tab( i ).col_type
39 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
40 else desc_tab( i ).col_max_len
41 end
42 , desc_tab( i ).col_charsetid
43 , desc_tab( i ).col_charsetform
44 );
45 end loop;
46 if instr( p_fmt, '@p@' ) > 0
47 then
48 t_fmt := p_fmt;
49 else
50 t_fmt := '@p@';
51 end if;
52 open rc for replace( 'select distinct ' || t_fmt || '
53 from( ' || p_stmt || ' )
54 order by ' || t_fmt
55 , '@p@'
56 , desc_tab( numcols - 1 ).col_name
57 );
58 loop
59 fetch rc into t_c2;
60 exit when rc%notfound;
61 atyp.addattr( t_c2
62 , case desc_tab( numcols ).col_type
63 when 1 then dbms_types.typecode_varchar2
64 when 2 then dbms_types.typecode_number
65 when 9 then dbms_types.typecode_varchar2
66 when 11 then dbms_types.typecode_varchar2 -- show rowid as varchar2
67 when 12 then dbms_types.typecode_date
68 when 208 then dbms_types.typecode_urowid
69 when 96 then dbms_types.typecode_char
70 when 180 then dbms_types.typecode_timestamp
71 when 181 then dbms_types.typecode_timestamp_tz
72 when 231 then dbms_types.typecode_timestamp_ltz
73 when 182 then dbms_types.typecode_interval_ym
74 when 183 then dbms_types.typecode_interval_ds
75 end
76 , desc_tab( numcols ).col_precision
77 , desc_tab( numcols ).col_scale
78 , case desc_tab( numcols ).col_type
79 when 11 then 18 -- for rowid col_max_len = 16, and 18 characters are shown
80 else desc_tab( numcols ).col_max_len
81 end
82 , desc_tab( numcols ).col_charsetid
83 , desc_tab( numcols ).col_charsetform
84 );
85 end loop;
86 close rc;
87 atyp.endcreate;
88 anytype.begincreate( dbms_types.typecode_table, rtype );
89 rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
90 rtype.endcreate();
91 return odciconst.success;
92 exception
93 when others then
94 return odciconst.error;
95 end;
96 --
97 static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
98 return number
99 is
100 prec pls_integer;
101 scale pls_integer;
102 len pls_integer;
103 csid pls_integer;
104 csfrm pls_integer;
105 elem_typ anytype;
106 aname varchar2(30);
107 tc pls_integer;
108 begin
109 tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
110 --
111 if instr( p_fmt, '@p@' ) > 0
112 then
113 sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
114 else
115 sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
116 end if;
117 return odciconst.success;
118 end;
119 --
120 static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
121 return number
122 is
123 cur integer;
124 numcols number;
125 desc_tab dbms_sql.desc_tab2;
126 t_stmt varchar2(32767);
127 type_code pls_integer;
128 prec pls_integer;
129 scale pls_integer;
130 len pls_integer;
131 csid pls_integer;
132 csfrm pls_integer;
133 schema_name varchar2(30);
134 type_name varchar2(30);
135 version varchar2(30);
136 attr_count pls_integer;
137 attr_type anytype;
138 attr_name varchar2(100);
139 dummy2 integer;
140 begin
141 cur := dbms_sql.open_cursor;
142 dbms_sql.parse( cur, p_stmt, dbms_sql.native );
143 dbms_sql.describe_columns2( cur, numcols, desc_tab );
144 dbms_sql.close_cursor( cur );
145 --
146 for i in 1 .. numcols - 2
147 loop
148 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
149 end loop;
150 --
151 type_code := sctx.ret_type.getinfo( prec
152 , scale
153 , len
154 , csid
155 , csfrm
156 , schema_name
157 , type_name
158 , version
159 , attr_count
160 );
161 for i in numcols - 1 .. attr_count
162 loop
163 type_code := sctx.ret_type.getattreleminfo( i
164 , prec
165 , scale
166 , len
167 , csid
168 , csfrm
169 , attr_type
170 , attr_name
171 );
172 t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
173 , '@p@'
174 , desc_tab( numcols - 1 ).col_name
175 );
176 end loop;
177 t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
178 for i in 1 .. numcols - 2
179 loop
180 if i = 1
181 then
182 t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
183 else
184 t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
185 end if;
186 end loop;
187 --
188 --dbms_output.put_line( t_stmt );
189 sctx.cur := dbms_sql.open_cursor;
190 dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
191 for i in 1 .. attr_count
192 loop
193 type_code := sctx.ret_type.getattreleminfo( i
194 , prec
195 , scale
196 , len
197 , csid
198 , csfrm
199 , attr_type
200 , attr_name
201 );
202 case type_code
203 when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
204 when dbms_types.typecode_varchar2 then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
205 when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
206 when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
207 when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
208 when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
209 when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
210 when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
211 when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
212 when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
213 end case;
214 end loop;
215 dummy2 := dbms_sql.execute( sctx.cur );
216 return odciconst.success;
217 end;
218 --
219 member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
220 return number
221 is
222 c1_col_type pls_integer;
223 type_code pls_integer;
224 prec pls_integer;
225 scale pls_integer;
226 len pls_integer;
227 csid pls_integer;
228 csfrm pls_integer;
229 schema_name varchar2(30);
230 type_name varchar2(30);
231 version varchar2(30);
232 attr_count pls_integer;
233 attr_type anytype;
234 attr_name varchar2(100);
235 v1 varchar2(32767);
236 n1 number;
237 d1 date;
238 ur1 urowid;
239 ids1 interval day to second;
240 iym1 interval year to month;
241 ts1 timestamp;
242 tstz1 timestamp with time zone;
243 tsltz1 timestamp with local time zone;
244 begin
245 outset := null;
246 if nrows < 1
247 then
248 -- is this possible???
249 return odciconst.success;
250 end if;
251 --
252 --dbms_output.put_line( 'fetch' );
253 if dbms_sql.fetch_rows( self.cur ) = 0
254 then
255 return odciconst.success;
256 end if;
257 --
258 --dbms_output.put_line( 'done' );
259 type_code := self.ret_type.getinfo( prec
260 , scale
261 , len
262 , csid
263 , csfrm
264 , schema_name
265 , type_name
266 , version
267 , attr_count
268 );
269 anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
270 outset.addinstance;
271 outset.piecewise();
272 for i in 1 .. attr_count
273 loop
274 type_code := self.ret_type.getattreleminfo( i
275 , prec
276 , scale
277 , len
278 , csid
279 , csfrm
280 , attr_type
281 , attr_name
282 );
283 --dbms_output.put_line( attr_name );
284 case type_code
285 when dbms_types.typecode_char then
286 dbms_sql.column_value( self.cur, i, v1 );
287 outset.setchar( v1 );
288 when dbms_types.typecode_varchar2 then
289 dbms_sql.column_value( self.cur, i, v1 );
290 outset.setvarchar2( v1 );
291 when dbms_types.typecode_number then
292 dbms_sql.column_value( self.cur, i, n1 );
293 outset.setnumber( n1 );
294 when dbms_types.typecode_date then
295 dbms_sql.column_value( self.cur, i, d1 );
296 outset.setdate( d1 );
297 when dbms_types.typecode_urowid then
298 dbms_sql.column_value( self.cur, i, ur1 );
299 outset.seturowid( ur1 );
300 when dbms_types.typecode_interval_ds then
301 dbms_sql.column_value( self.cur, i, ids1 );
302
303 outset.setintervalds( ids1 );
304 when dbms_types.typecode_interval_ym then
305 dbms_sql.column_value( self.cur, i, iym1 );
306 outset.setintervalym( iym1 );
307 when dbms_types.typecode_timestamp then
308 dbms_sql.column_value( self.cur, i, ts1 );
309 outset.settimestamp( ts1 );
310 when dbms_types.typecode_timestamp_tz then
311 dbms_sql.column_value( self.cur, i, tstz1 );
312 outset.settimestamptz( tstz1 );
313 when dbms_types.typecode_timestamp_ltz then
314 dbms_sql.column_value( self.cur, i, tsltz1 );
315 outset.settimestampltz( tsltz1 );
316 end case;
317 end loop;
318 outset.endcreate;
319 return odciconst.success;
320 end;
321 --
322 member function ODCITableClose( self in PivotImpl )
323 return number
324 is
325 c integer;
326 begin
327 c := self.cur;
328 dbms_sql.close_cursor( c );
329 DECLARE
330 X_KILL VARCHAR2(100);
331 BEGIN
332 SELECT B.ADDRESS || ',' || B.HASH_VALUE
333 INTO X_KILL
334 FROM v$session a,v$sqlarea b
335 WHERE a.SID = userenv('SID')
336 AND UPPER(b.SQL_TEXT) LIKE '%PIVOTFUN%'
337 AND a.sql_id = b.sql_id;
338 --DBMS_OUTPUT.PUT_LINE ('X_KILL: ' || X_KILL);
339 DBMS_SHARED_POOL.PURGE (X_KILL, 'C');
340 END;
341 return odciconst.success;
342 end;
343 end;
344 /
Type body created.
SYS@orcl_12.1.0.2.0> show errors
No errors.
SYS@orcl_12.1.0.2.0> create or replace
2 function pivotfun( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
3 return anydataset pipelined using PivotImpl;
4 /
Function created.
SYS@orcl_12.1.0.2.0> show errors
No errors.
SYS@orcl_12.1.0.2.0> GRANT EXECUTE ON pivotfun TO PUBLIC
2 /
Grant succeeded.
SYS@orcl_12.1.0.2.0> CREATE PUBLIC SYNONYM pivotfun FOR SYS.pivotfun
2 /
Synonym created.
SYS@orcl_12.1.0.2.0> CONNECT scott/tiger
Connected.
SCOTT@orcl_12.1.0.2.0> select *
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job'))
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1
20 2 2 1
30 1 1 4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> insert into scott.emp
2 ( empno, deptno, job, ename)
3 values
4 ( 1112, 30, 'QUIZMSTER', 'ALEX')
5 /
1 row created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> select *
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job'))
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1
20 2 2 1
30 1 1 1 4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from scott.emp where empno = 1112
2 /
1 row deleted.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
|
|
|
|
| Re: transposing of data in oracle [message #653324 is a reply to message #653282] |
Fri, 01 July 2016 15:17 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara, you are forgetting about multi-session environment. Assume session 1 issued that "pivotfun" statement. And while it is running session 2 added QUIZMSTER row to emp table and issued same statement. Oracle will take session 2 issued statement and will find it in shared pool since session 1 "pivotfun" statement is still running and QUIZMSTER column will not be calculated. For example, I'll create function sleep to imitate timing:
create or replace
function sleep(
p_sleep_interval number
)
return number
is
begin
dbms_lock.sleep(p_sleep_interval);
return p_sleep_interval;
end;
/
Now session 1 issues:
select t.*,sleep(5) from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job')) t
/
And while it's running session 2 issues:
insert into emp
( empno, deptno, job, ename)
values
( 1112, 30, 'QUIZMSTER', 'ALEX')
/
select t.*,sleep(5) from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job')) t
/
As a result both session 1 & 2 produce:
SQL> select t.*,sleep(5) from table(pivot('select deptno,job,decode(count(1),0,null,count(1)) count_job from emp group by deptno,job')) t
2 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN SLEEP(5)
---------- ---------- ---------- ---------- ---------- ---------- ----------
30 1 1 4 5
20 2 2 1 5
10 1 1 1 5
SQL>
Now I rerun statement in session 2:
SQL> /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN SLEEP(5)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
30 1 1 1 4 5
20 2 2 1 5
10 1 1 1 5
SQL>
As I already mentioned, the only 100% working way I know is making each statement distinct. Adding DBMS_SHARED_POOL.PURGE can only solve shared pool "littering" issue.
SY.
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653328 is a reply to message #653327] |
Fri, 01 July 2016 16:40 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
There is an easy way. Just like their is a function call in the type body to cleanup the call, there is a function to start the procedure. Remove the code I suggested and make the following alterations to the code for type function ODCITableStart. This will always generate a unique select and force a hard parse.
139 dummy2 integer;
.... x_stmt varchar2(32767);
140 begin
.... x_stmt := p_stmt||' /* '||to_char(the_sequence.nextval)||' */';
141 cur := dbms_sql.open_cursor;
142 dbms_sql.parse( cur, x_stmt, dbms_sql.native );
|
|
|
|
| Re: transposing of data in oracle [message #653329 is a reply to message #653328] |
Sat, 02 July 2016 01:39 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I realize that the new change is intended to make each select unique, so that there is no conflict between sessions. However, I don't understand why you say to remove the previous change. Don't we still want to purge the shared pool to avoid cluttering it with the various unique selects? I ran the pivotfun.sql script to create everything once, using a sequence that started with 10 to avoid possible conflicts with prior tests. Then I started the test502.sql script in one session. I used sleep(10) to make sure that I had enough time to click on a different window that I had set up and hit the enter key to start the test503.sql script before 10 seconds had elapsed. After both had finished, I ran a second run of the same thing. I have included my results below. I don't think they are the desired results. Please check it out and see what you think. Note the missing quizmstr column in the first run of test503.sql in the second session and the quizmstr column with null values in the second run of test502.sql in the first session.
-- pivotfun.sql (run once):
connect sys as sysdba
drop public synonym pivotfun
/
drop function pivotfun
/
drop type pivotimpl
/
drop sequence the_sequence
/
-- by Anton Scheffer
-- downloaded https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip
-- unzipped
-- edited pivotfun.sql:
-- changed function name from pivot to pivotfun to avoid conflict with existing Oracle name
-- commented out dbms_output lines in pivotimpl
-- added section to purge from shared pool and added section to create unique select to force hard parse
-- as suggested by Bill B at:
-- http://www.orafaq.com/forum/mv/msg/201346/653235/#msg_653235
create sequence the_sequence start with 10
/
CREATE OR REPLACE
type PivotImpl as object
(
ret_type anytype, -- The return type of the table function
stmt varchar2(32767),
fmt varchar2(32767),
cur integer,
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return number,
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return number,
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
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
)
/
show errors
create or replace type body PivotImpl as
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
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);
dummy2 integer;
x_stmt varchar2(32767);
begin
x_stmt := p_stmt || '/*' || to_char (the_sequence.nextval) || '*/';
cur := dbms_sql.open_cursor;
dbms_sql.parse( cur, x_stmt, dbms_sql.native );
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 - 2
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;
if instr( p_fmt, '@p@' ) > 0
then
t_fmt := p_fmt;
else
t_fmt := '@p@';
end if;
open rc for replace( 'select distinct ' || t_fmt || '
from( ' || p_stmt || ' )
order by ' || t_fmt
, '@p@'
, desc_tab( numcols - 1 ).col_name
);
loop
fetch rc into t_c2;
exit when rc%notfound;
atyp.addattr( t_c2
, case desc_tab( numcols ).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 ).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
return odciconst.error;
end;
--
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
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 );
--
if instr( p_fmt, '@p@' ) > 0
then
sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
else
sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
end if;
return odciconst.success;
end;
--
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
return number
is
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
t_stmt varchar2(32767);
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);
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 - 2
loop
t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
end loop;
--
type_code := sctx.ret_type.getinfo( prec
, scale
, len
, csid
, csfrm
, schema_name
, type_name
, version
, attr_count
);
for i in numcols - 1 .. attr_count
loop
type_code := sctx.ret_type.getattreleminfo( i
, prec
, scale
, len
, csid
, csfrm
, attr_type
, attr_name
);
t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
, '@p@'
, desc_tab( numcols - 1 ).col_name
);
end loop;
t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
for i in 1 .. numcols - 2
loop
if i = 1
then
t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
else
t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
end if;
end loop;
--
--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;
end;
--
member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
return number
is
c1_col_type pls_integer;
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;
--
--dbms_output.put_line( 'fetch' );
if dbms_sql.fetch_rows( self.cur ) = 0
then
return odciconst.success;
end if;
--
--dbms_output.put_line( 'done' );
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
);
--dbms_output.put_line( 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;
--
member function ODCITableClose( self in PivotImpl )
return number
is
c integer;
begin
c := self.cur;
dbms_sql.close_cursor( c );
DECLARE
X_KILL VARCHAR2(100);
BEGIN
SELECT B.ADDRESS || ',' || B.HASH_VALUE
INTO X_KILL
FROM v$session a,v$sqlarea b
WHERE a.SID = userenv('SID')
AND UPPER(b.SQL_TEXT) LIKE '%PIVOTFUN%'
AND a.sql_id = b.sql_id;
--DBMS_OUTPUT.PUT_LINE ('X_KILL: ' || X_KILL);
DBMS_SHARED_POOL.PURGE (X_KILL, 'C');
END;
return odciconst.success;
end;
end;
/
show errors
create or replace
function pivotfun( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return anydataset pipelined using PivotImpl;
/
show errors
GRANT EXECUTE ON pivotfun TO PUBLIC
/
CREATE PUBLIC SYNONYM pivotfun FOR SYS.pivotfun
/
-- test502.sql:
set linesize 100
select t.*, sleep (10)
from table (pivotfun (
'select deptno, job, count(*) count_job
from scott.emp
group by deptno, job')) t
order by deptno
/
-- test503.sql:
set linesize 100
insert into emp ( empno, deptno, job, ename) values ( 1112, 30, 'QUIZMSTER', 'ALEX')
/
commit
/
select t.*, sleep (10)
from table (pivotfun (
'select deptno, job, count(*) count_job
from scott.emp
group by deptno, job')) t
order by deptno
/
select t.*, sleep (10)
from table (pivotfun (
'select deptno, job, count(*) count_job
from scott.emp
group by deptno, job')) t
order by deptno
/
delete from emp where empno=1112
/
commit
/
-- first run of test502.sql from first session:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SCOTT@orcl_12.1.0.2.0> @test502
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> set linesize 100
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 4 10
3 rows selected.
-- first run of test503.sql from second session started right after test502.sql in first session:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SCOTT@orcl_12.1.0.2.0> @test503
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> set linesize 100
SCOTT@orcl_12.1.0.2.0> insert into emp ( empno, deptno, job, ename) values ( 1112, 30, 'QUIZMSTER', 'ALEX')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 4 10
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 1 4 10
3 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from emp where empno=1112
2 /
1 row deleted.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
-- second run of test502.sql in first session after others had finished:
SCOTT@orcl_12.1.0.2.0> @test502
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> set linesize 100
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 4 10
3 rows selected.
-- second run of test503.sql started in second session within 10 seconds of starting second run of test502.sql in first session:
SCOTT@orcl_12.1.0.2.0> @test503
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> set linesize 100
SCOTT@orcl_12.1.0.2.0> insert into emp ( empno, deptno, job, ename) values ( 1112, 30, 'QUIZMSTER', 'ALEX')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 1 4 10
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select t.*, sleep (10)
2 from table (pivotfun (
3 'select deptno, job, count(*) count_job
4 from scott.emp
5 group by deptno, job')) t
6 order by deptno
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT QUIZMSTER SALESMAN SLEEP(10)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 1 1 1 10
20 2 2 1 10
30 1 1 1 4 10
3 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from emp where empno=1112
2 /
1 row deleted.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
|
|
|
|
|
|
| Re: transposing of data in oracle [message #653333 is a reply to message #653331] |
Sat, 02 July 2016 02:40 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I can't believe I did that and didn't realize it. I thought I was in the odcitablestart function when I made the changes. I fixed the script and changed the sequence to start with 20, ran it again, then ran the same tests again, but got the same results. I have attached the fixed pivotfun.sql script below. It is half past midnight here, so I hoped I typed everything in the right place this time.
connect sys as sysdba
drop public synonym pivotfun
/
drop function pivotfun
/
drop type pivotimpl
/
drop sequence the_sequence
/
-- by Anton Scheffer
-- downloaded https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip
-- unzipped
-- edited pivotfun.sql:
-- changed function name from pivot to pivotfun to avoid conflict with existing Oracle name
-- commented out dbms_output lines in pivotimpl
-- added section to purge from shared pool and added section to create unique select to force hard parse
-- as suggested by Bill B at:
-- http://www.orafaq.com/forum/mv/msg/201346/653235/#msg_653235
create sequence the_sequence start with 20
/
CREATE OR REPLACE
type PivotImpl as object
(
ret_type anytype, -- The return type of the table function
stmt varchar2(32767),
fmt varchar2(32767),
cur integer,
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return number,
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return number,
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
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
)
/
show errors
create or replace type body PivotImpl as
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
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);
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 );
--
anytype.begincreate( dbms_types.typecode_object, atyp );
for i in 1 .. numcols - 2
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;
if instr( p_fmt, '@p@' ) > 0
then
t_fmt := p_fmt;
else
t_fmt := '@p@';
end if;
open rc for replace( 'select distinct ' || t_fmt || '
from( ' || p_stmt || ' )
order by ' || t_fmt
, '@p@'
, desc_tab( numcols - 1 ).col_name
);
loop
fetch rc into t_c2;
exit when rc%notfound;
atyp.addattr( t_c2
, case desc_tab( numcols ).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 ).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
return odciconst.error;
end;
--
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
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 );
--
if instr( p_fmt, '@p@' ) > 0
then
sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );
else
sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
end if;
return odciconst.success;
end;
--
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )
return number
is
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
t_stmt varchar2(32767);
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);
dummy2 integer;
x_stmt varchar2(32767);
begin
x_stmt := p_stmt || '/*' || to_char (the_sequence.nextval) || '*/';
cur := dbms_sql.open_cursor;
dbms_sql.parse( cur, x_stmt, dbms_sql.native );
dbms_sql.describe_columns2( cur, numcols, desc_tab );
dbms_sql.close_cursor( cur );
--
for i in 1 .. numcols - 2
loop
t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
end loop;
--
type_code := sctx.ret_type.getinfo( prec
, scale
, len
, csid
, csfrm
, schema_name
, type_name
, version
, attr_count
);
for i in numcols - 1 .. attr_count
loop
type_code := sctx.ret_type.getattreleminfo( i
, prec
, scale
, len
, csid
, csfrm
, attr_type
, attr_name
);
t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
, '@p@'
, desc_tab( numcols - 1 ).col_name
);
end loop;
t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
for i in 1 .. numcols - 2
loop
if i = 1
then
t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
else
t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
end if;
end loop;
--
--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;
end;
--
member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
return number
is
c1_col_type pls_integer;
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;
--
--dbms_output.put_line( 'fetch' );
if dbms_sql.fetch_rows( self.cur ) = 0
then
return odciconst.success;
end if;
--
--dbms_output.put_line( 'done' );
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
);
--dbms_output.put_line( 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;
--
member function ODCITableClose( self in PivotImpl )
return number
is
c integer;
begin
c := self.cur;
dbms_sql.close_cursor( c );
DECLARE
X_KILL VARCHAR2(100);
BEGIN
SELECT B.ADDRESS || ',' || B.HASH_VALUE
INTO X_KILL
FROM v$session a,v$sqlarea b
WHERE a.SID = userenv('SID')
AND UPPER(b.SQL_TEXT) LIKE '%PIVOTFUN%'
AND a.sql_id = b.sql_id;
--DBMS_OUTPUT.PUT_LINE ('X_KILL: ' || X_KILL);
DBMS_SHARED_POOL.PURGE (X_KILL, 'C');
END;
return odciconst.success;
end;
end;
/
show errors
create or replace
function pivotfun( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 )
return anydataset pipelined using PivotImpl;
/
show errors
GRANT EXECUTE ON pivotfun TO PUBLIC
/
CREATE PUBLIC SYNONYM pivotfun FOR SYS.pivotfun
/
|
|
|
|
| Re: transposing of data in oracle [message #653342 is a reply to message #653328] |
Sat, 02 July 2016 08:34 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Bill B wrote on Fri, 01 July 2016 17:40There is an easy way. Just like their is a function call in the type body to cleanup the call, there is a function to start the procedure. Remove the code I suggested and make the following alterations to the code for type function ODCITableStart. This will always generate a unique select and force a hard parse.
This will not work. You are confusing things. ODCI table interface has two phases: run time and compile time. Start - Fetch - Close is run time phase. Describe - Prepare is compile time phase. Again, Describe is only called if hard parse is detected. Adding comment in ODCITableStart to make statement distinct and force hard parse is too late in the game and will not make any difference.
SY.
|
|
|
|
| Re: transposing of data in oracle [message #653797 is a reply to message #653342] |
Tue, 19 July 2016 03:49 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Now that the topic is getting colder, as it is not usable in a real world -- how much time will last before your production instance freezes with just 1 such query (which requires hard parse each time) every second? Unless you have hundreds GB or even TB of SGA and dozens CPU, only few minutes -- I will say I have written a similar package to pivot and transpose some years ago (just because I wanted a PIVOT query in 10g) but never posted it for the above reason. I will send it, as a curiosity to study, to those who will ask me in PM.
Here are the specification of the functions (PIVOT is overloaded to support different ways to call it):
FUNCTION Pivot (
p_pivot IN VARCHAR2 -- Pivot expression
) RETURN ANYDATASET PIPELINED USING ot_pivot2;
FUNCTION Pivot (
p_source IN VARCHAR2, -- Source table/subquery
p_aggr IN VARCHAR2, -- Aggregate function
p_aggexpr IN VARCHAR2, -- Aggregate expression
p_aggalias IN VARCHAR2 DEFAULT NULL, -- Option alias for aggregate result
p_for IN VARCHAR2, -- FOR columns / expressions
p_in IN VARCHAR2, -- IN values: list of constants,
-- expressions, ANY [ASC|DESC]
-- or queries
p_inalias IN VARCHAR2 DEFAULT NULL -- Optional alias for each IN value
-- if IN value is an expression
-- an alias is mandatory
) RETURN ANYDATASET PIPELINED USING ot_pivot;
FUNCTION Pivot (
p_source IN VARCHAR2, -- Source table/subquery
p_aggr_expr IN VARCHAR2, -- Aggregate expression
p_pivot_cols IN VARCHAR2, -- Pivot (FOR) columns / expressions
p_select_cols IN VARCHAR2 DEFAULT '*' -- Selected columns in result
) RETURN ANYDATASET PIPELINED USING ot_pivot3;
FUNCTION Transpose (
p_source IN VARCHAR2, -- Source table/subquery
p_pivot_cols IN VARCHAR2 DEFAULT NULL, -- Pivot columns
p_delim IN VARCHAR2 DEFAULT '_', -- Value delimitor
p_key IN VARCHAR2 DEFAULT 'TRUE' -- Pivot columns are a key?
) RETURN ANYDATASET PIPELINED USING ot_transpose;
PIVOT examplesset linesize 83
set numwidth 10
select *
from table(pkg_pivot.pivot(
'(select deptno, sal, extract(year from hiredate) year from EMP)
pivot (SUM("SAL") SUMSAL
for ("DEPTNO") in (10,20,30,40))'
))
order by year
/
YEAR SUMSAL_10 SUMSAL_20 SUMSAL_30 SUMSAL_40
---------- ---------- ---------- ---------- ----------
1980 800
1981 7450 5975 9400
1982 1300
1987 4100
select *
from table(pkg_pivot.pivot(
'(select deptno, sal, extract(year from hiredate) year from EMP)
pivot (SUM(SAL)
for (DEPTNO) in (10 "d#10",20 "d#20",30,40 "d#40"))'
))
order by year
/
YEAR d#10 d#20 30 d#40
---------- ---------- ---------- ---------- ----------
1980 800
1981 7450 5975 9400
1982 1300
1987 4100
select *
from table(pkg_pivot.pivot(
'(select deptno, sal, comm, extract(year from hiredate) year from EMP)
pivot (sum(sal+nvl(comm,0)) "sumsal"
for (deptno) in (select deptno from dept order by deptno desc))'
))
order by year
/
YEAR sumsal_40 sumsal_30 sumsal_20 sumsal_10
---------- ---------- ---------- ---------- ----------
1980 800
1981 11600 5975 7450
1982 1300
1987 4100
select *
from table(pkg_pivot.pivot(
'(select deptno, sal, comm, extract(year from hiredate) "year" from EMP)
pivot (sum(sal+nvl(comm,0)) "sumsal"
for (deptno) in (any asc))'
))
order by "year"
/
year sumsal_10 sumsal_20 sumsal_30
---------- ---------- ---------- ----------
1980 800
1981 7450 5975 11600
1982 1300
1987 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in ((1980,20), (1981,10),(1981,20),(1981,30), (1982,10), (1987,20), (2000,100))
)'
))
/
1980_20 1981_10 1981_20 1981_30 1982_10 1987_20 2000_100
---------- ---------- ---------- ---------- ---------- ---------- ----------
800 7450 5975 11600 1300 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0)) "sumsal"
for (year,deptno)
in ((1980,20) v02, (1981,10) v11,(1981,20) v12,(1981,30) v13, (1982,10) v21, (1987,20) v71, (2000,100) vAA)
)'
))
/
sumsal_v02 sumsal_v11 sumsal_v12 sumsal_v13 sumsal_v21 sumsal_v71 sumsal_vAA
---------- ---------- ---------- ---------- ---------- ---------- ----------
800 7450 5975 11600 1300 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (ANY asc)
)'
))
/
1980_20 1981_10 1981_20 1981_30 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 7450 5975 11600 1300 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (select distinct extract(year from hiredate) year, deptno from emp order by deptno, year)
)'
))
/
1981_10 1982_10 1980_20 1981_20 1987_20 1981_30
---------- ---------- ---------- ---------- ---------- ----------
7450 1300 800 5975 4100 11600
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (any ASC, any DESC)
)'
))
/
1980_20 1981_30 1981_20 1981_10 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 11600 5975 7450 1300 4100
set numwidth 7
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (select distinct extract(year from hiredate) year from emp order by 1,
select deptno from dept order by 1)
)'
))
/
1980_10 1981_10 1982_10 1987_10 1980_20 1981_20 1982_20 1987_20 1980_30 1981_30
------- ------- ------- ------- ------- ------- ------- ------- ------- -------
1982_30 1987_30 1980_40 1981_40 1982_40 1987_40
------- ------- ------- ------- ------- -------
7450 1300 800 5975 4100 11600
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (select distinct extract(year from hiredate) year from emp order by 1,
(10,20))
)'
))
/
1980_10 1981_10 1982_10 1987_10 1980_20 1981_20 1982_20 1987_20
------- ------- ------- ------- ------- ------- ------- -------
7450 1300 800 5975 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (any desc, select deptno from dept order by 1)
)'
))
/
1987_10 1982_10 1981_10 1980_10 1987_20 1982_20 1981_20 1980_20 1987_30 1982_30
------- ------- ------- ------- ------- ------- ------- ------- ------- -------
1981_30 1980_30 1987_40 1982_40 1981_40 1980_40
------- ------- ------- ------- ------- -------
1300 7450 4100 5975 800
11600
set numwidth 10
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, comm, substr(job,1,1) job, mgr from emp where mgr is not null)
pivot (
sum(sal+nvl(comm,0))
for (mgr, deptno, job)
in (any, select deptno from dept order by 1, any desc)
)'
))
/
7698_10_S 7839_10_M 7698_10_C 7782_10_C 7788_10_C 7902_10_C 7566_10_A
---------- ---------- ---------- ---------- ---------- ---------- ----------
7698_20_S 7839_20_M 7698_20_C 7782_20_C 7788_20_C 7902_20_C 7566_20_A
---------- ---------- ---------- ---------- ---------- ---------- ----------
7698_30_S 7839_30_M 7698_30_C 7782_30_C 7788_30_C 7902_30_C 7566_30_A
---------- ---------- ---------- ---------- ---------- ---------- ----------
7698_40_S 7839_40_M 7698_40_C 7782_40_C 7788_40_C 7902_40_C 7566_40_A
---------- ---------- ---------- ---------- ---------- ---------- ----------
2450 1300
2975 1100 800 6000
7800 2850 950
set numwidth 9
SELECT *
from table(pkg_pivot.pivot(
q'[(SELECT deptno, sal, hiredate, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (to_char(hiredate,'YYYY'), deptno)
in (any asc, select deptno from dept)
)]'
))
/
1980_10 1981_10 1982_10 1987_10 1980_20 1981_20 1982_20 1987_20
--------- --------- --------- --------- --------- --------- --------- ---------
1980_30 1981_30 1982_30 1987_30 1980_40 1981_40 1982_40 1987_40
--------- --------- --------- --------- --------- --------- --------- ---------
7450 1300 800 5975 4100
11600
SELECT *
from table(pkg_pivot.pivot(
q'[(SELECT deptno, sal, hiredate, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (to_char(hiredate,'YYYY'), deptno)
in (any asc, any)
)]'
))
/
1980_20 1981_10 1981_20 1981_30 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 7450 5975 11600 1300 4100
TRANSPOSE examples (you can see in the last example that the pivot columns to transpose must not be a key for the query, the original row set can have several rows for a single value of the pivot columns, these rows are then concatenated):
-- select 'col '||empno||' format a11' from emp where empno between 7300 and 7700;
select *
from table(pkg_pivot.transpose(
'select * from emp where empno between 7300 and 7700',
'empno'
))
/
EMPNO 7369 7499 7521 7566 7654 7698
-------- ----------- ----------- ----------- ----------- ----------- -----------
COMM 300 500 1400
DEPTNO 20 30 30 20 30 30
ENAME SMITH ALLEN WARD JONES MARTIN BLAKE
HIREDATE 17-DEC-1980 20-FEB-1981 22-FEB-1981 02-APR-1981 28-SEP-1981 01-MAY-1981
JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER
MGR 7902 7698 7698 7839 7698 7839
SAL 800 1600 1250 2975 1250 2850
-- select 'col '||deptno||'_'||empno||' format a11' from emp where empno between 7300 and 7700;
select *
from table(pkg_pivot.transpose(
'select * from emp where empno between 7300 and 7700',
'deptno, empno'
))
/
DEPTNO_E 20_7369 20_7566 30_7499 30_7521 30_7654 30_7698
-------- ----------- ----------- ----------- ----------- ----------- -----------
COMM 300 500 1400
ENAME SMITH JONES ALLEN WARD MARTIN BLAKE
HIREDATE 17-DEC-1980 02-APR-1981 20-FEB-1981 22-FEB-1981 28-SEP-1981 01-MAY-1981
JOB CLERK MANAGER SALESMAN SALESMAN SALESMAN MANAGER
MGR 7902 7839 7698 7698 7698 7839
SAL 800 2975 1600 1250 1250 2850
-- select 'col '||deptno||'/'||empno||' format a11' from emp where empno between 7300 and 7700;
select *
from table(pkg_pivot.transpose(
'select * from emp where empno between 7300 and 7700',
'deptno, empno',
'/'
))
/
DEPTNO/E 20/7369 20/7566 30/7499 30/7521 30/7654 30/7698
-------- ----------- ----------- ----------- ----------- ----------- -----------
COMM 300 500 1400
ENAME SMITH JONES ALLEN WARD MARTIN BLAKE
HIREDATE 17-DEC-1980 02-APR-1981 20-FEB-1981 22-FEB-1981 28-SEP-1981 01-MAY-1981
JOB CLERK MANAGER SALESMAN SALESMAN SALESMAN MANAGER
MGR 7902 7839 7698 7698 7698 7839
SAL 800 2975 1600 1250 1250 2850
col PRESIDENT format a11
col ANALYST format a24
col MANAGER format a37
select * from table(pkg_pivot.transpose(
q'[select * from emp where job in ('MANAGER', 'PRESIDENT', 'ANALYST')]',
'job',
'=>',
'FALSE'
))
/
JOB PRESIDENT ANALYST MANAGER
-------- ----------- ------------------------ -------------------------------------
COMM
DEPTNO 10 20=>20 20=>10=>30
EMPNO 7839 7788=>7902 7566=>7698=>7782
ENAME KING SCOTT=>FORD JONES=>BLAKE=>CLARK
HIREDATE 17-NOV-1981 19-APR-1987=>03-DEC-1981 02-APR-1981=>01-MAY-1981=>09-JUN-1981
MGR 7566=>7566 7839=>7839=>7839
SAL 5000 3000=>3000 2975=>2450=>2850
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:44:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
