We are using Oracle 8i.
OK gnarus... here she is:
Here is the body of my CREATE PROCEDURE:
CREATE OR REPLACE
PROCEDURE cmpgnmp_stat_bydis (i_campgn_id IN VARCHAR2
,io_mp_cur IN OUT manpower_figures_pkg.mp_cur_type)
IS
BEGIN
OPEN io_mp_cur FOR
SELECT bsaorg_id,
bsaorg_name,
parent_bsaorg_id,
parent_bsaorg_name,
par_borgtyp_code,
giving_cat_name,
council,
subcouncil,
servicearea,
district,
subdistrict,
SUM(positions_filled) positions_filled,
SUM(positions_needed) positions_needed
FROM (SELECT borg.bsaorg_id bsaorg_id,
borg.name bsaorg_name,
borg.parent_bsaorg_id parent_bsaorg_id,
par_borg.name parent_bsaorg_name,
par_borg.borgtyp_code par_borgtyp_code,
cgc.campgiv_name giving_cat_name,
(SELECT MAX (DECODE(borgtyp_code,'CNCL', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) council,
(SELECT MAX (DECODE(borgtyp_code,'SUBC', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) subcouncil,
(SELECT MAX (DECODE(borgtyp_code,'SRVA', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) servicearea,
(SELECT MAX (DECODE(borgtyp_code,'DIS', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) district,
(SELECT MAX (DECODE(borgtyp_code,'SUBD', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) subdistrict,
DECODE(cp.person_id, NULL, 0, 1) positions_filled,
1 positions_needed
FROM campaigns c,
campaign_positions cp,
campaign_giving_categories cgc,
bsa_orgs borg,
bsa_orgs par_borg
WHERE c.campgn_id = i_campgn_id
AND cp.camppos_id IN (SELECT camppos_id
FROM campaign_positions
START WITH campgn_id = i_campgn_id
AND supervisor_camppos_id IS NULL
CONNECT BY supervisor_camppos_id = PRIOR camppos_id)
AND c.campgn_id = cp.campgn_id
AND c.campgn_id = cgc.campgn_id
AND cgc.campgiv_id = cp.campgiv_id
AND borg.bsaorg_id = cp.managing_bsaorg_id
AND borg.parent_bsaorg_id = par_borg.bsaorg_id
UNION ALL
SELECT borg.bsaorg_id bsaorg_id,
borg.name bsaorg_name,
borg.parent_bsaorg_id parent_bsaorg_id,
par_borg.name parent_bsaorg_name,
par_borg.borgtyp_code par_borgtyp_code,
cgc.campgiv_name giving_cat_name,
(SELECT MAX (DECODE(borgtyp_code,'CNCL', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) council,
(SELECT MAX (DECODE(borgtyp_code,'SUBC', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) subcouncil,
(SELECT MAX (DECODE(borgtyp_code,'SRVA', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) servicearea,
(SELECT MAX (DECODE(borgtyp_code,'DIS', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) district,
(SELECT MAX (DECODE(borgtyp_code,'SUBD', name))
FROM bsa_orgs
START WITH bsaorg_id = borg.bsaorg_id
CONNECT BY bsaorg_id = PRIOR parent_bsaorg_id) subdistrict,
DECODE(cp.person_id, NULL, 0, 1) positions_filled,
1 positions_needed
FROM campaigns c,
campaign_positions cp,
campaign_giving_categories cgc,
unit_districts ud,
bsa_orgs borg,
bsa_orgs par_borg
WHERE c.campgn_id = i_campgn_id
AND cp.camppos_id IN (SELECT camppos_id
FROM campaign_positions
START WITH campgn_id = i_campgn_id
AND supervisor_camppos_id IS NULL
CONNECT BY supervisor_camppos_id = PRIOR camppos_id)
AND c.campgn_id = cp.campgn_id
AND c.campgn_id = cgc.campgn_id
AND cgc.campgiv_id = cp.campgiv_id
AND ud.unit_id = cp.unit_id
AND (ud.expire_date IS NULL
OR ud.unitdis_id = (SELECT MAX(unitdis_id)
FROM unit_districts
WHERE unit_id = cp.unit_id
)
)
AND borg.bsaorg_id = ud.bsaorg_id
AND borg.parent_bsaorg_id = par_borg.bsaorg_id
)
GROUP BY bsaorg_id,
bsaorg_name,
parent_bsaorg_id,
parent_bsaorg_name,
par_borgtyp_code,
giving_cat_name,
council,
subcouncil,
servicearea,
district,
subdistrict;
END cmpgnmp_stat_bydis;
/
Here are the errors:
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE CMPGNMP_STAT_BYDIS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
25/23 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 current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
28/70 PLS-00103: Encountered the symbol "COUNCIL" when expecting one of
the following:
; return returning and or
45/29 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
SAMPLE_
The symbol "having" was substituted for "(" to continue.
45/56 PLS-00103: Encountered the symbol "POSITIONS_FILLED" when
expecting one of the following:
* & = - + ; < / > at for in mod not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like between group having
intersect minus order overlaps start union where connect
is null is not || is dangling year DAY_
70/23 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 current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
73/70 PLS-00103: Encountered the symbol "COUNCIL" when expecting one of
the following:
; return returning and or
90/29 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
SAMPLE_
The symbol "having" was substituted for "(" to continue.
90/56 PLS-00103: Encountered the symbol "POSITIONS_FILLED" when
expecting one of the following:
* & = - + ; < / > at for in mod not rem <an exponent (**)>
<> or != or ~= >= <= <> and or like between group having
intersect minus order overlaps start union where connect
is null is not || is dangling year DAY_
116/13 PLS-00103: Encountered the symbol "

" when expecting one of the
following:
. ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
group having intersect minus order start union where connect
||
The symbol "(" was substituted for "

" to continue.
128/1 PLS-00103: Encountered the symbol "END"
Thanks,
rshawtx