how to call a stored procedure in declare of cursor in plsql? and want to execute the procedure before the cursor get the values

Question

CURSOR c
   IS
      SELECT      d1.description
               || '  '
               || (SELECT aa.cust_po_number
                     FROM cust_ont_line_cust_po_no aa
                    WHERE aa.header_id = ooha.header_id
                      AND aa.line_number = oola.line_number) descc,
               cms.oc_name, cmrz.size_id || 'X' || cmra.inseams size_inseam,
                  cmrp.unit_id
               || cmrp.year_id
               || '-'
               || LPAD (cmrp.plyline_no, 5, '0')
               || cmrp.shade
               || '-'
               || LPAD (cmrz.sizline_id, 2, '0') cut_no,
               CASE
                  WHEN ont.chk_smv = 'Y'
                     THEN cms.smv
                  ELSE cms.smv_lw
               END smv,
               CASE
                  WHEN ont.chk_smv = 'Y'
                     THEN cms.default_rate
                  ELSE cms.rate_lw
               END rate,
               st.line_id seq, cmrp.ply * cmrz.cutting_ratio qty,
               (SELECT DISTINCT c.oc_name
                           FROM cust_mfg_suboperations c
                          WHERE c.oc_id = SUBSTR (cms.oc_id, 1, 5)
                            AND c.division_id = p_mfgorg) section,
               SUBSTR (cms.oc_id, 1, 5) section_no, cms.oc_id,
               cmrz.cutting_ratio, SUM (oola.ordered_quantity) so_qty
          FROM oe_order_headers_all ooha,
               oe_order_lines_all oola,
               cust_mfg_routing_plan_det cmrpd,
               cust_mfg_suboperations cms,
               cust_mfg_routing_plan cmr,
               cust_style_bulletin st,
               cust_ont_art_bom_hd ont,
               cust_mfg_routing_plan_obdet cmrpo,
               cust_mfg_ratioplan_plyline cmrp,
               cust_mfg_ratioplan cmra,
               cust_ratioplan_sizline_ratio cmrz,
               mtl_system_items msi,
               fnd_flex_values_vl d1
         WHERE cmr.operation_id = cmrpd.operation_id
           AND cmra.obsno = oola.user_item_description
           AND d1.flex_value(+) = msi.attribute11
           AND cmrpd.operat_obline_id = cmrpo.operat_obline_id
           AND d1.flex_value_set_id(+) = 1016404
           AND cmrpd.oc_id = cms.oc_id
           AND st.oc_id = cms.oc_id
           AND cmrpo.operation_id = cmr.operation_id
           AND cmrpo.inventory_item_id = ont.inventory_item_id
           AND oola.user_item_description = p_ocnum
           AND cmrz.salesorder_id = cmr.salesorder_id
           AND cmrz.plyline_id = cmrp.plyline_id
           AND cmrpo.inseam_id = cmra.inseams
           AND cmrp.ratioplan_id = cmra.ratioplan_id
           AND oola.inventory_item_id = msi.inventory_item_id
           AND oola.header_id = ooha.header_id
           AND oola.org_id = ooha.org_id
           AND msi.organization_id = p_mfgorg
           AND ont.bom_id = st.bom_id
           AND cms.division_id = p_mfgorg
           AND cmra.inseams = p_inseam
           AND ooha.order_number = cmr.salesorder_id
      GROUP BY d1.description,
               cms.oc_name,
               cmrz.size_id || 'X' || cmra.inseams,
                  cmrp.unit_id
               || cmrp.year_id
               || '-'
               || LPAD (cmrp.plyline_no, 5, '0')
               || cmrp.shade
               || '-'
               || LPAD (cmrz.sizline_id, 2, '0'),
               CASE
                  WHEN ont.chk_smv = 'Y'
                     THEN cms.smv
                  ELSE cms.smv_lw
               END,
               CASE
                  WHEN ont.chk_smv = 'Y'
                     THEN cms.default_rate
                  ELSE cms.rate_lw
               END,
               st.line_id,
               cmrp.ply * cmrz.cutting_ratio,
               cmrz.cutting_ratio,
               cms.oc_id,
               ooha.header_id,
               oola.line_number
      ORDER BY oc_name, cut_no;

--Name of procedure--

cust_wip_ratiosizes ('', '', p_mfgorg, p_ocnum, p_inseam);

Answer 1

You can do this in the begin block... you cannot call the procedure in the declare stage.

example :

DECLARE

CURSOR cur_variable is 
--fetch the rows reqd;

begin
   begin
   call the procedure you want to call ; 
   cust_wip_ratiosizes ('', '', p_mfgorg, p_ocnum, p_inseam);
   exception when others then
   end;

   open cur_variable;
  fetch cur_variable into xyz;
  close cur_variable;

end ;