Testing Oracle trigger firing before or after transaction control statements

create table vsk_test1 (col1 varchar2(100));

create table vsk_audit1 (audit1 varchar2(100));

CREATE OR REPLACE TRIGGER VSK_TEST1_TRG 

BEFORE INSERT OR DELETE OR UPDATE of col1 ON VSK_TEST1 

REFERENCING OLD AS old NEW AS new 

FOR EACH ROW 

BEGIN

  insert into vsk_audit1 (audit1) values (systimestamp || :new.col1);

END;

insert into vsk_test1 (col1) values ('aaaaaaaaaaa');

rollback;

select * from vsk_test1;

select * from vsk_audit1;

drop table vsk_test1;

drop table vsk_audit1;

Find day, date, etc in SQL Server

I am not the original author of these queries and have found them while surfing as usual when looking for a quick solution.

First Day Of Current Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

First Day Of Last week
select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

First Day Of Next Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

First Day Of Current Month
select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

First Day Of Last Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

First Day Of Next Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

First Day of Last Year
select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)

First Day Of Current Year
select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

First Day Of Next Year
select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)

Last Day Of Current Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

Last Day Of Last Week
select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

Last Day Of Next Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

Last Day Of Current Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

Last Day Of Last Month
select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

Last Day Of Next Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

Last Day Of Current Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

Last Day Of Last Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)

Last Day Of Next Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)

BizTalk Artifacts Monitoring

I have to create jobs to monitor send ports, orchestrations and receive locations and this post will be update as i make progress.

Below is a list of things to remember and consider when creating these monitoring jobs:

• All BizTalk databases should not be considered as our usual databases and extreme caution should be practices when using them
• BizTalk installation has 4 databases BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkTrackingDb and SSODB
• These databases are dependent on each other and data is moved between them 
• Check on using WITH (READPAST): excludes locked rows from result set

To Be Cont....

SELECT TOP from @VAR and DISTINCT TOP @VAR

Simple and useful:

SELECT TOP (@MAX_REC_COUNT)
from DBO.MY_TABLE WITH (NOLOCK);

This way we can get the @max_rec_count from a config table instead of hard coding the record count to retrieve. This is useful when you can modify the config table from a UI instead of making code changes.

I wish there was a better way to get DISTINCT TOP @VAR from a query but unfortunately no better way than:

SELECT TOP @MAX_REC_COUNT 
FROM (
           SELECT DISTINCT COL1, COL2, ...COLN
           from DBO.MY_TABLE WITH (NOLOCK)
          ) XX;


HTH

Database and Instance in Oracle and SQL Server

http://channel9.msdn.com/posts/SQL-Server-Essentials-for-Oracle-DBAs-01-Database-and-Instance

Good and very informational video. Hope they don't mind me sharing knowledge :)

SP_HELPDB

sp_helpdb function returns information about that databases in the particular database server. Below is an example:

SP_EXECUTESQL expects NTEXT/NCHAR/NVARCHAR

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Pretty straight forward error, just change the datatype of the variable you are using to assign the dynamically generated string.

Hope this helps....

PL/SQL Collections

This is one of those topics i used to dread when i started out as a database developer, not anymore. But still from time to time i need to refresh my memory when it comes to collections:

I recently started reading a book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" and it describes collections in such a way that i have to make a post of it. It is precise, with an example and perfect read for me:

Below are a few pointers that i usually like to remember:

Associative Arrays (TABLE OF)

• AKA Index by tables
• Of course use an index by BINARY_INTEGER or PLS_INTEGER or VARCHAR2
• Sparse collection
• Need not be consecutive
• Unbounded - no upper boundary
• Collection is extended by assigning values to non existing index values
• Data can be deleted
• Start with FIRST method to access data

Nested Tables (TABLE OF)

• No index
• Unbounded colleciton
• Dense collection when creating collection
• Data can be deleted
• NEXT and PRIOR (?) helps access collection elements

VARRAYS (VARRAY OF)

• No index
• Bounded collection so cannot be extended above the specified limit
• Data cannot be deleted
• Dense data collection
• Consecutive data
• Start with FIRST method to access data

Methods that can be used with collections. Not all methods can be used with all collections:

• EXISTS - exists(v) - returns boolean
• DELETE - delete, delete(v1) and delete(v1,v8) - removes data
• COUNT - returns number
• TRIM - trim and trim (v) - removes data
• LIMIT - returns number
• EXTEND - extend, extend(v1) and extend(v1,v8) - adds NULL elements
• FIRST - returns index of first element
• LAST - returns index of last element
• PRIOR - prior(v) - returns index of prior element
• NEXT - next(v) - returns index of next element

Hope this helps....