Skip to Content
Technical Articles

SAP Advanced SQL Migration. PL/SQL DBMS_OUTPUT package conversion for SAP Hana

    You have probably heard about the SQL dialects conversion tool called SAP Advanced SQL Migration developed by SAP, this tool helps you to migrate non-SAP SQL code to SAP SQL code and this is the first of a series of technical blogs that will describe internal details about how that SAP tool works and how the conversion for some special functionalities/aspects from the distinct supported Database Management Systems are being implemented.

    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)

          hdbsql SP4=>

 

More SAP Advanced SQL Migration tool tips coming soon …

 

Other related posts:

 

Be the first to leave a comment
You must be Logged on to comment or reply to a post.