Number vs Number(p, s) in Oracle 11g

While I was working on Oracle last year, I always wondered how & what to chose as the data type for my numeric fields. The source of this post gave me a deep insight into this question and helped me achieve the best.

Excerpts from the source are listed here for easy access..

Differences between Number & Number(p,s):

As a decimal type, NUMBER allows you to indicate the precision (total number of digits) and scale (number of digits to the right of the decimal point) when defining a field of this type (NUMBER(p, s)).


NUMBER(9, 2): Nine significant digits in total (precision) of which 2 (scale) may be used for the decimal part of the value (digits to the right of the decimal point).

NUMBER(9): Nine significants digits in total, none of them for the decimal part. Yes, that’s the way to restrict your fields for integer values storage.

NUMBER: “I will save whatever you give me” with an accuracy of up to 38 significant digits.

NUMBER(*,2): You set no limit to the precision but reducing (rounding) the decimal part to two digits.

NUMBER(9,-2): Nine digit for the integer part which will be “rounded” at the last two digits (interesting), i.e.: 987,654,321 -> 987,654,300.

There are 2 advantages of using Number(p,s) a=rather than just Number data type:

a) To restrict the entry of data: If we specify precision and scale, we are adding a restriction that allows us to establish a greater shielding on the data (the more “downstream” the better, and the shield will apply to any application developed over this database).

Problem: It is vital to know precisely in advance the needs of the field, which  is not sometimes easy. For a field which is, for instance, intended to hold the surface of a construction in a cadastral application, precision and scale could be set without further problems (usually a two digit scale for area values in square meters).

But what precision and scale should be assigned to a coefficient K that can be fixed arbitrarily by a per year shifting taxation law? Perhaps what today is a ratio of two decimal digits, tomorrow will have six, causing to have to redefine the structure of the table every year with the usual associated  impact in a productive environment.

b) The saving of disk space: It is common thinking that if you reduce precision the needs of storage cost will be reduced in the same meassure, and therefore you will save disk space.

Detailed explanation can be found here.

Posted in Oracle | Leave a comment

Drop & Truncate all tables in Oracle

Below is the generalized script for dropping and truncating all the table objects from on Oracle database:

DROP Script:

SELECT ‘DROP ‘ || OBJECT_TYPE || ‘ ‘ || OBJECT_NAME ||’ cascade constraints PURGE;’ 


SELECT ‘TRUNCATE ‘ || OBJECT_TYPE || ‘ ‘ || OBJECT_NAME ||’ drop storage;’ 
Posted in Oracle | Leave a comment

Scripting all Stored Procs from database

I had to rename all the stored procs by finding a keyword and replacing it with another word. The below code was very helpful in scripting out all the SPs from the sql server DB and then using find & replace 🙂

SELECT ‘IF object_ID(N”[‘+schema_name(schema_id)+‘].[‘+ Name +‘]”) IS NOT NULL
DROP PROCEDURE [‘+schema_name(schema_id)+‘ ].[‘+ Name +‘]’+CHAR(13+                                               CHAR(10)+‘GO’+CHAR(13)+CHAR(10)+OBJECT_DEFINITION(OBJECT_ID)+
FROM sys.procedures
WHERE is_ms_shipped =0
DECLARE @lnCurrent INT, @lnMax INT
SELECT @lnMax =MAX(Id)FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax
SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
WHILE @LongName <>
PRINT LEFT(@LongName,8000)
SET @LongName =SUBSTRING(@LongName, 8001,LEN(@LongName))
SET @lnCurrent = @lnCurrent + 1
Posted in New Learning, SQL | Leave a comment

Install missing templates in Visual Studio..

You’re just one step away installing the missing templates in visual studio. I started VS 2010 and found some templates missing. A quick google search helped me resolve this issue.

Close all instances of Visual Studio’s and run Visual Studio Command Prompt. (Vista users: For elevated permissions, make sure you right click the VS command prompt and Run as administator).

Navigate to the location of devenv.exe. This file is located in <Visual Studio Installation Path>\Common 7\IDE.

Then type in: devenv /installvstemplates and press Enter. Allow some time to complete this operation. Once done, re-open Visual Studio and find the earlier missing template in place now :).


Reference: MSDN Library

Posted in New Learning, Visual Studio | Leave a comment

Steps to grant ‘Allow log on through Terminal Services’ Right

To log on to the remote computer, you must be granted the Allow log on through Terminal Services right. By default, members of the Remote Destop Users group have this right. If you are not a member of the Remote Desktop Users group or another group that has this right, or if the Remote Desktop User group does not have ths right, you must be granted this right manually.

I was setting up remote access for a user on a domain controller for some tests. This user was not an admin (but belonged to the Remote Desktop Users) and kept getting the same error message above. Setting this user to domain admin solved the problem, but of course I did not want to make any remote user a domain admin.

It so happens that it is not enough for a user to belongs to the Remote Desktop Users to gain the rights it needs. Here is how you fix this:

Open gpedit.msc (the local group policy editor)
Expand Local Computer Policy –> Computer Configuration –> Windows Settings –> Security Settings –> Local Policies –> User Rights Management
Look for the setting on the right called Allow log on through Remote Desktop Services
Double click this policy
Add the user/group you would like to have remote access to the box.

Happy granting remote access 🙂

Posted in New Learning | Leave a comment

Adding Authentication Types in IIS 7.0 on Windows Server 2008

In my ongoing project I had to enable both Form-Based & Windows authentication. Alas! my windows server 2008 R2 machine didn’t had windows authentication in IIS.

To enable the windows authentication provider in IIS 7.0, I followed the below simple steps and achieved the result.

1. Open up Server Manager (one way to do is by right clicking the computer node from within the start menu and choosing  Manage).

2. Then, expand the Roles node. Right click the Web Server (IIS) and choose Add Role Services.

3. You’ll get the following wizard, just scroll down a bit and check Windows Authentication under the security node.

4. After selecting the Windows Authentication check box, click Install. Once done successfully, you should be able to view the windows authentication in the list!!!!

Whooo hoooo.. 4 steps back you were with no windows authentication on the machine & now it has been enabled!! Congrats 🙂

Thanks to my src who helped me understand this.

Posted in New Learning | Leave a comment

View Vs SPs

View – A View in simple terms is a subset of a table. It can be used to retrieve data from the tables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure –  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.

Additional differences links as follows..

Posted in SQL | Leave a comment