Search This Blog

Thursday, December 31, 2015

Running direct SQL query from Entity Framework


First we will create a test table in our database

CREATE TABLE [dbo].[Address](
                [Id] [int] IDENTITY(1,1) NOT NUL primary keyL,
                [AddressLine1] [varchar](100) NULL,
                [AddressLine2] [varchar](100) NULL,
                [City] [varchar](100) NULL,
                [ZipCode] [varchar](15) NULL,
                [Phone] [varchar](15) NULL,
                [Mobile] [varchar](15) NULL
)
Create a console application ,Add Ado.Net entity model for database in which we have just created test table,while selecting tables make sure Address table is selected.

In my case database was Playground & context class generated is  PlaygroundEntities.

Inside your main we will add our code that will do native query on top of context class using ExecuteStoreQuery

Here is my code inside main
static void Main(string[] args)
        {
            PlaygroundEntities db = new PlaygroundEntities();
            //var query = from m in db.Addresses where m.City == "kankavali" select m;
           
            object[] parameters = { "1"};
            ObjectResult<ShortAddress> ads = db.ExecuteStoreQuery<ShortAddress>("select Id,AddressLine1 from Address where id= {0}", parameters);
            foreach (ShortAddress i in ads)
            {
                Console.WriteLine(i.AddressLine1);
                Console.WriteLine();
            }
           
            Console.ReadKey();
        }
As in select I am taking only 2 columns and address entity has more column we need to have one more class of whose type our result is.

That’s why we will add a simple class as follows

class ShortAddress
    {
        public int Id { get; set; }
        public string AddressLine1 { get; set; }
    }
time to insert some test records in Address table. Now we are ready to run console application and check output

Saturday, December 19, 2015

Design Pattern - Lazy Singletone Service Locator Implementation

 Service locator design pattern is a pattern that is used to remove direct dependency of service client on service provider.

Main idea here is building a centralized repository of services then client will get required service from repository this way we will decouple service implementation & Service clients.

To demonstrate this pattern we will create a console application say (MyConsoleApp).
Then add a class (say MyEntity.cs).

Below is my code from MyEntity.cs.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace MyConsoleApp
{
    //helper class
    public class Utils
    {
        public static string RandomString(int maxSize)
        {
            char[] chars = new char[62];
            chars =
            "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890".ToCharArray();
            byte[] data = new byte[1];
            using (RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider())
            {
                crypto.GetNonZeroBytes(data);
                data = new byte[maxSize];
                crypto.GetNonZeroBytes(data);
            }
            StringBuilder result = new StringBuilder(maxSize);
            foreach (byte b in data)
            {
                result.Append(chars[b % (chars.Length)]);
            }
            return result.ToString();
        }
    }

    //interface
    public interface IAddIntService
    {
        int Add(int param1,int param2);
        string ServiceInstanceId
        {
            get;
            set;
        }
        DateTime InstatiateTime
        {
            get;
            set;
        }
    }

    public interface IAddLongService
    {
        long Add(long param1,long param2);
        string ServiceInstanceId
        {
            get;
            set;
        }
        DateTime InstatiateTime
        {
            get;
            set;
        }
    }

    public interface IAddDoubleService
    {
        double Add(double param1, double param2);
        string ServiceInstanceId
        {
            get;
            set;
        }
        DateTime InstatiateTime
        {
            get;
            set;
        }
    }

    //implementation
    class AddInt : IAddIntService
    {
        //constructor
        public AddInt()
        {
            this.ServiceInstanceId = Utils.RandomString(15);
            this.InstatiateTime = DateTime.Now;
        }

        public string ServiceInstanceId
        {
            get;
            set;
        }
        public DateTime InstatiateTime
        {
            get;
            set;
        }

        public int Add(int param1, int param2)
        {
            return Convert.ToInt32(param1 + param2);
        }
    }

    class AddLong : IAddLongService
    {
        public AddLong()
        {
            this.ServiceInstanceId = Utils.RandomString(15);
            this.InstatiateTime = DateTime.Now;
        }
        public DateTime InstatiateTime
        {
            get;
            set;
        }
        public long Add(long param1, long param2)
        {
            return Convert.ToInt64(param1 + param2);
        }
        public string ServiceInstanceId
        {
            get;
            set;
        }
    }
   
    class AddDouble : IAddDoubleService
    {
        public AddDouble()
        {
            this.ServiceInstanceId = Utils.RandomString(15);
            this.InstatiateTime = DateTime.Now;
        }
        public DateTime InstatiateTime
        {
            get;
            set;
        }
        public double Add(double param1, double param2)
        {
            return Convert.ToDouble(param1 + param2);
        }
        public string ServiceInstanceId
        {
            get;
            set;
        }
    }
}

Here I am creating three interfaces
1)  IAddIntService:It has method to implement
    int Add(int param1,int param2);
2)  IAddLongService:It has method to implement
    long Add(long param1,long param2);
3)  IAddDoubleService :It has method to implement
      double Add(double param1, double param2);
      Each interface has two additional properties
       a) InstatiateTime: Holds time at which object created
       b) ServiceInstanceId: Holds instance id i.e. random 15 digited string to differentiate instance created approximately at same time.

Interfaces above are implemented as follows
1)AddInt class implements IAddIntService
2)AddLong class implements IAddLongService
3)AddDouble class implements IAddDoubleService
 
At the time of instantiation of these three class objects in respective constructor I am initializing properties ServiceInstanceId & InstatiateTime.


Now Let’s add one more class file say “MyPattern.cs
Below is code in MyPattern.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace MyConsoleApp
{
    public interface IServiceLocator
    {
        T GetService<T>();

        DateTime InstatiateTime
        {
            get;
            set;
        }
        string ServiceInstanceId
        {
            get;
            set;
        }
    }

    //service instance eager loaded
    class ServiceLocator : IServiceLocator
    {
        //repository to maintain list of available services
        private IDictionary<object, object> services;

        //constructor
        internal ServiceLocator()
        {
            this.InstatiateTime = DateTime.Now;
            this.ServiceInstanceId = Utils.RandomString(15);
            services = new Dictionary<object, object>();

            //list of available services
            this.services.Add(typeof(IAddIntService), new AddInt());
            this.services.Add(typeof(IAddLongService), new AddLong());
            this.services.Add(typeof(IAddDoubleService), new AddDouble());
        }

        public T GetService<T>()
        {
            try
            {
                return (T)services[typeof(T)];
            }
            catch (KeyNotFoundException)
            {
                throw new ApplicationException("The requested service is not registered");
            }
        }

        //property implemented
        public DateTime InstatiateTime
        {
            get;
            set;
        }

        public string ServiceInstanceId
        {
            get;
            set;
        }
    }

    //service instance lazy loaded
    internal class LazyServiceLocator : IServiceLocator
    {
        //repository that keeps maps of service type & interface that it implements so that when required we can instantiate object of desired type
        private IDictionary<Type, Type> services;

        //repository that keeps maps of service type object instatiated & interface that it implements so when available no need to reinstantiate
        private IDictionary<Type, object> instantiatedServices;

        //constructor
        internal LazyServiceLocator()
        {
            this.InstatiateTime = DateTime.Now;
            this.ServiceInstanceId = Utils.RandomString(15);

            //initialize repositories
            this.services = new Dictionary<Type, Type>();
            this.instantiatedServices = new Dictionary<Type, object>();

            this.BuildServiceTypesMap();
        }

        private void BuildServiceTypesMap()
        {
            this.services.Add(typeof(IAddIntService), typeof(AddInt));
            this.services.Add(typeof(IAddLongService), typeof(AddLong));
            this.services.Add(typeof(IAddDoubleService), typeof(AddDouble));
        }

        public T GetService<T>()
        {
            if (this.instantiatedServices.ContainsKey(typeof(T)))
            {
                //found in earlier instantiated objects
                return (T)this.instantiatedServices[typeof(T)];
            }
            else
            {
                // lazy initialization
                try
                {
                    // use reflection to invoke the service
                    ConstructorInfo constructor = services[typeof(T)].GetConstructor(new Type[0]);

                    T service = (T)constructor.Invoke(null);

                    // add the service to the ones that we have already instantiated
                    instantiatedServices.Add(typeof(T), service);

                    return service;
                }
                catch (KeyNotFoundException)
                {
                    throw new ApplicationException("The requested service is not registered");
                }
            }
        }

        //property implemented
        public DateTime InstatiateTime
        {
            get;
            set;
        }

        public string ServiceInstanceId
        {
            get;
            set;
        }
    }

    //service locator SingleTon
    internal class LazySingleTonServiceLocator : IServiceLocator
    {
        //repository that keeps maps of service type & interface that it implements so that when required we can instantiate object of desired type
        private IDictionary<Type, Type> services;

        private static readonly object TheLock = new Object();

        //stores the instance of service locator that will be instantiated only once then reused
        private static IServiceLocator ServiceLocatorInstance;

        //repository that keeps maps of service type object instatiated & interface that it implements so when available no need to reinstantiate
        private readonly IDictionary<Type, object> instantiatedServices;

        //private constructor so service locator instance can not be created by others than GetServiceLocatorInstance method
        private LazySingleTonServiceLocator()
        {
            this.InstatiateTime = DateTime.Now;
            this.ServiceInstanceId = Utils.RandomString(15);

            //initialize repositories
            this.services = new Dictionary<Type, Type>();
            this.instantiatedServices = new Dictionary<Type, object>();

            this.BuildServiceTypesMap();
        }

        private void BuildServiceTypesMap()
        {
            this.services.Add(typeof(IAddIntService), typeof(AddInt));
            this.services.Add(typeof(IAddLongService), typeof(AddLong));
            this.services.Add(typeof(IAddDoubleService), typeof(AddDouble));
        }

        //static method to retreive service locator instance
        public static IServiceLocator GetServiceLocatorInstance
        {
            get
            {
                lock (TheLock) // thread safety
                {
                    if (ServiceLocatorInstance == null)
                    {
                        ServiceLocatorInstance = new ServiceLocator();//private constructor accessible from same class only
                    }
                }

                return ServiceLocatorInstance;
            }
        }

        //property implemented
        public DateTime InstatiateTime
        {
            get;
            set;
        }

        public string ServiceInstanceId
        {
            get;
            set;
        }

        public T GetService<T>()
        {
            if (this.instantiatedServices.ContainsKey(typeof(T)))
            {
                //found in earlier instantiated objects
                return (T)this.instantiatedServices[typeof(T)];
            }
            else
            {
                // lazy initialization
                try
                {
                    // use reflection to invoke the service
                    ConstructorInfo constructor = services[typeof(T)].GetConstructor(new Type[0]);

                    T service = (T)constructor.Invoke(null);

                    // add the service to the ones that we have already instantiated
                    instantiatedServices.Add(typeof(T), service);

                    return service;
                }
                catch (KeyNotFoundException)
                {
                    throw new ApplicationException("The requested service is not registered");
                }
            }
        }
    }
}

In this code I am creating IServiceLocator interface which has generic method

T GetService<T>();

Further I am adding to properties on the line of our entity interfaces.
1)  InstatiateTime
2)  ServiceInstanceId
That will save time at which instance of class implementing IServiceLocator created at and its instance Id.

There are three different implementation of interface IServiceLocator

1) ServiceLocator: Here constructor create repository of  available  services  method GetService provide available service. Drawback in this implementation is object of services created forehand even before somebody needs it.

2) LazyServiceLocator: Here also constructor create repository of available service type & their interface type. When First request for certain service come it creates the object of service and cache that object in on more repository which keeps map between service type & its object. Subsequent request to same service are serviced through cached object.This implementation takes care of drawback in earlier implementation i.e.  We are creating object of service whenever actual object is needed i.e. Lazy implementation.       
       
        Again what happens is if we end up with creating multiple instance of LazyServiceLocator class which but one instance is suffice to handle multiple call.

3) LazySingleTonServiceLocator: Here we improve our design further by making constructor of LazySingleTonServiceLocator private  & adding a public static method to get required instance

public static IServiceLocator GetServiceLocatorInstance

Inside this method calling private constructor two create instance of LazySingleTonServiceLocator and caching it in a static variable, on subsequent request we will not create a new instance of LazySingleTonServiceLocator object  but provide one that is already created.Locking is used so that multiple thread do not enter in object instantiation logic and creating multiple instance of LazySingleTonServiceLocator defeating purpose of making it singleton.

Inside program.cs add following code ,it demonstrate use of Service Locator classes

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

namespace MyConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            #region NonLazyLocator
            Console.WriteLine(" ############### Simple Service Locator ###############");

            IServiceLocator Locator = new ServiceLocator();
            Console.WriteLine("\n Simple Service Locator Object created \n\n Instance Id:{0}:{0} \n Instatiated At {1}", Locator.ServiceInstanceId, Locator.InstatiateTime.ToLongTimeString());

            //sleep
            Console.WriteLine("\n Sleeping for moments for 20 secs...\n");
            System.Threading.Thread.Sleep(20000);

            //asking for service to add int
            //int
            Console.WriteLine("\n Looking for compatible service to add int...\n");
            IAddIntService LocatedService = Locator.GetService<IAddIntService>();
            int intOp = LocatedService.Add(25, 57);
            Console.WriteLine(" Service Instance to add int found...\n");
            Console.WriteLine(" Service Instance Id:{0}:{0} \n Instatiated At {1}", LocatedService.ServiceInstanceId, LocatedService.InstatiateTime.ToLongTimeString());

            //long
            Console.WriteLine("\n Looking for compatible service to add long...\n");
            IAddLongService LocatedServiceLong = Locator.GetService<IAddLongService>();
            long intOpLong = LocatedServiceLong.Add(25, 57);
            Console.WriteLine(" Service Instance to add long found...\n");
            Console.WriteLine(" Service Instance Id:{0}:{0} \n Instatiated At {1}", LocatedServiceLong.ServiceInstanceId, LocatedServiceLong.InstatiateTime.ToLongTimeString());

            //int again
            Console.WriteLine("\n Looking for compatible service to add int again...\n");
            IAddIntService LocatedServiceAgain = Locator.GetService<IAddIntService>();
            int intOpAgain = LocatedServiceAgain.Add(56, 35);
            Console.WriteLine(" Service Instance to add int found...\n");
            Console.WriteLine(" Service Instance Id:{0}:{0} \n Instatiated At {1}", LocatedServiceAgain.ServiceInstanceId, LocatedServiceAgain.InstatiateTime.ToLongTimeString());

            Console.WriteLine("\n Conclusion:Service Locator & Service Instances created at same time\n");
            Console.WriteLine(" ####################################################\n\n\n");
            #endregion

            #region LazyLocator
            Console.WriteLine("\n ############### Lazy Service Locator ###############");

            IServiceLocator LazyLocator = new LazyServiceLocator();
            Console.WriteLine("\n Lazy Service Locator Object created \n\n Instance Id:{0} \n Instatiated At :{1}\n", LazyLocator.ServiceInstanceId, LazyLocator.InstatiateTime.ToLongTimeString());

            //sleep
            Console.WriteLine("\n Sleeping for moments for 20 secs...\n");
            System.Threading.Thread.Sleep(20000);

            //asking for service to add int
            IAddIntService LocatedLazyIntService = LazyLocator.GetService<IAddIntService>();
            int intOpLazy = LocatedLazyIntService.Add(25, 57);
            Console.WriteLine("\n Looking for compatible service to add int ...\n");
            Console.WriteLine(" Service Instance to add int found...\n");
            Console.WriteLine(" Service Instance Id:{0} \n Instatiated At: {1}\n", LocatedLazyIntService.ServiceInstanceId, LocatedLazyIntService.InstatiateTime.ToLongTimeString());

            //asking for service to add long
            IAddLongService LocatedLazyLongService = LazyLocator.GetService<IAddLongService>();
            long longOpLazy = LocatedLazyLongService.Add(255, 457);
            Console.WriteLine("\n Looking for compatible service to long int ...\n");
            Console.WriteLine(" Service Instance to add long found...\n");
            Console.WriteLine(" Service Instance Id:{0} \n Instatiated At: {1}\n", LocatedLazyLongService.ServiceInstanceId, LocatedLazyLongService.InstatiateTime.ToLongTimeString());

            //asking again for service to add int again
            IAddIntService LocatedLazyIntServiceAgain = LazyLocator.GetService<IAddIntService>();
            int intOpLazyAgain = LocatedLazyIntServiceAgain.Add(253, 567);
            Console.WriteLine("\n Looking for compatible service to add int again...\n");
            Console.WriteLine(" Service Instance to add int found...\n");
            Console.WriteLine(" Service Instance Id:{0} \n Instatiated At: {1}\n", LocatedLazyIntServiceAgain.ServiceInstanceId, LocatedLazyIntServiceAgain.InstatiateTime.ToLongTimeString());

            Console.WriteLine("\n Conclusion:Service Locator & Service Instances created at different time (lazy loading...)\n");
            Console.WriteLine(" ####################################################");
            #endregion

            #region LazySingleTonLocator
            Console.WriteLine("\n\n\n ############### Lazy SingleTon Service Locator ###############");

            //pulling service locator object
            IServiceLocator LazySingleTonLocator = LazySingleTonServiceLocator.GetServiceLocatorInstance;
            Console.WriteLine("\n Lazy Singletome Service Locator Object created (once as SingleTon) \n\n Instance Id:{0} \n Instatiated At :{1}\n", LazySingleTonLocator.ServiceInstanceId, LazySingleTonLocator.InstatiateTime.ToLongTimeString());

   IAddIntService LocatedLazySingleTonIntService = LazySingleTonLocator.GetService<IAddIntService>();
            int intOpLazySingleTon = LocatedLazySingleTonIntService.Add(25, 57);

            //sleep
            Console.WriteLine("\n Sleeping for moments for 20 secs...\n");
            System.Threading.Thread.Sleep(20000);

            //again pulling Service Locator object
            IServiceLocator LazySingleTonLocatorAgain = LazySingleTonServiceLocator.GetServiceLocatorInstance;
            Console.WriteLine("\n Lazy Singletome Service Locator pulled again \n\n Instance Id:{0} \n Instatiated At :{1}\n", LazySingleTonLocator.ServiceInstanceId, LazySingleTonLocator.InstatiateTime.ToLongTimeString());

            Console.WriteLine("\n Conclusion:Lazy Singletome Service Locator instance is not created multiple time (SingleTon)");
            Console.WriteLine(" ####################################################");
            #endregion

            Console.ReadKey();
        }
    }
}


How to call our 1st Implementation?
 
IServiceLocator Locator = new ServiceLocator();
IAddIntService LocatedService = Locator.GetService<IAddIntService>();
int intOp = LocatedService.Add(25, 57);

How to call our 2nd Implementation?
 
IServiceLocator LazyLocator = new LazyServiceLocator();
IAddIntService LocatedLazyIntService = LazyLocator.GetService<IAddIntService>();
 int intOpLazy = LocatedLazyIntService.Add(25, 57);

How to call our 3rd  Implementation?
 
IServiceLocator LazySingleTonLocator = LazySingleTonServiceLocator.GetServiceLocatorInstance;
IAddIntService LocatedLazySingleTonIntService = LazySingleTonLocator.GetService<IAddIntService>();
           
int intOpLazySingleTon = LocatedLazySingleTonIntService.Add(25, 57);

Compile you console application and run, Here is my output
 
Output:
 
############### Simple Service Locator ###############
 Simple Service Locator Object created
 Instance Id:U1JGBWKB4deh5fj:U1JGBWKB4deh5fj
 Instatiated At 2:36:22 PM
 Sleeping for moments for 20 secs...
 Looking for compatible service to add int...
 Service Instance to add int found...
 Service Instance Id:Jr0gzKODGNs4K0n:Jr0gzKODGNs4K0n
 Instatiated At 2:36:22 PM
 Looking for compatible service to add long...
 Service Instance to add long found...
 Service Instance Id:d37g9tIkCQcmZvG:d37g9tIkCQcmZvG
 Instatiated At 2:36:22 PM
 Looking for compatible service to add int again...
 Service Instance to add int found...
 Service Instance Id:Jr0gzKODGNs4K0n:Jr0gzKODGNs4K0n
 Instatiated At 2:36:22 PM
 Conclusion:Service Locator & Service Instances created at same time #####################################################################
############### Lazy Service Locator ###############################
Lazy Service Locator Object created
Instance Id:QwADmquGl9p4JBm
Instatiated At :2:36:42 PM
Sleeping for moments for 20 secs...
Looking for compatible service to add int ...
Service Instance to add int found...
Service Instance Id:oI7FJx3mZWmCdAc
 Instatiated At: 2:37:02 PM
 Looking for compatible service to long int ...
Service Instance to add long found...
 Service Instance Id:UUz7Qkv7XjmvMOb
 Instatiated At: 2:37:02 PM
 Looking for compatible service to add int again...
 Service Instance to add int found...
 Service Instance Id:oI7FJx3mZWmCdAc
 Instatiated At: 2:37:02 PM
Conclusion:Service Locator & Service Instances created at different time (lazy loading...)
 ####################################################################
 ############### Lazy SingleTon Service Locator ####################
Lazy Singletome Service Locator Object created (once as SingleTon)
Instance Id:5pPqsOZB3NReL44
Instatiated At :2:37:02 PM
 Sleeping for moments for 20 secs...
 Lazy Singletome Service Locator pulled again
 Instance Id:5pPqsOZB3NReL44
 Instatiated At :2:37:02 PM
 Conclusion:Lazy Singletome Service Locator instance is not created multiple time (SingleTon)
 ####################################################
In console I am printing instance time & Instance Id of service locator object & service object whenever required to confirm that service instance are created lazily & service locator is not creating multiple time enforcing SingleTon implementation.


My code is adaptation of Stephan (see reference) but with service class that I can connect more easily.


References:

http://stefanoricciardi.com/2009/09/25/service-locator-pattern-in-csharpa-simple-example/

Thursday, December 10, 2015

Installing dbeaver application Debian 8



Dbeaver is Free multi-platform database tool.It supports  most of the popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MongoDB,

We will first Download debian package dbeaver-ce_3.5.5_amd64.deb from


After completing download open with package installer complete the installation.Launch dbeaver create a new connection, for oracle.oracle jdbc driver you need to download first manually then need to add it to library.

  Now you will be able to create oracle connection object but testing connection if give timezone region not found error then as we have done for SQL Developer we have to pass certain driver properties.Can be seen in connection setting of current connection.

To pass this timezone to application we have to pass parameter at the time of running it so from bash shell we can run it as

dbeaver -vmargs -Duser.timezone=GMT+IST

Now if we saw connection succeeds but each time we have to run app with this additional parameter here parameter value  is specific to India should be replaced by relevant to your installation.

To  run this app with required parameter we will create a shell script as follows

Script

#!/bin/bash
/usr/share/dbeaver/dbeaver -vmargs -Duser.timezone=5.30 &>/dev/null &


saving this in mydbeaver.sh copy to /usr/local/bin

Now we will give this script suitable file permission

chmod u+x /usr/local/bin/mydbever.sh

issue mydbever.sh from command line, your application will able to connect to oracle without hassles of passing parameters each time.

In bash file instead of IST, GMT+5.30 is added as postgres connection somehow can't work with IST paramter value.

Dbeaver is nice application that has capability to connect to multiple databases. we are able to connect ORACLE,MySQL & Postgres simultenously in Dbeaver.

References:
http://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

http://dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=596

Saturday, December 5, 2015

Installing Oracle 10 g XE on Debian 8

Adding repository to /etc/apt/sources.list using nano
deb http://oss.oracle.com/debian unstable main non-free
Get Signature for repository
wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
Update Package List
apt-get update
Install Oracle 10
apt-get install oracle-xe
When i was installing i got error regarding package libwxbase3.0 then i installed lower version of it libwxbase2.8-0 then re issued
apt-get install oracle-xe
Install Oracle-Xe Client
apt-get install oracle-xe-client
Check if port in use
lsof -i :8080
Configure Oracle-xe to setup password for SYS & SYSTEM
root@mymachine:/home/myuser# sudo /etc/init.d/oracle-xe configure
Output
Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press <Enter> to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"
root@mymachine:/home/myuser#
Where to find tnsnames.ora & listener.ora ?
1) tnsnames.ora
/usr/lib/oracle/xe/app/oracle/product/10.2.0/
server/network/admin/tnsnames.ora
2)listener.ora
/usr/lib/oracle/xe/app/oracle/product/10.2.0/
server/network/admin/listener.ora
How to Start Oracle Service?
start
/etc/init.d/oracle-xe start
stop
/etc/init.d/oracle-xe stop
restart
/etc/init.d/oracle-xe restart
If While starting Sqlplus have error
For/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found
as root
gedit /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh
Scroll down until you find this:
if [[ -n "$LC_ALL" ]]; then
locale=$LC_ALL
elif [[ -n "$LANG" ]]; then
locale=$LANG
else
locale=
fi
Then change it to this (remove a pair of brackets):
if [ -n "$LC_ALL" ]; then
locale=$LC_ALL
elif [ -n "$LANG" ]; then
locale=$LANG
else
locale=
fi
secondly Change #!/bin/sh to #!/bin/bash in nls_lang.sh
Then click SAVE.
Setting Enviornment variables
open bashhrc file as super user
>gedit ~/.bashrc
add enviornment variables as follows at the bottom of the file opened & save.
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=XE
export PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib
Check Enviornment variables
echo the variable are see if desired values saved or not.On bash prompt run each command below one by one
echo $ORACLE_HOME
echo $ORACLE_SID
echo $PATH
check if listener is running
lsnrctl status
output
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-DEC-2015 13:28:37
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-DEC-2015 09:55:38
Uptime 0 days 3 hr. 32 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.localhost.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
To Start Listener
lsnrctl start
To stop listener
lsnrctl stop
To restart listener
lsnrctl reload
set password to oracle account
sudo passwd oracle
Using tnsping to check listner
tnsping localhost :1521/XE
output
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 05-DEC-2015 15:45:55
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)
Running Oracle
Login as Oracle in shell prompt then issue below command
sqlplus sys as sysdba
will ask for password give password set in re configuring.
Or
sqlplus SYS/sangram@localhost:1521/XE as SYSDBA
Creating test table
SQL> create table MyTable(id int,name varchar2(50));
Table created.
SQL> insert into MyTable Values(1,'sangram');
SQL> insert into MyTable values(2,'sagar');
SQL> select * from MyTable;
Output
ID NAME
---------- --------------------------------------------------
1 sangram
2 sagar
How to install Oracle Developer tool on Debian 
First install sqldeveloper-package on debian using APT.This package is required for converting sqldeveloper-*-no-jre.zip file that is available on oracle site.
Download sqldeveloper-*-no-jre.zip file from oracle
We will create deb package from this zip file.Make sure latest JDK already installed
then try
make-sqldeveloper-package ~/Downloads/sqldeveloper-*-no-jre.zip
it gives some warning but you get required deb package.Run the package and install.
After installing confirm listener.ora has XE as service see following text in file
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
)
check XE service is running using lsnrctl status command
create a new user in oracle give him sysdba previlages.
Open Sql Developer try to create new connection with newly created user use BASIC connection TYPE while connecting if you got timezone related error check Sql developer's Help->About then in property tab user.timezone if empty we need to set it in sql developer's config file here
/usr/share/sqldeveloper/ide/bin/ide.conf add (for india)
add below line into ide.conf.
AddVMOption -Duser.timezone=GMT+5.30
Restart machine and recheck SQL Developer is connecting to database.
Timezone can be assigned as IST also for list of possible values of timezone check table V$TIMEZONE_NAMES.

SELECT * FROM V$TIMEZONE_NAMES WHERE tzname LIKE 'Asia/Calcutta'

Troubleshooting
Here are some Queries Usefull for troubleshoting
Getting Global Name
select * from global_name;
Get Current USER
select user from dual;
GET LIST OF DATABASES
select name from v$database;
GET CURRENT INSTANCE NAME
select instance from v$thread;