Tushar: please be aware that binary collations do not sort upper-case and lower-case versions of the same letters together. Your sample data is very simple and only contains upper-case letters so it might mask problems with binary collations if your data is not entirely upper-case.
It would greatly help to know what encoding you are using for
VARCHAR in Oracle. Is it a Unicode encoding, such as UTF-8 or UTF-16? Or is it an 8-bit code page, such as Windows-1252? That will make a difference here. If the encoding needs to be Unicode (i.e.
NVARCHAR = UTF-16 in SQL Server is only Unicode option until SQL Server 2019 when
VARCHAR can be UTF-8), then you might need a binary collation. But if you are using an 8-bit code page, then there might be a better solution that will at least keep upper-case and lower-case of the same letter together.
Here is an example showing both the problem with the binary collation, and the only 8-bit collation that sorts an underscore after the letters:
DECLARE @Test1 TABLE ([Prod_Name] VARCHAR(30));
INSERT INTO @Test1 VALUES
('apple'), ('shell'), ('CHANNEL'), ('SHELL'), ('_DISTRIBUTOR BELT');
SELECT * FROM @Test1
ORDER BY [Prod_Name] COLLATE Latin1_General_100_BIN2 ASC;
SELECT * FROM @Test1
ORDER BY [Prod_Name] COLLATE SQL_1xCompat_CP850_CI_AS ASC;
Of course, even if you are using an 8-bit encoding,
SQL_1xCompat_CP850_CI_AS is not necessarily the best option IF you have Extended ASCII characters (values in the 128 - 255 range), and are not currently using Windows-850, and you have characters that are not common between your current code page and Windows-850.
If you do need to use a binary collation, then please use
Latin1_General_100_BIN2 instead of
Latin1_General_BIN. It is newer and sorts Unicode Supplementary Characters properly (whereas the
BIN collations do not). Basically, anyone using SQL Server 2008 or newer should be using the
_BIN2 collations and not the
_BIN collations. For more details on why, please see my post: Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)