Ora - 01001 Invalid cursor

Here  is the rules for using stored procedure as a datasouce for crystal reports:

 "To call Stored Procedure from crystal report,

Set data source of report to Stored Procedure (DataBase Expert Wizard). That procedure must met thses requirement

1- You must create a package that defines the REF CURSOR (type of field that will be retrieved).

2- The procedure must have a parameter that is a REF CURSOR type. This is because CR uses this parameter to access and define the result set that the stored procedure returns.

3- The REF CURSOR parameter must be defined as IN OUT (read/write mode).

4- Parameters can only be input (IN) parameters. CR is not designed to work with OUT parameters.

5- The REF CURSOR variable must be opened and assigned its query within the procedure.

6- The stored procedure can only return one record set. The structure of this record set must not change, based on parameters.

7- The stored procedure cannot call another stored procedure."

In my stored procedure instead of calling another stand alone stored procedure because of the rules, I have declared a procedure with in my stored procedure and calling that procedure to execute before returning my dataset(which is just a sysdate in this case). I have used this technique in my several other reports which is based on stored procedures and has worked fine.

I think the error is due to the fact that the called procedure with in the main stored procedure uses a ref-cursor and I am doing some mistake in opening/closing the ref cursor. Though the main stored procedure executes a single record set(as required) it has to execute the called procedure.

The purpose of this stored procedure is to return only the sysdate while it executes a procedure in it which populates a table.

Attached here is my stored procedure which my report is based upon.

When I execute the report I get ora-01001 error.

Please help me to fix it. I will be glad to furnish any other details you may want..

Thanks




 

CREATE OR REPLACE PROCEDURE CISADM."CM_ABC" (
   p_rep_cursor   IN OUT   cr_package.cr_cursor,
   p_date                  VARCHAR2
)
IS
   v_date  CHAR (6);
   v_return_cd       NUMBER (5)     := 0;


/*called procedure definition*/
PROCEDURE CM_ABC_DETAIL (
   p_date                  VARCHAR2
)
IS
   v_date            CHAR (6);
   rc cm_abc_rpt_pkg.c_ft_rc;

begin
   BEGIN
         IF p_date IS NULL OR p_date = ''
         THEN
            v_date := TO_CHAR (SYSDATE, 'YYYYMM');
         END IF;
         
         v_date := p_date;
BEGIN
   
    OPEN RC FOR 
    
     /*complex sql here*/
    select < >
    from
    tab1, tab2 etc..
    where
    <filters>;

    insert into  cm_abc_rpt_table
            (select * from 
                 table(cm_abc_rpt_pkg.fn(rc, v_date)));
            commit;
            close rc;
  end;
end;   

/*end of called procedure*/

/*stored procedure body*/
begin
   BEGIN
         IF p_date IS NULL OR p_date = ''
         THEN
            v_date := TO_CHAR (SYSDATE, 'YYYYMM');
         END IF;
         
         v_date := p_date;
         

         EXCEPTION
         WHEN OTHERS
         THEN
            v_return_cd := 9999;
            --[Set return code to be invalid date parameters]
            v_date := NULL;
      END;

   

CM_ABC_DETAIL(v_date);

    IF v_return_cd = 0
      THEN
            OPEN p_rep_cursor FOR
               SELECT   sysdate from dual;     
       
      END IF;
   
END;
/

Select allOpen in new window