BINARY_INTEGER versus PLS_INTEGER

BINARY_INTEGER is derived from INTEGER and can be assigned negative as well as positive values. Same is true for PLS_INTEGER.

In earlier versions of Oracle 8i and 9i botth these were different and PLS_INTEGER was supposed to be faster than BINARY_INTEGER when it comes to arithmetic calculations.

But after 10g both can be used in the same way without sacrificing time.

Google is my friend...

NUMTODSINTERVAL usage in Oracle

I had used this function sometime back and had totally forgotten about it. Used it again in a procedure where i had to subtract minutes value stored in a cursor variable (or local variable) of a procedure from systimestamp and compare it against created_date of a table column.

select count(*) from my_db.my_table 
where created_date > (systimestamp - numdstointerval (cursor_loop_rec.minutes, 'minute'));

In this if count > 0 then instances have been created for that particular composite in the required time frame.

Hope this helps...

Nested Select in SQL Server

Hope this works as expected as i wanted an alternative to creating temp table to get the distinct values.


select top 100 * from (
select distinct a.account_id, 
                                     a.account_type as newapp_at, 
                                     c.account_type as myapp_at,    
                                     c.acct_modified_date 
from dbo.account a (nolock), dbo.account_xref b (nolock), 
                        dbo.account_stage_arch c (nolock)
where a.account_id = b.account_id
    and b.source_key = c.source_key
    and isnull (a.account_type, '') = ''
    and isnull (c.account_type, '') = ''
    and isnull (b.end_date, '') = ''
    and b.source_system = 'MYAPP') x
order by acct_modified_date desc;

Well i can also use the RANK function to get it done.

Hope this helps....

ANALYZE INDEX and INDEX_STATS

ANALYZE INDEX myidx1 VALIDATE STRUCTURE;

SELECT * FROM INDEX_STATS;

INDEX_STATS only stores index analysis of the last performed ANALYZE INDEX statement.

You can find valuable information about any index in this table and can be used for various purposes. 

I used it to determine the fragmentation on an index, it can be determined if the index needs to be rebuilt or not.

1. If del_lf_rows is less than the estimated record count in the table

2. if lf_rows is less than the lf_blks

3. if height value is greater than 4

In all the cases above the index needs to be rebuilt....

Item cannot be found in the collection

SQL Server 2000

Error: Item cannot be found in the collection corresponding to the requested name or ordinal.

Resolution: Check field names and match them against table column names, i had mis-spelled one of them. One other reason might be the field name does not exist in the database.

Server not configured for RPC

Received this error message today when trying to execute an update statement out a linked server:

The RPC Out parameters value was set to False which must be changed to Ture (picture above) and it worked without any errors.

Hope this helps....

sp_delete_jobschedule SQL Server

I had to create a deployment script today which included modifications to an existing job. Ideally the normal process requires taking backup of the existing object and then implementing the modified object.

Could not find a built in function to rename the job, so my approach was to delete schedule of the existing job and create a new job with a different name (appended v2 i.e. version 2) with the same schedule.

sp_delete_jobschedule 
    @job_name = N'my existing job name', 
    @name = N'existing schedule name'

sp_xml_preparedocument and sp_xml_removedocument in SQL Server

Got a chance of using the 2 built in functions below:

sp_xml_preparedocument

sp_xml_removedocument

Implementation: Input to a the procedure is a text datatype which contains XML with account_id's and we just needed the list of account_id's in our procedure.

CREATE PROCEDURE dbo.get_account_info
(account_list text)

AS

BEGIN


DECLARE @ATempTable TABLE    
 (  
  [id] varchar(30) NULL  
 )  


 DECLARE @doc_out int 


 EXEC sp_xml_preparedocument @doc_out OUTPUT, @account_list    
  
 INSERT INTO @ATempTable ([row_id])  
 SELECT [id] FROM OPENXML (@doc_out, '/root/account', 1) WITH (id varchar(30))  
  
 EXEC sp_xml_removedocument @doc_out

SELECT @doc_out

END