This discussion is archived
1 Reply Latest reply: Dec 26, 2012 9:28 PM by Prabodh RSS

Sub-selects in forms causing ora-24333

661403 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points