Wednesday, May 27, 2009

ORACLE - Use of tnsnames.ora and listener.ora files

LISTENER.ORA
This file contains configuration for the server side.It contains details like on which port, which protocol etc the server should listen for connection requests from clients. This file is found in ORACLE_HOME/network/admin directory.

TNSNAMES.ORA
The file contains configuration for client side. It can be found in the ORACLE_HOME/network/admin . This file will also be present on the server if client is also on the same machine as server. This file needs to be configured for all clients with the IP address and port of the server so that clients can communicate with the server.

I will come up with the configuration of these files shortly for using a gateway.

Tuesday, May 26, 2009

OTG - Oracle Transparent Gateway

OTG or Oracle Transparent Gateway is a tool used to communicate with non-Oracle products (may be other databases like DB2) and technologies. Using OTG, we can access data in remote DB2 using Oracle's PL/SQL syntax. OTG has DRDA gateway which connects with DB2. If you want to connect to other databases like Sybase n all, you can use other gateways.

To use a gateway, we need to first install it( quite obvious). After installation, we need to configure it which is a tedious task even if we try to do it by reading manuals.( I've been working on it for 3 days, still no progress).

Well, thats all i know about it but I will surely write about the configuration in my next post. So wish me luck :).

Friday, May 22, 2009

PL/SQL Wrapper

Have you ever tried to see the contents of the object file in C ?
If not, then let me tell you that it contains all unreadable characters ie, given the object code, nobody can generate the C code from it.
If you want a similar kind of protection in PL/SQL code, you need to use wrap command. In this way, you can protect your source code. You can now distribute your wrapped code without the fear of it being copied. The PL/SQL compiler recognizes and loads wrapped code automatically.


Using PL/SQL Wrapper

wrap iname=plsqlsource.sql oname=plsqlwrapcode.plb

where the extension plb means PL/SQL binary.

Saturday, May 16, 2009

ORACLE 8i, 11g - What is "i" and "g"

Meaning of "i" and "g"

Well "g" in Oracle 10g and onwards indicates grid computing. Grid computing means clubbing of several computers, by means of a network, where they work on a single problem at the same time. It enables resource sharing for multiple applications thus giving advantages like flexibility,low cost, efficiency, performance, scalability etc.

The "i" in Oracle 8i,9i indicates support for the Internet with built-in Java Virtual Machine (JVM). With a JVM (Java interpreter) built into the DBMS, triggers and stored procedures can be written and executed in Java . It thus enables Internet developers to write applications as well as procedures in Java.

Friday, May 15, 2009

ORACLE DATA TYPES

Lets discuss three data types CHAR, VARCHAR and VARCHAR2 and understand the differences between them. This is also one of the frequently asked questions in interviews.

CHAR
It is used for storing fixed length character strings. For example FLAGS in tables which contain Y or N for YES or NO respectively.
NAME CHAR(50);
When we declare such a type for name which contains variable no of characters, it results in wastage of lot of space.
NAME := 'MANIK' fills 5 places rest of the places will be padded with spaces.
So, its not a good idea to use this type for variable length strings.
NOTE: When we don't specify a size with CHAR, it defaults to one.


VARCHAR
Its not used generally, I learnt from internet that it is kept for furture use, otherwise it behaves same as VARCHAR2 as discussed next

VARCHAR2
It is used for storing variable length strings. In Oracle 10g, a VARCHAR2 column may be up to 4000 bytes in a table and it may be up to 32767 bytes in a PL/SQL program.

NAME VARCHAR2(50) := 'MANIK';
This does not leads to wastage of space. Only 5 places are occupied.