SSC-PRF-0006

Nested cursor inside query is not supported in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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 [email protected]