0

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.

Jayanta Mandal
  • 23
  • 7
  • You tagged ora-00942 but you do not mention it in your question. Please post the entire error message. – Koen Lostrie Mar 15 '23 at 08:30
  • 1
    As the params that you pass to the procedure (the in ones) are varchar2, I suspect the problem is that the SQL you're building (v_sql) is wrong, and the open fails. you need to put the in_params between quotes in v_sql. – gsalem Mar 15 '23 at 08:50
  • @gsalem not if they're numbers. OP should use bind variable syntax instead of concatenation, but that wouldn't explain why it works in one env and not in the other. – Koen Lostrie Mar 15 '23 at 08:55
  • The anonymous block you've posted isn't valid so your real one may be doing something else you haven't shown that causes this. But the `when others` in your procedure is probably hiding the real issue (and used like this is often considered a bug in itself) - do you have `set serveroutput on` when you run your block? Or (preferably) remove that handler, or add `raise`. As you're passing strings and not quoting them the data type of the values and the columns matter, so include the table definition, sample data and values you pass. As you tagged ORA-00942 does the table actually exist in dev? – Alex Poole Mar 15 '23 at 09:13
  • @KoenLostrie, I agree that it is best to use bind vars. And I actually said 'I suspect' because in the calling procedure shown, the values are not numbers but non-numeric strings – gsalem Mar 15 '23 at 09:32
  • 1
    Two other thoughts... (1) you mentioned "grant select", so if tableA is in another schema then make sure (a) the grant is directly to package owner not a role, and (b) that it has a synonym or is prefixed with its owning schema; and (2) it doesn't look like you need to use dynamic SQL here, and if you make the cursor query static you might see the issue at compile time instead of runtime. – Alex Poole Mar 15 '23 at 11:21
  • You are trapping errors - remove the exception handler. It could be erroring on the OPEN and you don't see it because you aren't configured to consume dbms_output text. Better to see a real error stack. And ditto on bind variables. – Paul W Mar 15 '23 at 11:37
  • There were some typos in the question earlier, will correct and repost. Thank you all – Jayanta Mandal Mar 15 '23 at 14:51

1 Answers1

0

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.

Jayanta Mandal
  • 23
  • 7