SSC-PRF-0006

Nested cursor inside query is not supported in Snowflake

Severity

None

Description

This message is shown when a cursor definition exists within a query. A cursor expression returns a nested cursor, a nested cursor is implicitly opened when the cursor expression is evaluated. For more information please refer to Oracle Cursor Expression.

Code examples

Input

IN -> Oracle_01.sql

SELECT
  category_id,
  category_name,
  CURSOR (
    SELECT
      product_id,
      product_name || ', ' || category_id
    FROM
      products e
    WHERE
      e.category_id = d.category_id
  ) EMP_CUR
FROM
  categories d;

Output

OUT -> Oracle_01.sql

SELECT
  category_id,
  category_name,
  --** SSC-PRF-0006 - NESTED CURSOR INSIDE QUERY IS NOT SUPPORTED IN SNOWFLAKE. **
  CURSOR
    !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (
    SELECT
      product_id,
      NVL(
      product_name :: STRING, '') || ', ' || NVL(category_id :: STRING, '')
    FROM
      products e
    WHERE
      e.category_id = d.category_id
  ) EMP_CUR
FROM
  categories d;

Recommendations

• Avoid them whenever possible due to their potential impact on performance and code complexity.

• Remove the use of nested cursors, instead, you can use SQL functions, joins, subqueries, window functions, common table expressions (CTEs), recursive queries, and other features to process bulk data and avoid the need for nested cursors.

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