Row Locks When Inserting - Oracle

My co-worker told me his application hanging while inserting a row into a table. By looking at the Oracle Enterprise Manager, I noticed it's caused by foreign key. Here I show an example.

Open a SQL Plus, create the tables and foreign key.

    create table tmp_test 
    ( id         number(5) not null, 
      test_type  char(1), 
      comments varchar2(100),
      constraint pk_tmp_test primary key (id) 
    );

    create table tmp_test_type 
    ( test_type char(1), 
      description  varchar2(100),
      constraint pk_tmp_test_type primary key (test_type) );

    alter table tmp_test add constraint fk1_tmp_test foreign key ( test_type )
      references tmp_test_type;

Now try to insert a row without referring type. Expecting an error:

    insert into tmp_test values( 1, 'A', 'Just a test' );

    ORA-02291: integrity constraint (EAS_DEMO.FK1_TMP_TEST) violated - parent key not found

Open another session using SQL Plus, insert a row in test_test_type table but don't commit:

    insert into tmp_test_type values( 'A', 'Test type A' );

Run the insert again using the first SQL Plus, you'll find it hangs. In Oracle Enterprise Manager, you can see "enq: TX - row lock contention".

Commit the changes and cleanup:

    drop table tmp_test;
    drop table tmp_test_type;

Delete Duplicate Rows

It's easy to delete duplicate rows in Oracle using cursor and rownum. Here shows an example.

Create table and insert some duplicate rows.

CREATE TABLE DUP_TEST
(
COMPANY_ID VARCHAR(8),
COMPANY_NAME VARCHAR(80),
ADDRESS VARCHAR(80)
);

-- Create test data, dup by company id
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address2');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address');
INSERT INTO DUP_TEST VALUES ('3', 'Company Three', 'Address3');

The duplicates are with company id 1 and 2. Let delete the duplicates.

set serveroutput on
declare
  rows_deleted integer := 0;
begin
  for cur in ( select company_id, count(*) cnt from DUP_TEST
               group by company_id having count(*) > 1 )
  loop
    delete from DUP_TEST
    where company_id = cur.company_id and rownum < cur.cnt;
    rows_deleted := rows_deleted + SQL%ROWCOUNT;
  end loop;
  dbms_output.put_line( 'records deleted: '  rows_deleted );
end;
/

Now show the result after deleting.

SQL> select * from dup_test;
COMPANY_ COMPANY_NAME ADDRESS
-------- -------------------- --------------------
1 Company One Address1
2 Company Two Address
3 Company Three Address3

It's a little bit diffcult to delete duplicate rows in SQL Server. I'll show one solution using a cursor. Consider the performance when you have large amount of data to search or delete.

declare cur cursor for select COMPANY_ID from DUP_TEST
declare @CompanyId int

open cur
fetch cur into @CompanyId

while @@fetch_status = 0
begin
if ( select count(*) from DUP_TEST where COMPANY_ID = @CompanyId ) > 1
begin
delete from DUP_TEST where current of cur
end
fetch cur into @CompanyId
end
close cur
deallocate cur
go

It goes through the full table and search duplicates for each row, if found, delete the current of cursor.

Ref: moving from Sybase to SQL

This is a good article discussing the difference between Sybase and SQL Server.

SQL Server - Append Query Result to .csv file

While searching on the web, I found a lot talking about appending SQL Server data to Excel files. But not many about appending data to .csv file. After some research, I got one.

First, create the csv file and prepare the column header:

master..xp_cmdshell 'echo EmpID,LastName,FirstName > C:\temp\Employees.csv'

It creates a file c:\temp\Employees.csv with 3 columns.

Then, append the data using OPENROWSET:

INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp',
[Employees#csv])
select EmployeeID, LastName, FirstName from NOrthwind..Employees
(9 row(s) affected)

Open the file c:\temp\Employees.csv, you'll see there are 9 rows appended.
Some points here are: 1) Database=C:\temp (need to put the folder here only), and 2) [Employees#csv] (need to replace . with # as the table name.

OK, it's similar to read the file back:

select * from OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp', [Employees#csv])

1 Davolio Nancy
...
9 Dodsworth Anne

At the end, I'll show another way to read the data in csv file:

select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp;',
'select top 3 * from "Employees.csv"')

It seems you cannot append to csv using MSDASQL.

Drop Linked Server in SQL Server

You have learned how to create linked server in SQL Server in Accessing Oracle From SQL Server. Sometimes you may want to drop it. I'll show you how.

First find and drop the logins associated with the linked server.
user master
go

select s.srvid, s.srvname, l.name login_name_associated
from dbo.sysxlogins x, dbo.syslogins l, dbo.sysservers s
where l.sid = x.sid and s.srvid = x.srvid and s.srvname = 'ora_test'
go
srvid srvname login_name_associated
----- --------- ------------------------
1 ora_test sa
1 ora_test analyst

Now sa and analyst need to be dropped from the linked server.

sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'analyst'
go
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'sa'
go

Then the linked server is ready to be dropped.

sp_dropserver @server = 'ora_test'
go

The logins have to be dropped from linked server first, otherwise an error message will show:

There are still remote logins for the server 'ora_test'.

Oracle Flashback Query Using AS OF TIMESTAMP

Since Oracle 9i Release 2, you can use "AS OF SCN | TIMESTAMP" flashback query to query the "before" data of a changed table. It's useful to find the changes you made or recover errors.
I used TIMESTAMP more often and found it simple but efficient. I'll show some examples below.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

SQL> update employees set salary = salary * 1.05 where employee_id = 100;
1 row updated.

SQL> commit;

SQL> select salary from employees where employee_id = 100;
SALARY
----------
25200

Here shows the way to query the "before" data of yesterday:

SQL> select salary from employees AS OF TIMESTAMP sysdate - 1 where employee_id = 100;
SALARY
----------
24000

To show ten (10) minutes ago:

select salary from employees AS OF TIMESTAMP sysdate - 10/60/24 where employee_id = 100;

Or you can use function SYSTIMESTAMP instead of sysdate.

select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) where employee_id = 100;
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) where employee_id = 100;

To recover the change:

SQL>update employees e1 set salary =
( select salary from employees AS OF TIMESTAMP sysdate - 1 e2
where e2.employee_id = e1.employee_id )
where employee_id = 100;
1 row updated.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

You may sometimes want to save the before data to a temporary table, then compare and reccover.

create table tmp_employees nologging as select * from employees AS OF TIMESTAMP sysdate - 1;

The SELECT ... AS OF will not work after the table structure changes or table truncation.

SQL> truncate table tmp_objects;
Table truncated.

SQL> select count(*) from tmp_objects as of timestamp sysdate - 1/24/60;

ORA-01466: unable to read data - table definition has changed

Accessing Oracle From SQL Server

SQL Server supports linked server to execute SQL commands against another database or data file. With the help of Oracle SQL*Net, connection from SQL Server to Oracle is fairly easy (comparing to Oracle to SQL Server using Oracle Generic Connectivity -- I'll discuss it later).

Below shows the steps. The target Oracle database is named TEST and with sample schema HR, password HR.

1. You need to install Oracle SQL*Net on the SQL Server box. The simple way is to install the Oracle client.

2. Use Oracle Net Configuration Assistant to configure a SQL*Net local service name. Or you can simply add an entry into file $ORACLE_HOME/network/admin/tnsname.ora:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE_SID)
)
)

Replace ORACLE_HOST and ORACLE_SID with your oracle server name and SID or service name.

3. Add linked server in SQL Server. Login as sa:

p_addlinkedserver @server = 'ora_test'
, @srvproduct = 'Oracle'
, @provider = 'MSDAORA'
, @datasrc = 'test'

Where ora_test is the linked server you're going to created (or you can name it whatever you want), test is the local service name of SQL*Net. Keep product and provider Oracle and MSDAORA respectively.

4. Map login on SQL Server, this will give the login permission to access the linked Oracle server.

sp_addlinkedsrvlogin @rmtsrvname = 'ora_test'
, @useself = false
, @locallogin = 'analyst'
, @rmtuser = 'hr'
, @rmtpassword = 'hr'

The example above gives analyst (a SQL Server login) permission to access hr schema in Oracle.

5. Test

select * from ora_test..HR.EMPLOYEES

You'll see:

100 Steven King SKING 515.123.4567 1987-06-17 00:00:00.000 AD_PRES 24000.00 NULL NULL 90
...

The object names in Oracle are in upper case. So you'll need to spell the schema and object names in upper case unless they're defined in lower or mixed case. In the example above, if you want select * from ora_test..HR.employees, you'll be informed that such table does not exist. Because SQL Server will translate the SQL to: select * from ora_test..HR."employees".

Trailing spaces in VARCHAR

I just noticed that many people are misunderstanding the trailing spaces in VARCHAR field. The truth is they're not trimmed automatically. At least in the databases I know, Oracle, Sybase, SQL Server, DB2, MySQL, etc.

Many know that when you save "abc" into char(5), two trailing spaces will be padded. And when you save "abc" into varchar(5), no space will be added.

How about save "abc " into varchar(5)? Well, the database will keep the trailing space. This sounds reasonable. Many make mistakes when copying data from char field to varchar field or changing the char type to varchar type. Remember, you'll need to trim, at least right trim them.

Multiply records

In one of my task, I needed to multiply some records in a table, so that they can have more chances to be picked up in a random draw. I did it in SQL Server, so I'll show it in SQL. It's similar in Oracle.
What I need to do is to Cartesian join the target records with a table with a certain number of records.

Create a test table and insert some records.

create table test_multiply( id int, type varchar(10), comments varchar(100) )
go
insert into test_multiply values ( 1, 'single', 'This row will show only once' )
insert into test_multiply values ( 2, 'times 10', 'This row will repeat 10 times' )
go

Now we're to multiply the second record 10 times, we join the records with a table contains 9 records.

insert into test_multiply
select a.* from test_multiply a
inner join ( select top 9 id from sysobjects ) x on 1=1
where a.type = 'times 10'
go
(9 rows affected)

Check the result.

select id, type, count(*) cnt from test_multiply group by id, type
go
id type cnt
----------- ---------- -----------
1 single 1
2 times 10 10

Similar in Oracle.

insert into test_multiply
select a.* from test_multiply a
inner join ( select 1 from all_objects where rownum < 1="1" type =" 'times">

PowerMultiSet in Oracle 10g

In one of my task, I needed to find all the possible combinations of a few strings. By searching the functions in Oracle, I found an interesting and powerful function: PowerMultiSet.

Oracle says the function returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table.

The submultisets are just the combinations of all elements (you're guaranteed :-) ) and will have 2ⁿ-1 sets.

Let see with an example:

create or replace type NUM_LIST_TYPE is table of number;
/
create or replace type NUM_LIST_LIST_TYPE is table of NUM_LIST_TYPE;
/

select NUM_LIST_TYPE( 1, 2, 3 ) base_set from dual;

BASE_SET
-----------------------
NUM_LIST_TYPE(1, 2, 3)

It shows three elements in the set: 1, 2 and 3. The run PowerMultiSet.

select cast( powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) as NUM_LIST_LIST_TYPE ) power_set from dual;

POWER_SET
--------------------------------------------------------------------------------
NUM_LIST_LIST_TYPE(NUM_LIST_TYPE(1), NUM_LIST_TYPE(2), NUM_LIST_TYPE(1, 2), NUM_
LIST_TYPE(3), NUM_LIST_TYPE(1, 3), NUM_LIST_TYPE(2, 3), NUM_LIST_TYPE(1, 2, 3))

Let's make it easy to read:

1
2
1 2
3
1 3
2 3
1 2 3

However, you have to create the type that is nested table of nested tables and cast the result into it. Otherwise, you'll get an error.

select powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) power_set from dual;

ORA-22833: Must cast a transient type to a persistent type

The number of elements in the input nested table cannot exceed 32. The number of submulties in such case would be 2³² -1, that's 4 gig.

String aggregate in Oracle

You may have known aggregate functions well, for example, sum, count, max, min, etc. How about add up strings? After searching on the web for a while, I found a function (See the code at the bottom).
The main function is STRING_AGG, which you can use the same way as other aggregate functions. Example:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;

Here's the output in SQL Plus:

      EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT,AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP,SA_MAN
          200 AD_ASST,AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The delimiter by default is comma (,), and is controlled by a store package STRING_AGG_CONTROL. To change it, you can use:

     begin string_agg_control.g_delim := '|'; end;
   /

Then run the sql again:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;
  EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT|AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP|SA_MAN
          200 AD_ASST|AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The function is implemented using an Oracle user defined type with the help of User-Defined Aggregates Interface. You can find more information in this doc.
Sounds perfect? Not that much -- you cannot order the strings. That means the source strings appear in the aggregate string randomly.
The script is attached:

create or replace package string_agg_control IS
  g_delim  varchar2(100) := ',';
end;
/

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
  g_delim   VARCHAR2(100),  -- Delimiter to seperate the strings

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg( NULL, string_agg_control.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || self.g_delim || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, self.g_delim ), self.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || self.g_delim || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Testing cursor variable in SQL Plus

In Oracle stored functions and procedures, you may often return or pass out a result set using cursor variable. Here're some ways I found you can test those cusor variables using SQL Plus. It uses the Oracle hr schema for the example.

Now I created a package:

create or replace package test_refcursor as
TYPE DynamicCursorType IS REF CURSOR;
procedure test( id in number,
result out DynamicCursorType );
end test_refcursor;
/

Create test package body:

create or replace package body test_refcursor as
procedure test (
id in number,
result out DynamicCursorType
)
as
begin
open result for select department_id,department_name
from departments where department_id < id;
end;
end test_refcursor;
/

The procedure test returns all the departments with department_id lower than passed in ID.

Now the first way to test it in a PL/SQL block:

set serveroutput on
declare
p test_refcursor.DynamicCursorType;
v_id number(4);
v_name varchar2(100);
begin
test_refcursor.test( 50, p );
-- The cusor has already opened, no need to open again.
loop
fetch p into v_id, v_name;
exit when p%NOTFOUND;
dbms_output.put_line( v_id || ', ' || v_name );
end loop;
end;
/

The result you can see:

10, Administration
20, Marketing
30, Purchasing
40, Human Resources

There's another way even easier in SQL Plus:

var p refcursor
exec test_refcursor.test( 50, :p );
print p

It'll print as if you select the data from a table:

DEPARTMENT_ID DEPARTMENT_NAME
------------- ----------------------------------
10 Administration
20 Marketing
30 Purchasing
40 Human Resources