Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, marc.

Asked: February 29, 2012 - 4:25 am UTC

Last updated: February 08, 2021 - 2:07 pm UTC

Version: 10.1.2

Viewed 50K+ times! This question is

You Asked

Hi,

I am have the following query below

create table fish (
fish_id number,
fish_type varchar2(3),
fish_weight number);


insert into fish values (1,'COD',20);
insert into fish values(1,'HAD',30);
insert into fish values(2,'COD',45);
insert into fish values(2,'HKE',10);
insert into fish values(2,'LIN',55);
insert into fish values(3,'CTY',90);
insert into fish values (3,'HAD',60);
insert into fish values (3,'COD',52);

select * from fish



which returns
FISH_ID,FISH_TYPE,FISH_WEIGHT
1 COD 20
1 HAD 30
2 COD 45
2 HKE 10
2 LIN 55
3 CTY 90
3 HAD 60
3 COD 52
........
............
......


I would like it to be displayed as

COD HAD HKE LIN CTY .......
1 20 30 X X X
2 45 X 10 55 X
3 52 60 X X X
....
....

The columns arent fixed as there can be multiple species, can this be accomplished , if so how? Thanks

and Tom said...

well, to do that in 10g you need a query like:

ops$tkyte%ORA11GR2> select fish_id,
  2         sum(decode(fish_type,'COD',fish_weight)) cod,
  3         sum(decode(fish_type,'HAD',fish_weight)) had,
  4         sum(decode(fish_type,'HKE',fish_weight)) hke,
  5         sum(decode(fish_type,'LIN',fish_weight)) lin,
  6         sum(decode(fish_type,'CTY',fish_weight)) cty
  7    from fish
  8   group by fish_id
  9   order by fish_id
 10  /

   FISH_ID        COD        HAD        HKE        LIN        CTY
---------- ---------- ---------- ---------- ---------- ----------
         1         20         30
         2         45                    10         55
         3         52         60                               90


In 11g you could use the PIVOT syntax instead - but regardless of release - the names, number and types of all selected columns must be known at parse time - the 'COD', 'HAD', etc bits have to appear in the query itself. You can use a method such as the following to achieve this.

NOTE: fish_type is obviously a foreign key to another table where fish_type is the primary key - it has to be... Please replace my select distinct... with a simple select against that table.

ops$tkyte%ORA11GR2> create or replace procedure go_fishing( p_cursor in out sys_refcursor )
  2  as
  3      l_query long := 'select fish_id';
  4  begin
  5      for x in (select distinct fish_type from fish order by 1 )
  6      loop
  7          l_query := l_query ||
  8             replace( q'|, sum(decode(fish_type,'$X$',fish_weight)) $X$|',
  9                      '$X$',
 10                      dbms_assert.simple_sql_name(x.fish_type) );
 11      end loop;
 12  
 13      l_query := l_query || ' from fish group by fish_id order by fish_id';
 14  
 15      open p_cursor for l_query;
 16  end;
 17  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec go_fishing( :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

   FISH_ID        COD        CTY        HAD        HKE        LIN
---------- ---------- ---------- ---------- ---------- ----------
         1         20                    30
         2         45                               10         55
         3         52         90         60


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dbms_assert.simple_sql_name

Rajeshwaran, Jeyabal, February 29, 2012 - 11:35 am UTC

Tom:

Can you explain me what is the need of dbms_assert.simple_sql_name in the abouve context?

I was looking into product docs but unable to get a clear Idea about it
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_assert.htm#BABHIEBA

1) Why should we need to use dbms_assert.simple_sql_name in the above query? what will happen if we dont use it?

2) I am able to achieve the same result without dbms_assert. Is there is any harm in the below code without using it (like sql-injection )

rajesh@ORA10GR2> create or replace procedure
  2  get_data(p_x out sys_refcursor)
  3  as
  4     l_query varchar2(400) :='select fish_id';
  5  begin
  6     for x in (select distinct fish_type from fish order by 1)
  7     loop
  8             l_query := l_query ||replace(', sum(decode(fish_type,''$X'',fish_weight)) as $X '
  9                     ,'$X',x.fish_type );
 10     end loop;
 11     l_query := l_query ||' from fish group by fish_id ';
 12     open p_x for l_query;
 13  end;
 14  /

Procedure created.

Elapsed: 00:00:00.28
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec get_data(:x);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
rajesh@ORA10GR2>
rajesh@ORA10GR2> print x;

   FISH_ID        COD        CTY        HAD        HKE        LIN
---------- ---------- ---------- ---------- ---------- ----------
         1         20                    30
         2         45                               10         55
         3         52         90         60

Elapsed: 00:00:00.60
rajesh@ORA10GR2>

Tom Kyte
February 29, 2012 - 12:07 pm UTC


here is a better link:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_assert.htm#sthref1040


1) I don't want to be subject to sql injection. If someone put a "fish" in there with SQL in it - one would be able to cause me to perform some SQL I didn't mean to.

2) it is there EXACTLY to avoid the sql injection attack that would otherwise be pretty easy to launch.


ANYTIME you are using string concatenation to build a query - and you concatenate in inputs from outside of your direct control - you have to watch out for sql injection.

I simply wanted to make sure that whatever the 'fish type' was - it represented a simple sql name - something "safe" to put into the sql statement.

marc vafaie, March 02, 2012 - 6:01 am UTC

Hi,

Is it possible to run the procedure as a pl/sql block and have the results be returned in the normal manner rather than using sql/pls and dbms_output as eventually want to call the procedure from a oracle report and have the results displayed.

Thanks
Tom Kyte
March 02, 2012 - 6:48 am UTC

what is a "oracle report"?

where did you see any dbms_output on this page? Whoever/whatever calls this procedure gets a "normal" cursor back, they would just fetch from it, format it, and print it however they wanted to.

To Marc

Alexander, March 02, 2012 - 11:53 am UTC

You probably want a ref cursor.
Tom Kyte
March 02, 2012 - 12:57 pm UTC

that is what the supplied procedure returns ;)

long type

James Su, March 02, 2012 - 7:31 pm UTC

Hi Tom,
What's the reason you used long instead of clob in your code?
Tom Kyte
March 03, 2012 - 8:34 am UTC

because I'm old.

literally. Habit.

a LONG in plsql is a subtype of varchar2 - it is just a nickname for a varchar2(32760).

I use it for big strings that aren't going to be huge strings.

Re: Dynamic Pivot

Narendra, March 06, 2012 - 12:17 pm UTC

As the original question is about dynamic pivoting, I could not help but post this
http://technology.amis.nl/blog/1207/dynamic-sql-pivoting-stealing-antons-thunder
Data Cartridge...seems like magic.

kehd, March 14, 2012 - 3:37 am UTC

Hallo,
as a beginner in PL/SQL I could not figure out what '$X$' in the decode statement is supposed to do. I could not find anything in the SQL and the PL/SQL Language Reference.

2.I tried to use your example in one of my projects. However, the content of the selected columns (fish_type in your example) in my project are not a simple sql names. They contain also < > - symbols. Is it possible to extend the statement to handle this?

May I hope that the PIVOT clause in 12g allows subqueries without the XML keyword?
Tom Kyte
March 14, 2012 - 7:12 am UTC

it is just a programming technique that is rather language independent.

Don't read anything special into it, just look at the code:

  8             replace( q'|, sum(decode(fish_type,'$X$',fish_weight)) $X$|',
  9                      '$X$',
 10                      dbms_assert.simple_sql_name(x.fish_type) );


Now, what does replace do - it takes the first argument (a string) as input and uses the second argument as a pattern to match and when it finds that pattern in the first argument it replaces it with the third argument.

I simply put $X$ as a string, a pattern, in the first string and I'm just asking for $X$ to be replaced in that first string with x.fish_type (assuming x.fish_type is "safe" to be replaced in there - that is what dbms_assert is doing for me)

It is just a programming technique.


in my project are not a simple
sql names.


they are dangerous to place in SQL then. You can use dbms_assert.Enquote_Literal for the first occurrence (in the decode) but should use simple_sql_name after quoting for the second one.

Suppose COD was <>1 in the above example. Then:


ops$tkyte%ORA11GR2> create or replace procedure go_fishing( p_cursor in out sys_refcursor )
  2  as
  3      l_query long := 'select fish_id';
  4  begin
  5      for x in (select distinct fish_type from fish order by 1 )
  6      loop
  7          l_query := l_query ||
  8                replace(
  9                replace( q'|, sum(decode(fish_type,$1$,fish_weight)) $2$|',
 10                        '$1$',
 11                        dbms_assert.enquote_literal(x.fish_type) ),
 12                        '$2$',
 13                        dbms_assert.simple_sql_name( '"' || x.fish_type || '"' ) );
 14      end loop;
 15  
 16      l_query := l_query || ' from fish group by fish_id order by fish_id';
 17  
 18      dbms_output.put_line( l_query );
 19      open p_cursor for l_query;
 20  end;
 21  /

Procedure created.

ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec go_fishing( :x )
select fish_id, sum(decode(fish_type,'<>1',fish_weight)) "<>1",
sum(decode(fish_type,'CTY',fish_weight)) "CTY",
sum(decode(fish_type,'HAD',fish_weight)) "HAD",
sum(decode(fish_type,'HKE',fish_weight)) "HKE",
sum(decode(fish_type,'LIN',fish_weight)) "LIN" from fish group by fish_id order
by fish_id

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

   FISH_ID        <>1        CTY        HAD        HKE        LIN
---------- ---------- ---------- ---------- ---------- ----------
         1         20                    30
         2         45                               10         55
         3         52         90         60


kehd, March 16, 2012 - 9:57 am UTC

Worked very well for me, thanks.

In my example I have to join 10 tables. The only adjustments I had to make was in the line

replace(replace( q'|, sum(decode(fish_type,$1$,fish_weight)) $2$|','$1$',
dbms_assert.enquote_literal(x.fish_type) ),'$2$', dbms_assert.simple_sql_name( '"' || x.fish_type || '"' ) );

My "fish_type" comes from a table "FishTypes" with the columns "Type_ID" and "Name". Since many tables have a column with the name "Name" I had to use "Fish_types.Name AS MyFishTypes". When I modified the last line a little, it worked:

replace(replace( q'|, sum(decode(Fish_types.Name,$1$,fish_weight)) $2$|','$1$',
dbms_assert.enquote_literal(x.MyFishTypes) ),'$2$', dbms_assert.simple_sql_name( '"' || x.MyFishTypes || '"' ) );

Thanks a lot

Kurt-Erich

A reader, August 22, 2012 - 2:21 am UTC


Pivot and Cursors

Kevin, November 02, 2012 - 8:32 pm UTC

While I'll be the first to say I'm not an oracly guy... it seems like more times than not oracle has found a way to inject a cursor, or make things harder in some fashion. I shouldn't have to use a cursor to do a pivot. cursors are slow and inefficient and should be avoided when set processing can accomplish the task.

SELECT non-pivoted column,
firstpivotedcolumn AS column name,
secondpivotedcolumn AS column name,
lastpivotedcolumn AS column name
FROM
(SELECT query that produces the data>)
AS aliasforsourcequery
PIVOT
(
aggregation function(column being aggregated)
FOR
column that contains the values that will become column headers
IN ( firstpivotedcolumn,secondpivotedcolumn,
last pivoted column)
) AS aliasforthepivottable (optional ORDER BY clause)

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/04/14/pivot-and-unpivot-table-in-sql-server/ source of example
Tom Kyte
November 05, 2012 - 8:39 am UTC

... cursors are
slow and inefficient and should be avoided when set processing can accomplish
the task. ....

false in many cases, not relevant in this case.



it works like it works.


and guess what, if you were in sqlserver and needed to do this and didn't know what type of fish you had - guess what you would be doing?

writing a query to get the fish types so you could construct the query!!! (that is, the same exact thing)


did you actually read the article in question and read this post and understand it all?


if we knew the fish types, we would just code the pivot query. We don't, hence we run a query to find out the fish types so we can BUILD the query (the set based thing)

you'd do the same thing in sqlserver.


I could have used PIVOT (I'd still need to run a query to get the query with thepivot). I chose not to - but using PIVOT would require the same thing.



how about this - create the table and populate in sqlserver. Show us how you would do this in sqlserver, then insert another row into the table with values (4,'XXX',10) and show us the query that now outputs an extra column for XXX without changing the original query.

pivot output

Awais, February 04, 2014 - 8:22 pm UTC

hello
after impleminting the pivot function i am unable to get the data in one row, the values are all comming in different row is there a way we could change this

PIVOT 30 day rolling window

dwellborn, November 20, 2014 - 8:06 pm UTC

Is it possible to pivot data where there are 30 columns representing today to 30 days back? I haven't been able to figure out how to get away from a hard-coded in clause for each of the 30 days.

I can add the XML to the pivot, but now I have to dig out the count element.

Compilation error

Parul, October 23, 2015 - 3:49 pm UTC

Hi all,

I am using SQL Developer to run my queries. We have Oracle 11G DB


We have a table called IBAVALUE

OBJida2a2 Displayname Value
10 Height 25
10 last_date 12/10/2014
10 Width 10
10 Role Planner
11 Role Administrator
11 last_date 03/22/2015
12 Height 12
12 Width 11
12 last_date 10/1/2015


I want to create another table which will have data like:

OBJida2a2 Height Width Last_date Role
10 25 10 12/10/2014 Planner
11 03/22/2015 Administrator
12 12 11 10/1/2015


When I used teh above given code:
create or replace procedure
get_IBA(p_x in out sys_refcursor)
as
l_query long :='select Objida2a2';
begin
for x in (select distinct displayname from qiwkibavalue order by 1)
loop
l_query := l_query ||replace(', sum(decode(displayname,''$X'',value)) as $X '
,'$X',dbms_assert.simple_sql_name(x.displayname) );
end loop;
l_query := l_query ||' from qiwkibavalue group by Objida2a2 order by Objida2a2';
open p_x for l_query;
end;

variable x REFCURSOR;
exec get_IBA(:x);
print x;

But I am getting this error :
Error(15,1): PLS-00103: Encountered the symbol "VARIABLE"

How can I save the results of the procedure in a table?
Thanks so much
Parul
Chris Saxon
October 24, 2015 - 8:37 pm UTC

Rather than provide some SQL, I think we have to ask: Why?

Why dynamically create tables and columns ? Surely if its totally fluid like that, how are applications going to run on top of that ?

What if Fish Name has space

Parul, October 27, 2015 - 8:46 pm UTC

What will happen if fish names are like "salmon fillet"
"Cod Fillet"?

pivot table

ali, February 05, 2021 - 5:53 pm UTC

hi
Can procedure data be displayed on a table?
Chris Saxon
February 08, 2021 - 2:07 pm UTC

I'm not sure what you mean - please clarify

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library