This discussion is archived
4 Replies Latest reply: Apr 21, 2012 12:51 PM by 932228 RSS

PL/SQL statement does not return data, but there is data! Why?

932228 Newbie
Currently Being Moderated
Hello Gurus:

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



p.s. The SQL statement:

select column_b into var_col_b
from table_1
where column_a = 'ROW_2_KEY';
  • 1. Re: PL/SQL statement does not return data, but there is data! Why?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Jennifer,

    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).
  • 2. Re: PL/SQL statement does not return data, but there is data! Why?
    932228 Newbie
    Currently Being Moderated
    Hi Frank:

    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.

    Thanks!!
    Jenn
  • 3. Re: PL/SQL statement does not return data, but there is data! Why?
    William Robertson Oracle ACE
    Currently Being Moderated
    929225 wrote:
    I guess "SQL Worksheets" can see data before such commits but stprocs & funcs cannot.
    Not quite - only the session that inserts the data can see it, until it commits, at which point it becomes visible to other sessions (subject to permissions).
  • 4. Re: PL/SQL statement does not return data, but there is data! Why?
    932228 Newbie
    Currently Being Moderated
    Duly noted. Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points