OC4J Configuration issue In Oracle 11g Enterprise Manager.

C:\Documents and Settings\Rajib>emctl start dbconsole
OC4J Configuration issue. D:\IN\app\Rajib\product\11.2.0\dbhome_1/oc4j/j2ee/OC4J_DBConsole_RAJIB_MICR;

C:\Documents and Settings\Rajib>emca -config dbcontrol db

STARTED EMCA at May 30, 2013 12:52:58 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: MICR
Database Control is already configured for the database MICR
You have chosen to configure Database Control for managing the database MICR
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener ORACLE_HOME [ D:\IN\app\Rajib\product\11.2.0\dbhome_1 ]: D:\IN\app\Rajib\product\11.2.0\dbhome
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): rajiboracledev@yahoo.com
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\IN\app\Rajib\product\11.2.0\dbhome_1

Local hostname ................ RAJIB
Listener ORACLE_HOME ................ D:\IN\app\Rajib\product\11.2.0\dbhome_1
Listener port number ................ 1521
Database SID ................ MICR
Email address for notifications ............... rajiboracledev@yahoo.com
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
May 30, 2013 12:54:57 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\IN\app\Rajib\cfgtoollogs\emca\MICR\emca_2013_05_30_12_52_58.
May 30, 2013 12:54:59 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 30, 2013 12:55:04 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
May 30, 2013 12:56:11 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
May 30, 2013 12:56:23 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library is already configured.
May 30, 2013 12:56:23 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO:  EM_SWLIB_STAGE_LOC (value) will be ignored.
May 30, 2013 12:56:23 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
May 30, 2013 12:57:14 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
May 30, 2013 12:57:14 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
May 30, 2013 12:57:23 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
May 30, 2013 12:57:23 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 30, 2013 12:58:09 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 30, 2013 12:58:09 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://RAJIB:1158/em <<<<<<<<<<<
May 30, 2013 12:58:12 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 30, 2013 12:58:12 PM

C:\Documents and Settings\Rajib>

Wrap PL/SQL Source Code.

#############  Original Source Code ##########################

CREATE OR REPLACE PROCEDURE EXPORT_IMAGE_WRAPE (p_file  IN  VARCHAR2,
                                         p_blob  IN  BLOB)
AS LANGUAGE JAVA
NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';

########  Save This Source Code In Your D Or Any Other Drive Name wrape_input.sql ##########

Now Open Command Prompt (CMD)

Type the following:

C:\Documents and Settings\Rajib>wrap iname=D:\wrape_input.sql oname=D:\wrape_output.sql

PL/SQL Wrapper: Release 11.2.0.1.0- Production on Wed May 29 20:19:17 2013

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing D:\wrape_input.sql to D:\wrape_output.sql

C:\Documents and Settings\Rajib>

Now You can see Wrap File Name wrape_output.sql In Your D Drive.

####################  Output Source Code  ########################
   CREATE OR REPLACE PROCEDURE EXPORT_IMAGE_WRAPE wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
cf eb
PzOCyr9qnqCDVJBb+N4Eu+anT1cwg43wNcusfI6iTBIYnR22JQK66p3OAqsOLvORscNsouup
K+B6vKxJuCu3nEf9YwFZBfK2aYwaIgT3C+aLTczzRpeKomCmUaZNzMY+/DKgRqpRdHIUtT7o
YezBVOZA6QkQnXDQKhVZmUe94blAXif4bfYAjP4QpYaiFRMQk4t+mi3A0rG8bXClM4qjM89X
3xPW1wYde7OCrg==

/
Now Execute this code in your Database.

Parameter :

iname >> Input Source File Name With Location.

oname >> Output Source File Name With Location.

Create And Use SYNONYM In Oracle Database.

############ CREATING A TABLE FOR TESTING SYNONYM #############

SQL> CREATE TABLE TOP_N_ANALYSIS (ID NUMBER, NAME VARCHAR2(30), SALARY NUMBER);

Table created.

############  INSERTING DATA INTO TABLE #############################

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(10,'RAJIB', 5000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(20,'JOHN', 4000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(30,'KING', 9000);

1 row created.

SQL> COMMIT;

Commit complete.

########## SHOW DATA FROM TABLE ################################

SQL> SELECT * FROM TOP_N_ANALYSIS;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        10 RAJIB                                5000
        20 JOHN                                 4000
        30 KING                                 9000

Synonym created.

########## SHOW DATA FROM SYNONYM IN HERE YOU GET ERROR ############

SQL> SELECT * FROM TOP_N;
SELECT * FROM TOP_N
              *
ERROR at line 1:
ORA-00942: table or view does not exist

########## CREATE SYNONYM FOR TOP_N_ANALYSIS TABLE #################

SQL> CREATE SYNONYM TOP_N FOR TOP_N_ANALYSIS;

Synonym created.

########## SHOW DATA FROM SYNONYM ############

SQL> SELECT * FROM TOP_N;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        10 RAJIB                                5000
        20 JOHN                                 4000
        30 KING                                 9000

########## DROP SYNONYM ############

SQL> DROP SYNONYM TOP_N;

Synonym dropped.

SQL> SELECT * FROM TOP_N;
SELECT * FROM TOP_N
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Using Top-N Queries In Oracle.

############ CONNECTING IN ORACLE DATABASE #######################

SQL> CONN RND/RND
Connected.

############ CREATING A TABLE FOR TESTING TOP N QUERY #############

SQL> CREATE TABLE TOP_N_ANALYSIS (ID NUMBER, NAME VARCHAR2(30), SALARY NUMBER);

Table created.

############  INSERTING DATA INTO TABLE #############################

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(10,'RAJIB', 5000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(20,'JOHN', 4000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(30,'KING', 9000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(40,'MORGAN', 2000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(50,'William', 11000);

1 row created.

SQL> INSERT INTO TOP_N_ANALYSIS(ID, NAME, SALARY) VALUES(60,'Steven', 8000);

1 row created.

SQL> COMMIT;

Commit complete.

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY ###############

SQL> SELECT ROWNUM AS RANK, ID, NAME, SALARY FROM(
  2  SELECT ID, NAME, SALARY
  3  FROM   TOP_N_ANALYSIS
  4  ORDER BY SALARY DESC)
  5  WHERE ROWNUM<=3;

      RANK         ID NAME                               SALARY
---------- ---------- ------------------------------ ----------
         1         50 William                             11000
         2         30 KING                                 9000
         3         60 Steven                               8000

########## SELECT LOW 3 PERSON WHO GET LOWEST SALARY ###############

SQL> SELECT ROWNUM AS RANK, ID, NAME, SALARY FROM(
  2  SELECT ID, NAME, SALARY
  3  FROM   TOP_N_ANALYSIS
  4  ORDER BY SALARY)
  5  WHERE ROWNUM<=3;

      RANK         ID NAME                               SALARY
---------- ---------- ------------------------------ ----------
         1         40 MORGAN                               2000
         2         20 JOHN                                 4000
         3         10 RAJIB                                5000

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY USING RANK() FUNCTION ########

SQL> SELECT  ID, NAME, SALARY
  2  FROM   (SELECT SALARY, ID, NAME,
  3                 RANK() OVER (ORDER BY SALARY DESC) AS RANK_VAL
  4          FROM   TOP_N_ANALYSIS)
  5  WHERE  RANK_VAL <= 3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        50 William                             11000
        30 KING                                 9000
        60 Steven                               8000

########## SELECT TOP 3 PERSON WHO GET HIGEST SALARY USING DENSE_RANK() FUNCTION ########

SQL> SELECT  ID, NAME, SALARY
  2  FROM   (SELECT SALARY, ID, NAME,
  3                 DENSE_RANK() OVER (ORDER BY SALARY) AS RANK_VAL
  4          FROM   TOP_N_ANALYSIS)
  5  WHERE  RANK_VAL <= 3;

        ID NAME                               SALARY
---------- ------------------------------ ----------
        40 MORGAN                               2000
        20 JOHN                                 4000
        10 RAJIB                                5000

ORA-12162: TNS:net service name is incorrectly specified

SQL> conn / as sysdba

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

[oracle@micr~#] export ORACLE_SID=micrdb

[oracle@micr~#] sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 27 14:08:20 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.
SQL>startup

Export Image Or Blob File From Database To Folder/Directory.

###############    Execute The Following Java Source In Database   ##################

CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS
    import java.lang.*;
    import java.sql.*;
    import oracle.sql.*;
    import java.io.*;

    public class BlobHandler
    {
      public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
      {
        File binaryFile = new File(myFile);
        FileOutputStream outStream = new FileOutputStream(binaryFile);
        InputStream inStream = myBlob.getBinaryStream();

        int size = myBlob.getBufferSize();
        byte[] buffer = new byte[size];
        int length = -1;

        while ((length = inStream.read(buffer)) != -1)
        {
          outStream.write(buffer, 0, length);
          outStream.flush();
        }

        inStream.close();
        outStream.close();
      }

    };

####################   Create Procedure Using Java Source   ###########################

   CREATE OR REPLACE PROCEDURE EXPORT_IMAGE (p_file  IN  VARCHAR2,
                                             p_blob  IN  BLOB)
    AS LANGUAGE JAVA
    NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';

############# Given Permition The Following For Execute Java Source Code ###########

    DECLARE
     V_USER VARCHAR2(30) := 'RND'; -- USER NAME
    BEGIN
      DBMS_JAVA.grant_permission(V_USER, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
      DBMS_JAVA.grant_permission(V_USER, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
      DBMS_JAVA.grant_permission(V_USER, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
    END;
    /

#########    Export Image Or BLOB File To Directory/Folder ################

DECLARE
CURSOR c1 IS
SELECT ID, PHATO
FROM TEST_IMAGE; 
   
BEGIN
FOR i IN c1 LOOP
  
 EXPORT_IMAGE('C:\IMG\'||i.ID||'.jpg',i.PHATO);
     
END LOOP;
END;
/

Using CREATE, TRUNCATE And DROP Statements In PL/SQL.

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE TEST_DDL(ID NUMBER)';
END;

BEGIN
INSERT INTO TEST_DDL(ID) VALUES (10);
END;

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_DDL';
END;

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TEST_DDL';
END;

Using Commit/Rollback In Oracle Trigger.

1. CREATE TABLE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

SQL> CREATE TABLE TEST_TRIGGER(ID NUMBER, NAME VARCHAR2(10));

Table created.

2. CREATE TABLE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

SQL> CREATE TABLE ERROR_CHECK(ID NUMBER);

Table created.

3. CREATE TRIGGER FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER==========================================================

SQL> CREATE OR REPLACE TRIGGER CHECT_COMMIT_TRIGGER
  2  AFTER INSERT ON TEST_TRIGGER
  3  BEGIN
  4  INSERT INTO ERROR_CHECK(ID) VALUES (10);
  5  COMMIT;
  6  END;
  7  /

Trigger created.

4. INSERTING VALUE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

SQL> INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB');
INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB')
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "MICR.CHECT_COMMIT_TRIGGER", line 3
ORA-04088: error during execution of trigger 'MICR.CHECT_COMMIT_TRIGGER'

IN THIS PART YOU GET AN ERROR
==========================================================

5. DROP TRIGGER
==========================================================

SQL> DROP TRIGGER CHECT_COMMIT_TRIGGER;

Trigger dropped.

6. CREATE AUTONOMOUS TRIGGER FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER

NOTE THAT ONLY IN AUTONOMOUS TRIGGER ARE ALLOWED FOR USES COMMIT/ROLLBACK
====================================================================


SQL> CREATE OR REPLACE TRIGGER CHECT_COMMIT_TRIGGER
  2  AFTER INSERT ON TEST_TRIGGER
  3  DECLARE
  4  PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6  INSERT INTO ERROR_CHECK(ID) VALUES (10);
  7  COMMIT;
  8  END;
  9  /

Trigger created.


7. INSERTING VALUE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================
SQL> INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB');

1 row created.

SQL>


SUCCESSFULLY INSERTED
===========================================================

Insert Image Into Table From A Directory.

1. CONNECT AS SYSDBA IN YOUR DATABASE FOR CREATE A DIRECTORY WHERE HAVE OUR IMAGE
=========================================================================

SQL> conn sys /as sysdba
Enter password:
Connected.

2. CREATE A DIRECTORY FOR COLLECT IMAGE FROM DIRECTORY
=========================================================================

SQL> CREATE OR REPLACE DIRECTORY
  2  IMAGE_DIR AS
  3  'D:\IMAGE\';

Directory created.

3. GIVEN DIRECTORY ACCES PERMITION TO USER
=========================================================================

SQL> GRANT READ, WRITE ON DIRECTORY IMAGE_DIR TO MICR;

Grant succeeded.

4. CONNECTING SPECTFIC USER FOR INSERTING IMAGE
=========================================================================

SQL> conn micr/micr;
Connected.

5. CREATING A FUNCTION FOR CONVERT IMAGE TO BLOB
=========================================================================

SQL> CREATE OR REPLACE FUNCTION FUN_IMAGE_TO_BLOB (
  2     P_IMAGE   IN   VARCHAR2,
  3     P_DIR    IN   VARCHAR2
  4  )
  5     RETURN BLOB
  6  IS
  7     fblob          BLOB;
  8     thebfile       BFILE;
  9     bsrc_offset    NUMBER := 1;
 10     bdest_offset   NUMBER := 1;
 11  BEGIN
 12     DBMS_LOB.createtemporary (fblob, FALSE, DBMS_LOB.SESSION);
 13     thebfile := BFILENAME (P_DIR, P_IMAGE);
 14     DBMS_LOB.fileopen (thebfile);
 15     DBMS_LOB.loadblobfromfile (dest_lob         => fblob,
 16                                src_bfile        => thebfile,
 17                                amount           => DBMS_LOB.getlength (thebfile),
 18                                dest_offset      => bdest_offset,
 19                                src_offset       => bsrc_offset
 20                               );
 21     DBMS_LOB.fileclose (thebfile);
 22     RETURN fblob;
 23  EXCEPTION
 24     WHEN OTHERS
 25     THEN
 26        RETURN NULL;
 27  END;
 28  /

Function created.

6. CREATING A TABLE FOR INSERT IMAGE
=========================================================================

SQL> CREATE TABLE TEST_IMAGE(ID NUMBER, PHATO BLOB);

Table created.

7. INSERTING IMAGE INTO DATABASE FROM A DIRECTORY
=========================================================================

SQL> INSERT INTO TEST_IMAGE(ID, PHATO) VALUES(10, FUN_IMAGE_TO_BLOB('ORA.jpg','IMAGE_DIR'));

1 row created.

SQL>COMMIT;

Find Sumation Of Largest Set Of Value.

CREATING FUNCTION FOR GET SUMATION OF LARGEST VALUE
========================================================================

SQL> CREATE OR REPLACE FUNCTION LARGEST_VALUE_FROM_NUMBER
  2  (P_VALUE1 NUMBER,
  3   P_VALUE2 NUMBER,
  4   P_VALUE3 NUMBER,
  5   P_VALUE4 NUMBER,
  6   P_SELECT NUMBER
  7   )
  8   RETURN NUMBER
  9   IS
 10   V_VAL1 NUMBER;
 11   V_VAL2 NUMBER;
 12   V_VAL3 NUMBER;
 13   V_VAL4 NUMBER;
 14
 15    TYPE REC_TYPE IS RECORD (
 16      ARRY_VAL  NUMBER);
 17
 18    TYPE TAB_TYPE IS TABLE OF REC_TYPE
 19      INDEX BY BINARY_INTEGER;
 20
 21    T_VALUE TAB_TYPE;
 22  BEGIN
 23    T_VALUE(1).ARRY_VAL := P_VALUE1;
 24    T_VALUE(2).ARRY_VAL := P_VALUE2;
 25    T_VALUE(3).ARRY_VAL := P_VALUE3;
 26    T_VALUE(4).ARRY_VAL := P_VALUE4;
 27
 28    SELECT SUM(VAL)
 29    INTO V_VAL1
 30    FROM (
 31    SELECT VAL FROM (
 32    SELECT T_VALUE(1).ARRY_VAL VAL  FROM DUAL
 33    UNION ALL
 34    SELECT T_VALUE(2).ARRY_VAL VAL FROM DUAL
 35    UNION ALL
 36    SELECT T_VALUE(3).ARRY_VAL VAL FROM DUAL
 37    UNION ALL
 38    SELECT T_VALUE(4).ARRY_VAL VAL FROM DUAL)
 39    ORDER BY 1 DESC)
 40    WHERE ROWNUM <=P_SELECT;
 41
 42
 43      RETURN V_VAL1;
 44
 45    EXCEPTION
 46    WHEN OTHERS THEN
 47    RAISE_APPLICATION_ERROR(-20001,'Error In Select Max Number Mode');
 48
 49  END;
 50  /

Function created.

EXECUTING FUNCTION
==========================================================================

SQL> SELECT LARGEST_VALUE_FROM_NUMBER(10,15,20,40,3) FROM DUAL;

LARGEST_VALUE_FROM_NUMBER(10,15,20,40,3)
----------------------------------------
                                      75

SQL>

Get Number In Word Using Oracle Function.

CREATING FUNCTION TO GET NUMBER IN WORD 
=========================================================================
SQL> CREATE OR REPLACE FUNCTION DFU_NUMBER_IN_WORD
  2  (
  3  P_AMOUNT NUMBER
  4  )
  5  RETURN VARCHAR2
  6  /* *****************************************************************************************
  7    * DATABASE FUNCTION UGET AMOUNT IN WORD                                                   *
  8    * USE PARAMETER VALUE FOR                                                                 *
  9    *  PP_AMOUNT =>> AMOUNTT IN NUMBER                                                        *
 10    *               WRITE AS 18/01/2012 BY MOHAMMAD RAJIB PRADHAN                             *
 11    *****************************************************************************************/
 12
 13  IS
 14    V_MAINAMUT VARCHAR2(2000) ;
 15    V_TOPAMOUN VARCHAR2(2000) ;
 16    V_BOTTAMUT VARCHAR2(2000) ;
 17    V_DECIMALT VARCHAR2(2000) ;
 18    V_TOPNUMBR NUMBER(20,5) ;
 19    V_MAIN_AMT NUMBER(20,5) ;
 20    V_TOPANTNU NUMBER(20,5) ;
 21    V_BOTTANNU NUMBER(20,5) ;
 22    V_DECIMALN NUMBER(20,5) ;
 23    V_AMTNUMBR NUMBER(20,5);
 24    V_TEXTRETN VARCHAR2(2000) ;
 25      BEGIN
 26        V_MAIN_AMT := NULL ;
 27        V_TOPAMOUN := NULL ;
 28        V_BOTTAMUT := NULL ;
 29        V_DECIMALT := NULL ;
 30
 31        -- TO GET PAISA PART
 32       V_DECIMALN    := P_AMOUNT - TRUNC(P_AMOUNT) ;
 33
 34         IF V_DECIMALN >0 THEN
 35        V_DECIMALN := V_DECIMALN *100;
 36        END IF;
 37
 38         V_AMTNUMBR  := TRUNC(P_AMOUNT) ;
 39
 40
 41         V_TOPNUMBR  := TRUNC(V_AMTNUMBR / 100000) ;
 42         V_MAIN_AMT  := TRUNC(V_TOPNUMBR / 100);
 43         V_TOPANTNU  := V_TOPNUMBR - V_MAIN_AMT * 100 ;
 44         V_BOTTANNU  :=  V_AMTNUMBR - (V_TOPNUMBR * 100000) ;
 45
 46        IF V_MAIN_AMT > 0 THEN
 47            V_MAINAMUT   := TO_CHAR(TO_DATE(V_MAIN_AMT,'J'),'JSP') ;
 48           IF V_MAIN_AMT  = 1 THEN
 49              V_MAINAMUT := V_MAINAMUT || ' CRORE ' ;
 50           ELSE
 51              V_MAINAMUT := V_MAINAMUT || ' CRORES ' ;
 52            END IF ;
 53         END IF ;
 54
 55         IF V_TOPANTNU > 0 THEN
 56            V_TOPAMOUN   := TO_CHAR(TO_DATE(V_TOPANTNU,'J'),'JSP') ;
 57            IF V_TOPANTNU = 1 THEN
 58              V_TOPAMOUN := V_TOPAMOUN || ' LAKH ' ;
 59            ELSE
 60              V_TOPAMOUN := V_TOPAMOUN || ' LAKHS ' ;
 61            END IF;
 62         END IF ;
 63        IF V_BOTTANNU > 0 THEN
 64          V_BOTTAMUT := TO_CHAR(TO_DATE(V_BOTTANNU,'J'),'JSP') ;
 65        END IF ;
 66        IF V_DECIMALN > 0 THEN
 67           IF NVL(V_BOTTANNU,0) + NVL(V_TOPANTNU,0) > 0 THEN
 68              V_DECIMALT := ' AND ' || TO_CHAR(TO_DATE(V_DECIMALN,'J'),'JSP') || ' Paise ' ;
 69           ELSE
 70              V_DECIMALT :=  TO_CHAR(TO_DATE(V_DECIMALN,'J'),'JSP') ||' Paise ';
 71            END IF ;
 72              END IF ;
 73
 74
 75         V_TEXTRETN := LOWER(V_MAINAMUT || V_TOPAMOUN || V_BOTTAMUT || ' Tk'  || V_DECIMALT || ' ONLY') ;
 76
 77         V_TEXTRETN := UPPER(SUBSTR(V_TEXTRETN,1,1))|| SUBSTR(V_TEXTRETN,2);
 78        V_TEXTRETN  := ' '|| V_TEXTRETN;
 79      RETURN INITCAP(V_TEXTRETN);
 80
 81     END DFU_NUMBER_IN_WORD;
 82  /

Function created.

USING FUNCTION TO GET NUMBER IN WORD 
=========================================================================





SQL> SELECT DFU_NUMBER_IN_WORD(152415.25) FROM DUAL;

DFU_NUMBER_IN_WORD(152415.25)
--------------------------------------------------------------------------------
 One Lakh Fifty-Two Thousand Four Hundred Fifteen Tk And Twenty-Five Paise  Only

SQL>

Export Data From Table To Text or Csv or Excel File In a Directory Using Oracle Function.

CREATING A DIRECTORY FOR GENERATE FILE
=============================================================================================

CREATE OR REPLACE DIRECTORY
FROM_DIR AS
'D:\FILE\';

CREATING A FUNCTION FOR GENERATE FILE
=============================================================================================

CREATE OR REPLACE function DFU_GENERATE_FILE_FROM_DATA( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2,
                                      p_header    IN VARCHAR2,
                                      p_endline   IN VARCHAR2 DEFAULT chr(13))
                                     
   /******************* USING FORMATE *******************************
    *DECLARE                                                        *
    *                                                               *
    * l_var    varchar2(4000);                                      *
    *                                                               *
    * BEGIN                                                         *
    *                                                               *
    * l_var  :=  DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM SLOGONIF',                *
    *                                 ',',                          *
    *                                'FROM_DIR' ,                   *
    *                                'RAJIB.CSV',                   *
    *                                ' ',                           *
    *                                ' ' );                         *                       
    *                                                               *
    * END;                                                          *
    *****************************************************************/  
  
  
  
   -- This routine makes certain assumptions.
   -- 1) There must be a query and it can't be greater then 32K.
   -- 2) The separator must only be one character in length and can't be
   --    a CR, LF, binary 0, or null (easy to change).
   -- 3) If the p_dir parameter is null, the p_filename must contain the
   --    path and filename (/tmp/output.txt)
   -- 4) If the p_header parameter is not null, then insert it into the first
   --    row of the output file. If the p_separator parameter is not a comma,
   --    the comma's in the header string will be replaced with the new
   --    separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
   --    the separator is a tab, what is put into the file would be
   --    'NAME<tab>FIRST_NAME<tab>LAST_NAME'
   -- 5) The value of p_endline will be appended to the end of each line of the
   --    output file. It can be used to add a carriage return before the
   --    Line Feed is inserted by the NEW_LINE (unix). If the server is running
   --    on a windows machine, set this to null since the NEW_LINE will save
   --    a CR,LF pair anyway. This can also be used if you needed to put
   --    something at the end. For exanple "'|'||CHR(13)" which would put a
   --    vertical bar and CR,LF on each line on a unix machine.
   --
   -- The following are the returned error codes
   -- -1 The query is empty
   -- -2 The output filename is empty
   -- -3 The separator is invalid.
   -- -4 The filename only contains the path, no filename specified.
   -- -5 The output file can not be opened.
   -- -6 The query could not be parsed. It was illegal.
   --  0 The query returned NO records.
   -- >0 The number of records returned.

return number
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
    l_dir           VARCHAR2(500);
    l_filename      VARCHAR2(32);
    x_pnt           NUMBER(4);
    l_header        VARCHAR2(2000);
begin
    -- sanity check the input
    IF p_query IS NULL THEN
       RETURN(-1);
    END IF;
    IF p_filename IS NULL THEN
       RETURN(-2);
    END IF;
    -- Do not allow CR, LF,binary 0, or null to be used as a separator.
    -- The length of the separator must be 1 if it exists.
    IF p_separator IS NULL OR
       p_separator IN (chr(13),chr(10),chr(0)) OR
       length(p_separator) > 1 THEN
       RETURN(-3);
    END IF;
    -- If the directory parameter is blank, assume that the directory
    -- is included in the filename.
    IF p_dir IS NOT NULL THEN
       l_dir := p_dir;
       l_filename := p_filename;
    ELSE
       x_pnt := instr(p_filename,'/',-1,1);
       -- If no path is specified or no filename is specified,
       -- the procedure will not work... get out.
       IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
          RETURN(-4);
       END IF;
       l_dir := substr(p_filename,1,x_pnt-1);
       l_filename := substr(p_filename,x_pnt+1);
    END IF;


    -- Check to see if the file can be opened. If ANY error is
    -- encountered, exit with a count of -1;
    BEGIN
       l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
    EXCEPTION
       WHEN OTHERS THEN
          RETURN(-5);
    END;

    -- Check to see if the query can be processed. if ANY error is
    -- encountered, close the output file and exit.
    BEGIN
       dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    EXCEPTION
       WHEN OTHERS THEN
          utl_file.fclose( l_output );
          RETURN(-6);
    END;

    -- If the p_header parameter is not null, then insert the line as
    -- the first line in the output file. This is used if the user wants
    -- to insert column headings. Make sure to use a comma in your header
    -- line and the routine will replace all comma;s with the specified
    -- separator.
    l_header := NULL;
    IF p_header IS NOT NULL THEN
       l_header := p_header;
       IF p_separator <> ',' THEN
          l_header := REPLACE(l_header,',',p_separator);
       END IF;
    END IF;

    -- Loop through all the parameters for the select. To support
    -- unknown querys, the assumption is that the query will return
    -- all columns as varchar2 columns where the data is correctly
    -- formatted for inport. A maximum of 255 columns are supported
    -- in the query. Each column can't be greater then 2000
    -- characters in length.

   
   
    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;
    -- This define_column insures that at least one column is defined for the
    -- routine.
    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    -- Fire the query.

    l_status := dbms_sql.execute(l_theCursor);

    -- Loop through all the rows returned by the query. Build up the output file
    -- by looping through the defined columns.

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        IF l_cnt = 0 AND l_header IS NOT NULL THEN
           utl_file.put(l_output, l_header);
           utl_file.put(l_output, p_endline);
           UTL_FILE.NEW_LINE (l_output,1);
           l_cnt := 1;
        END IF;
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
       utl_file.put(l_output, p_endline);
       UTL_FILE.NEW_LINE (l_output,1);
       l_cnt := l_cnt+1;
    end loop;

    -- Processing done. close the cursor and output file.

    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );
   
    -- Return the number of rows built in the csv file.

    return l_cnt;
    -- If Any error occures outside of the errors checked above, then raise
    -- and error and blow out the procedure.
    EXCEPTION
       WHEN OTHERS THEN
          RAISE;
end DFU_GENERATE_FILE_FROM_DATA;
/


USEING A FUNCTION FOR GENERATE FILE
=============================================================================================

DECLARE                                                       
                                                              
L_DATA  varchar2(4000);                                     
                                                               
 BEGIN                                                        
                                                            
   L_DATA :=  DFU_GENERATE_FILE_FROM_DATA( 'SELECT * FROM EMPLOYEES',               
                               ',',                         
                                 'FROM_DIR' ,                  
                               'RAJIB.CSV',                  
                                ' ',                          
                               ' ' );                                             
                                                                
   END;

FIND YEAR, MONTHS, DAY, HOUR, MINUTE AND SECOUND BETWEEN TWO DATE USING FUNCTION.

SQL> CREATE OR REPLACE function  FUN_YEAR_MONTHS_DAY( p_date1 DATE , p_date2 DATE, p_type VARCHAR2)
  2  return char
  3  is
  4
  5   Years        NUMBER;
  6   months       NUMBER;
  7   days         NUMBER;
  8   day_fraction NUMBER;
  9   hrs          NUMBER;
 10   mints        NUMBER;
 11   sec          NUMBER;
 12
 13  begin
 14
 15   Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
 16   months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
 17   days  :=trunc(p_date2 - add_months(p_date1,trunc(months_between(p_date2,p_date1) )));
 18
 19   day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);
 20
 21   hrs   :=trunc(day_fraction*24);
 22   mints :=trunc((((day_fraction)*24)-(hrs))*60);
 23   sec   :=trunc(mod((p_date2-p_date1)*86400,60));
 24   If p_type='YY' Then
 25      return(years||' Years ');
 26   elsif p_type='MM' Then
 27      return(months||' Months ');
 28   elsif p_type='DD' Then
 29      return(days||' Days ');
 30   elsif p_type='YMD' Then
 31      return(years||' Years '||months||' Months '||days||' Days ');
 32   elsif p_type='ALL' Then
 33   return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes '||sec||' Seconds');
 34  End If;
 35
 36  end;
 37  /

Function created.

SQL> SELECT FUN_YEAR_MONTHS_DAY('12-JAN-1990',SYSDATE,'ALL') FROM DUAL;

FUN_YEAR_MONTHS_DAY('12-JAN-1990',SYSDATE,'ALL')
--------------------------------------------------------------------------------
23 Years 4 Months 5 Days 17 Hours 51 Minutes 55 Seconds

SQL>

DELETING DUPLICATE ROWS FROM A TABLE.

SQL> CREATE TABLE TEST_DUP(ID NUMBER, NAME VARCHAR2(200));

Table created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (10, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (10, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'KING');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'RAJIB');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (30, 'MORGAN');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (20, 'KING');

1 row created.

SQL> Insert into TEST_DUP
  2     (ID, NAME)
  3   Values
  4     (30, 'MORGAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEST_DUP;

        ID NAME
---------- --------------------
        10 RAJIB
        10 RAJIB
        20 KING
        20 RAJIB
        30 MORGAN
        20 KING
        30 MORGAN

7 rows selected.

SQL> DELETE FROM TEST_DUP
  2  WHERE ROWID NOT IN (SELECT MIN (ROWID)
  3  FROM TEST_DUP
  4  GROUP BY ID,NAME);

3 rows deleted.

SQL> SELECT * FROM TEST_DUP;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        20 RAJIB
        30 MORGAN

SQL> COMMIT;

Commit complete.

SQL>

Change Database Parameter And Memory Size.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Rajib>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 16 20:36:55 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn sys /as sysdba
Enter password:
Connected.
SQL>
SQL> show parameter process

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0                             
db_writer_processes                  integer     1                             
gcs_server_processes                 integer     0                             
job_queue_processes                  integer     10                            
log_archive_max_processes            integer     2                             
processes                            integer     150                           
SQL> show session
SP2-0158: unknown SHOW option "session"
SQL> show parameter session

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0                             
java_soft_sessionspace_limit         integer     0                             
license_max_sessions                 integer     0                             
license_sessions_warning             integer     0                             
logmnr_max_persistent_sessions       integer     1                             
session_cached_cursors               integer     20                            
session_max_open_files               integer     10                            
sessions                             integer     170                           
shared_server_sessions               integer                                   
SQL> show parameter sga

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE                         
pre_page_sga                         boolean     FALSE                         
sga_max_size                         big integer 584M                          
sga_target                           big integer 584M                          
SQL> show parameter pga

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 194M                          

SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> alter system set sga_max_size=684M scope=spfile;

System altered.

SQL> alter system set sga_target=684M scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=294M scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  717225984 bytes                                      
Fixed Size                  1292684 bytes                                      
Variable Size             192939636 bytes                                      
Database Buffers          515899392 bytes                                      
Redo Buffers                7094272 bytes                                      
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.
SQL> show parameter processes

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0                             
db_writer_processes                  integer     1                             
gcs_server_processes                 integer     0                             
job_queue_processes                  integer     10                            
log_archive_max_processes            integer     2                             
processes                            integer     300                           
SQL> show parameter session

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0                             
java_soft_sessionspace_limit         integer     0                             
license_max_sessions                 integer     0                             
license_sessions_warning             integer     0                             
logmnr_max_persistent_sessions       integer     1                             
session_cached_cursors               integer     20                            
session_max_open_files               integer     10                            
sessions                             integer     335                           
shared_server_sessions               integer                                   
SQL> show parameter sga

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE                         
pre_page_sga                         boolean     FALSE                         
sga_max_size                         big integer 684M                          
sga_target                           big integer 684M                          
SQL> show parameter pga

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 294M                          
SQL> show parameter transa

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
transactions                         integer     368                           
transactions_per_rollback_segment    integer     5                             
SQL> exit

Create Oracle Database Link.

A database link is a schema object in one database that enables you to access objects on another database.

1. Database link using TNS

CREATE PUBLIC DATABASE LINK "REMOTDB"
 CONNECT TO HR
 IDENTIFIED BY HR
 USING 'TESTDB';

 Public Database Link For Access All Database User.

CREATE DATABASE LINK REMOT
CONNECT TO scott IDENTIFIED BY tiger
USING 'TESTDB';

 Private Database Link For Fixed Database User.

2. Database link Without Using TNS.

CREATE PUBLIC DATABASE LINK MICRDB
 CONNECT TO MICR
 IDENTIFIED BY MICR
 USING '10.11.201.153:1521/ORCL';

Installation and configuration Oracle Database 11g R2 64bit In Red Hat 5.5, 5.8 And 6.2 Step by Step.

Required Software
1.            Operating System Red Hat Linux 5.5 64 bit.
2.            OracleDatabase 11g R2 64 bit for Linux.

######################  Setup Start Oracle Database 11g R2  ########################## 

1. Set IP and host name the following way.
    System >Administration >Network
    Now set IP in device tag and set host name in Hosts tag.
    Now check your IP and host name the following way.
 
2. Open terminal and write the following

[root@micr~#]vi /etc/hosts
127.0.0.1            localhost.localdomain  localhost
10.11.201.200    micr.localdomain  micr        # micr is the host name

Now ping your user name in the following way.

[root@micr~#]ping micr

(Hints replay 10.11.201.200   IP address. If replay 127.0.0.1 problem)

3. Set minimum parameter settings in the following way.

[root@micr~#]vi /etc/sysctl.conf
 Now press i for insert the following lines. And past the following lines.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912                 # Set this value calculation RAM size*1024*1024*1024
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

[ N.B: If any item is duplicate then set comments previous item Using #)

Check for duplicate item 
[root@micr~#]/sbin/sysctl –p

4. Now set limits the following way 
[root@micr~#]vi /etc/security/limits.conf
 Now press i for insert the following lines. And past the following lines.

oracle              soft     nproc   2047
oracle              hard    nproc   16384
oracle              soft     nofile  4096
oracle              hard    nofile  65536
oracle              soft     stack   10240

5.Now install the following packages if they are not already present from your DVD.

cd /media/cdrom/Server

rpm -Uvh binutils-2.*

rpm -Uvh compat-libstdc++-33*

rpm -Uvh compat-libstdc++-33*.i386.rpm

rpm -Uvh elfutils-libelf*

rpm -Uvh gcc-4.*

rpm -Uvh gcc-c++-4.*

rpm -Uvh glibc-2.*

rpm -Uvh glibc-common-2.*

rpm -Uvh glibc-devel-2.*

rpm -Uvh glibc-headers-2.*

rpm -Uvh ksh*

rpm -Uvh libaio-0.*

rpm -Uvh libaio-devel-0.*

rpm -Uvh libgomp-4.*

rpm -Uvh libgcc-4.*

rpm -Uvh libstdc++-4.*

rpm -Uvh libstdc++-devel-4.*

rpm -Uvh make-3.*

rpm -Uvh sysstat-7.*

rpm -Uvh unixODBC-2.*

rpm -Uvh unixODBC-devel-2.*

rpm -Uvh numactl-devel-*

6. Create the new groups and users.

[root@micr~#]groupadd oinstall                                                         -- Add new group oinstall
[root@micr~#]groupadd dba                                                             -- Add new group dba
[root@micr~#]groupadd oper                                                            -- Add new group oper
[root@micr~#]groupadd asmadmin                                                    -- Add new group asmadmin
[root@micr~#]useradd -g oinstall  -G dba,oper,asmadmin oracle        -- Creating user with group permition.
[root@micr~#]passwd oracle                                                             -- Set password for oracle user.

8.     Disable secure Linux

[root@micr~#]vi /etc/selinux/config
Making sure the SELINUX flag is set as follows.
SELINUX=disabled

9.    Disable Firewall Configuration

Open terminal and execute the following you can see Security Level Configuration Tool and disable firewall.
[root@micr~#]system-config-securitylevel

10.    Now reboot your system.
 [root@micr~#]reboot

11.     Now logon as root user and perform the following 

Create and grant permission the directories in which the Oracle software will be installed.
[root@micr~#]mkdir -p /u01/app/oracle/product/11.2.0/db_1             -- Creating directory.
[root@micr~#]chown -R oracle:oinstall /u01                                       -- Changing ownership
[root@micr~#]chmod -R 775 /u01                                                     -- Changing Mode

12.     Now logout from root and login as oracle user and set bash profile in the following way.

[oracle@micr~#]vi  .bash_profile
# Now set the following parameter.
ORACLE_HOSTNAME=micr.localdomain; export ORACLE_HOSTNAME  #( User Name = micr)
ORACLE_UNQNAME=micrdb; export ORACLE_UNQNAME             #(Database name = micrdb)
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID= micrdb; export ORACLE_SID                                        #(Database name = micrdb)
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

11.      Now extract database and go to runInstaller path and perform the following.

[N.B. – All file disks 1 and disk 2 combine with one folder (database).]
 Copy all file from Disk -2 and past in disk-1 in path =database/stage
Open terminal and execute the following.

[oracle@micr~#]./runInstaller

######################  End Of Oracle Database 11g R2 Installation ################### 

##############################  Setup Auto Start ###############################

12. Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
[oracle@micr~#]vi  /etc/oratab

micrdb:/u01/app/oracle/product/11.2.0/db_1:Y

13. Logon as root user

14. Create a new startup script
[oracle@micr~#]vi /etc/init.d/dbora

# Past The Lines

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/11.2.0/db_1      # Oracle Database Home Location
ORA_OWNER=oracle                                                   # Oracle Database User Name
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl stop dbconsole"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
;;
esac

15. Change the permission
[oracle@micr~#]chmod 750 /etc/init.d/dbora

16. Add it to chkconfig
[oracle@micr~#] chkconfig --level 345 dbora on

17. Start and Stop the service using,
[oracle@micr~#]/etc/init.d/dbora start
[oracle@micr~#]/etc/init.d/dbora stop

18. Now reboot Your Server You can see database auto started.

If You face any problem you can Feel free to contract with me. My email Id is:- rajiboracledev@yahoo.com
Thanks

############################ End of  Setup Auto Start ###############################