SSC-FDM-OR0009

SQL implicit cursor values may differ.

Description

Generate Procedures and Macros using JavasScript as the target language adding the following flag -t JavaScript or --PLTargetLanguage JavaScript

Some parts in the output code are omitted for clarity reasons.

This EWI is shown when SQL implicit cursor value is used. This is because Oracle uses different values depending on the type of query. For example, for SELECT the value used to set SQL implicit cursor values are the number of rows returned by the query. When the query type is UPDATE/CREATE/DELETE/INSERT the value used is the number of rows affected, this is the main reason why this EWI is displayed.

Example Code

Input Code:

IN -> Oracle_01.sql

-- Additional Params: -t JavaScript
--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE AUTHID DEFINER IS
  stmt_no  POSITIVE;
BEGIN
  IF SQL%ROWCOUNT = 0 THEN
   EXIT ;
  END IF;
  IF SQL%ISOPEN THEN
   EXIT ;
  END IF;
  IF SQL%FOUND THEN
   EXIT ;
  END IF;
  IF SQL%NOTFOUND THEN
   EXIT ;
  END IF;
END;

Output Code:

OUT -> Oracle_01.sql

--Transformation for implicit cursor
CREATE OR REPLACE PROCEDURE SP_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  //AUTHID DEFINER
  !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlInvokerRightsClause' NODE ***/!!!
  null
  // SnowConvert Helpers Code section is omitted.

  let STMT_NO = new POSITIVE();
  if (SQL.ROWCOUNT /*** SSC-FDM-OR0009 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/ == 0) {
    break;
  }
  if (SQL.ISOPEN) {
    break;
  }
  if (SQL.FOUND) {
    break;
  }
  if (SQL.NOTFOUND) {
    break;
  }
$$;

Recommendations

• No additional user actions are required.

• If you need more support, you can email us at [e-mail]

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review.