1 Reply Latest reply: Dec 26, 2012 11:28 PM by Prabodh RSS

    Sub-selects in forms causing ora-24333

    661403
      We are running in a new 11g environment and are receiving an ora-24333 from forms with sub-selects. The same form works in 10g and the sql statement, itself, works in sqlplus. The following is an example of code

      exec_sql.parse(oracle_handle, oracle_cursor,
      'update vendor a
      set (status, vendor_type) =
      (select decode(rec_sttus,'||''' '''||','||'''ACTIVE'''||',
      '||'''I'''||','||'''INACTIVE'''||',
      '||'''D'''||','||'''DELETE'''||',
      '||'''F'''||','||'''FOREIGN'''||',
      '||'''N'''||','||'''RENUMBERED'''||',
      '||'''K'''||','||'''KEEPER'''||',
      rec_sttus),
      vndr_type
      from prch_vndr b
      where a.vendor_no = b.vndr_num)
      where vendor_no in
      (select vndr_num
      from prch_vndr)');

      t_temp := exec_sql.execute(oracle_handle, oracle_cursor);
      exec_sql.parse(oracle_handle, oracle_cursor, 'commit');
      t_temp := exec_sql.execute(oracle_handle, oracle_cursor);

      Any ideas where we might have a configuration setting missing or what has changed with 11g forms of which we are unaware?

      Edited by: kwalker on Dec 26, 2012 2:00 PM

      Edited by: kwalker on Dec 26, 2012 2:00 PM
        • 1. Re: Sub-selects in forms causing ora-24333
          Prabodh
          Hi Kelly,
          We are running in a new 11g environment and are receiving an ora-24333 from forms with sub-selects. The same form works in 10g and the sql statement, itself, works in sqlplus. The following is an example of code
          Always post code snippets in {code} tags as explained in FAQ.
          >
          exec_sql.parse(oracle_handle, oracle_cursor,
          'update vendor a
          set (status, vendor_type) =
          (select decode(rec_sttus,'||''' '''||','||'''ACTIVE'''||',
          '||'''I'''||','||'''INACTIVE'''||',
          '||'''D'''||','||'''DELETE'''||',
          '||'''F'''||','||'''FOREIGN'''||',
          '||'''N'''||','||'''RENUMBERED'''||',
          '||'''K'''||','||'''KEEPER'''||',
          rec_sttus),
          vndr_type
          from prch_vndr b
          where a.vendor_no = b.vndr_num)
          where vendor_no in
          (select vndr_num
          from prch_vndr)');
          
          t_temp := exec_sql.execute(oracle_handle, oracle_cursor);
          exec_sql.parse(oracle_handle, oracle_cursor, 'commit');
          t_temp := exec_sql.execute(oracle_handle, oracle_cursor);
          Any ideas where we might have a configuration setting missing or what has changed with 11g forms of which we are unaware?>
          a. EXEC_SQL package for simple updates looks like an overkill. EXECUTE IMMEDIATE will meet the requirement.
          b. The ORA-24333 error is related to data. Is the database and schema for 10g, SQLDeveloper and 11g the same? If they are different then check data in the database/schema used with Forms 11g.

          Cheers,