Re: Oracle packages

odeborne_at_hotmail.com (Olivier Deborne) wrote in message news:<b0c018e3.0211210657.6c02b2e5_at_posting.google.com>...
> 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

 From Oracle9i (9.0.1) you can use the complete range of syntax of SQL inside a PL/SQL code (procedure, function, package and anonymous block). This is not true for previous releases, and most likely the problems with your procedure are the 'SQL statements' inside the SELECT list and 'START BY' clause; I am not sure which one, or it could be both of them.

Mario from Rome. Received on Thu Nov 21 2002 - 15:28:34 CST