Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Riaz.

Asked: January 07, 2004 - 9:00 am UTC

Last updated: October 09, 2011 - 11:09 am UTC

Version: 8.1.7.2.1

Viewed 1000+ times

You Asked

Hello Tom !

Thanks GOD I can ask question here...i was waiting for this since a long time. I have some silly questions for you:

(1) I have a table like:

reg_no varchar2(13)
ver_date date

And data is Like:

12 08-10-2002 18:20:31
11 08-10-2002 18:20:34
11 08-10-2002 18:20:36
11 08-10-2002 18:20:39
11 08-10-2002 18:20:41
11 08-10-2002 18:20:44
03 08-10-2002 18:20:50
11 08-10-2002 18:20:52
11 08-10-2002 18:20:55
11 08-10-2002 18:20:57
11 08-10-2002 18:21:00
11 08-10-2002 18:21:07
12 08-10-2002 18:21:13
11 08-10-2002 18:21:16
11 08-10-2002 18:21:18
11 08-10-2002 18:21:21
11 08-10-2002 18:21:23
12 08-10-2002 18:21:26

I want to have a query that can tell me on specific time(s), whether the record was inserted or not (just yes/no) for each day. The output should look like:

Date: 08-10-2002

(reg_no)
11 12 03

Mor. Y N Y
AN N Y Y
Eve. Y(or N) N N

Date: 09-10-2002
............................
.............................
and so on

Where

Mor(Morning) is time between 1000 hrs and 1030 hrs
AN (After Noon) is time between 1400 hrs and 1430 hrs
Eve (Evening) is time between 1700 hrs and 1730 hrs

(2) How we can put triggers, packages etc in Large Pool
(3) Can i have a query which, on giving the specific year, prints the calender of that year ? The Calender should look like the calenders we daily see.
(4) What is the diff. between Oracle Instance & Oracle Database ?

Regards


and Tom said...

1)
ops$tkyte@ORA920PC> select ver_date,
2 when,
3 nvl( max( decode( reg_no, 11, 'Y' ) ), 'N' ) "11",
4 nvl( max( decode( reg_no, 12, 'Y' ) ), 'N' ) "12",
5 nvl( max( decode( reg_no, 03, 'Y' ) ), 'N' ) "03"
6 from (
7 select distinct
8 reg_no, trunc(ver_date) ver_date,
9 case when to_char(ver_date,'hh24') between 0 and 11 then 'Morn'
10 when to_char(ver_date,'hh24') between 12 and 17 then 'After'
11 else 'Evening'
12 end when
13 from t
14 )
15 group by ver_date, when
16 /

VER_DATE WHEN 11 12 03
------------------- ------- -- -- --
08-10-2002 00:00:00 Morn N Y N
08-10-2002 00:00:00 After Y N N
08-10-2002 00:00:00 Evening Y Y Y

ops$tkyte@ORA920PC> select * from t;

REG_NO VER_DATE
---------- -------------------
12 08-10-2002 18:20:31
11 08-10-2002 13:20:34
11 08-10-2002 18:20:36
11 08-10-2002 18:20:39
11 08-10-2002 18:20:41
11 08-10-2002 18:20:44
3 08-10-2002 18:20:50
11 08-10-2002 18:20:52
11 08-10-2002 18:20:55
11 08-10-2002 18:20:57
11 08-10-2002 18:21:00
11 08-10-2002 18:21:07
12 08-10-2002 18:21:13
11 08-10-2002 18:21:16
11 08-10-2002 18:21:18
11 08-10-2002 18:21:21
11 08-10-2002 18:21:23
12 08-10-2002 11:21:26

18 rows selected.


2) doesn't even make SENSE.

we invented the large pool specifically to KEEP triggers/packages/etc away from the data we put in the large pool!!!!

The shared pool (where these things go) is managed in an LRU fashion. It makes sense for them there.

The large pool (where UGA, rman and PQ message buffers go) is managed in a heap using "allocate" and "free". Memory is not "aged" out -- it is released


3) don't know what calendar you see daily, but give

break on month skip 1
set linesize 20
column month 20
select lpad( Month, 20-(20-length(month))/2 ) month,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
from (
select to_char(dt,'fmMonthfm YYYY') month,
to_char(dt+1,'iw') week,
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' ), to_number(week)
/


a look see


4) </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:533422350794 <code>

Rating

  (41 ratings)

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

Comments

A reader, January 08, 2004 - 4:36 pm UTC


A reader, January 08, 2004 - 4:52 pm UTC

Tom,

What tool/utility do you use to load table data examples in your db??

Thanks.

Tom Kyte
January 08, 2004 - 8:25 pm UTC

unfortunately, it is cut and paste and vi.

I edit what they provide here.

I LOVE it when people give me inserts, table creates and so on!!!

tiny fix

Marcio, January 08, 2004 - 8:56 pm UTC

on calendar script

column month format a20

Tom Kyte
January 09, 2004 - 8:12 am UTC

thanks :)

Hmmm...

A reader, January 08, 2004 - 9:09 pm UTC

I find it interesting that Riaz had been waiting for a long time to ask Tom a question, and yet when he finally got the chance all he could come up with was silly questions! Just pulling your leg Riaz...

A reader, January 08, 2004 - 10:18 pm UTC


!!!Note to Everybody !!!

"Please provide inserts, table creates and so on "

Thanks ...

Ok

Ram, January 09, 2004 - 12:37 am UTC

Dear sir,
why don't you use
$exec owa_util.calendarprint ?
Bye!


Tom Kyte
January 09, 2004 - 8:27 am UTC

cause that would be HTML?

Regarding Question. 3

Riaz Shahid, January 09, 2004 - 7:54 am UTC

Tom ! Actually what i needed in Question 3 was a output like:

Month: Jan 2004

S M T W T F S
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 30

Month: Feb 2004

S M T W T F S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29

And So on....

Tom Kyte
January 09, 2004 - 8:46 am UTC

uh huh.

did you run the script supplied?????????

ops$tkyte@ORA920PC> break on month skip 1
ops$tkyte@ORA920PC> set linesize 20
ops$tkyte@ORA920PC> column month format a20
ops$tkyte@ORA920PC> select lpad( Month, 20-(20-length(month))/2 ) month,
  2         "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
  3    from (
  4  select to_char(dt,'fmMonthfm YYYY') month,
  5         to_char(dt+1,'iw') week,
  6         max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
  7         max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
  8         max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
  9         max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
 10         max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
 11         max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
 12         max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
 13    from ( select trunc(sysdate,'y')-1+rownum dt
 14             from all_objects
 15            where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
 16  )
 17   group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
 18         )
 19   order by to_date( month, 'Month YYYY' ), to_number(week)
 20  /
 
MONTH
--------------------
Su Mo Tu We Th Fr Sa
-- -- -- -- -- -- --
    January 2004
             1  2  3
 
 
 4  5  6  7  8  9 10
 
 
11 12 13 14 15 16 17
 
 
18 19 20 21 22 23 24
 
 
25 26 27 28 29 30 31
 
 
   February 2004
 1  2  3  4  5  6  7
 
 
 8  9 10 11 12 13 14
 
 
15 16 17 18 19 20 21
 
 
22 23 24 25 26 27 28
 
 
29
......


and if the extra lines bother you -- just put it into a plsql "for loop" and use dbms_output to format it however pleases you most. 

Why I am not getting output like You showed ?

Riaz Shahid, January 09, 2004 - 9:07 am UTC

Hello Tom !

Sorry for bothering but i am not getting the output as yours one.

Instead i get like this:

SQL> break on month skip 1
SQL> set linesize 20
SQL> column month format a20
SQL> select lpad( Month, 20-(20-length(month))/2 ) month,
  2             "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
  3        from (
  4      select to_char(dt,'fmMonthfm YYYY') month,
  5             to_char(dt+1,'iw') week,
  6             max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
  7             max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
  8             max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
  9             max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
 10            max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
 11            max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
 12            max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
 13       from ( select trunc(sysdate,'y')-1+rownum dt
 14                from all_objects
 15               where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
 16     )
 17      group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
 18            )
 19      order by to_date( month, 'Month YYYY' ), to_number(week)
 20  /

MONTH               
====================
Su                  
====================
Mo                  
====================
Tu                  
====================
We                  
====================
Th                  
====================
Fr                  
====================
Sa                  
====================
    January 2004    
                    
                    
                    
                    
 1                  
 2                  
 3                  
                    
                    
 4                  
 5                  
 6                  
 7                  
 8                  
 9                  
10                  
                    
                    
11                  
12                  
13                  
14                  
15                  
16                  
17                  
                    
                    
18                  
19                  
20                  
21                  
22                  
23                  
24                  
                    
                    
25                  
26                  
27                  
28                  
29                  
30                  
31                  
                    
                    
   February 2004    
 1                  
 2                  
 3                  
 4                  
 5                  
 6                  
 7                  
                    
                    
 8                  
 9                  
10                  
11                  
12                  
13                  
14                  
                    
                    
15                  
16                  
17                  
18                  
19                  
20                  
21                  
                    
                    
22                  
23                  
24                  
25                  
26                  
27                  
28                  
                    
                    

MONTH               
====================
Su                  
====================
Mo                  
====================
Tu                  
====================
We                  
====================
Th                  
====================
Fr                  
====================
Sa                  
====================
29                  
                    
                    
                    
                    
                    
                    
                    
                    
     March 2004     
                    
 1                  
 2                  
 3                  
 4                  
 5                  
 6                  
                    
                    
 7                  
 8                  
 9                  
10                  
11                  
12                  
13                  
                    
                    
14                  
15                  
16                  
17                  
18                  
19                  
20                  
                    
                    
21                  
22                  
23                  
24                  
25                  
26                  
27                  
                    
                    
28                  
29                  
30                  
31                  
                    
                    
                    
                    
                    
     April 2004     
                    
                    
                    
                    
 1                  
 2                  
 3                  
                    
                    
 4                  
 5                  
 6                  
 7                  
 8                  
 9                  
10                  
                    
                    
11                  
12                  
13                  
14                  
15                  
16                  
17                  
                    
                    

MONTH               
====================
Su                  
====================
Mo                  
====================
Tu                  
====================
We                  
====================
Th                  
====================
Fr                  
====================
Sa                  
====================
18                  

(and so on....)

20 rows selected.

Elapsed: 00:00:09.38

SQL> spo off
 

Tom Kyte
January 09, 2004 - 9:17 am UTC

run with a clean login.sql file -- you must have other (unknown to me) settings.

but as I said, you can use use dbms_output with "set server serveroutput on format wrapped" to get what EVER you want.


add a

set pagesize 9999

in there as well.

A reader, January 09, 2004 - 12:06 pm UTC


For output extra lines calendar.

Marcio, January 09, 2004 - 8:06 pm UTC

from your calendar script I left my linesize with 131 and got this:
ops$marcio@MARCI9I1> @cal

MONTH Su Mo Tu We Th Fr Sa
-------------------- -- -- -- -- -- -- --
January 2004 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

February 2004 1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29

March 2004 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
....




Tom Kyte
January 10, 2004 - 10:45 am UTC

yup, i was trying to emulate the unix 'cal' command

9i?

George, January 12, 2004 - 10:50 am UTC

I ran in 9i and got similar results to Riaz's:

MONTH
--------------------
Su
-----------------------------------------------------------------------------------------------------------------------------------
Mo
-----------------------------------------------------------------------------------------------------------------------------------
Tu
-----------------------------------------------------------------------------------------------------------------------------------
We
-----------------------------------------------------------------------------------------------------------------------------------
Th
-----------------------------------------------------------------------------------------------------------------------------------
Fr
-----------------------------------------------------------------------------------------------------------------------------------
Sa
-----------------------------------------------------------------------------------------------------------------------------------
January 2004




1
2
3


4
5
6
7
8
9
10


11
12
13
14
15
16
17


18
19

Works like a charm in 8i

Tom Kyte
January 12, 2004 - 1:25 pm UTC

you have cursor_sharing=force set on so all of the constants "disappear"

Help with query ...

Anand, January 12, 2004 - 5:39 pm UTC

I have a table COUNTRY :

SQL> select * from country;

       CID COUNTRY_NAME
---------- --------------------
         1 USA
         2 CANADA

that contains country names.

and another table STATE :

SQL> select * from state;

       SID        CID STATE_NAME
---------- ---------- --------------------
         1          1 TEXAS
         2          1 VIRGINIA
         3          1 CALIFORNIA
         1          2 ONTARIO

that contains names of states under a country in the COUNTRY table.

I am looking for a query that would display the country name and all the states under that particular coutry in a single row like :

USA Virginia Texas Alabama California
Canada Ontario Quebec

How can I achieve this?

Thanks

 

!!!

Christo Kutrovsky, January 12, 2004 - 5:43 pm UTC

>>>> you have cursor_sharing=force set on so all of the constants "disappear"


Now I give 5 stars for figuring this out, with close to no information. I am most impressed with your telepathic skills.


Tom Kyte
January 13, 2004 - 1:16 am UTC

(having seen this about 1,000,000 times by now helps too :)

Excellent

Riaz Shahid, January 13, 2004 - 2:57 am UTC

Now I've found why the same query wasn't providing same results as that of yours. Actually i had cursor_sharing set to force.when i set it back to exact, It worked and provided me the same results.

Thanks for your great help...

RE: Help with query ... (Anand)

Marcio, January 13, 2004 - 6:51 am UTC

Anand you can find it out on this site, just search
to "stragg".

To do this in 9i
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2196162600402

To do this in 8i 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:229614022562 <code>

Like this in 9i:
ops$t_mp00@MRP9I1> create or replace type string_agg_type as object
2 (
3 total varchar2(4000),
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT string_agg_type ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN string_agg_type,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT string_agg_type,
22 ctx2 IN string_agg_type)
23 return number
24 )
25 /

Type created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> create or replace type body string_agg_type
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5 return number
6 is
7 begin
8 sctx := string_agg_type( null );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT string_agg_type,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 self.total := self.total || ',' || value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN string_agg_type,
22 returnValue OUT varchar2,
23 flags IN number)
24 return number
25 is
26 begin
27 returnValue := ltrim(self.total,',');
28 return ODCIConst.Success;
29 end;
30
31 member function ODCIAggregateMerge(self IN OUT string_agg_type,
32 ctx2 IN string_agg_type)
33 return number
34 is
35 begin
36 self.total := self.total || ctx2.total;
37 return ODCIConst.Success;
38 end;
39
40
41 end;
42 /

Type body created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> CREATE or replace
2 FUNCTION stragg(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5 /

Function created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> select country_name, state_name
2 from country natural join state
3 /

COUNTRY_NA STATE_NAME
---------- ------------------------------
USA TEXAS
USA VIRGINIA
USA CALIFORNIA
CANADA ONTARIO

4 rows selected.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> column state_name format a30 word_wrap
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> select country_name, stragg(state_name) state_name
2 from country natural join state
3 group by country_name
4 /

COUNTRY_NA STATE_NAME
---------- ------------------------------
CANADA ONTARIO
USA TEXAS,VIRGINIA,CALIFORNIA

2 rows selected.


Please Clarify

Reader, January 13, 2004 - 11:30 am UTC

Tom
Please explain
' you have cursor_sharing=force set on so all of the constants "disappear"
'
Regards









Tom Kyte
January 13, 2004 - 5:50 pm UTC

search for 

cursor_sharing force

on this site.... to see what it means/does....

ops$tkyte@ORA9IR2> alter session set cursor_sharing=force;
 
Session altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select 'hello', 'world', 55 from dual;
 
'HELLO'                          'WORLD'                                  55
-------------------------------- -------------------------------- ----------
hello                            world                                    55
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text
  2  like 'select %, %, % from dual';
 
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
select :"SYS_B_0", :"SYS_B_1", :"SYS_B_2" from dual


cursor sharing is an "autobinder", a crutch for bad programmers.... 

Answer to Reader from Columbus, OH USA

Christo Kutrovsky, January 13, 2004 - 3:18 pm UTC

To: Reader  from Columbus, OH USA 

Let me try to explain, Tom can correct me if I am wrong.

Because Oracle no longer nows that "Su" is can only be at max 2 caraters long.

Consider this:
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"

Becomes:
max(decode(to_char(dt,:"SYS_B_06"),:"SYS_B_07",lpad(to_char(dt,:"SYS_B_08"),:"SYS_B_09"))) "Su"

See how the second parameter of lpad, '2' is replaced with :"SYS_B_09", so the width of the column becomes unknown, thus defaults to max possible. sqlplus uses the with to format the query, thus he formats them with a possibility of 4000 caraters.

Consider this example:

SQL> create table XX (a varchar2(5));

Table created.

SQL> insert into XX values ('abc');

1 row created.

SQL> select * from XX;

A
-----
abc

SQL> alter table XX modify a varchar2(50);

Table altered.

SQL> select * from XX;

A
--------------------------------------------------
abc


See how in the first there were 5 '-' caraters, in the second there are '50'. Well, think about 4000.

I hope this helps.
 

Tom Kyte
January 13, 2004 - 5:54 pm UTC

yup....

Cursor Sharing and the column width

Henry Yick, January 13, 2004 - 10:45 pm UTC

I'm impressed with the finding you've made on this. In my 9i environment, nomatter I set the cursor_sharing to EXACT or FORCE, the column width of the day is 4 all the time. Like that:

MONTH
--------------------
Su Mo Tu We
---- ---- ---- ----
Th Fr Sa
---- ---- ----
January 2004

1 2 3


4 5 6 7
8 9 10


11 12 13 14
15 16 17
....

Although a few column formating can solve the problem. But what is the clue of this behaviour?

Regards

Tom Kyte
January 14, 2004 - 1:09 am UTC

perhaps you have a multi-byte character set.

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

what is the output of that.

Cursor Sharing and column width

Henry Yick, January 14, 2004 - 2:07 am UTC

Exactly! You are right. Mine is a multi-byte system and that's why. Thanks so much.

For the second question, I have tried the query in EXACT mode and retry the same query by changing the cursor_sharing to FORCE mode on session level. I belive the system will not perform autobinding and just use the cached statement if there's an exact match(with constant). That's why the result is the same even in FORCE mode.

Correct me if I'm wrong.


I had to change

Christo Kutrovsky, January 14, 2004 - 11:29 am UTC

I had to change the query (adding a dummy space somewhere) to force oracel to hard-parse it again, otherwise the force/exact change has no effect.



Thank you, Marcio

Anand, January 16, 2004 - 10:54 am UTC


Thanks , Marcio. It works like a charm.

A reader, February 13, 2004 - 7:35 am UTC


Logic and Strategies

Hien, March 22, 2004 - 6:11 pm UTC

Tom

I am refering to the cal emulation script. Brilliant!

Could you please share with us what is the strategy to come up such a script. The script somewhat looks complex but it produces such elegant output.

Perhaps you can explain step-by-step how you came up with the script.

Thank heaps.

Hien

Tom Kyte
March 22, 2004 - 9:15 pm UTC

I'll have to write "zen and the art of sql" someday -- hard to explain -- but if you start with the innermost inline view, I started there and built out iteratively.


select lpad( Month, 20-(20-length(month))/2 ) month,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
from (
select to_char(dt,'fmMonthfm YYYY') month,
to_char(dt+1,'iw') week,
max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' ), to_number(week)
/

I started with the set of dates for the year:

select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')

then I "pivoted" using a max/decode routine I use lots...

then I used sqlplus to format it a tiny bit...

Another Misc.Question

Ram, March 23, 2004 - 12:11 am UTC

Dear Sir,
 What may be wrong with the following query?
SQL> select deptno,max_sal,(select ename from emp where sal = max_sal and
  2  rownum = 1) max_ename,min_sal,(select ename from emp where sal =
  3  min_sal and rownum = 1) min_ename from(
  4    select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
  5      group by deptno)
  6  /
  select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
                    *
ERROR at line 4:
ORA-00979: not a GROUP BY expression 

Please do reply.


 

Tom Kyte
March 23, 2004 - 7:08 am UTC

1 select deptno,max_sal,(select ename from emp where sal = max_sal and
2 rownum = 1) max_ename,min_sal,(select ename from emp where sal =
3 min_sal and rownum = 1) min_ename from(
4 select deptno,max(sal) as max_sal,min(sal) as min_sal from emp
5* group by deptno)
scott@ORA9IR2> /

DEPTNO MAX_SAL MAX_ENAME MIN_SAL MIN_ENAME
---------- ---------- ---------- ---------- ----------
10 5000 KING 1300 MILLER
20 3000 SCOTT 800 SMITH
30 2850 BLAKE 950 JAMES


runs "fine" for me in 8.0.6 and up.....

Nice

Ram, March 27, 2004 - 5:53 am UTC

Hi Tom,
What do the group functions Variance and Stddev provide?
SQL> select variance(sal) from emp;

VARIANCE(SAL)                                                                   
-------------                                                                   
   1398313.87                                                                   
                                                                     

SQL> select stddev(sal) from emp;

STDDEV(SAL)                                                                     
-----------                                                                     
 1182.50322                                                                     
I have used them but don'n know what they represent?
Could you please explain?
Bye!



 

Tom Kyte
March 27, 2004 - 10:49 am UTC

google em. they are standard statistical functions. they come from "math"

(if you don't know what they are, you probably don't need them)

Statistical measures

Jim, March 27, 2004 - 11:18 am UTC

standard deviation is the square root of the varience. They are statistical measures of how tightly or loosly a series of measurements "group" around a mean. (average) If you have ever heard the expression in school "Graded on a curve" it refers to adjusting grades to follow a statistical bell curve (usually what is refered to as a normal distribution). (there are a ton of other distributions used for a variety of things)

Tom is correct, if you don't know what they are then you probably don't need them. However, they are important.




OK

Kumar, March 28, 2004 - 12:57 pm UTC

Hi Tom,
What is a multicolumn subquery?Where they can be useful?
Bye!


Tom Kyte
March 28, 2004 - 5:10 pm UTC

select * from emp
where (deptno,job) in ( select deptno, job from some_other_table );


try getting the right answer without using a multi-column subquery.... That is where they are useful.

Small bug due to "iw" format in cal query

Sean D Stuber, August 16, 2005 - 11:40 am UTC

the 'iw' format will return 53 for January 1, 2005, thus messing up the sorting. Try sorting by weekends instead...

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,
"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
FROM (
SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Su",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mo",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tu",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "We",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Th",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fr",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sa",
NEXT_DAY(dt-1,'SATURDAY') weekend
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM ALL_OBJECTS
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')
)
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), NEXT_DAY(dt-1,'SATURDAY')
)
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), weekend

Calendar SQL

Reena, June 04, 2007 - 5:03 am UTC

Tom,
Your code for generating calendar has been most useful to me.I have a question.
in a previous thread you said
<quote>
I started with the set of dates for the year:

select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
</quote>

My question is :
why do you use the table all_objects for generating the set of dates for the year ?(what if it doesnt have the number of rows to satisfy our query criteria) Just wondering...Is there any other way to do it ?
Thanks in advance.
Reena
Tom Kyte
June 05, 2007 - 8:16 am UTC

all_objects has thousands of rows, it is highly unlikely not to have enough rows for this purpose.

in 9iR2 and above, you can use:

ops$tkyte%ORA10GR2> with data as (select trunc(sysdate,'y')-1+level dt
  2                  from dual
  3                 connect by level <= add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') )
  4  select *
  5    from data
  6  /

DT
---------
01-JAN-07
02-JAN-07
03-JAN-07
04-JAN-07
...
29-DEC-07
30-DEC-07
31-DEC-07

365 rows selected.





Wowwwwwwwwwww!!!!

Jay, June 05, 2007 - 10:45 am UTC

Tom,

How on earth did you come up with this logic for the calendar??? I'm still trying to figure out what exactly you are doing and I have the code right in front of me :-(

This is complex!!!

Great job! I hope you or Sean will explain the logic for us. That would be very helpful :-O

Sean was right about the sorting problem that was happening with your code.

This is cool stuff Tom. Thanks a ton.

Mo or Su for start of week?

MH, July 07, 2007 - 8:30 am UTC

Tom,

Referring to the calendar query: it's GREAT!

One question tough:
I found out that my daynumbers differ from yours, so I needed to change the column prompts.

I.e.:

your

max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"

mine

max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Mo"

Q: What's causing this difference?

Checking and playing with values for v$nls_parameters,
nls_session_parameters, nls_database_parameters
(DUTCH vs. AMERICAN for NLS_DATE_LANGUAGE and NLS_LANGUAGE) didn't make any difference..was I changing the wrong parameters?


I ran your query on:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Many thanks for your time and great site.

Regards,

Martijn


OK

Ramesh, December 13, 2007 - 9:19 am UTC

Hi Tom,
I have a table a with c as int column with column length of 8.
I have data in this table 1,22,567,9999.
I would like to do an LPAD so that 0 gets prefixed to column values so that 
length of column values equal to default column length as per data dictionary.

SQL> create table a(c number(8))
  2  /

Table created.

SQL> insert into a values(12)
  2  /

1 row created.

SQL> insert into a values(223)
  2  /

1 row created.

SQL> insert into a values(4567)
  2  /

1 row created.

SQL> insert into a values(4)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from a
  2  /

         C
----------
        12
       223
      4567
         4

4 rows selected.



SQL> select t1.*,lpad(c,<<col length>>,'0')
  2  from a t1
  3  /

How to arrive at the <<col length>>?
Any idea you can give?


Tom Kyte
December 13, 2007 - 10:00 am UTC

well, if you wanted this to be "dynamic" - you would have to do something like:

ops$tkyte%ORA10GR2> create table t
  2  ( x number(5),
  3    y number(10),
  4    z number(15)
  5  )
  6  /

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1,2,3 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select to_char( x, 'fm'||rpad('0',(select data_precision
  2                                       from user_tab_columns
  3                                      where table_name = 'T'
  4                                        and column_name = 'X'),'0') ) x,
  5         to_char( y, 'fm'||rpad('0',(select data_precision
  6                                       from user_tab_columns
  7                                      where table_name = 'T'
  8                                        and column_name = 'Y'),'0') ) y,
  9         to_char( z, 'fm'||rpad('0',(select data_precision
 10                                       from user_tab_columns
 11                                      where table_name = 'T'
 12                                        and column_name = 'Z'),'0') ) z
 13    from t
 14  /

X     Y          Z
----- ---------- ---------------
00001 0000000002 000000000000003


but I would strongly recommend a VIEW here instead - you don't want to do unnecessary work and those scalar subqueries epitomize unnecessary work if you ask me - we can just maintain the view as part of our application if the lengths change in the future:
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view v
  2  as
  3  select to_char( x, 'fm' || rpad('0', 5,'0') ) x,
  4         to_char( y, 'fm' || rpad('0',10,'0') ) y,
  5         to_char( z, 'fm' || rpad('0',15,'0') ) z
  6    from t
  7  /

View created.

ops$tkyte%ORA10GR2> select * from v;

X     Y          Z
----- ---------- ---------------
00001 0000000002 000000000000003

Setting Line size

sriram, March 22, 2008 - 3:26 pm UTC


Hi Tom ,

Wishing you and your family a happy Easter

We are running a sql script and spool the output. We are hardcoding LINESIZE ( 600 here ), PAGESIZE etc here in th beginning of the script. Since it is hardcoded it is unnecessarily creating lot of spaces in the csv files it generates. One way is to count the size of all the coumns used in the sql scripts and then give exactly the same linesize. But it will be an issue later point if we add new columns or so. Nobobdy will notice the linesize that time.



Since the csv files is consuming lot of spaces and extra lines we have to run another script in unix to replace that, before loading those datas in our database. This impacts our perforamce heavily. Is there any way we can set this linesize automatically. i.e, it automatically shrinks or expands depends on the size of the data it retrives?.

Sample format

SET NEWPAGE 0

SET SPACE 0

SET LINESIZE 600

SET PAGESIZE 0

SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SET TERMOUT OFF

spool ../../csv/data.csv

< SELECT STATEMENTS >

spool off



exit



Thanks
Tom Kyte
March 24, 2008 - 11:13 am UTC

set trimspool on


ops$tkyte%ORA10GR2> set linesize 600
ops$tkyte%ORA10GR2> set trimspool off
ops$tkyte%ORA10GR2> spool x
ops$tkyte%ORA10GR2> select * from dual;

D
-
X

ops$tkyte%ORA10GR2> spool off
ops$tkyte%ORA10GR2> !ls -l x.lst
-rw-rw-r--  1 tkyte tkyte <b>1875</b> Mar 24 11:20 x.lst

ops$tkyte%ORA10GR2> set trimspool on
ops$tkyte%ORA10GR2> spool x
ops$tkyte%ORA10GR2> select * from dual;

D
-
X

ops$tkyte%ORA10GR2> spool off
ops$tkyte%ORA10GR2> !ls -l x.lst
-rw-rw-r--  1 tkyte tkyte <b>78 </b>Mar 24 11:20 x.lst

fix to calendar

Chris, June 06, 2008 - 4:19 pm UTC

Tom,

I noticed when I run your calendar code now (in 2008), I get this for December:

   December 2008     28  29  30  31
                          1   2   3   4   5   6
                      7   8   9  10  11  12  13
                     14  15  16  17  18  19  20
                     21  22  23  24  25  26  27




With a slight tweak, I got it to display correctly:

Replace all occurrences of to_char(dt+1,'iw') with to_char(dt+1,'iyiw')

December in 2008 then display's correctly:
   December 2008          1   2   3   4   5   6
                      7   8   9  10  11  12  13
                     14  15  16  17  18  19  20
                     21  22  23  24  25  26  27
                     28  29  30  31



Good One

Guru, August 07, 2008 - 6:04 am UTC

Hi,

I have a question what does the 'iy' will do in this query.

Thanks,

Alexander, March 17, 2010 - 11:55 am UTC

I just saw the latest list of the wealthiest people in the world, some new guy toppled Bill and Warren. But Larry is listed at #6! Dang. I knew Oracle was doing well, but I didn't realize he was to the point were if you asked him for a quarter, he'd say, "what's a quarter?"
Tom Kyte
March 18, 2010 - 6:58 am UTC

well, it has been quite a few years since he's been on that last, many many years actually, he passed Bill Gates at one point some 10 years ago.

CALENDAR

ARJUN, September 24, 2010 - 1:25 am UTC

AWESOME

super stuff!!

Inge Heijnen, October 20, 2010 - 2:11 pm UTC

The bit with the level from oracle 9 onwards was exaclty what i needed. :D
Using a production calendar to get minimum date i was able to make a calendar that will update nicely every year and make weeknumber etc available.
What's not in the database we make :D :D
Thanks so much!

Expansion for Oracle

Rajeshwaran, Jeyabal, October 08, 2011 - 12:07 pm UTC

Tom:

Is this the Expansion for ORACLE?

Oakridge Relational Analytical Computing Logical Engine

https://forums.oracle.com/forums/thread.jspa?threadID=111536
Tom Kyte
October 09, 2011 - 11:09 am UTC

Oracle is not an acronym.

It is the name, and that is all. I comes from the original project name, "project Oracle", being performed for a customer back before it was a product.

Simple Calendar SQL

theaos, April 10, 2013 - 9:01 am UTC


You can use another simple calendar sql below,

and view details about this sql on
http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=69#3398



with t as (
 select to_date('200811','yyyymm') ym from dual)
 SELECT * FROM (
 SELECT MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '1', LEVEL)) SUN
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '2', LEVEL)) MON
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '3', LEVEL)) TUE
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '4', LEVEL)) WED
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '5', LEVEL)) THU
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '6', LEVEL)) FRI
       , MIN (DECODE (TO_CHAR (YM + LEVEL - 1, 'd'), '7', LEVEL)) SAT
    FROM T
 CONNECT BY LEVEL <= LAST_DAY (YM) - YM + 1
   GROUP BY TRUNC (YM + LEVEL, 'iw')
   ORDER BY 7)


result:

       SUN        MON        TUE        WED        THU        FRI        SAT
 ---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                            1
          2          3          4          5          6          7          8
          9         10         11         12         13         14         15
         16         17         18         19         20         21         22
         23         24         25         26         27         28         29
         30                                                                  



max/decode

BTes, September 21, 2013 - 6:48 pm UTC

Hello Tom,

My doubt is on calender query.. May I know how max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd') "sun" .... work ? I mean ,how does max and decode work together..?

As far as I know, max() returns maximum value in that column, but i do not see anything like that in this case.. could u please explain it.

Regards




More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.