SSMA for Oracle 8.21 unable to parse Oracle package

Question

Hi,

We are trying to migrate an Oracle schema to SQL Server using SSMA for Oracle.
Oracle version: 19c (also tried in 12c)
SSMA for Oracle version: 8.21
SQL Server version: 2016

We have two packages in Oracle.
Using the SSMA for Oracle GUI, I see that it can read both the specification and body for package_1.
But it reads only the specification for package_2.
The error message for package_2 is as follows:

==============================================
Oracle Server Object Collector error: package : package_2

No package statement was found in the header of package 'package_2'. This might be caused by a parsing error.

This was for our main Oracle database 19c.
I extracted the schema DDL from the main db and compiled it locally to our Test VM that has Oracle 12c.
SSMA throws the same error even with the local Oracle 12c.

Upon reviewing the Oracle code, I don't see anything dubious. The package was compiled successfully.
Only thing is - the package body is pretty huge - about 3800 lines.
Is there any limitation in SSMA that prevents it from parsing Oracle packages?
Any particular syntax that it cannot parse?

Thanks in advance.
Homebrew9

Answer 1

Hi @Homebrew9 ,

>Is there any limitation in SSMA that prevents it from parsing Oracle packages?

No, I search this from MS document, but did not find any related limits for this.

Suggest you using the latest version SSMA 8.24, if it is still not work, please check if any invalid syntax while creating a package in Oracle code. But I am not familiar with Oracle, suggest you putting your code in Oracle forum for help.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi,
Sorry for the late response. The fix for this issue was very simple.
We had multi-line comments in our Oracle package body, and SSMA version 8.21 could not parse it.
An illustrative sample is shown below:

   210
   211  CREATE OR REPLACE PACKAGE BODY SCHEMA_1.PACKAGE_2
   212  AS
   213     /**********************************************************
   214      Multiline comment here
   215      Multiline comment here
   216      Multiline comment here
   217      ...
   218      ...
   219      ...
   220      ...
   221     ***********************************************************/
   222     v_dummy   VARCHAR2(6);     --Single line comment 1                      --Single line comment 2
   223                                --Single line comment 3
   224
   225     PROCEDURE proc_1       (p_param1        VARCHAR2,
   226                             p_param2        BOOLEAN DEFAULT FALSE)
   227     IS

I stripped off blocks like the one from line 213 through 221 from the entire package spec and body and recompiled it in Oracle.
After that, SSMA was able to parse it and convert it to T-SQL.
Comments in the lines 222 and 223 were retained and were parsed correctly.

I have also noticed that SSMA "embeds" multi-line comments in its output sometimes.
Here's an illustrative sample from Oracle and its corresponding T-SQL generated by SSMA.

Oracle snippet

  2770        -- Single-line comment 1
  2771        v_var   := p_var;   --Single-line comment 2             --Single-line comment 3
  2772                            --Single-line comment 4
  2773        DBMS_OUTPUT.PUT_LINE (
  2774           'inside proc_1' || v_var);

Corresponding SQL-Server snippet

  9957           /* Single-line comment 1*/
  9958           EXECUTE ssma_oracle.set_pv_varchar 'SCHEMA_1', 'PACKAGE_2', 'V_VAR', @P_VAR/*Single-line comment 2 --Single-line comment 3*/
  9959
  9960           /*
  9961           *   SSMA error messages:
  9962           *   O2SS0560: Identifier DBMS_OUTPUT.PUT_LINE cannot be converted because it was not resolved.
  9963           *   This may happen because system package that defines the identifier was excluded from loading in Project Settings.
  9964
  9965           /*Single-line comment 4*/
  9966           EXECUTE DBMS_OUTPUT.PUT_LINE
  9967           */
  9968

This issue shows up quite often in SSMA error message templates.
Looks like SSMA always uses multi-line comment markers for Oracle's single-line comments.
For DBMS_OUTPUT, SSMA responds with the error template as a multi-line comment, but if there is a comment right above DBMS_OUTPUT, then that shows up as an "embedded" multi-line comment.