DBMS_APPLICATION_INFO
Note: You should test and pick which works best or which combinations of these work best for your needs. Test thoroughly and compare results while ensuring none of these cause overhead issues with the SQL Server instance. For example SQL Profiler is resource intense so use caution.
Based on the general description of the DBMS_APPLICATION_INFO package functionality per the Oracle docs, a few things with SQL Server will provide equivalent functionality. I'll list a few below for you to dig into with more detail and for a starting point, etc.
DBMS_APPLICATION_INFO
Application developers can use the DBMS_APPLICATION_INFO package with
Oracle Trace and the SQL trace facility to record names of executing
modules or transactions in the database for later use when tracking
the performance of various modules and debugging.
Source
The SQL Server DBMS_APPLICATION_INFO equivelants. . .
DBMS_SESSION
Based on the general description of the DBMS_SESSION package functionality per the Oracle docs, use ALTER EVENT SESSION and ALTER ROLE or sp_addrolemember for providing equivalent functionality with SQL Server.
DBMS_SESSION
This package provides access to SQL ALTER SESSION and SET ROLE
statements, and other session information, from PL/SQL. You can use
DBMS_SESSION to set preferences and security levels.
Source
DBMS_OUTPUT
Based on the general description of the DBMS_OUTPUT package functionality per the Oracle docs, you can just use PRINT or SELECT to output the message and write the logic accordingly. Without getting into all the detail of the DBMS_OUTPUT Subprograms one by one, it seems that's the general and basic functionality it provides is controlling output and debug output of stored procedures and such.
DBMS_OUTPUT
The DBMS_OUTPUT package enables you to send messages from stored
procedures, packages, and triggers. The package is especially useful
for displaying PL/SQL debugging information
Additionally and for example, according to a response on the "How to Print the Value of a Variable in SQL Server" post it is stated and sounds right to me too based on my past MSSQL usage. . .
I don't think there is a straight up SQL equivalent. I don't know of
any direct capabilities for leaving a list for a trigger or another
routine. Usually, I would just use a #temp table or function
parameters to pass intermediate results.
If you want to see intermediate results for edit purposes, the PRINT
function works - e.g. in a stored procedure, it's a common debugging
routine to insert print statements to show intermediate results in the
edit environment.
Source
Further Resources
