Database and Instance in Oracle and SQL Server

http://channel9.msdn.com/posts/SQL-Server-Essentials-for-Oracle-DBAs-01-Database-and-Instance

Good and very informational video. Hope they don't mind me sharing knowledge :)

SP_HELPDB

sp_helpdb function returns information about that databases in the particular database server. Below is an example:

SP_EXECUTESQL expects NTEXT/NCHAR/NVARCHAR

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Pretty straight forward error, just change the datatype of the variable you are using to assign the dynamically generated string.

Hope this helps....

PL/SQL Collections

This is one of those topics i used to dread when i started out as a database developer, not anymore. But still from time to time i need to refresh my memory when it comes to collections:

I recently started reading a book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" and it describes collections in such a way that i have to make a post of it. It is precise, with an example and perfect read for me:

Below are a few pointers that i usually like to remember:

Associative Arrays (TABLE OF)

• AKA Index by tables
• Of course use an index by BINARY_INTEGER or PLS_INTEGER or VARCHAR2
• Sparse collection
• Need not be consecutive
• Unbounded - no upper boundary
• Collection is extended by assigning values to non existing index values
• Data can be deleted
• Start with FIRST method to access data

Nested Tables (TABLE OF)

• No index
• Unbounded colleciton
• Dense collection when creating collection
• Data can be deleted
• NEXT and PRIOR (?) helps access collection elements

VARRAYS (VARRAY OF)

• No index
• Bounded collection so cannot be extended above the specified limit
• Data cannot be deleted
• Dense data collection
• Consecutive data
• Start with FIRST method to access data

Methods that can be used with collections. Not all methods can be used with all collections:

• EXISTS - exists(v) - returns boolean
• DELETE - delete, delete(v1) and delete(v1,v8) - removes data
• COUNT - returns number
• TRIM - trim and trim (v) - removes data
• LIMIT - returns number
• EXTEND - extend, extend(v1) and extend(v1,v8) - adds NULL elements
• FIRST - returns index of first element
• LAST - returns index of last element
• PRIOR - prior(v) - returns index of prior element
• NEXT - next(v) - returns index of next element

Hope this helps....

Quick Tip #4

To see what SQL a session is running in the database, find the SPID for that user using sp_who2 and then pass it as an input to the dbcc command.


sp_who2

dbcc inputbuffer (:SPID)

NULL SELF Argument Is Disallowed - Oracle

ORA-30625: method dispatch on NULL SELF argument is disallowed 

Cause: A member method of a type is being invoked with a NULL SELF argument. 
Action: Change the method invocation to pass in a valid self argument.


Well i was trying to parse an XML with multiple namespaces and was using EXISTSNODE function. When traversing between nodes and subnodes i forgot to remove a subnode name and the XPATH formed was incorrect:

    l_xmltype := xmltype (l_doc) ;
    l_index := 1;
    v_count := l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo/subscriptionKey/subscriptionInfo [' || to_char (l_index
    ) || ']', v_namespaces);
    dbms_output.put_line ('------' || v_count);
    --
    WHILE l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || TO_CHAR (l_index
    ) || ']', v_namespaces) > 0
    LOOP
        l_value := l_xmltype.extract (
        'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || to_char (
        l_index) || ']/duration/text()', v_namespaces) .getstringval () ;
        dbms_output.put_line ('-------------> ' || l_value) ;
        --
        l_index := l_index + 1;

    END LOOP;

This error is same as the evil NULL POINTER EXCEPTION in JAVA....

Just correcting the subnode path (remove subscriptionInfo from EXISTSNODE) to represent right structure helped resolve this problem.

Many thanks to A_Non On XML for his detailed explanation and examples on this blog about XML parsing. I hope he does not mind a well deserved praise.

PLSCOPE_SETTINGS in Oracle

Today i was working on an object creation script and had a trigger for audit columns like date inserted, date updated etc...

I constantly kept getting the error below and on further research found out a workaround from one of the blogs (thank you fellow blogger):


Error report:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be created
Failed to resolve object details 

Workaround: ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

By default the value is IDENTIFIERS:ALL 
The database default value is NONE and later found out it was SQL Developer setting.


After altering the session the trigger was compiled without any problems....hope this 
helps.

PL/SQL Compiler Setting in SQL Developer

Google is my friend :)

SYS_CONTEXT in Oracle

I know there is a functions to get user environment details in Oracle along with other valuable information for auditing purposes in your application. But i always tend to forget it, not anymore

SYS_CONTEXT (NAMESPACE, PARAMETER)       <<<< LOOK MA I AM PINK

Example: select sys_context('USERENV', 'HOST') from dual;

SET DEADLOCK_PRIORITY HIGH

Incase you are expecting a DEADLOCK in an environment where your process/job is going to run executing DML statements and you are fully aware that the process take priority over all other processes/jobs, you can set its priority to HIGH.

This specifies the rank/priority of your process higher than any other process when deadlock is detected.

Deadlock priority is set before the TRY block in the procedure.

Hope this helps....

Error: CREATE/ALTER PROCEDURE must be the first statement in a query batch

I had not come across this error before so was a new one for me. 

declare @cmd varchar(200)
set @cmd = 'sp_rename myproc1, myproc'
exec @cmd
GO

create procedure myproc (input_id int)
as
begin
  if input_id = 0
  begin
    print 'i am in begin block #2'
  end
end

The GO indicates end of batch or acts as a batch separator was missing from the script and as a result the error was generated. Once i added the GO statement, the script executed like a happy little monster....

Hope this helps....

DATEADD and DATEDIFF

I always try to remember these functions but for some reason end up going to MSDN to find the actual function name and syntax.

DATEADD (datepart , number , date )
select DATEADD (mm, 1, getdate())

DATEDIFF ( datepart , startdate , enddate )
select DATEDIFF (hh, getdate(), '2013-04-16 00:11:11.00')

Table below is from MSDN, just to you everyone an overview of possible option to use 
with these functions (along with any other DATE functions)

    

   
datepart
   
abbreviations
  
   
year
   
yy,   yyyy
  
   
quarter
   
qq,   q
  
   
month
   
mm,   m
  
   
dayofyear
   
dy,   y
  
   
day
   
dd,   d
  
   
week
   
wk,   ww
  
   
hour
   
hh
  
   
minute
   
mi,   n
  
   
second
   
ss,   s
  
   
millisecond
   
ms
  
   
microsecond
   
mcs
  
   
nanosecond
   
ns
  

Hope this helps and as always good is my friend....

Retrieve table structure over linked server

exec linkedserver.master.dbo.sp_executesql N'use mydb; exec sp_help order_details'

OR


exec linkedserver master.dbo.sp_executesql N'use mydb; SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''order_details'''

Hope this helps....

Set the PAGE_VERIFY Database Option to CHECKSUM

PAGE_VERIFY rule checks if the database option is set to CHECKSUM, if yes, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. 

When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

This is just a copy paste from MSDN and is one of the important things to remember when migrating to newer version (2008 and above) of SQL Server.

Hope this helps....

SQL Server Registry Read

Good built utility in SQL Server (i am using 2008 R2) to read registry values.

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

select @tcp_port 

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

Dynamic Sort By in SQL Server

Sort by column is sent as an input to the stored procedure and the sort by field can be different during different web service calls.

Sorting based on different fields at runtime does not need you to generate SQL statements with sort field appended and then execute them dynamically. It can be done as follows:

use mydb
go


set ansi_nulls on
go
set quoted_identifier off
go


create procedure dbo.get_account_info
(@sort_by_column varchar(255))
as
begin

set nocount on

select account_id, name_id, address_id, start_date, end_date, first_name, middle_name, last_name
from dbo.accounts
where status = 'A'

order by
(case @sort_by_column
when 'last_name' then m.last_name
when 'first_name' then first_name
when 'account_id' then account_id
when 'end_date' then end_date
when 'start_date' then start_date
else account_id
end)

end

Hope this helps.