Declare a cursor within FOR loop ORACLE

Question

I am trying to execute the following procedure. What i have is that there is one sql at the very start in wo_list, i need to iterate over it and pass the pyid value in this wo_list to another query for which i need to create another cursor. So I open a for loop, start my wo_list cursor loop, and now within this loop i need to create another cursor from another query by passing one of the values present in wo_list.

      CREATE OR REPLACE
     PROCEDURE FIX_DIMS_DOC_PROD_DATA_1
    AS

  tempivaultprod VARCHAR(8);
  tempivaultdoc  VARCHAR(8);

 CURSOR wo_list
 IS
 SELECT A.Pyid,
  A.Pxcreatedatetime,
  A.Pxcreateoperator,
  A.Pxcreateopname,
  A.Packageid,
  E.Doc_Desc,
  A.Concat_Prod_Desc,
  A.Primary_Ecid,
  A.Primary_Cust_Name,
  A.Isnamdocument,
  A.PYSTATUSWORK,
  F.Req_No,
  F.Req_Src,
  A.concat_prod_id,
  A.documenttypeid
FROM DIMS_DOC_MASTER A,
  Dims_Doc_Map_Product B,
  Dims_Doc_Type E ,
  Dims_Doc_Asctd_Req_Dtl F
WHERE B.Dims_Doc_Id = A.PYID
AND NOT EXISTS
  (SELECT 1
  FROM Dims_Prod_Type_Doc_Type C
  WHERE C.DIMS_PROD_ID = B.Dimsdocprodid
  AND C.Doc_Type_Id    = A.Documenttypeid
  )
 AND E.Doc_Id = A.Documenttypeid
 AND A.PYID   = F.DIMS_DOC_ID (+);
 BEGIN
 FOR DIMS_DOC_REC IN wo_list
 LOOP
  BEGIN
  CURSOR wo_list1
  IS
    SELECT DIMSDOCPRODID
    FROM DIMS_DOC_MAP_PRODUCT
    WHERE DM.DIMS_DOC_ID=DIMS_DOC_REC.pyid;

  FOR DIMS_DOC_PROD IN wo_list1
  LOOP
  BEGIN
    dbms_output.enable(30000);
    SELECT ivault_doc_type
    INTO tempivaultdoc
    FROM dims_doc_prod_details
    WHERE doc_type_id=DIMS_DOC_REC.documenttypeid;
    SELECT ivault_prod_type
    INTO tempivaultprod
    FROM dims_doc_prod_details
    WHERE actual_dims_product=DIMS_DOC_PROD.DIMSDOCPRODID;
    INSERT
    INTO Dims_Prod_Type_Doc_Type
      (
        DOC_TYPE_ID,
        DIMS_PROD_ID,
        CBOVERRIDE,
        REQUIRED,
        PODDOCUMENT,
        WHEM_OR_EMEA_REGION,
        APACREGION,
        ISACTIVE,
        CRE_TS,
        UPDT_TS,
        CRE_USR_SID,
        UPDT_USR_SID,
        WCBI_UPDATETIMESTAMP,
        IVAULT_PROD_TYPE_ID,
        IVAULT_DOC_TYPE_ID,
        ROUTINGRULE_DESC,
        ROUTINGRULE_CD,
        OLD_IVAULT_PROD_ID,
        OLD_IVAULT_DOC_ID,
        EMEA_ROUTING_CD,
        EMEA_ROUTING_DESC,
        APAC_ROUTING_DESC,
        APAC_ROUTING_CD,
        NASQUEUENAME,
        IS_ORIG_REQD,
        LATAM_ROUTING_DESC,
        LATAM_ROUTING_CD
      )
      VALUES
      (
        DIMS_DOC_REC.documenttypeid,
        DIMS_DOC_PROD.DIMSDOCPRODID,
        'N',
        'N',
        'N',
        'N',
        'N',
        'N',
        systimestamp,
        systimestamp,
        'DIMS',
        'DIMS',
        SYSTIMESTAMP,
        tempivaultprod,
        tempivaultdoc,
        'Document Specialist',
        'DocumentSpecialist',
        NULL,
        NULL,
        'DocControlReview',
        'Doc Control Review Only',
        'In-Country Review',
        'InCountryReview',
        NULL,
        NULL,
        'In-Country Review',
        'InCountryReview'
      );
     COMMIT;
    END;
 END LOOP;
END;
 END LOOP;
EXCEPTION
 WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
(
  'Error:'||SQLERRM||CHR(10)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
)
 ;
END;

Answer 1

Compilation error is solution to your question. But Why we need these many cursors. We can do this in single query and it is going better than current approach. Below Query You can use for it. I have taken few assumptions like dims_doc_prod_details table has both columns(doc_type_id,actual_dims_product) values always.If not we can change query as required. I have not filtered required columns from select that can be done.

INSERT INTO Dims_Prod_Type_Doc_Type
SELECT M.*,
  N.DIMSDOCPRODID,
  N.ivault_doc_type,
  N.tempivaultprod
FROM
  (SELECT A.Pyid,
    A.Pxcreatedatetime,
    A.Pxcreateoperator,
    A.Pxcreateopname,
    A.Packageid,
    E.Doc_Desc,
    A.Concat_Prod_Desc,
    A.Primary_Ecid,
    A.Primary_Cust_Name,
    A.Isnamdocument,
    A.PYSTATUSWORK,
    F.Req_No,
    F.Req_Src,
    A.concat_prod_id,
    A.documenttypeid
  FROM DIMS_DOC_MASTER A,
    Dims_Doc_Map_Product B,
    Dims_Doc_Type E ,
    Dims_Doc_Asctd_Req_Dtl F
  WHERE B.Dims_Doc_Id = A.PYID
  AND NOT EXISTS
    (SELECT 1
    FROM Dims_Prod_Type_Doc_Type C
    WHERE C.DIMS_PROD_ID = B.Dimsdocprodid
    AND C.Doc_Type_Id    = A.Documenttypeid
    )
  AND E.Doc_Id = A.Documenttypeid
  AND A.PYID   = F.DIMS_DOC_ID (+)
  ) M,
  DIMS_DOC_MAP_PRODUCT N,
  dims_doc_prod_details O
WHERE M.pyid        =N.doc_type_id
AND M.documenttypeid=o.doc_type_id
AND N.DIMSDOCPRODID =o.actual_dims_product;

Answer 2

You must put your cursor definition before the "begin" of your procedure.

You can declare it so that it takes a variable.

 cursor wo_list1(p_doc_id number) is
            select dimsdocprodid
              from dims_doc_map_product
             where dm.dims_doc_id = p_doc_id;

Then inside the procedure use it with the value you want

   for dims_doc_prod in wo_list1(dims_doc_rec.pyid) loop