I have a valid SQL statement that works in another schema, but in the new one, using a user with similar privileges, cannot retrieve the data in a stored procedure. (The data is in the table! Why? Because when I dump the same SQL statement into a "SQL Worksheet", I get the data I want. Using the same user, no less, too.)
When debugging with Oracle SQL Developer, I can't make out anytihng meaningful from the log.
Am I missing a privilege here or something completely different?
Any help would be greatly appreciated, thank you;
p.s. The SQL statement:
select column_b into var_col_b
where column_a = 'ROW_2_KEY';
Whenever you have a problem, post enough code for people to re-create the problem and test their ideas. In this case, that includes the complete code for the stored procedure (simplified as much as possible so that it still has the same problem), and CREATE TABLE and INSERT statements for the tables referenced in the procedure. I realize that's not easy to do, but without it, all we can do is guess at what the prolem might be. Guessing is not a very good way to solve problems.
Make sure that privileges on all the tables involved are granted directly to the procedure owner, and not merely to some role that the owner has. Roles don't count inside AUTHID DEFINER stored procedures; that probably explains why you see data when you run the query outside of the stored procedure.
Does the procedure raise an error? If so, post the complete error message, including line numbers. Say what causes the error (e.g., compiling the procedure).
All compiled and valid SQL statements from all stored procedures & functions were not returning data, so that is why I thought it was due to privileges (or something similar), but what was weird was I was using a "user" defined similar in another schema where everything was working there.
What had happened was I created the new schema and imported an "export.sql" without doing a "commit". I never noticed the need to perform that action until I decided to give my "user" full privileges and log completely out of Oracle SQL Developer - it asked just before completion of close if I wanted to commit or rollback.
I guess "SQL Worksheets" can see data before such commits but stprocs & funcs cannot.
Anyways, everything is working as according to expectations once the commit of the imported data into the tables was performed.