Ora - 01001 Invalid cursor

gs79
gs79 used Ask the Experts™
on
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;
/

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
you are getting the error because you close the cursor:  close rc;

when returning a cursor from a procedure, it must be open.


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

Then you don't need the refcursor at all.  You only need that if you wish to generate a report from a procedure.



Author

Commented:
For readability I am copy pasting the code snippet here:

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;
/

Author

Commented:
slightwv: Thank you for your response.

I get the similar error even after removing
'close rc;' line in the code. Infact i didnt have it before. I added that line only after getting similar error:

Ora 00604: recursive sql error followed by
ora 01001: invalid cursor.

Now
If I comment p_rep_cursor definition and comment the section which returns the sysdate, and let the stored procedure execute only the called procedure 'CM_ABC_DETAIL(V_date)' via crystal report, I get the following error:

Database Connector Error: Cannot obtain the error message from the server.

How ever the stored procedure executes fine in pl/sql; ie. the below code executes fine

begin
CM_ABC('201010');
end;
/

the requirement is to execute this procedure via crystal report and to execute a stored procedure via crystal report the procedure has to have a ref_cursor as the 'in out' parameter though it is not returning any data for the report..

Please let me know what your thoughts are..

Thanks
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I am wondering if this is because two ref_cursor variables are being used in the stored procedure?

ie p_rep_cursor inout cr_package.cr_cursor,

and

RC cm_abc_rpt_pkg.c_ft_rc

since variables p_rep_cursor and RC are both ref cursor type, I am not sure if the crystal allows to have two ref-cursors although it returns a single record set via p_ref_cursor variable..

Please let me know what your thoughts are..

Thanks..
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Sorry, I'm out of the office for the afternoon.  If no other expert shows up, I'll take a look at this tomorrow.

Author

Commented:
Slightwv:

The crystal requires to have a ref-cursor as inout parameter mandatorily. However it doesnt have to return a record set as you said. I commented the last section which returned the sysdate as shown below. The crystal report ran fine without any data being returned however it executed the procedure which was much needed..

Thanks for your help..

IF v_return_cd = 0
      THEN
            OPEN p_rep_cursor FOR
               SELECT   sysdate from dual;    
       
      END IF;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help and that you have it working.  Sorry I couldn't be more active on this for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial