Why do Oracle commits when we issue a DDL statement?

In DDL operation, DDL locks wiil be taken place automatically against the respective object. So one needs to remove the locks as soon as the statement got executed, it can be achieved by commiting the transaction. The next Question is what if the transaction fails. the answer is oracle simply rollback.The next complication is if it rollback, user looses their transactions that happend just before the DDL statement.Thats why Oracle executes a commit statement just before the DDL also. Thats the reason why the end user transactions will be committed irrespective of the success of DDL. A DDL statement actually execute as below
commit;
DDL
commit;
Exception
rollback

Do all value types reside is stack?

The simple answer is NO.
In any class we have variables at global level and local (specific to a method/function) level..
At the time of execution the method code will be loaded into stack andhence all the value types refered in the method will be reside in stack. If the values types are declared at class level those variables will be stored in heap.

A brief description about Oracle architecture

First I'd like to discuss about common terminology that helps us in understanding the architecture.
In Oracle we can describe storage mechanism in 2ways logical storage structures and physical storage structures storage consist of DATA FILES which are nothing but the (OS)files on hard disc.
Logical Storage:
'DataBlock' is the smellest unit of storage for I/O operations in oracle DB. Whose size is (obviously) greater than OS block and its multiple of OS block. its size can be configured and by default its 2048. A datablock consist of contiguous OS blocks.

'Extents' The next level of logical space unit is Extent. It consists of contiguous DataBlocks. Database allocates space in terms of extents when ever its required.

'Segments' Is the next level of logical storage unit. A segment is a set of extents that are allocate d for a specific type of datastructure (that means data and indexes will get stored in different segments).

'TableSpace' is the top level unit of logical storage structure in Oracle database.The data of a database is logically stored in tablespaces and physically stored in datafiles that are associated to a tablespace. A database is comprised of 1 or more logical storage units called tablespace. By default every DB creates a tablespace called 'System' when the database is created.Each tablespace is consist of 1 or more physical(OS) files called datafiles.

At the time of table creation one can mention its tablespace. A database object (table, view etc) can span a datafile but can't a tablespace.

Anatomy of DB Block:
Irrespective of its content(data, index or clustured data) each database block consist of below parts.
1.Common and variable header 2.Table directory 3.Row directory 4. Free space 5. Row data

Header contains the block information like address of the block, type of segment(data, index or rollback). Please note part of the header size is fixed but total header size is variable.

Table directory the name it self explains, it contains table information which having rows in the block.(this shows a block can hold data related to more than 1 table). But please not that a datablock can hold rows of different tables only if they are clustered else a data block can't hold data related to 2 different tables.
Note: If a data block contains data for cluster(having 2 or more tables) then datablock free space is managed by the cluster storage clause not by individual table pct*** factors.

Row directory this part of the block contains the information(address of each row) about the rows in the block. Please note that space allocated in the row directory can't be reclaimed when the row is deleted (place where we need to think about High Water Mark)

RowData This portion contains actual data (table data or index).

Free Space is used to accommodate new rows(insert) or updates to existing rows. Based on PCTFREE factor row insertion will be decided(i.e a new row will occupy the same block or other block).

PCTFREE and PCTUSED are the 2 space management parameters that allow the user to control the use of free space in a block.

PCTFREE decides the %of the block to be kept free for future updates to rows. 'pctfree 10' says that 10% of every block of the segment that used for the table kept free for future updates on the table.And the other important point is as and when a blocks pctfree reaches specified limit it'll get out of the free list.
Data will be filled in the block by sparing the % specified in pctfree. Now we deleted some rows in the block.Question arises as "Now is the block is ready for new insertions?"(as it has some free space apart from pctfree). This will be answered by PCTUSE. The block can't be considered for new insertions until and unless its usage falls below the PCTUSE. viz if you say PCTUSE 50, and the block is filled fully(sparing space for pctfree). The block will be considered for insertions if and only if its usage falls below 50%.

How Oracle allocates Extents?
A segment in a database is created with at least one extent. This is called segment's initial extent. As and when this initial extent is full and the database object need more space to accommodate its data, Oracle allocates an extent(incremental) to the segment. But how?..
Oracle searches for free contiguous block of size(= size of required extent + 1(if its > 5 blocks)) in the same tablespace of the segment. Lets say if extent requires 10 blocks. It searches for 10+1 contiguous block and if it finds the same then it allocates that space as an extent to the segment. If the required extent size <= 5 then it just searches for required blocks (no +1). Note: Infact this additional block is added to address internal fragmentation. We can just remeber this as below. If(No of blocks required > 5)
No of searched blocks = No of blocks required + 1
else
No of searched blocks = No of blocks required
case 1.
If oracle didn't find the exact match but finds a larger no of contiguous blocks.It allocates blocks to the extent as below.
i. If 'No of contiguous blocks available' > 'No of searched blocks' + 5
Then it just allocates 'No of searched blocks' from available to the extent & hence to segment.
ii. If 'No of contiguous blocks available' <= 'No of searched blocks' + 5 Then it allocates all the block to the extent and then to segment. case 2. If Oracle does't find a larger set of contiguous data blocks, It then coalesces free space in the tablespace so that it results in the formation of larger contiguous data blocks. Once again oracle start searching as mentioned in case 1. if it fails to find then Oracle returns an error. Note: Infact SMON(System Monitor back ground Process) will periodically coalesces the free space to create larger chunks of contiguous blocks.

Some thing about SGA: System Global Area

*Please note that above information is just my understanding, by reading various article on net with some of my own assumptions. Pease let me know if I am wrong anywhere.

Is HashCode Unique?

I found in many sites and artilce saying Hashcode is unique. But thats not true always.
just consider this example. As u know HashCode is int32, that means at a given point of time u can have max 2^32 different values. At that point of time u can have more than 2^32 different objects. That means hashcode must be duplicated.

There is another claim saying at least for string objects hascode is unique, that is also not true, the reason is. As we have 26 alphabets total no of differe string u can have is 26!(26 factorial) (I am just considering 26 char length single word only), which is bigger number than 2^32. That means two different string can have same hashcode is it not!.
Based on the above discussion we can say Hashcode is not unique.

*Please not that this is just my understanding, Correct me if i am wrong.

Sending a DataTable as parameter to Stored Procedure(SQL Sever 2000)

Quite often i faced a cese where i need to send a DataTable or array to the procedure as parameter. As we all know SQL 2000 takes paramteres as varchar,char,int,text etc. But there are occation where we required to send Full DataTable to DB. In those case we usually pump data to DB, record by record. Here is a work around for that. Lets Assume we have to send a table(see below) as parameter to Stored Procedure.

Person	Discovery
Einstein	Theory of relativity
Newton	Laws of Cooling
Niels Bhore	Atomic Model

Now convert the above table as string using a (C#)class.The resultant string looks like this
"
'''Person'',''Discovery'',~''Einstein'',''Theory of relativity'',~''Newton'',''Laws of Cooling'',~',~''Niels Bhore'',''Atomic Model'',~'
"
We can send this string to SP as a parameter directly. So the SP that take this string as input parameter looks as below.
Create Procedure usp_update_peopleTable
@inputTable varchar(8000)
----
as
begin
---
end

But there is constraint as varchar can just hold(8000 chars) so we have to declare a variable type which can hold more data. So we have to use Text/Image instead of Varchar. so the SP looks as below.
Create Procedure usp_update_peopleTable
@inputTable text
----
as
begin
---
end
I just want to repat the the stuff what we done still now.In App server we have a table that we need to send to DB. we converted the table as a string, now we sent the string to SP.
Now we have to chage input string to SP as Table again so that we can manipulate that as we require.So next task is to covert the String to Table in DB. Below SP address the same task. This SP takes 2 input parameters and results a Table, 1st param the strig that u have to convert to a table, 2nd param number of columns of the table. Teh SP is as below.

alter procedure usp_get_table_from_text
@inputtxt text,
@no_cols int
as
begin
declare @charposition int,@row varchar(8000),@strsql varchar(8000)
create table #data_holder
(
rowid int,
textvalue text
)

set @strsql = 'create table output_table ('
while(@no_cols != 0)
begin
set @strsql = @strsql+ ' col' + cast(@no_cols as varchar) + ' varchar(2000),'
set @no_cols = @no_cols -1
end
set @strsql = substring(@strsql,1,len(@strsql)-1)+ ')'

exec(@strsql)

set @charposition = charindex('~',@inputtxt)
insert into #data_holder values(1,@inputtxt)

while(@charposition > 1)
begin
select @row = substring(textvalue,1,@charposition-1) from #data_holder where rowid = 1

set @strsql = 'insert into output_table values(' + substring(@row,1,len(@row)-1) + ')'
exec(@strsql)

update #data_holder set textvalue= substring(textvalue,@charposition+1,datalength(textvalue))


select @charposition = charindex('~',textvalue) from #data_holder where rowid = 1

end
drop table #data_holder
select * from output_table
drop table output_table
end

Just for understanding create SP in u r DB and execute below Query. SO that you can get some understanding what its doing.

exec usp_update_peopleTable '''Person'',''Discovery'',~''Einstein'',''Theory of relativity'',~''Newton'',''Laws of Cooling'',~',~''Niels Bhore'',''Atomic Model'',~',2

I am so sorry to say that there were no cooments in above SP. i'll add comments as and when i get soem time.
Conclusion:
Steps to use the stuff
Objective: when vere u need to send a Table directly to DB folow these steps.
1. Covert the Table to String in App Server.
2. send teh converted string to your SP that consumes the Table.
3. In your SP use sp 'usp_get_table_from_text' to convert the string to table
4. Manipulate the table as you want.
Hope this might help you all.

External Tool for VS(Visual Studio)2003 to change the file attributes

Hi All: This Article describes adding your own tool to VS 2003
Problem:
If you intigrate VS 2003 with VSS, as and when u get the latest version the respective file(s) become "Read Only". So when ever u want to edit the file, u have to check out the file, else if u want to make changes locally, u have to dig till that file using Win Explorer and have to uncheck the Read only attribute. Imagine if the project is having many subdirectories, then its bit difficult to change the read only attribute of the file(s) by navigating thru explorer.
Solution:
If there is any way that address the above task, directly from VS IDE that will difinitely help us. Here is a way out. I just added a external tool, that appears under Tools menu.

U have to write a class that acceppts a file_path as argument. And u have to change teh file attributes thru code.Here i wrote a class that exactly do the same stuff.

using System;
using System.IO;
using System.Text;

namespace Surendra.ExternalTools.SetFileAttributes
{
public class ChangeFileAccessAttributes
{
public static void Main(string[] args)
{
string strFilePath = args[0].ToString();

File.SetAttributes(strFilePath, FileAttributes.Archive);
}
}
}

Creation of EXE:
Now u have to create an EXE for the above class. u can achieve teh same through CMD as below.

"C:\Surendra\PersonalProjects\ExternalTools>csc ChangeFileAccessAttributes.cs"

Integration of the tool to VS IDE:

step1: Open MS VS. Click on Tools Main Menu, then click on External tool..
A pop up will open for u.



Step 2:Click on Add button.
just fill out the text boxes as
Title: Remove Read Only
Command: Path of the ChangeFileAccessAttributes.exe
Arguements: "$(ItemPath)"
And finally press OK button..
Thats it.. your new tool will get ready and that appears under Tools menu as shown below.

Avoid Dynamic SQL when u want to use a variable in IN clause

When we want to filter the result set using IN clause. We end up having dynamic SQL.
The scenario goes like this.

Basic Query:
select
* --(writing * is not a god practice write down column names)
from
employee
where
employee_id in (100,10001,11560)

If the give employee ids come thru a variable(viz as input param to SP). U can get the result using dynamic SQL as below.

@strInput varchar(200)
set @strInput = '100,10001,11560'

@strQuery varchar(1000)
set @strQuery = 'select * from employee where employee_id in (' + @strInput + ')'

exec(@strQuery)

We can achieve the same with out dynamic SQL. The query below will address the same.

select * from employee where charindex(','+cast(employee_id as varchar)+',',@strInput) > 0


Note:
we need to convert the employee_id to varchar so that we can ap(pre)pend ',' to the id.
Why we need to add ',' to employee_id column?. (Think your self).
The reason is if u have employee_id's as 1,11,1111 then without ',' will give wrong results.

Hope you all appreciate the usage of charindex. More in the next article. Njoy reading.

Surendra

How the word 'Interface' came into OOP terminology

I thought of framing the question as (why the name for Interface is interface in Object Oriented Programming) :-). What ever, Just keep OOP aside and think what is an 'interface'?. What is the interface to your home/room/office?.

Interface is nothing but the way an object get exposed to the external world. That means you can play around with any object by its interface only. Now come to OOPs. U can play around with an object/class using its variables and its methods only(Is it Not?). Therefore interface to(of) a class is nothing but a its(class's) variables and method signatures.

Now come to Interface in OOPs..Afterall interface is also a class but it just consist only the variables and method signatures. Hence A class which just contains the details of its interface is called Interface.