SQL Query With Pipelined Function in the Where Clause is Hanging (Doc ID 1435109.1)

Last updated on MARCH 21, 2012

Applies to:

PL/SQL - Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

When running SQL query which includes pipelined function on test database it's ok, but on production database it gets stucked and never ends.

The workaround for this behavior is to double the condition, which allow query to run properly and returns results immediately. This part of code solves the issue:

All databases are 10.2.0.5.

WHERE PD.ID IN (SELECT COLUMN_VALUE FROM TABLE (WPSUTILS.SPLIT ( /*IDS*/'592032' /*IDS*/, ',')))    
AND PD.ID IN (SELECT COLUMN_VALUE FROM TABLE (WPSUTILS.SPLIT ( /*IDS*/'592032' /*IDS*/, ','))) ---doubled condition
AND PD.ID = PDR.PLANDOCID

Running the following SQL in sqlplus returned "592032" successfully.

SELECT COLUMN_VALUE FROM TABLE (WPSUTILS.SPLIT ( /*IDS*/'592032' /*IDS*/, ',')); 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms