This content has been marked as final. Show 4 replies
Welcome to the forum!
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).
Your reply was so quick, thanks.
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.