Systems Engineering and RDBMS

Archive for February 7th, 2009

Back to the Basics: Specify parameter lengths

Posted by decipherinfosys on February 7, 2009

This is one of the issues that we have seen in junior programmers or at times even senior developers who have not done database programming before or database programmers who have done PL/SQL (Oracle) programming and are now doing it for SQL Server.  When you use these data-types: varchar(n), char(n), nvarchar(n), nchar(n), you should always specify the value for n.  Now, if you do not specify the value for it, it will not give you an error but you will not be getting your desired results.  Here is an example:

CREATE PROC dbo.TEST_PROC
(
@parm1        varchar
)
AS
BEGIN
SET NOCOUNT ON
select len(@parm1), @parm1;

END
GO

In this procedure, we did not specify the length for the input parameter.  We just gave it as varchar.  Since the length is not known, SQL Server will default it to a length of 1.  You can see that when you execute this procedure … here is the example:

EXEC dbo.TEST_PROC ‘A really long string’

And you will see that the length is returned as 1 and it will return only “A” i.e. the first character.  The same is true whether we have the variable in a T-SQL block or whether it is a local variable in the procedure.  So, make sure that you always specify the length for your parameters.  This is one of the common mistakes that we have seen on projects in which folks are converting from Oracle to SQL Server.  This is infact documented in BOL (and see the note about CAST() and CONVERT() functions as well):

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Posted in SQL Server | Leave a Comment »

SQL Server 2008 January 2009 BOL Update

Posted by decipherinfosys on February 7, 2009

The January 2009 BOL update for SQL Server 2008 is now available – you can download it from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=765433F7-0983-4D7A-B628-0A98145BCB97&displaylang=en

It is always a good idea to update your BOL version with the latest and greatest since it not only has fixes to the texts but more samples as well to help understand the new features and the modifications to the existing ones.

Posted in SQL Server | Leave a Comment »

DBMS_UTLITY.FORMAT_ERROR_STACK

Posted by decipherinfosys on February 7, 2009

As the name suggests, the supplied DBMS_UTILITY package has some very useful procedures and functions that can be used for maintaining and debugging PL/SQL code.  One such procedure is FORMAT_ERROR_STACK.  A common use of this procedure is in exception handling scenarios to display error messages just like SQLERRM.  Here is an example:

DECLARE
a NUMBER(9);

BEGIN
SELECT col3
INTO a
FROM TEST;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(SQLERRM(-1428));
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Here is the output.

ORA-01422: exact fetch returns more than requested number of rows
ORA-01428: argument ” is out of range
ORA-01422: exact fetch returns more than requested number of rows

So what is the difference between SQLERRM and FORMAT_ERROR_STACK procedures?

SQLERRM displays only first 512 bytes of the error while FORMAT_ERROR_STACK displays entire error message. So Oracle recommends use of later in the PL/SQL code so that we get entire error message and not the truncated message. If you are using dbms_output package then you have to make sure that you are not constrained by serveroutput limitation. In 10g, Oracle lifted the earlier limitation of 255 bytes in ‘set serveroutput on’ command.

Also SQLERRM can take a specific error code as an argument and that is the reason we have used it twice, one with the argument and one without an argument.  For clarity purposes, we have given a different error code.  It also indicates that when an argument is passed to SQLERRM, it overrides the actual exception code.  FORMAT_ERROR_STACK procedure doesn’t take any argument.

Even though the procedure name indicates STACK, it has nothing to do with the stack. There are other procedures in dbms_utility package, which are used to create the stack trace of the error.  We will cover that in a future blog post.

Posted in Oracle | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 81 other followers