Re: strange PL/SQL Error

Thanks!

So, there is really NO way to use MINUS inside a stored Procedure? The Problem is, that NOT EXISTS and NOT IN aren't as fast as MINUS is. Therefore I would prefer MINUS.
Sebastian

On Sun, 29 Jul 2001 19:18:25 +0100, "Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote:

>The problem is almost certainly because the PL/SQL engine has its own
>(backlevel) SQL interpreter that doesn't properly support inline views. You
>can rewrite the query to use not in or not exists e.g.
>
>INSERT INTO CUSTOMERDATA.NEW_ASSETS (LOCATION, NEUEASSETSOI, VERS)
>SELECT SA_ANLAGENKENNZ||OBJEKTTABELLE.SA_SUB||'-'||OBJEKTTABELLE.SA_SU,
>ROWNUM, 1
>FROM OBJEKTTABELLE
>WHERE SA_ANLAGENKENNZ||OBJEKTTABELLE.SA_SUB||'-'||OBJEKTTABELLE.SA_SU NOT IN
> (SELECT LOCATION FROM MNT.ASSET));
>
>
>INSERT INTO CUSTOMERDATA.NEW_ASSETS (LOCATION, NEUEASSETSOI, VERS)
>SELECT SA_ANLAGENKENNZ||OBJEKTTABELLE.SA_SUB||'-'||OBJEKTTABELLE.SA_SU,
>ROWNUM
>FROM OBJEKTTABELLE
>where not exists
> (SELECT * FROM MNT.ASSET where asset.location = SELECT
>OBJEKTTABELLE.SA_ANLAGENKENNZ||OBJEKTTABELLE.SA_SUB||'-'||OBJEKTTABELLE.SA_S
>U);
>
>
>
>
>"Sebastian Scholz" <e9926043_at_student.tuwien.ac.at> wrote in message
>news:3b6420e2.5781723_at_news.tuwien.ac.at...
>> Hello,
>>
>> this is really strange: This Insert works well when I'm at the
>> Console:
>>
>> INSERT INTO CUSTOMERDATA.NEW_ASSETS (LOCATION, NEUEASSETSOI, VERS)
>> SELECT CODE, ROWNUM, 1 FROM
>> ((SELECT
>> (SA_ANLAGENKENNZ||OBJEKTTABELLE.SA_SUB||'-'||OBJEKTTABELLE.SA_SU) AS
>> CODE FROM OBJEKTTABELLE)
>> MINUS
>> (SELECT LOCATION FROM MNT.ASSET));
>>
>>
>> However, I need this Code inside a stored Procedure.
>> But I get this Message from the Compiler:
>>
>> PLS-00707: unsupported construct or internal error [num]
>> Cause: At run time, this is an internal error. At compile time, it
>> indicates one of the following problems:
>> A call was made to a remote subprogram that has a parameter type or
>> default expression not supported at the calling site.
>> An incomplete upgrade or downgrade was done to a database that has
>> stored procedures. Perhaps incorrect versions of system packages such
>> as STANDARD.SQL were installed.
>> A compiler bug was encountered. In such cases, legal PL/SQL syntax
>> will fail to compile.
>>
>> I'm using Oracle Version 8.0.5 .
>>
>> Please help!
>>
>> Thank you in advance,
>> Sebastian
>>
>>
>>
>>
>>
>
>
  Received on Mon Jul 30 2001 - 08:53:03 CDT