VEL wrote:
> I wrote a stored procedure that declares a cursor as follows:
>
> CURSOR RANKED_PF_CURSOR IS
> SELECT campaign_guid, device_uid
> FROM (SELECT cd_campaign_guid, cd_device_uid,
> (SELECT cs_rank FROM compl_status WHERE pf_compl_status =
> cs_compl_status) AS pf_rank,
> (SELECT cs_rank FROM compl_status WHERE cd_compl_status =
> cs_compl_status) AS cd_rank
> FROM pending_flow, vw_message_request, campaign_devices
> WHERE mr_campaign_guid = campaign_guid
> AND mr_account_uid = account_uid
> AND pf_msg_req_guid = mr_msg_req_guid
> AND cd_campaign_guid = campaign_guid
> AND cd_device_uid = pf_device_uid)
> WHERE pf_rank < cd_rank;
>
> When I load my package I get the following errors:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 22/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>
>
> 22/120 PLS-00103: Encountered the symbol "AS" when expecting one of the
> following:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> ; return returning and or
>
> 23/115 PLS-00103: Encountered the symbol "AS" when expecting one of the
> following:
> , ; for <an identifier>
> <a double-quoted delimited-identifier> group having intersect
> minus order start union where connect
>
>
> If I run the same same query from pl/sql it works fine.
>
> Any ideas.
>
> VEL
If you're using 9i, I can't address this issue.
On 8i, PL/SQL seems to have trouble with the "AS" when used with an
inline view. It doesn't have that trouble when it's used as a column
alias (select column as alias...). Take out the "AS" in your inline
views and it should compile. (Oracle apparently didn't fully implement
this feature.)
Received on Wed Nov 13 2002 - 16:10:20 CST
