Code Conversion - Oracle Performance Review Messages¶

CREATE OR REPLACE PACKAGE BODY schema_test.pkg_test AS
    CURSOR c_emp IS
        SELECT emp_id, emp_name
        FROM employees;
    PROCEDURE process_all IS
        v_id   NUMBER;
        v_name VARCHAR2(100);
    BEGIN
        OPEN c_emp;
        FETCH c_emp INTO v_id, v_name;
        CLOSE c_emp;
    END process_all;
END pkg_test;

CALL PACKAGE_CURSOR.CREATE_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP"', 'SELECT emp_id, emp_name FROM schema_test.employees', 'SELECT emp_id, emp_name, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS _row_num FROM schema_test.employees');

CREATE OR REPLACE PROCEDURE SCHEMA_TEST_PKG_TEST.process_all ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    v_id NUMBER(38, 18);
    v_name VARCHAR(100);
  BEGIN
    --** SSC-FDM-OR0057 - CHECK UDF IMPLEMENTATION FOR PACKAGE_CURSOR.OPEN_CURSOR. **
    CALL PACKAGE_CURSOR.OPEN_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP"');
    --** SSC-PRF-OR0001 - PACKAGE_CURSOR.FETCH_CURSOR INCURS A TIME PENALTY ON EACH INVOCATION DUE TO INTERNAL STORED PROCEDURE EXECUTION OVERHEAD. TO REDUCE THE NUMBER OF CALLS AND IMPROVE PERFORMANCE, CONSIDER FETCHING ROWS IN BATCHES BY SETTING THE limit_per_fetch PARAMETER TO A VALUE GREATER THAN 1. **
    LET "SCHEMA_TEST_PKG_TEST.C_EMP.fetch_result_1" RESULTSET;
    "SCHEMA_TEST_PKG_TEST.C_EMP.fetch_result_1" := (
      CALL PACKAGE_CURSOR.FETCH_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP"')
    );
    LET "SCHEMA_TEST_PKG_TEST.C_EMP.batch_cursor_1" CURSOR
    FOR
      "SCHEMA_TEST_PKG_TEST.C_EMP.fetch_result_1";
    OPEN "SCHEMA_TEST_PKG_TEST.C_EMP.batch_cursor_1";
    FETCH
      "SCHEMA_TEST_PKG_TEST.C_EMP.batch_cursor_1"
    INTO
      v_id,
      v_name;
    CLOSE "SCHEMA_TEST_PKG_TEST.C_EMP.batch_cursor_1";
    CALL PACKAGE_CURSOR.CLOSE_CURSOR('"SCHEMA_TEST_PKG_TEST.C_EMP"');
  END;
$$;

CREATE OR REPLACE PROCEDURE dbms_output_put_proc
IS
BEGIN
    DBMS_OUTPUT.PUT('COLUMN: FRM_STATUS_G__C ');
END;

CREATE OR REPLACE PROCEDURE dbms_output_put_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    --** SSC-PRF-OR0002 - THE GENERATED DBMS_OUTPUT HELPER LOGS TO A TABLE ON EACH CALL, WHICH MAY IMPACT PERFORMANCE. REMOVE DIAGNOSTIC OUTPUT CALLS THAT ARE NOT REQUIRED. **
    CALL DBMS_OUTPUT.PUT_UDP('COLUMN: FRM_STATUS_G__C ');
  END;
$$;

CREATE TABLE "PORTFOLIO_ANALYTICS" ("PORTFOLIO_ID" NUMBER(20,0), "CASH_PCT" NUMBER, "BETA" NUMBER);

CREATE OR REPLACE PROCEDURE MD_PORT_ANALYTIC_POP_Q AS
  TYPE ANAL_UPLOAD_FIELDS_TAB IS TABLE OF "PORTFOLIO_ANALYTICS"%ROWTYPE;
  LV_ANAL_UPLOAD_FIELDS_TAB ANAL_UPLOAD_FIELDS_TAB;
BEGIN
  SELECT PORTFOLIO_ID, CASH_PCT, NULL AS BETA
    BULK COLLECT INTO LV_ANAL_UPLOAD_FIELDS_TAB
    FROM (
      WITH SRC AS (SELECT 10 PORTFOLIO_ID, 1.5 CASH_PCT FROM DUAL)
      SELECT PORTFOLIO_ID, CASH_PCT, NULL AS BETA FROM SRC
    );
  INSERT INTO "PORTFOLIO_ANALYTICS" ("PORTFOLIO_ID", "CASH_PCT", "BETA")
  VALUES (
    LV_ANAL_UPLOAD_FIELDS_TAB(1).PORTFOLIO_ID,
    LV_ANAL_UPLOAD_FIELDS_TAB(1).CASH_PCT,
    LV_ANAL_UPLOAD_FIELDS_TAB(1).BETA
  );
END;

CREATE OR REPLACE TABLE "PORTFOLIO_ANALYTICS" (
  "PORTFOLIO_ID" NUMBER(20) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
  "CASH_PCT" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
  "BETA" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
;

CREATE OR REPLACE PROCEDURE MD_PORT_ANALYTIC_POP_Q ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
--    --** SSC-FDM-OR0053 - EMBEDDED COLLECTION TYPE DEFINITION 'PL COLLECTION TYPE DEFINITION' WAS REMOVED. COLLECTION VARIABLES ARE TRANSFORMED TO ARRAY BY OTHER RULES. **
--    TYPE ANAL_UPLOAD_FIELDS_TAB IS TABLE OF "PORTFOLIO_ANALYTICS"%ROWTYPE;
    LV_ANAL_UPLOAD_FIELDS_TAB ARRAY /*** SSC-FDM-OR0084 - COLLECTION TYPE 'ANAL_UPLOAD_FIELDS_TAB' WAS MAPPED TO A SNOWFLAKE ARRAY; ELEMENT INDEXING WAS ADJUSTED FROM 1-BASED TO 0-BASED. ***/;
  BEGIN
    SELECT
      ARRAY_AGG(OBJECT_CONSTRUCT('PORTFOLIO_ID', PORTFOLIO_ID, 'CASH_PCT', CASH_PCT, 'BETA', NULL)) /*** SSC-PRF-OR0003 - BULK COLLECT WAS TRANSLATED TO ARRAY_AGG, WHICH MATERIALIZES THE ENTIRE RESULT SET INTO A SINGLE ARRAY. FOR LARGE RESULT SETS THIS MAY EXCEED SNOWFLAKE'S PER-VARIABLE MEMORY LIMIT; REVIEW THE EXPECTED VOLUME AND CONSIDER BOUNDING THE QUERY. ***/
    INTO
      :LV_ANAL_UPLOAD_FIELDS_TAB
    FROM
      (
        WITH SRC AS
        (
          SELECT
            10 PORTFOLIO_ID,
            1.5 CASH_PCT
          FROM
            DUAL
        )
        SELECT
          PORTFOLIO_ID,
          CASH_PCT,
          NULL AS BETA
        FROM
          SRC);
    INSERT INTO "PORTFOLIO_ANALYTICS"("PORTFOLIO_ID", "CASH_PCT", "BETA")
    SELECT
      :LV_ANAL_UPLOAD_FIELDS_TAB[0]:PORTFOLIO_ID,
      :LV_ANAL_UPLOAD_FIELDS_TAB[0]:CASH_PCT,
      :LV_ANAL_UPLOAD_FIELDS_TAB[0]:BETA;
  END;
$$;