Re: Oracle packages

Hi,

Thanks for your replies.

I found that if I take the subqueries off the main query then it runs just fine with oracle 8.1.7 so it seems that it doesn't accept multiple "select" after OPEN CURSOR FOR. So I tried to make the query on a new view which returns all the columns I want but in that case the hierarchy returns by the CONNECT BY... START WITH... is not correct. I'm stuck.

Please help!

Thanks,
Olivier.

"Olivier Deborne" <odeborne_at_hotmail.com> wrote in message news:b0c018e3.0211210657.6c02b2e5_at_posting.google.com...
> Hello,
>
> I just created the following package and procedure:
>
> CREATE OR REPLACE PACKAGE RECIPE_STRUCTURE_PKG
> AS TYPE crType IS REF CURSOR;
> END RECIPE_STRUCTURE_PKG;
>
> /
>
> CREATE OR REPLACE PROCEDURE RECIPE_STRUCTURE_RPT (
> crCursor IN OUT RECIPE_STRUCTURE_PKG.crType,
> pPART_NO IN RECIPE_STRUCTURE.PART_NO%TYPE)
> AS
> BEGIN
>
> OPEN crCURSOR FOR
>
> SELECT level,
> PART_NO,
> (SELECT DESCRIPTION FROM INVENTORY_PART WHERE PART_NO =
> RECIPE_STRUCTURE.PART_NO) PART_DESCRIPTION,
> LINE_SEQUENCE,
> COMPONENT_PART,
> (SELECT DESCRIPTION FROM INVENTORY_PART WHERE PART_NO =
> RECIPE_STRUCTURE.COMPONENT_PART) COMPONENT_DESCRIPTION,
> QTY_PER_ASSEMBLY,
> (SELECT LEAD_TIME_CODE FROM INVENTORY_PART WHERE PART_NO =
> RECIPE_STRUCTURE.COMPONENT_PART) LEAD_TIME_CODE
> FROM
> RECIPE_STRUCTURE
>
> START WITH RECIPE_STRUCTURE.PART_NO = pPART_NO
>
> CONNECT BY PRIOR RECIPE_STRUCTURE.COMPONENT_PART =
> RECIPE_STRUCTURE.PART_NO;
>
> END RECIPE_STRUCTURE_RPT;
>
> /
>
> It compiles just fine on my server (ora 90) but on another server
> which runs Oracle 8.0.1.7 I get the following error:
>
> PLS-00103: Encountered the symbol "SELECT" when expecting one of the
> following:
>
> ( - + mod not null others <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> avg
> count c
>
> Any idea someone?
>
> Thanks for your help.
>
> Olivier Deborne.
Received on Thu Nov 21 2002 - 16:12:59 CST