PLS-00103 error when declaring a cursor with grouped Subqueries

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 Received on Wed Nov 13 2002 - 15:42:34 CST