ORA-01001: invalid cursor error in Dev environment

Question

I'm facing a strange issue in my oracle-cloud dev environment. When I try to execute the stored proc, it gives me ORA-01001: Invalid cursor error in the dev environment. However, the same block runs fine in oracle-test.

To give you a summary consider the following code -

Create or replace package body "my_package" as

   Procedure my_proc (out_param OUT Ref cursor, in_param_1 IN Varchar2, in_param_2 IN Number) Is

   v_sql      Varchar2(200) : = '';

Begin
    v_sql = 'Select col1, col2, col3 from TableA 
              where col4 = ' || in_param_1 || ' AND col5 = ' || in_param_2 ;

    Open out_param For v_sql ;

Exception 
    When Others Then
       dbms_output.put_line ('Error in execution - ' || sqlErrm) ;
End ;

And my plSql block for test simply calls the method, and tries to do some operation on the returned values from the open cursor.

Plsql block is like below

DECLARE
    TYPE rc IS REF CURSOR;
    out_data rc;

    my_col1 Varchar2(15) := '';
    my_col2 Number := 0;
    my_col3 Varchar2(10) := '';
Begin
   my_proc (out_data, 'MY PARAM1', 'MY PARAM2') ;
   Loop
       FETCH out_data INTO my_col1, my_col2, my_col3
       Exit When out_data%NOTFound

       dbms_output.put_line(my_col1 || ', ' || my_col2 || ', ' || my_col3);
   End Loop ;
End ;

I checked the grants on the objects - applied all which are needed (grant select / grant execute).

Have seen a lot of other threads here - like this, or this or this, but none actually worked for me.

Pls note, my procedure is fine, so is the plSql block to test it. I could test everything fine in the test server. I have issue only in the dev-server.

Answer 1

I found the answer today, it was a package level grant issue - which I forgot to grant.

In summary, if you have the stored proc created in testSchema, and some of the tables/views you are using is scattered through the databases, then best way is to consolidate all the tables/views and then give a select grant to all these objects.

In high level, this grant script you need to run

Grant Select On schemaX.tableA To testSchema;
Grant Select On schemaX.tableB To testSchema;
Grant Select On schemaY.tableM To testSchema;
Grant Select On schemaZ.viewX To testSchema;

Bottomline - all objects need to be visible to the schema where you'r planning to execute your stored proc.

This was my useCase only, but I think this would come handy to many of the users here.