Why wont subquery Selects work in a package body ref cursor

[rshawtx]

I need to create a REF CURSOR that has a subquery SELECT in it. I go through Oracle's entire topic on REF CURSORS and they do not make any mention of this type of restriction. When I compile my stored procedure, it compiles with errors. I know that the SQL statement that comprises my cursor is valid because it runs on SQL*Plus. Is there any information on this restriction? I can't use inline functions because Crystal Reports prohibit embedded stored objects in stored procs that you intend to base a report on. Are there any other ways I can do this?

Thanks
rshawtx

 

[Turkbear]

Hi,
Not knowing what your query does ( or what version of Oracle) , it is hard to suggest
alternatives, but, try using the query to build a view and
access the view in Crystal...

 

[gnarus]

It would be helpful if you were to include a copy of PL/SQL you have written. Or at least include the errors.

 

[rshawtx]

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 &quot;COUNCIL&quot; when expecting one of
the following:
; return returning and or

45/29 PLS-00103: Encountered the symbol &quot;(&quot; 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 &quot;having&quot; was substituted for &quot;(&quot; to continue.

45/56 PLS-00103: Encountered the symbol &quot;POSITIONS_FILLED&quot; 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 &quot;SELECT&quot; 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 &quot;COUNCIL&quot; when expecting one of
the following:
; return returning and or

90/29 PLS-00103: Encountered the symbol &quot;(&quot; 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 &quot;having&quot; was substituted for &quot;(&quot; to continue.

90/56 PLS-00103: Encountered the symbol &quot;POSITIONS_FILLED&quot; 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 &quot&quot; 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 &quot;(&quot; was substituted for &quot&quot; to continue.

128/1 PLS-00103: Encountered the symbol &quot;END&quot;

Thanks,

rshawtx

 

[rshawtx]

We are using Oracle 8i.

I tried posting the SELECT statment here but it timed out.

Close as I figure is that it can't be done--according to a document I dug up in Metalink that ironically no one I know has access to. (i.e. Doc ID: 160663.999)

It IS a valid SELECT statement, but when I OPEN it with a defined REF CURSOR IN OUT parameter in a CREATE PROCEDURE statement, I get a series of errors starting with :
PLS-00103: Encountered the symbol &quot;SELECT&quot; 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>
On the line where I have my sub query.

rshawtx

 

[Turkbear]

Hi
I pretty sure SubQueries are not supported in 8i within PlSql
( they are actually handled by the sqlplus parser which is different from PlSql's parser)
In version 9.x it will work..
Try Dynamic Sql within your proc to work-around..

 

[rshawtx]

Thanks Turkbear!!!