Code:
INSERT INTO gtt_facility
  SELECT get_list
          (CURSOR (SELECT f.datarange
                        FROM facility f
                    WHERE f.credit_id =
                                               af.credit_id
                         AND f.facility_id = af.facility_id )
             ) AS datarange
     FROM authorized_facility af
    WHERE af.credit_id = :p_cred_app_id
In the above insert statement, the cursor returns nothing.
But this statement is executing successfully in the 9i environment.

If we try to execute the same code in 10g,

The error "ORA-06503: PL/SQL: Function returned without value" pointing to the function call "get_list" is thrown.


Our DBA suggested to set the optimizer_features_enable parameter from 10.2 to 9.2 using the below query,
alter system set optimizer_features_enable="9.2.0";

I hav the following questions.

  • why do this error occurs in 10g alone but not in 9i?
  • is this one, the right solution for this problem or
  • is there any alternative solution other than this??