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....