Re: ORA-29506 when compiling java

On Aug 6, 10:52 pm, Dan <daniel.oster..._at_visaer.com> wrote:
> I have a 9i Release 1 database that is getting the following error
> when compiling java functions.
> ----------------
> CREATE OR REPLACE AND COMPILE NOFORCE JAVA SOURCE NAMED Model_Scorer_1
> USING CLOB SELECT SOURCE FROM PROSPECT_MODEL WHERE ID =
> '40288114141fa66201141fa670b20001';
>
> *
> ERROR at line 1:
> ORA-29506: invalid query derived from USING clause
> ORA-00936: missing expression
> -----------------
>
> I looked into this error and a site recommended changing any auto
> space management tablespaces to manually managed for 9.1. I already
> have the TS's as manually managed, so this isn't the problem.
>
> Does anyone have any ideas? Can 9.1 handle CLOB's at all? Is there a
> way I can fix this in 9.1 (I know I should upgrade...)?
>
> Thanks,
> Dan

Hmm... Which site recommended this irrelevant nonsense? ASSM has nothing to do with either of the reported errors. The documentation says:

The USING clause implicitly supplies the keyword SELECT. Therefore, omit this keyword from the subquery.

So the right statement would be

CREATE OR REPLACE AND COMPILE NOFORCE JAVA SOURCE NAMED "Model_Scorer_1"
USING CLOB SOURCE FROM PROSPECT_MODEL WHERE ID = '40288114141fa66201141fa670b20001';

Note that I also surrounded the name with double quotes - otherwise Oracle will convert it to uppercase, which you probably don't want to happen.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Aug 07 2007 - 12:44:18 CDT