Wednesday, June 17, 2009

The different types of joins in oracle database

The different types of joins in oracle database:
Joins are used to produce the single data set output from multiple tables based on requirement to get matched records or mismatched records.
the different types of joins we can see in oracle database and one can write joins in either ANSI/ISO or SQL92 standard and both will work on 9i later version of oracle database systems.

I found interesting in writing of join conditions as joins drives the entire query efficient execution and returns output in best optimized way. one can use join concepts in best possible way to get desired results in faster way.

These are the joins one can see oracle database and will explain one by one.

Self joins
Inner joins
Equi-joins
non equi-joins
Outer joins
Cross joins
Anti-joins
Semi-joins

Self Joins:
As we know it is a joining of table itself and we can say table is joined or compared to itself. This table name appears twice in the FROM clause of select statement and followed by table aliases to qualify the column names in the join condition.

Example:


Inner join:
An inner join is a join of more than two tables that returns all those rows that satisfy the given join condition.

Equi-joins

non equi-joins

Outer joins

Cross joins

Anti-joins

Semi-joins

-- more yet to come...
----- Have a fun in working in Oracle Technologies-- Cheers--

The different types of joins in oracle database

The different types of joins in oracle database:
Joins are used to produce the single data set output from multiple tables based on requirement to get matched records or mismatched records.
the different types of joins we can see in oracle database and one can write joins in either ANSI/ISO or SQL92 standard and both will work on 9i later version of oracle database systems.

I found interesting in writing of join conditions as joins drives the entire query efficient execution and returns output in best optimized way. one can use join concepts in best possible way to get desired results in faster way.

These are the joins one can see oracle database and will explain one by one.

Self joins
Inner joins
Equi-joins
non equi-joins
Outer joins
Cross joins
Anti-joins
Semi-joins

Self Joins:
As we know it is a joining of table itself and we can say table is joined or compared to itself. This table name appears twice in the FROM clause of select statement and followed by table aliases to qualify the column names in the join condition.

Example:


Inner join:
An inner join is a join of more than two tables that returns all those rows that satisfy the given join condition.

Equi-joins

non equi-joins

Outer joins

Cross joins

Anti-joins

Semi-joins

-- more yet to come...
----- Have a fun in working in Oracle Technologies-- Cheers--

Tuesday, June 16, 2009

Revoking oracle directory creation permission from a user in oracle

Revoking oracle directory creation permissions from a user in oracle:

CREATE ANY DIRECTORY usually useful when one want to create oracle directories in schema level, however it is found that one can create oracle directories and drop any directories without having create any directory system privileges in case schema has either imp_full_database or exp_full_database, bit surprising but may be it has been included in the view of impdp and expdp point of view. however when schema is not supposed to create any directories then we must check whether it has above said privileges to avoid misuse of oracle directories and misuse of directory concept when user supposed to exp or imp of his own user data..

hopefully this info may help in tightening security where security is mandatory.

Revoking oracle directory creation permission from a user in oracle

Revoking oracle directory creation permissions from a user in oracle:

CREATE ANY DIRECTORY usually useful when one want to create oracle directories in schema level, however it is found that one can create oracle directories and drop any directories without having create any directory system privileges in case schema has either imp_full_database or exp_full_database, bit surprising but may be it has been included in the view of impdp and expdp point of view. however when schema is not supposed to create any directories then we must check whether it has above said privileges to avoid misuse of oracle directories and misuse of directory concept when user supposed to exp or imp of his own user data..

hopefully this info may help in tightening security where security is mandatory.

Thursday, June 11, 2009

MD 50 and MD 70 Documents - oracle application development

Some technical jargon of oracle -apps- process documents in implementation:

What are MD 50 and MD 70 Documents?

AIM (Oracle Application Implementation Methodology)

I have come across in my career with following documents and would like to have information here for future references

The documents explained here are mainly used in oracle development which consists of SQL, PLSQL, Forms, Reports, Graphics -Oracle Developer suits development. I used these docs quite some time back and I liked these most as I found information on existing components and understanding of user requirement in better way. It was excitement for me to make this MD 70 documents in initial days of my career.

Yes, there are other documents that are known as

BR Documents: Business Requirement Documents -Personally not used

MD Documents: Modular Designing Documents - these are usually based on BR 120 etc

MD50: This document holds the functional specifications that are required for component to be developed and it is meant for Technical guys or programmers to understand the functional requirements for customization or design/implementations of software artifacts. Usually Functional guy will be made this.

MD60: Module expert would create MD60 containing the requirements for the problem or the solution to be developed.

MD70: Technical guy or Programmer would create MD70 which holds design solutions for the requirements or component solution to be designed. This document would have all the technical stuff, sql, plsql code ,forms, reports etc .. for reuse purpose and process adherence purpose.


If any one of you know more info on this, please add comments here..


cheers..

MD 50 and MD 70 Documents - oracle application development

Some technical jargon of oracle -apps- process documents in implementation:

What are MD 50 and MD 70 Documents?

AIM (Oracle Application Implementation Methodology)

I have come across in my career with following documents and would like to have information here for future references

The documents explained here are mainly used in oracle development which consists of SQL, PLSQL, Forms, Reports, Graphics -Oracle Developer suits development. I used these docs quite some time back and I liked these most as I found information on existing components and understanding of user requirement in better way. It was excitement for me to make this MD 70 documents in initial days of my career.

Yes, there are other documents that are known as

BR Documents: Business Requirement Documents -Personally not used

MD Documents: Modular Designing Documents - these are usually based on BR 120 etc

MD50: This document holds the functional specifications that are required for component to be developed and it is meant for Technical guys or programmers to understand the functional requirements for customization or design/implementations of software artifacts. Usually Functional guy will be made this.

MD60: Module expert would create MD60 containing the requirements for the problem or the solution to be developed.

MD70: Technical guy or Programmer would create MD70 which holds design solutions for the requirements or component solution to be designed. This document would have all the technical stuff, sql, plsql code ,forms, reports etc .. for reuse purpose and process adherence purpose.


If any one of you know more info on this, please add comments here..


cheers..

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

Tuesday, June 9, 2009

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;
-- returns the IP address of client machine from which connection is established

few more..
select USERENV('LANGUAGE') ,
USERENV('ISDBA') from dual;
..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;
-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;

-- returns the IP address of client machine from which connection is established

few more..
select USERENV('LANGUAGE')  ,
USERENV('ISDBA') from dual;

..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;

-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...

Monday, June 8, 2009

Number of connections established on or hitting oracle listener port

Number of connections established on or hitting oracle listener port:

The easiest way to find the listener(s) running on oracle db system is as follows:

lsnrctl status

it will list all the database names that are running currently.

whats the way to find listener port then?.. here is the way to find using simple command

tnsping service_name

example: tnsping mydb

finally we got host , port and service_name in simple text, we got everything that one can use to make a connection with port

the final query answer and purpose of this post come here that is finding the number of of connections established/hitting oracle listener port
( there are many options, one can opt which is best based on purpose and results)

netstat -an | grep port_number

example: netstat -an | grep 1521

netstat -anp | grep port_number

there are many usages of this command, with which one can find what is running on a particular port or how many connections established etc ..

(looking more info on netstat, the easiest way to find is man netstat )

Hope this will help you .. or anything missed out here that one feel to add.. so why wait, make a comment now.. :)

Number of connections established on or hitting oracle listener port

Number of connections established on or hitting oracle listener port:

The easiest way to find the listener(s) running on oracle db system is as follows:

lsnrctl status

it will list all the database names that are running currently.

whats the way to find listener port then?.. here is the way to find using simple command

tnsping service_name

example: tnsping mydb

finally we got host , port and service_name in simple text, we got everything that one can use to make a connection with port

the final query answer and purpose of this post come here that is finding the number of of connections established/hitting oracle listener port
( there are many options, one can opt which is best based on purpose and results)

netstat -an | grep port_number

example: netstat -an | grep 1521

netstat -anp | grep port_number

there are many usages of this command, with which one can find what is running on a particular port or how many connections established etc ..

(looking more info on netstat, the easiest way to find is man netstat )

Hope this will help you .. or anything missed out here that one feel to add.. so why wait, make a comment now.. :)