My OCAJP6 Java Certificate

Bismillahir Rahmanir Raheem.

Thanks To Almighty  Allah that I passed this exam.

I remember those days for preparing for this exam.

My SQL Expert Certificate

Bismillahir Rahmanir Raheem.

Thanks to All mighty dear Allah that I passed this exam.

I will  soon write a blog for the preparation of this exam.

Changing NLS_Language NLS_Territory NLS_Calendar

Bismillahir Rahmanir Raheem

When I installed my Oracle database I probably selected US or I think default installation is US.
But you can change it as you like later
We will  see how to change it

You can see all your NLS_parameters from a dynamic view called NLS_Parameters

select *
from NLS_Parameters;

You will see in the results parameters like
NLS_Language    AMERICAN
NLS_Calendar    GREGORIAN
NLS_Territory     AMERICA

and others

so lets change the default values

alter session set NLS_Language='Arabic';

alter session set NLS_Territory='United Arab Emirates';

alter session set NLS_Calendar='Arabic Hijrah';


 Similarly you can change any NLS_parameters as you like

You might  want to know what are the values that you can assign to these NLS parameters

here is link to oracle documentation to help you

Oracle Database Globalization Support

Working with oracle timezone format TZR TZH TZM

Bismillahir Rahmanir Raheem

Today we will look to some format that are used to convert or outputting more precise information.

TZH--
This gives the hour from the timezone and is only valid with timestamp and interval formats

TZM----
This gives the minute from the timezone and is only valid with timestamp and interval formats

TZR---
This gives the time zone region information and is only valid with timestamp and interval formats

alter session set time_zone='asia/calcutta';

select to_char(systimestamp,'TZR-TZH-TZM')
from dual;

this will give us

Asia/calcutta-+05-30

NOte-- remember all these formats are only valid with timestamp and interval types not with date type

INSERTING ROWs INTO TABLE FROM SELECTING ROWS FROM ANOTHER TABLE

Bismillahir Rahmanir Raheem

We will insert rows into a table from retreiving rows from another table

Create table table1(id number,name varchar2(20));

insert into table1 values(111,'Mahtab Alam');
insert into table1 values(222,'Aftab Alam');
insert  into table1 values(333,'Sdre Alam');

Now we have three rows in table1 .
Lets add these rows into table2

create table table2(RollNo number,Student_name varchar2(20));

insert into table2
select id,name
from table1;

or

create table table3(pid number);

insert into table3(pid)
select id
from table1;

Oracle To_date() function for converting into date datatype

Bismillahir Rahmanir Raheem

Suppose I have table Entry as

Create table Entry(Entered number);

insert into Entry values(12122012);
insert into Entry values(10102013);
insert into Entry values(11112013);

select * from Entry;





Now lets change it to some meaningful  way , lets change it to date which it actually represents

SELECT  ENTERED,TO_DATE(ENTERED,'DDMMYYYY')
FROM     ENTRY;

HOW TO FIND OUT THE AVAILABLE TIMEZONES IN ORACLE SQL

BISMILLAHIR RAHMANIR RAHEEM

You can query the dynamic view named  as   v$timezone_names to get the list  of
available timezones .

select *
from v$timezone_names;

This will give you a huge list  of timezone names .
Now you can use these names to change your timezone

You can change your timezone either by giving timezone offset or timezone regional name.

Its better to use timezone regional names

Here is the query

alter session set time_zone='ASIA/CALCUTTA';