In this blog I am going to describe how SAP Advanced SQL Migration tool performs the migration for an Oracle functionality not existing in any of the supported SAP target Databases. This functionality has been implemented developing an specific set of Run Time Components (see RTCs for further details) to mimic Oracle behavior.
DBMS_OUTPUT is an Oracle built-in package that allows you to display output messages or debugging information from PL/SQL blocks (stored procedures, functions, triggers …etc.), it works with an internal buffer where Oracle clients can send messages to and recover message from. The API provided by this package includes the following subprograms:
An important thing to notice is that some oracle clients can bypass the “buffered” behavior provided by this Oracle functionality with specific options, for example SQL*Plus allows you to enable DBMS_OUTOUT and recover the messages from the buffer immediately without buffering setting ‘serveroutput’ to ‘on’:
NOTE: PUT_LINE acts as an immediate “print” into the client console in this case.
Oracle DBMS_OUTPUT functionality been implemented in each of the supported SAP Advanced SQL Migration targets database (SAP HANA, SAP ASE, SAP IQ and SAP SQL Anywhere) using a set of stored procedures, that set consists of some internal procedures (not directly used by the converted code) plus seven procedures, one corresponding to each of the subprograms provided by Oracle in its API. The mapping between Oracle subprograms and target procedures is described below, the procedure definitions indicated correspond to SAP HANA:
NOTE: the procedure definitions for SAP SQL Anywhere and SAP IQ are totally similar, in ASE there is a small difference, the prefix for the names is ‘xp_’ instead of ‘sp_’
These stored procedures are provided by SAP Advanced SQL Migration and the tool already performs this conversion for you using them, manual intervention is not required to convert PSQ/SQL code using DBMS_OUTPUT package.
The way to mimic Oracle “buffered”/”unbuffered” implementation is to use the “mode” parameter that has been included in “sp_dbmtk_enable_buffered_output”, by default it is configured to ‘print’ what means buffer bypassing, if you set that mode parameter to “buffer” then you get the “buffered” behavior:
NOTE: DBMS_OUTOUT is also converted to SAP ASE, SAP SQL Anywhere and SAP IQ with similar RTCs.
Other related posts:
DBMS_OUTPUT is an Oracle built-in package that allows you to display output messages or debugging information from PL/SQL blocks (stored procedures, functions, triggers …etc.), it works with an internal buffer where Oracle clients can send messages to and recover message from. The API provided by this package includes the following subprograms:
An important thing to notice is that some oracle clients can bypass the “buffered” behavior provided by this Oracle functionality with specific options, for example SQL*Plus allows you to enable DBMS_OUTOUT and recover the messages from the buffer immediately without buffering setting ‘serveroutput’ to ‘on’:
SQL> set serveroutput on
SQL> call DBMS_OUTPUT.PUT_LINE ('HELLO');
HELLO
Call completed.
SQL>
NOTE: PUT_LINE acts as an immediate “print” into the client console in this case.
Oracle DBMS_OUTPUT functionality been implemented in each of the supported SAP Advanced SQL Migration targets database (SAP HANA, SAP ASE, SAP IQ and SAP SQL Anywhere) using a set of stored procedures, that set consists of some internal procedures (not directly used by the converted code) plus seven procedures, one corresponding to each of the subprograms provided by Oracle in its API. The mapping between Oracle subprograms and target procedures is described below, the procedure definitions indicated correspond to SAP HANA:
NOTE: the procedure definitions for SAP SQL Anywhere and SAP IQ are totally similar, in ASE there is a small difference, the prefix for the names is ‘xp_’ instead of ‘sp_’
These stored procedures are provided by SAP Advanced SQL Migration and the tool already performs this conversion for you using them, manual intervention is not required to convert PSQ/SQL code using DBMS_OUTPUT package.
The way to mimic Oracle “buffered”/”unbuffered” implementation is to use the “mode” parameter that has been included in “sp_dbmtk_enable_buffered_output”, by default it is configured to ‘print’ what means buffer bypassing, if you set that mode parameter to “buffer” then you get the “buffered” behavior:
C:\>hdbsql -u SYSTEM -p *** -n :30015 -d SP4 -m -cgo -separatorownline
Welcome to the SAP HANA Database interactive terminal.
Type: \h for help with commands
\q to quit
hdbsql SP4=> call sapdbmtk.sp_dbmtk_enable_buffered_output('print')
> go
0 rows affected (overall time 29,590 msec; server time 14,172 msec)
hdbsql SP4=> call sapdbmtk.sp_dbmtk_buffered_output_writeln ('HELLO')
> go
CAST('HOLA' AS VARCHAR(10000))
"HELLO"
1 row selected (overall time 114,418 msec; server time 5828 usec)
NOTE: DBMS_OUTOUT is also converted to SAP ASE, SAP SQL Anywhere and SAP IQ with similar RTCs.
Other related posts:
- SAP Managed Tags:
