Display Calender in SQL and PLSQL

Today I will present how to display calendar specific month from oracle SQL or PLSQL like below image

So I will present the solution to do this

First I will create Object type to handle week days( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)

 CREATE OR REPLACE TYPE WEEK_DAY AS OBJECT  
   (SUN NUMBER (2),  
   MON NUMBER (2),  
   TUE NUMBER (2),  
   WED NUMBER (2),  
   THU NUMBER (2),  
   FRI NUMBER (2),  
   SAT NUMBER (2));  

Display Detail Rows as One Column in Master

Sometimes, it is required to display detail rows as a single column in master row.
For example in scott schema you have two tables ( DEPT, EMP) which relation is 1-M .

If required to display Employees names separated by comma  as one column per every DEPTNO, ÷In order to the final output like the following.





The easiest way to get output of previous diagram is the following SQL query.

    SELECT d.deptNo,
           MAX (SUBSTR (SYS_CONNECT_BY_PATH (d.eName, ','), 2)) employees
      FROM (SELECT deptNo,
                   eName,
                   ROW_NUMBER ()
                      OVER (PARTITION BY deptNo ORDER BY deptNo, eName)
                      rnum
              FROM scott.emp) d
START WITH d.rnum = 1
CONNECT BY d.rnum = PRIOR d.rnum + 1 AND PRIOR d.deptNo = d.deptNo
  GROUP BY d.deptNo

Thanks

Get All PLSQL Errors

I developed a function to get all PLSQL errors in schema.
This function should be used after calling program units so that can get PLSQL errors and you can use for logging and tracing.


Function Code

CREATE OR REPLACE FUNCTION GET_PLSQL_ERROS
   RETURN VARCHAR2
IS
   LC$RETVALUE   VARCHAR2 (4000);

   CURSOR LCUR$ERRORS
   IS
        SELECT DISTINCT NAME, TYPE
          FROM USER_ERRORS
      ORDER BY 1, 2;

   PROCEDURE ADD_LINE (IN_LINE IN VARCHAR2)
   IS
   BEGIN
      LC$RETVALUE := SUBSTR (LC$RETVALUE || IN_LINE || CHR (10), 1, 4000);
   END ADD_LINE;
BEGIN
   FOR LREC$ERRORS IN LCUR$ERRORS
   LOOP
      ADD_LINE (LREC$ERRORS.NAME || ' ' || LREC$ERRORS.TYPE);

      ADD_LINE (
         RPAD ('-', LENGTH (LREC$ERRORS.NAME || LREC$ERRORS.TYPE) + 1, '-'));

      FOR LREC$ERROR_DET
         IN (  SELECT LINE, POSITION, SUBSTR (TEXT, 1, 128) TEXT
                 FROM USER_ERRORS
                WHERE NAME = LREC$ERRORS.NAME AND TYPE = LREC$ERRORS.TYPE
             ORDER BY SEQUENCE)
      LOOP
         ADD_LINE (
               LPAD (LREC$ERROR_DET.LINE, 4)
            || ' '
            || LPAD (LREC$ERROR_DET.POSITION, 3)
            || ' '
            || LREC$ERROR_DET.TEXT);
      END LOOP;

      ADD_LINE ('*******************' || CHR (10));
   END LOOP;

   IF LENGTH (LC$RETVALUE) = 4000
   THEN
      LC$RETVALUE := SUBSTR (LC$RETVALUE, 1, 3996) || CHR (10) || '...';
   END IF;

   RETURN NVL (LC$RETVALUE, 'No Errors');
END;
/

Hijrah Date in Oracle Database

Some Arabic countries use Hijrah calendar specially in Saudi Arabia.
So today I will post about Hijrah Date and how to convert between different calendars.

The main notes in this post
1-NLS Calendars in Oracle Database
2-Convert Georgian calendar to Hijrah calendar
3-Convert Hijrah Calendar to Georgian
4-Fix deviations between calendars
5-How to create lexical file for the deviation

Get Oracle Home & Oracle SID from PLSQL

The below script gets Oracle Home and Oracle SID from PL/SQL

 DECLARE  
   LC$ORACLE_HOME  VARCHAR2 (2000);  
   LC$ORACLE_SID  VARCHAR2 (256);  
 BEGIN  
   DBMS_SYSTEM.GET_ENV ('ORACLE_HOME', LC$ORACLE_HOME);  
   DBMS_SYSTEM.GET_ENV ('ORACLE_SID', LC$ORACLE_SID);  
   
   -- Oracle Home  
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME = ' || LC$ORACLE_HOME);  
   
   -- Oracle SID  
   DBMS_OUTPUT.PUT_LINE ('ORACLE_HOME = ' || LC$ORACLE_SID);  
 END;  

Thanks

Recycle Bin in Database

Oracle introduced in database 10g new feature called "Recycle Bin" to store the dropped database objects.
If any table is dropped then any associated object to this table such as indexes, constraints and any other dependent object are renamed with a prefix of bin$$.

Use of Recycle Bin

If user drop an important object accidentally, and he want to get it again.
With Recycle Bin feature user can easily restore the dropped objects.

Enable and Disable Recycle Bin

You can use the below query to distinguish which Recycle Bin is enabled or no

 SELECT Value FROM V$parameter WHERE Name = 'recyclebin';  

It will return on or off
on means that Recycle Bin is enabled and off is disabled.

You can enable and disable Recycle Bin per session and system, there fore you can use the below scripts to enable and disable Recycle Bin per session or system.

 ALTER SYSTEM SET recyclebin = ON;  
   
 ALTER SESSION SET recyclebin = ON;  
   
 ALTER SYSTEM SET recyclebin = OFF;  
   
 ALTER SESSION SET recyclebin = OFF;  

Oracle Database SQL Expert Certification

Hi all,

I am now an Oracle Database SQL Expert Certified  .

I wrote 1Z0-047 today and passed it with 100% score.

Thanks

WITH Clause in SELECT statement

I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITH clause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Generate list of dates and times

I want to create query returns list of dates and time for example

1-Jan-2012

1-Jan-2012 12:30:00 AM

1-Jan-2012 13:00:00 AM

1-Jan-2012 13:30:00 AM

1-Jan-2012 14:00:00 AM

.................

.................

1-Jan-2012 11:00:00 PM

1-Jan-2012 11:30:00 PM

To execute the previous requirement I can use the below query

   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <= 48;  

Find Unused Columns in Oracle Database

Sometimes during development of new systems, you may add new columns to tables and then you don't use it and forget dropping it.

So you want to know which these columns to drop.
Usually unused columns have NULL value, So I created a function to return array of column names in my schema have NULL value.

I created GET_NULL_COLUMNS function returns VARRAY of varchar2
It has only one parameter (IN_TABLE_NAME)
If I pass a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.

Reset Sequence Value

I have old table already have a lot of data, I take decision to create new sequence to use it for getting serials in primary key of table to it.

For example : table SCOTT.EMP has EMPNO primary key and I want to create new sequence EMPNO_SEQ to store NEXTVAL of sequence in EMPNO column.

CREATE SEQUENCE SCOTT.EMPNO_SEQ
   START WITH 1
   MAXVALUE 9999999
   MINVALUE 0
   CACHE 25;

Oooooops, EMPNO column already has stored data that is maximum than sequence next value.
So I take decision to create generic procedure to reset sequence next value to maximum value of primary key in any table.

I created RESET_SEQUENCE procedure which takes three parameters
a-IN_SEQUENCE_NAME : name of sequence that I will use.
b-IN_TABLE_NAME : name of table which I will store sequence next value in its column
c-IN_COLUMN_NAME : name of column, If it is null I will get column which is primary key

PRAGMA INLINE in Subprogram Calling

Introduction

Subprogram is procedure or function that is declare in declaration section like below

 DECLARE  
   PROCEDURE XXX (IN_PARAMATER NUMBER)  
   IS  
   BEGIN  
    --CODE OF PROCEDURE  
   END;  
   
   FUNCTION YYY (IN_PARAMETER NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    --CODE OF FUNCTION  
   END;  
 BEGIN  
   --CODE OF ANONYMOUS BLOCK  
 END;  

   
When you call subprogram(procedure or function) in your code multiple time, in every call it will be loaded again so it will take more time to execute.
To enhance previous drawback we use inline to replace the subprogram call with the copy of already called subprogram before.

Handle Business Days

Introduction
In real business life we consider days as business days only, we exclude holidays and weekends from our calendar days.
So today I will produce solution of how to work with business days.

At first public holidays are different from country to others so I will create table to store every public holidays.

 CREATE TABLE HOLIDAYS (HOLIDAY_DATE DATE, DESCR VARCHAR2 (100));  
   
 ALTER TABLE HOLIDAYS ADD (  
  CONSTRAINT HOLIDAYS_PK  
  PRIMARY KEY  
  (HOLIDAY_DATE));  

I should get weekends days also so I will get using below query
Note that at my country weekend is Fridays and Saturday.

   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION 
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  

The previous query contains whole weekends from 5th January 1900 to 18th August 2091.

Now I will create database view for whole holidays( Public holidays and weekends)

 CREATE OR REPLACE VIEW HOLIDAYS_VIEW  
 (HOLIDAY_DATE)  
 AS   
 SELECT HOLIDAY_DATE FROM HOLIDAYS  
 UNION  
   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION  
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999;  
   

Interchange the Values of Columns

Sometimes you want to interchange the values of two columns in specific database table.

Suppose that I have EMP table with column EMPNO, ENAME, JOB, and by wrong the value in ENAME column  is the value of JOB column and vice versa.
To correct the data I should interchange the values of ENAME and JOB.

I have three solutions for doing this
1- Add temporary column to table
2- Rename columns
3-DML statement

DBMS_COMPARISON Package

DBMS_COMPARISON is a new package introduced by oracle in database 11g which is used for comparing database objects in different databases.

The DBMS_COMPARISON package can compare the following types of database objects:
    a- Tables
    b- Single-table views
    c- Materialized views
    d- Synonyms for tables, single-table views, and materialized views

The DBMS_COMPARISON package cannot compare data in columns of the following data types:
    a- LONG
    b- LONG RAW
    c- ROWID
    d- UROWID
    e- CLOB
    f- NCLOB
    g- BLOB
    h- BFILE
    i- User-defined types (including object types, REFs, varrays, and nested tables)
    j- Oracle-supplied types (including any types, XML types, spatial types, and media types)

CHAR vs VARCHAR vs VARCHAR2

Today I will present short notes about popular string data types in SQL and PLSQL.

CHAR

it is used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
Example

 CREATE TABLE CHAR_TAB (CHAR_COL CHAR (10));  

 INSERT INTO CHAR_TAB (CHAR_COL)  
    VALUES ('Mahmoud');  
 COMMIT;  

Let's now select data and lenght of data and dump of data in table CHAR_TAB

 SELECT CHAR_COL, LENGTH (CHAR_COL), DUMP (CHAR_COL) FROM CHAR_TAB;  

The output will be like

As We noticed in result that length is 10 characters inspire of I entered 'Mahmoud' which has only 7 characters.
We also noticed that in dump he padded at latest ASCII code three times number 32 which is ASCII code of space.

Generate Random Password in Oracle

In my application I need to generate password for users first time after registration.
The generated password must has some criteria which system administrator will configure it.
1- Character should be UPPERCASE                              =====> Abbreviation [U]
2- Character should be LOWERCASE               =====> Abbreviation [L]
3- Character should be NUMBER                  =====> Abbreviation [N]
4- Character should be any character                      =====> Abbreviation [A]
5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]

So I thought to create dynamic function "RANDOM_PASSWORD" to return random password regarding to previous criteria.
The the system administrator will pass criteria per every character in password text to function and it will return random password
For example :-
first character should be UPPERCASE               ======> U
second character should be LOWERCASE          ======> L
third character should be NUMBER                   ======>N
forth character should be any character             ======>A
fifth character should be NON-ALPHANUMERIC  ======>S
sixth character should be Number                    ======> N   

This will generate string "ULNASN" regarding to abbreviation.

Oracle DB 11g New Feature (Read Only Tables)

I posted before about new features about Oracle Database 11g you can read it from below

Oracle DB 11g New Feature ( Virtual Columns ) 

Oracle DB 11g New Feature ( Compound Triggers )

Today I will produce new feature which called Read Only Tables

Read only tables are like normal  tables but it restricts any transaction to perform any DML(Insert, Update, Delete) operation against it.

Before oracle database version 11g READ ONLY was related to DATABASE and TABLE SPACE only but in version 11g you can do READ ONLY to tables too.

Play with NULL Value

NULL is big problem in whole programming language, Today I will write about NULL and  what's the problems we face in code and tips to play with NULL values.

Calling any method or variable of Class has NULL value raise exception in other programming language like C++, Java, C# ,.... etc but in PLSQL it doesn't raise any exception.
If I use NULL in any calculation or logical condition, the output will be NULL.

I wrote before about Three-Valued Logic and the problem  in three-valued logic in PLSQL is NULL value.

Lets see example work with NULL before begin illustration.

CREATE OR REPLACE PROCEDURE MY_NAME (IN_NAME VARCHAR2)
IS
BEGIN
   IF IN_NAME != 'Mahmoud'
   THEN
      DBMS_OUTPUT.PUT_LINE ('My name is not Mahmoud');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My name is Mahmoud');
   END IF;
END;

BEGIN
   MY_NAME (NULL);
END;

If I run previous code it will print

 My name is Mahmoud  

Create Insert Statement for Table Data

In every site we have more than one environment (Testing, Development, Production, .....etc).
Sometimes we insert any data in one environment and want to migrate it to another environment.

Usually we use database editors to do this task like (Toad, Plsql Developer, SQL developer, .... etc), but in my post today I will create PLSQL function that will generate insert statement for you.

Here is the GEN_INSERT_STATEMENT function is used return SQL select  statement against input table parameter which we can use it to generate insert statement