Thursday, June 14, 2007

Cast and Convert a column in dataview

For example you have an string column in dataview.
But the actual data in that column in numeric.
So you can not make calculations.
And you need to change the datatype of that column to int,decimal,etc...

There are several ways for converting a column datatype in a dataview.
The easiest way that I found:

DataTable dt = GetData_Bla_Bla();
DataView dv = dt.DefaultView;
dv.RowFilter = "CONVERT(COLUMN_NAME,'System.Decimal')>100";


I hope it works for you.

Tuesday, June 12, 2007

Oracle Database Express(XE) Edition

Oracle XE is very similar to SQL Server Desktop edition.
Oracle finally made a product like SQL Server Desktop Edition for simple installation and running on a single machine. It can be installed on Windows XP.
Developers who want to use Oracle can setup their machines and work on it independently.

But of course there are some limitations.And I think they don't prevent development.
  • Can host only one instance on installed pc.
  • Max. db size is 4GB.
  • Can run with only one processor(but can be installed on a multiple CPU machine).
  • Can be installed on a server with any amount of memory, but will use only up to 1 GB of available RAM memory.

It is fairly easy to setup.It can be downloaded from Oracle site.Its size is very small. 157 MB for Windows.

You can download Oracle 10g Express(XE) Edition from this link: http://www.oracle.com/technology/software/products/database/xe/index.html

You may check Oracle Express Edition Installation Guide from:

http://download-uk.oracle.com/docs/cd/B25329_01/doc/install.102/b25143/toc.htm

After installation you should make some settings.

Add this line to tns.ora file in C:\oracle\product\11.1.0\client_1\Network\Admin\tnsnames.ora

If file doesn't exist create first.


XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = XE)
(SERVICE_NAME = XE)
)
)

You may check this article for Oracle XE Setup.

http://www.oraclekonsulent.dk/en/index.php?blog_id=13

Good Luck!!!

How to Create Auto Increment Column in Oracle

Oracle hasn't got a Auto Increment feature by default as SQL Server.
But there are several ways to do this.
I'll mention trigger way.

  • First you define a number column in your table.
  • Then create a sequence.
  • And create a "before trigger" using sequence on table.

  1. You have a table like this:
    CREATE TABLE SERVICES
    (
    SERVICEID NUMBER(10) NOT NULL,
    SERVICENAME VARCHAR2(20 BYTE) NOT NULL
    )
  2. Create a sequence:
    create sequence SERVICES_seq
    start with 1
    increment by 1
    nomaxvalue;
  3. Create a before insert trigger:
    CREATE trigger tr_SERVICES
    before insert on SERVICES
    for each row
    begin
    select SERVICES_seq.nextval into :new.SERVICEID from dual;
    end;
    /

That's it!

Oracle Date Column default value

If you want to set current date for a column in Oracle It is simple.
Just set it to sysdate

Create Table Table_Name
(
Column_Name DATE DEFAULT sysdate
)