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