Today will be awsome

Today while entering to my office pantry, i found something positive written on pantry glass door. So below is the sentence:

"Today will be awsome"

I started thinking, this is just a small sentence, but how much impact it will make on someone's life.

So for example, today morning i wake with this thought in my mind.

Then I write the positive or good stuff i want to happen on that day.

So, During the day i may remember this or not but when i back to home, before sleep i will check what i have written, did this happen today with me and if not, then what efforts​ i spent to make it work.

If i make this practice on regular basis i may achieve what i am looking for.

Beacuse if i make this as practice i will spent some efforts to achieve what i want or written. And person who spent efforts to achieve something he wants, is always satisfied with whatever he received in return. And remember "satisfaction is key to happiness", thats what i believe.

-Mohsin

Pivot in Oracle SQL

Recently i came across one post on OTN in which user need pivot kind of output and people giving him some weird and lengthy solution so i thought to share this on my blog:

first of all we need to prepare test table and test data

CREATE TABLE fruits
(
person VARCHAR2(30),
fruitname VARCHAR2(30)
);


insert into fruits values ('abhi','APPLE');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','ORANGE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','MANGO');
insert into fruits values ('abu','MANGO');
insert into fruits values ('abu','ORANGE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','MANGO');
insert into fruits values ('anirudh','MANGO');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','LICHI');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');

Solution 1: Using aggregate function and case statement:

SQL> SELECT person,
  2  SUM(CASE WHEN fruitname='APPLE' THEN 1 ELSE 0 END) AS APPLE,
  3  SUM(CASE WHEN fruitname='LICHI' THEN 1 ELSE 0 END) AS LICHI,
  4  SUM(CASE WHEN fruitname='MANGO' THEN 1 ELSE 0 END) AS MANGO,
  5  SUM(CASE WHEN fruitname='ORANGE' THEN 1 ELSE 0 END) AS ORANGE
  6  FROM fruits
  7  GROUP BY person
  8  ;

PERSON                              APPLE      LICHI      MANGO     ORANGE
------------------------------ ---------- ---------- ---------- ----------
abhi                                    1          7          5          1
abu                                     3          8          2          1
anirudh                                 6          5          2          9
shakti                                  4          1          6          5

Solution 2: Using PIVOT Operator:

SQL> SELECT * FROM    (SELECT PERSON ,FRUITNAME
  2          FROM   fruits)
  3  PIVOT  (COUNT(FRUITNAME) AS FRUITNAMES FOR (FRUITNAME) IN ('APPLE' AS apple, 'MANGO' AS mango, 'ORANGE' AS orange));

PERSON                         APPLE_FRUITNAMES MANGO_FRUITNAMES ORANGE_FRUITNAMES
------------------------------ ---------------- ---------------- -----------------
abhi                                          1                5                 1
abu                                           3                2                 1
anirudh                                       6                2                 9
shakti                                        4                6                 5

Note: Here i have not included LITCHI

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1328M

1) create pfile from spfile;
2) SQL> startup nomount pfile=D:\app\oracle\product\11.2.0\dbhome_1\database\INITorcl.ora
3)  alter database open;
4) alter database open;
5) create spfile from pfile;

Spool table in excel sheet / html page

set feed on markup html on
spool c:\test.xls
select * from emp;
spool off
set feed off markup html off

same way you can export it in html also, you just need to change file extension from .xls to .html

Converting exponential value to numeric

e.g.
select 1987252000000000000 from dual
1.987252E18

Check this:
select to_char(''||1987252000000000000) from dual
1987252000000000000

Find word count from string

select (length('mmohsamohsdmohsg')-length(replace('mmohsamohsdmohsg','mohs',''))) / length('mohs') from dual;

undo retention for ORA-01555

1) alter system set undo_retention = 1500 scope=both; 2) Check undot tablespace size 3) Disk size where datafile of tablespaces are located 4) Query optimiztion if required which causes this issue