SnowConvert: Embedded Code Units Report¶

What is a Embedded Code Unit?¶

A Code Unit, as the name suggests, is the most atomic, standalone executable element. In most cases, these are statements, but they also include script files as well because those are executed as a single element.

So according to the previous definition, an embedded code unit is when a Code Unit is inside a Top Level Code Unit. For more information please refer to Top-Level Code Units Report.

Examples of Embedded Code Units¶

In the following section, we can see some examples of Embedded Code Units.

CREATE OR REPLACE PACKAGE my_package1 IS
    PROCEDURE outer_procedure(input_value NUMBER);
END my_package1;
/

CREATE OR REPLACE PACKAGE BODY my_package1 IS
    FUNCTION outer_function(value NUMBER) RETURN NUMBER IS
        BEGIN
            RETURN value * 2;
        END inner_function;

    PROCEDURE outer_procedure(input_value NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Result of inner function: ' || inner_function(input_value));
        DBMS_OUTPUT.PUT_LINE('Input Value: ' || input_value);
    END outer_procedure;
END my_package1;

Information in the Embedded Code Units Report¶

Example¶

Assume that the following CREATE PACKAGE in ORACLE SQL is located in its file called Oracle_01.sql.

CREATE OR REPLACE PACKAGE my_package1 IS
    PROCEDURE calculate_salary(emp_id IN NUMBER);
END my_package1;
/

CREATE OR REPLACE PACKAGE BODY my_package1 IS
    PROCEDURE calculate_salary(emp_id IN NUMBER) IS
        emp_name VARCHAR2(100);
        emp_salary NUMBER;
    BEGIN
        SELECT name, salary INTO emp_name, emp_salary FROM employees WHERE employee_id = emp_id; 
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id);
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
        DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
    END calculate_salary;
END my_package1;

CREATE SCHEMA IF NOT EXISTS my_package1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees" **
CREATE OR REPLACE PROCEDURE my_package1.calculate_salary(emp_id NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        emp_name VARCHAR(100);
        emp_salary NUMBER(38, 18);
    BEGIN
        SELECT name, salary INTO
            :emp_name,
            :emp_salary
        FROM
            employees
        WHERE employee_id = :emp_id;
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF('Employee ID: ' || NVL(:emp_id :: STRING, ''));
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF('Employee Name: ' || NVL(:emp_name :: STRING, ''));
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        CALL DBMS_OUTPUT.PUT_LINE_UDF('Employee Salary: ' || NVL(:emp_salary :: STRING, ''));
    END;
$$;

The Embedded Code Units report will have only one embedded procedure.

Here are all the values that would be reported in the entry of this embedded procedure inside the package:

• The Partition Key value will depend on migration so the value here will vary.

• The File Type will be SQL because it was migrated on a file with the .sql extension.

• The ParentCategory will be PACKAGE BODY because the PACKAGE BODY is the top level code unit that contains the embedded procedure.

• The ParentID will be my_package1 because is the top level code unit name that contains the embedded procedure.

• The Category for the embedded procedure will be PROCEDURE because the CREATE PROCEDURE statement is part of the PROCEDURE Code Unit Category.

• The Code Unit itself will be CREATE PROCEDURE.

• The Code Unit Name will be calculate_salary(NUMBER).

• The File Name where this code unit was found would be Oracle_01.sql.

• Assuming that the CREATE PROCEDURE statement is in the PACKAGE BODY DEFINITION, the Line Number will be 8.

• The Lines of Code number would be 9.

• The EWI Count column will report 0 because the output code does not have EWIs.

• The FDM Count column will report 3 because the output code has three FDM related to the UDFs that were added to the output code.

• The PRF Count column will report N/A because the output code does not have PRFs.

• The Highest EWI Severity in this case would be “N/A” because there are no EWIs.

• The UDFs Used column will be DBMS_OUTPUT.PUT_LINE_UDF because this custom User Defined Function was added to convert the DBMS_OUTPUT.PUT_LINE.

• The EWI column will show N/A because there are no EWI issues.

• The FDM column will show “SSC-FDM-OR0035” in this case.

• The PRF column will show N/A because there are no PRF issues.

• The Conversion Status will be “Success” .

• The LoC Conversion Percentage is 100% because all the lines were converted successfully.