RSS

Category Archives: Database

Copy rows in the same table with modified data in Oracle

To copy data, modify column values and insert it into the same table we’ll have to modify the primary key and unique columns. Here are 3 different approaches:

The 1st approach:

BEGIN

  FOR r IN (SELECT * FROM MyTable WHERE Column2 = 'Value2') 
  LOOP
    r.Table_ID := PK_Seq.nextval;
    r.Column2  := 'NewValue2';
    r.Column3  := 'NewValue3';
    INSERT INTO MyTable VALUES r;
  END LOOP;

END;

Read the rest of this entry »

Advertisements
 
Leave a comment

Posted by on August 8, 2012 in Database, Oracle, SQL

 

Accessing Oracle database without installing Oracle Client

Background:
A legacy client-server database accessing .NET Windows Form application deployed by ClickOnce. In order to communicate with the database, Oracle Client 8i is installed on every client machine. Clearly installation jobs are disturbing. Another problem is distributed transaction not being supported until Oracle Client 9i. It should be better to implement a WCF service to centralize data access but the application is already too big to reshape.

Solution:
What I’m trying to achieve here is to enable distributed transaction with newer version Oracle Client without the need to install it on each client PC and still be able to dispatch the application by ClickOnce. It took me a long way to figure this out and make it work. Here are the steps, the files downloaded are for 32-bit application:
Read the rest of this entry »

 
10 Comments

Posted by on August 1, 2012 in .NET, Database, Oracle

 

Tags: , ,

Select the first row of each group

This example demonstrates how to select the first records grouping by Salesman and Store.

Sales:

ID Store Salesman Amount
1 A Lin 120.00
2 A Lin 80.50
3 A Fields 200.00
4 B Lin 160.50
5 B Lin 70.00
6 A Fields 130.00
7 B Fields 180.00
8 A Fields 110.50

The result should be like this:

Result:

ID Store Salesman Amount
3 A Fields 200.00
7 B Fields 180.00
1 A Lin 120.00
4 B Lin 160.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

ORA-01790 when Union Null and Date

In Oracle 8i or earlierUnion Null and Date Type column will cause ORA-01790: expression must have same datatype as corresponding expression.

-- This query will fail
SELECT PayDate FROM Table1
UNION
SELECT '' FROM Table2

The solution is using To_Date(NULL)instead of Null.

SELECT PayDate FROM Table1
UNION
SELECT To_Date(NULL) FROM Table2

Reference:NULL in union causing ORA-01790

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

Query using Oracle Parameter returns no result

I had a parameterized query with some string variables binding to the OracleParameters. The query works with certain parameters but doesn’t with others. Take a simplified database table with only 2 columns for example:

SampleTable:

Name Type
Currency Char(3)
SerialNo Char(8)

My original query looks something like this. The first query works. The second runs normally without error message but returns no result when there are records matched in the database.

Dim currency As string = "USD"
Dim serialNo As string = "1311B"

'Quering with cmd1 works
Dim cmd1 As New OracleCommand("SELECT * FROM SampleTable WHERE Currency = :Currency")
cmd1.Parameters.Add(New OracleParameter("Currency", currency))

'Quering with cmd2 works but no record is returned when there are matching records in the database
Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = :SerialNo")
cmd2.Parameters.Add(New OracleParameter("SerialNo", serialNo))

Read the rest of this entry »

 
Leave a comment

Posted by on July 23, 2012 in .NET, ADO.NET, Database, Oracle, SQL

 

Select values calculated between rows

The example here uses 2 different approaches one using JOIN and another using LAG analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.

Sales:

ID Store SaleMonth Amount
1 A Jan 120.00
2 A Feb 80.50
3 A Feb 200.00
4 B Jan 160.50
5 B Feb 70.00
6 A Feb 130.00
7 B Mar 180.00
8 A Mar 110.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 2, 2012 in Database, Oracle, SQL

 

Using Oracle SQL LIKE operator in parameterized OracleCommand

The SQL LIKE operator can be used in the CommandText with Oracle parameters. The syntax of comparing a OracleParameter using SQL LIKE operator is as follows:

Dim cmd As New OracleCommand
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE :param || '%'"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param || '%'"

Reference:Usage of Oracle binding variables with LIKE in C#

 
Leave a comment

Posted by on June 29, 2012 in .NET, ADO.NET, Oracle, SQL, VB