NULL Polymorphism

We might be using the several features of a programming language on our daily routine of work.

But how many of us care to appreciate the concepts behind the feature?

The following example is only to appreciate the concept of Polymorphism and does not have a major impact over real time usage.

Sometimes it is always necessary ,where we need to write query to retrieve data from various table based on some condition.These queries might be simple or complex depending on the need. Such queries which involves more joins will be difficult  to read  and hard to maintain.
For ex: select * from <t1> join <t2> on <t1>.<column> = <t2>.<column> join <t3>……

In such cases, CTE comes handy. This feature was introduced in SQL2005.

CTE (Common Table Expression) , powerful technique that is often used to improve query speed for complex queries.Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don’t necessarily need a view defined for the system.It is used when a subquery is executed multiple times(useful for recursive query)

A Common Table Expression contains three core parts:

The CTE name – Followed by WITH keyword.
The column list (optional)
The query (appears within parentheses after the AS keyword)

Syntax:
With <CTE_Name> (<Column_Name_1>, <Column_Name_2> ,<Column_Name_n>….. )
As
(
<cte_Query_Definition>
)

–TSQL which uses the CTE

The query using the CTE must be the first query appearing after the CTE.

Advantages
1) Create a recursive query.
2) Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
3)Enable grouping by a column that is derived from a scalar sub select.
4) Reference the resulting table multiple times in the same statement.
5) Improves readability
6) Maintainability becomes easy.

Disadvantages:
The following clauses cannot be used in the CTE_query_definition:
ORDER BY (except when a TOP clause is specified)
INTO
OPTION clause with query hints
FOR XML
FOR BROWSE

Before we straight away start with MongoDB CRUD operation, let me tell you how to install and configure MongoDB.

Installation of MongoDB is pretty simple; download the mongodb binaries from http://www.mongodb.org/downloads.

Create a folder say mongodb2_2 (you can name anything you want) and copy all files from bin directory to folder which you have created now.

Congrats!  You are done with mongodb installation, now you are ready to use mongodb.

To start mongodb, just enter mongod.

If you want to know more on command line option then enter mongod –help

Now just enter command mongo, it will now connect to test database of localhost.

Ok, now you are connected to test database.  To see all the databases, you can use show dbs command.

In mongoDb, there is no concept of table instead you will be using collection to store data (internally it will store in JSON (JavaScript Object Notation) format.

To see all the collection in your database you can use command show collections.

By the time now, if you start feeling that, ‘I know some basic concept of Mongo Db’, then please answer the below question.

So, we have now basic idea of what is mongo db, in my next blog, I will answer the above question and then we will try to learn how to perform CRUD operation in MONGO DB.

To be continued…

Establish a server instance

The are 2 main approaches to achieve this:

1. UTL_SMTP: Available in Oracle 8i and 9i (for 10g use UTL_MAIL), is not easy to use to send emails with attached files but if you understand the concept of how the SMTP server handles this will be much easier. I will cover this below.
2. Using Java, easier to send attachments but difficult to install and setup (you got to have access to user SYS in order to compile and run some functions in Java and also to run some commands to be able to compile the Java procedure)

In this article I will refer to the first approach, using UTL_STMP. I will save the Java approach for a later post in this blog.

How does SMTP work to send attachments?

What the SMTP server does in order to send an email with a text file attached it’s to get the text that arrives in one of the parameters or arguments passed to the function. The SMTP receives the text and encodes or decodes the text on the fly, depending if it’s sending or receiving the email. So when it arrives to your computer it’s when it gets ‘translated’ into a certain type of file.

This is the way that the process of sending a text attachment works. The SMTP server uses some commands to send the text and not the file itself. This means that contents when arrive to the destination will be stored as an attached file.

This is the reason why when sending an attached file it’s not needed to specify a path. You just pass the contents which will be encoded or decoded on the fly when receiving or sending the email.

How SMTP works using PL/SQL

Using UTL_SMTP to send mails with attachments

UTL_SMTP, as the name itself refers, it’s a utility Package included by default in Oracle Database since 8i release. It has some built in functions and procedures that basically provide an interface with the SMTP protocol commands. So the idea behind it is to interact with the SMTP server initiating and maintaining a conversation to achieve the functionality of sending emails from any stored procedure defined in PL/SQL

To implement this functionality inside your PL/SQL procedures, some functions have to be called; there is no need to initialize/setup the package because it comes pre-installed in the database server. Check the functions used, the parameters are self explanatory and the variables have to be declared previously.

Note that to send the attachment there is no need to call any specific function, the action of initializing some parameters, like the file name that will contain the attached text when the email it reaches its destination, is the only thing needed by the STMP server to send an email including text as an attachment.

-- initiate the conversation with the SMTP server, establishing the connection
v_smtp_connection := utl_smtp.open_connection( v_smtp_host, v_smtp_port );

-- perform a handshake with the SMTP server
utl_smtp.helo( v_smtp_connection, v_smtp_host );

-- close the connection
UTL_SMTP.quit(l_mail_conn);

Sample Code

This code is to demonstrate the use of the UTL_SMTP package that is used in Warehouse Project .The text to be attached comes as a parameter to the function, it can hold up to 32768 characters long, if you need you can use a CLOB datatype which can handle up to 4 Gigs. There is also a place to name the file name that will hold the attached text once it reaches its destination.

The values To, Cc, Bcc are only used to display the recicpients of the email in a certain way in the email client (Hotmail, Eudora, Outlook, Yahoo, etc) . The values on this fields have nothing to do actually with the mechanism that sends the email as this fields are used only for representation purposes of how the recipients will be shown in each email client who will be used to read the email.

This function is commonly used in applications to send results from queries and it’s usually used inside a for cursor loop.

Function send_mail(
v_recipient varchar2, -- Field To of the email
v_subject varchar2, -- Subject of the email
v_body varchar2, -- Body of the email
v_attachment varchar2, -- Text that will be sent as an attached file
v_error_msg out varchar2) -- To hold the errors in the exception section

return boolean is

end send_mail;

If the trigger is calling the parent table select statement that results below oracle error

ORA-04091: table <tablespace>.<table> is mutating, trigger/function may not see it

eg:

create or replace TRIGGER EMPLOYEE_AUTRG AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
  declare
    P_DEP_ID INTEGER;
    …………
    begin
       BEGIN
         FOR CUR_REC IN (
         SELECT  DEP_ID FROM EMPLOYEE WHERE EMPLYEE_NAME LIKE ‘<VALUE>’)
         LOOP
            ………………..
            UPDATE DEPARTMENT SET DEPTNAME = <value>  WHERE DEP_ID = CUR_REC.DEP_ID;
            ………………..
        END IF;
    END IF;
   EXCEPTION when OTHERS THEN
      dbms_output.put_line('no data found ');
   END;
  end if;
END;

Solution 1.

to resolve the above error add PRAGMA AUTONOMOUS_TRANSACTION with in the declare variables provided there must not be any update statement in the trigger

If update statement is available in the trigger which results in different sql exception (ORA-06519: active autonomous transaction detected and rolled back)  for such cases we can use commit after update statement  , here we can use commit in trigger with PRAGMA AUTONOMOUS_TRANSACTION in declare variables

Workable updated trigger:

create or replace TRIGGER EMPLOYEE_AUTRG AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
  declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    P_DEP_ID INTEGER;
    …………
    begin
       BEGIN
         FOR CUR_REC IN (
         SELECT  DEP_ID FROM EMPLOYEE WHERE EMPLYEE_NAME LIKE ‘<VALUE>’)
         LOOP
            ………………..
            UPDATE DEPARTMENT SET DEPTNAME = <value>  WHERE DEP_ID = CUR_REC.DEP_ID;
            commit;
            ………………..
        END IF;
    END IF;
   EXCEPTION when OTHERS THEN
      dbms_output.put_line('no data found ');
   END;
  end if;
END;

Solution 2.

We can use statement level triggers by removing  the row level we can avoid mutating ,trigger/function error

Statement level trigger example:

create or replace TRIGGER EMPLOYEE_AUTRG AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
  declare
    PRAGMA AUTONOMOUS_TRANSACTION;
    P_DEP_ID INTEGER;
    …………
    begin
       BEGIN
         FOR CUR_REC IN (
         SELECT  DEP_ID FROM EMPLOYEE WHERE EMPLYEE_NAME LIKE ‘<VALUE>’)
         LOOP
            ………………..
            UPDATE DEPARTMENT SET DEPTNAME = <value>  WHERE DEP_ID = CUR_REC.DEP_ID;
            commit;
            ………………..
        END IF;
    END IF;
   EXCEPTION when OTHERS THEN
      dbms_output.put_line('no data found ');
   END;
  end if;
END;

Solution 3.

We can use compound trigger to avoid mutating,trigger/function error and this is introduced in oracle 11g

Compound trigger example:

create or replace TRIGGER EMPLOYEE_AUTRG AFTER UPDATE
ON EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
COMPOUND TRIGGER
  declare
    P_DEP_ID INTEGER;
    …………
    begin
       BEGIN
         FOR CUR_REC IN (
         SELECT  DEP_ID FROM EMPLOYEE WHERE EMPLYEE_NAME LIKE ‘<VALUE>’)
         LOOP
            ………………..
            UPDATE DEPARTMENT SET DEPTNAME = <value>  WHERE DEP_ID = CUR_REC.DEP_ID;
            ………………..
        END IF;
    END IF;
   EXCEPTION when OTHERS THEN
      dbms_output.put_line('no data found ');
   END;
  end if;
END;

Replication is a mechanism to copy or distribute data and database objects among servers at different locations and then synchronizing between these servers to maintain consistency.

The purpose of replication may be varied and hence different approaches of replications are used for different scenarios. In this article we will mainly talk about Replication in SQL server (2008 to be specific but it is applicable to 2005 as well).

Below we will shortly discuss different types of replication in SQL and their purposes. And then talk about how to setup Peer-to-Peer replication between two SQL servers.

Before going to the types of replication let me mention the usual metaphors used for various participants in Replication:

The metaphor used here is of a Magazine publication and subscription.

Publisher – Publisher (like for a Magazine) published DB objects and data

Subscriber – Subscriber (like for a Magazine) subscribes to a publisher changes.

Article – A set of tables, SPs, views (A publisher can publish more than one article. A subscriber can subscribe to more than one article from each and more than one publisher.

Distributor – An agent (a process) which is responsible for relaying the changes from publisher to subscriber.

Following are the main types of Replication in SQL server.

Snapshot replication:

As the name suggests when the synchronization occurs, entire snapshot of data and schema at a moment is copied to the subscriber without need to monitor updates to data. A snapshot of the current schema of the publisher articles is created by an Agent called “Snapshot Agent”. This snapshot is created in form for snapshot files in a snapshot folder. The “Distributor Agent” relays these files to the subscriber at specified intervals.  This type of replication typically copies the entire data set every time.

Note that Snapshot agent is used by other replication types also for the purpose of creating initial snapshot of Database to be initialized in each participating server.

It is suitable to use Snapshot replication where

Latency can be tolerated.

Data changes infrequently.

Large volume of data is changed in a small instant.

Transactional Replication:

A transactional replication has all the transactions at the Publisher read and logged by a process called “Log reader agent”. These transactions are relayed to the subscriber by the Distributor agent. An important point to note is transactional replication uses Snapshot agent to replicate initial schema and data to the subscribers.

Transactional Replication is suitable where

Latency cannot be tolerated or at least low latency is desired.

An insert, update, delete transactions occur at high frequency.

The publisher or subscriber is a non-SQL database such as Oracle.

There is one more aspect to Transactional replication i.e. Updatable Subscriptions. It means the ability for the subscriber database to be changed and possible replayed back to publisher (peer-to-peer).

Note that with updatable subscriber and latency of the publisher can cause conflicts which need to be handled. We are not discussing conflict resolution here.

Merge Replication:

Merge replication again (as Transactional replication) starts with a Snapshot agent synchronizing the initial snapshot. But the subsequent data changes to schema and data are tracked by a process called “Merge  Agent”.

Merge Replication is suitable where

            Subscribers may have to go offline e.g. Mobile users etc.

            Each subscriber may work on different partition of data.

Ok, now we describe how we setup a Peer-to-peer replication which is nothing but a Transactional replication with updatable Subscription.

Note that we only try to configure two servers. However it can be extended to any number of servers          

Configure Databases at each server.

Lets Identify two servers and call them MASTERSERVER and SLAVESERVER as follows.

Let the database be MyDB_Master and MyDB_Slave. (You can have same names to the database also.)

At MasterServer (MyDB_Master)

Run MyDB_Master  script to initializeMaster  DB

Configure user id/password  for  SlaveAgentID (chepub in sceenshots) at Master. Give permissions of dB owner to this id for MyDB_Master

At SlaveServer (MyDB_Slave)

Run MyDB_Slave  scriptto initializa slave DB

You typically use snapshot agent to replicate the initial database and schema at the subscriber. But in this case I have to chosen not to use Snapshot agent for this.

Configure user id/password  for  MasterAgentID (elipub in screenshots) at Slave. Give permissions of dB owner to this id for MyDB_Slave

Configure Distributor at both Nodes. Below is described for Master node.

Right click on “Replication” and select Configure Distributor

Specify the host server itself as Distributor agent server.

Specify the Distributor Name and further on select default settings and “Finish”

Repeat the same in Slave server but with different Distributor Name

Configuring Publisher at Master

Right click on Publications and select New Publications

Select MyDB_Master

Select 2^nd option – Transactional Publication

Select the tables to be replicated.

Uncheck both check boxes.

Use SQL server agent service account

Sepcify the Publication name and “Finish”

Go to MasterPublications -> right click -> proeprties -> Supcription Options -> Peer to Peer = True.

Configure Peer 2 Peer

Go to MasterPublication, right click, select Configure Peer to Peer

Right clisk on Surface and select Add New Peer node

Select  SlaveServer and connect using MasterAgentID (configured earlier on SlaveServer.

Db = MyDB_Slave , Connect to all nodes = true, Originator Id =2

Loag reader agent – security – SQL server agnt.

While setting subscribers, specify SQL server agent for Ditrtributor agent in both rows.

(refer pic Config P2P 5 (Subscriber agent sec settings))

For Subscriber agent for CIMS_TEST, agent id is MasterAgentID

For Subscriber agent for MyDB_Slave, agent id is SlaveAgentID

DB Initialization select first option i.e. no restoration required.

Replication setup is done.

Right click on Replication and select “Launch Replication Monitor”

Recently I was tasked with creating test data for a new project that we started. Creating large volumes of test data with all constraints and checks repeatedly with variations requires a well though through test data creation plan.

There were multiple options to go about creating test data.

1. Find a third party tool to create test data.
2. If tool doesn’t support your requirement, create a custom script to meet the requirement.
3. A least preferred option, manually create insert scripts. 

Third party tools: 

There are some good tools available which can create valid test data based on your DB design and table constraints. Some tools even provide the flexibility to an extent where you can predefine the type of data, for e.g. first name will be populated with valid first names; SSN will be populated with proper formatting etc. 

I found Red Gates’ SQL Data Generator tool very useful in creating test data. Below screenshot illustrates various data selection fields available with this tool.  

Sample Data field values

Test Data creation Script:

If tools don’t help you, then your next best option is to pull your fingers out and write yourself a light weight data generator. This below code creates a one random record and inserts into the given table.

declare @select varchar(max), @insert varchar(max), @column varchar(100), 

    @type varchar(100), @identity bit, @db nvarchar(100) 

set @db = N'Orders' 

set @select = 'select ' 

set @insert = 'insert into ' + @db + ' (' 

declare crD cursor fast_forward for 

select column_name, data_type,  

COLUMNPROPERTY( 

    OBJECT_ID( 

       TABLE_SCHEMA + '.' + TABLE_NAME),  

    COLUMN_NAME, 'IsIdentity') AS COLUMN_ID 

from Northwind.INFORMATION_SCHEMA.COLUMNS 

where table_name = @db 

open crD 

fetch crD into @column, @type, @identity 

while @@fetch_status = 0 

begin 

if @identity = 0 or @identity is null 

begin 

    set @insert = @insert + @column + ', '  

    set @select = @select  +  

        case @type 

            when 'int' then '1' 

            when 'varchar' then '''test''' 

            when 'nvarchar' then '''test''' 

            when 'smalldatetime' then 'getdate()' 

            when 'bit' then '0' 

            else 'NULL' 

        end + ', '  

end 

fetch crD into @column, @type, @identity 

end  

set @select = left(@select, len(@select) - 1) 

set @insert = left(@insert, len(@insert) - 1) + ')' 

exec(@insert + @select) 

close crD 

deallocate crD 

Manual test data creation:  This is the least preferred option because it is time consuming and error prone. But, hey, if you have time and patience to manually creation thousands of records, go for it.

Deleting the indexes (Primarykey and foriegnkey) from MySql InnoDB

When we try to delete the foreign key index from the innodb table

(ALTER TABLE `<DBNAME>`.`<TABLENAME>` DROP INDEX `<INDEXNAME>`;)

it will through below error

“Error on rename of ‘.\<DBNAME>\#sql-1410_4′ to ‘.\<DBNAME>\<TABLENAME>’ (errno: 150)”

It’s because we are trying to delete the index before deleting the foreign key. So first we need to drop foreign key and then index.

EX:

ALTER TABLE <TABLENAME> DROP FOREIGN KEY `<INDEXNAME>`;
ALTER TABLE <TABLENAME> DROP INDEX `<INDEXNAME>`;

Similarly when we try to delete primay key

(ALTER TABLE `<DBNAME>`.`<TABLENAME>` DROP INDEX `PRIMARY`;)

It will through the below error

“Incorrect table definition; there can be only one auto column and it must be defined as a key”

Before deleting primary key we need to delete auto increment for the field

EX:

ALTER TABLE <TABLENAME> CHANGE `<FIELDNAME>` `<FIELDNAME>` INT(11) NOT NULL;
ALTER TABLE <TABLENAME> DROP INDEX `PRIMARY`;

This blog talks about the use of plsql table

first step is to create a table and some contents:-

create table period_tab (payperiod date);

insert into period_tab values (sysdate);
insert into period_tab values (sysdate+1);
insert into period_tab values (sysdate+2);
insert into period_tab values (sysdate+3);
insert into period_tab values (sysdate+4);
insert into period_tab values (sysdate+5);

commit;

The next step in the process is to create a type and a table of that type. I’m going to make it very basic for the purposes of this demonstration, but your type can be as complex as you need it to be.

CREATE OR REPLACE TYPE DateType IS OBJECT ( PayPeriod DATE );
/

CREATE OR REPLACE TYPE TableList IS TABLE OF DateType;
/

For the demonstration, I am going to bulk collect to populate the table and then I will use it in a sql select.

DECLARE
  List1 TableList;
BEGIN
  SELECT DateType(payperiod)
    BULK COLLECT INTO List1
    FROM period_tab;

  FOR c1 IN (
    SELECT payperiod
      FROM TABLE( CAST( List1 AS TableList)) )
  LOOP
    DBMS_OUTPUT.PUT_LINE( c1.payperiod );
  END LOOP;

END;
/

The TABLE() function (and everything inside of it) can be used as an inline view and joined to other tables as needed.

Now, one thing to consider. If you are manually populating a table, it makes sense to use it this way. However, if you are using bulk collect to populate the table and then turn around and use that table in a query, it might make sense to not use a PL/SQL table at all and embed that query in the SQL.

For example, the above example would be functionally equivalent (but would perform better for larger data sets) if I rewrote it like this:

BEGIN
  FOR c1 IN (
    SELECT payperiod
      FROM (SELECT payperiod
              FROM period_tab) )
  LOOP
    DBMS_OUTPUT.PUT_LINE( c1.payperiod );
  END LOOP;

END;
/

And that could be re-written like this:

BEGIN
  FOR c1 IN (
    SELECT payperiod
      FROM period_tab )
  LOOP
    DBMS_OUTPUT.PUT_LINE( c1.payperiod );
  END LOOP;

END;
/

Remember, keep it as simple as you can but no simpler. If you can do it in SQL, you should. But it’s nice to know that the TABLE() function is available.