PL/SQL: ORA-00904: : invalid identifier

Question

I am running the following SP but getting the error c1.pyid is invalid identifier. I am trying to use two different query results from one cursor. If there is any other way of using IF-else clause in a cursor, i am open to that too.

 CREATE OR REPLACE
  PROCEDURE FIX_DOCUMENT_RECORDS ( i_flag in varchar)
AS
Op_ID      VARCHAR(8);
Op_Name    VARCHAR(32);
  skill      VARCHAR(32);
temp_count VARCHAR(8);
temp_status VARCHAR(8):='Submitted';
QRYSTR VARCHAR2(400);
TYPE REF_CUR IS REF CURSOR;
 c1 REF_CUR;

BEGIN
 IF (i_flag='1') THEN
 QRYSTR:='SELECT *
FROM dims_doc_master
WHERE concat_prod_id      IS NULL
OR documenttypeid      IS NULL
AND (pystatuswork       = temp_status);';
ELSE
QRYSTR:='SELECT *
FROM dims_doc_master
WHERE (documentimageid IS NULL
AND p8id               IS NULL)
   AND (pystatuswork       = temp_status);';
END IF;   

 open c1 FOR QRYSTR;
LOOP
BEGIN

  DBMS_OUTPUT.PUT_LINE('loop begin');
  UPDATE DIMS_DOC_MASTER
  SET pystatuswork  ='Cancelled',
    documentstatus  ='Cancelled',
    cancellationdate='31-JAN-14',
    cancelledbysid  = c1.pxcreateoperator,
    cancelreason    ='Cancelled due to corruption.'
  WHERE pyid        =c1.pyid;

  DBMS_OUTPUT.PUT_LINE('After updation'||c1.pyid );
  --Begin PC_DOCUMENT UPDATION
  UPDATE PC_DOCUMENT
  SET pystatuswork  ='Cancelled',
    cancellationdate='31-JAN-14'
  WHERE pyid        =c1.pyid;
  --Begin insert into History
  --Select Operator name and ID

  SELECT skill
  INTO skill
  FROM operator_map_skill
  WHERE pyuseridentifier=c1.pxcreateoperator
  AND rownum            =1;
  INSERT
  INTO DIMS_DOC_HIST
    (
      DIMS_DOC_ID,
      DOC_CHG_USR,
      DOC_CHG_DT,
      DOC_NEW_STS,
      DOC_CHG_CMNT,
      CRE_TS,
      ROLE,
      RSN_DESC,
      TARGETROLE,
      DOC_CHG_USR_ID,
      DOC_ASG_USR_ID,
      DOC_ASG_USR,
      PREVSTATUS,
      PREVSTATUSDT,
      ASSIGNEDTODT,
      TODISPLAY,
      ACTIVITY_NAME
    )
    VALUES
    (
      c1.pyid,
      'DIMS',
      systimestamp,
      'Cancelled',
      'Cancelled due to corruption',
      '31-JAN-14',
      skill,
      NULL,
      skill,
      c1.pxcreateoperator,
      c1.pxcreateoperator,
      c1.pxcreateopname,
      'Submitted',
      NULL,
      systimestamp,
      'Y',
      'Updation through Script'
    );

  dbms_output.put_line
  (
    'Document ID= '||c1.pyid
  )
  ;
  SELECT COUNT(*)
  INTO temp_count
  FROM PC_ASSIGN_WORKBASKET
  WHERE pxrefobjectinsname=c1.pyid;
  IF(temp_count          IS NOT NULL) THEN
    DELETE FROM PC_ASSIGN_WORKBASKET WHERE pxrefobjectinsname=c1.pyid;
  ELSE
    DELETE FROM PC_ASSIGN_WORKLIST WHERE pxrefobjectinsname=c1.pyid;
  END IF;
  COMMIT;
END;
END LOOP;
 CLOSE c1;

END;  

Answer 1

You seem confusing cursor and fetched row.

In your current procedure: you open a cursor, do a loop (which looks to be endless since there is no EXIT statement), and after the loop you close the cursor (but it looks it will never happen)

To fetch results from a cursor, do the following:

CREATE OR REPLACE PROCEDURE ...
  ...
  c1 REF_CUR; 
  ddm_record dims_doc_master%rowtype; 
BEGIN 
  ...
  OPEN c1;
  LOOP 
     FETCH c1 INTO ddm_record; 
     EXIT WHEN c1%NOTFOUND;
     ...
     DBMS_OUTPUT.PUT_LINE('Document ID= ' || ddm_record.pyid); -- not c1.pyid 
  END LOOP;
  CLOSE c1;
END; 
/ 

Inspired from examples here: http://plsql-tutorial.com/plsql-explicit-cursors.htm

Answer 2

Try embedding the flag in your where clause:

open c1 FOR 
SELECT *
FROM dims_doc_master
WHERE (i_flag='1' AND 
      (concat_prod_id      IS NULL
    OR documenttypeid      IS NULL
   AND (pystatuswork       = temp_status))
OR (i_flag<>'1' AND
      (documentimageid IS NULL
   AND p8id               IS NULL)
   AND (pystatuswork       = temp_status));

The logic can probably be simplified but logically that would work.