This discussion is archived
10 Replies Latest reply: Apr 15, 2013 12:32 PM by tem RSS

intermittent error "no data found" accessing SPROC in oracle database

tem Newbie
Currently Being Moderated
I'm running into an INTERMITTENT error using Java 1.6 connecting to Oracle database 11.2 using GlassFish 3.2 JDBC pool, all on CentOS servers (java app server is separate from database server).

I'm not sure if it is a database or Java problem, but I get the following error from the java program:

Apr 10, 2013 8:35:44 AM com.mycompany.MyClass MySPROC
SEVERE:
stack trace: java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "MYSCHEMA.MY_PACKAGE", line 144
ORA-06512: at line 1


If I run that line 144 of code in Oracle database using SQL Developer, it returns data fine. Also, if I re-run the client program to repeat the same execution of the java code, there is no problem retrieving the data. However, at some time in the future, the same program attempts to retrieve the same data and the error appears intermittently.

Anyone know what could lead to this error? The most logical problem would be if the database table didn't have any data for line 144 sql query, but since it's intermittent, and the database table doesn't change, and the java program variables accessing the database stored procedure do not change (I'm in a development environment and all the data is controlled, so I know there's no changes entering the java program from the client), I think we can eliminate this as the suspect.

Are there other scenarios that lead to this error people have seen that might be happening here?

My Java POJO has the following structure, and I'm not sure how to modify it to see more of the stack trace...

public String MyJavaPOJO(...) throws Exception {

try {
...
} catch (Exception e) {
loggerWrapper.myLogger.log(Level.SEVERE, "\nstack trace: "+e.fillInStackTrace());
} finally { 
closeDatabase(var1, cs, rset, conn);
}
return myOutputVariable;
}

Any advice much appreciated.
  • 1. Re: intermittent error "no data found" accessing SPROC in oracle database
    gimbal2 Guru
    Currently Being Moderated
    ORA error is pretty clearly database sourced, since its the database generating the error. Sorry, but you'll have to dig deeper why an "external client" might run into such a problem. Your Java program is no more than that, just any other client. Also I would check the DBMS logs (or have the DBMS logs checked) to be 100% sure there isn't some other glitch logged in there. It might well be that what you're seeing is only the result of another problem.
  • 2. Re: intermittent error "no data found" accessing SPROC in oracle database
    tem Newbie
    Currently Being Moderated
    Thanks gimbal2, Would it be useful somehow to modify the reporting of the stack trace in my POJO to show more of the error (if so, how), just for debugging purposes?

    I checked the GlassFish server.log file, and nothing was reported when the error occurred. I also checked the Oracle database alert log file, and nothing was reported. Not sure if one of these is what you refer to by DBMS log (if not, where to find DBMS log?).

    I suspect it is a result of another problem, that's why I'm asking for "hints". Thanks
  • 3. Re: intermittent error "no data found" accessing SPROC in oracle database
    rp0428 Guru
    Currently Being Moderated
    >
    Anyone know what could lead to this error? The most logical problem would be if the database table didn't have any data for line 144 sql query, but since it's intermittent, and the database table doesn't change, and the java program variables accessing the database stored procedure do not change (I'm in a development environment and all the data is controlled, so I know there's no changes entering the java program from the client), I think we can eliminate this as the suspect.
    >
    Then you would be eliminating the one thing that is causing your problem.
    >
    I'm not sure if it is a database or Java problem, but I get the following error from the java program:

    Apr 10, 2013 8:35:44 AM com.mycompany.MyClass MySPROC
    SEVERE:
    stack trace: java.sql.SQLException: ORA-01403: no data found
    ORA-06512: at "MYSCHEMA.MY_PACKAGE", line 144
    ORA-06512: at line 1

    stack trace: java.sql.SQLException: ORA-01403: no data found
    >
    That exception means just that: no data found.

    There is NO DATA: for that user, executing that procedure, using those parameters, to get data from some unspecified source at that time.
    >
    ORA-06512: at "MYSCHEMA.MY_PACKAGE", line 144
    . . .
    If I run that line 144 of code in Oracle database using SQL Developer, it returns data fine
    >
    So you want help but won't post the actual code that is causing the problem? Does that make any sense to you?

    It is the 'procedure' that is raising the exception so unless the procedure has NO parameters you can't run 'line 144' the same way that Java executes a procedure.

    The most common cause of that exception is, drum roll please, NO DATA. The query produces no data for the query/tables/views/predicates specified. Sometimes that is due to a 'SELECT . . . INTO' query that doesn't produce a value.

    When procedure code 'runs fine' executed manually but not when the procedure executes the most common reason is privileges: ROLES are disabled when PL/SQL code is exeucted. The user executing the procedure (or the procedure owner in the case of DEFINER RIGHTS) must have all necessary privilges granted directly to them and NOT granted thru roles.

    Another common cause is that the parameters being passed are different than what you think they are. That is very common when one of the parameters is a STRING but contains a value that is supposed to be a DATE or TIMESTAMP.

    Unless you post the procedure DDL, the exact query that you run manually and the Java code that executes the procedure there isn't anything for us to look at to be able to help you.

    In particular I note that you did not indicate that you ran the procedure itself manually in SQL*PLUS but only a query from it.

    The short answer is that is is most likely an issue with your procedure code and/or privileges. The only contribution Java would make to result in that exception would be calling the procedure with invalid parameters that resulted in no data being found.
  • 4. Re: intermittent error "no data found" accessing SPROC in oracle database
    tem Newbie
    Currently Being Moderated
    Thanks for the comments rp0428,
    stack trace: java.sql.SQLException: ORA-01403: no data found
    ORA-06512: at "MYSCHEMA.MY_PACKAGE", line 144
    ORA-06512: at line 1
    There are two parts to this package, the package itself and its body. I'm not sure which file this error implicates. The package itself (looking at SQL Developer) is:

    create or replace MY_PACKAGE as
    PROCEDURE abc (
    ...
    )
    PROCEDURE def (
    myVar1 IN varchar2,
    myNumber1 OUT number, -- line 144
    myNumber2 OUT number, -- line 145
    ...
    )
    END MY_PACKAGE;

    The package body is:

    create or replace package body MY_PACKAGE as
    PROCEDURE get_data(
    in_var1 IN varchar2,
    ...
    myVar1 IN varchar2,
    myNumber1 OUT number,
    myNumber2 OUT number,
    ...
    )
    is
    begin
    ...
    -- there is a comment on line 144
    SELECT a_column_name INTO out_number_of_pts FROM my_table WHERE user_name = myVar1; -- line 145
    ...
    end get_data;

    PROCEDURE abc (
    ...
    )

    PROCEDURE def(
    myVar1 IN varchar2,
    myNumber1 OUT number,
    myNumber2 OUT number,
    ...
    )

    Maybe that helps...

    I think what you're saying is the reason for the error is that there is no data in table=my_table for column=a_column_name where user_name=myVar1. However, when I re-run the client program that calls the Java program that calls the database SPROC, there's no error generated. There is no one but me using the database, java, and client programs, and the data hasn't changed to my knowledge in any of these programs in the few seconds it takes me to repeat this exercise. Since I'm executing the same program from the client, roles have not changed (still logging in as same user, etc.). Usually the client program produces good results, some small amount of time I see this error.

    Are there other causes of "no data found" other than a SELECT statement that returns no data? For example, if the database connectivity was temporarily interrupted, or other scenario, could that manifest as "no data found"? I suspect "no" from your reply above.

    Is there a way to access, if it's useful, more information from Oracle logs when this happens, or capture more detail in Java somehow?
  • 5. Re: intermittent error "no data found" accessing SPROC in oracle database
    rp0428 Guru
    Currently Being Moderated
    >
    -- there is a comment on line 144
    SELECT a_column_name INTO out_number_of_pts FROM my_table WHERE user_name = myVar1; -- line 145
    . . .
    I think what you're saying is the reason for the error is that there is no data in table=my_table for column=a_column_name where user_name=myVar1.
    >
    Yes - that is exactly what I am saying.

    That exception points directly to that line of code as the cause. If 'myVar1' is the only IN parameter then, for that query, the value being passed is the ONLY thing that will determine whether Oracle finds any data.

    So if Java, for whatever reason, passes NULL or some other value for which there is no 'user_name' in the table you will get NO DATA FOUND and the exception.
    >
    Are there other causes of "no data found" other than a SELECT statement that returns no data? For example, if the database connectivity was temporarily interrupted, or other scenario, could that manifest as "no data found"? I suspect "no" from your reply above.
    >
    No - based on that code the value passed for 'myVar1', and the actual COMMITTED data in the table at the time the query is executed will determine whether data is found.

    If another session does an insert/update of 'USER_NAME' to a value of 'ABC' but DOES NOT COMMIT then your session will NOT see that data. Sometimes people use sql developer or toad to insert/update some test data and then run their application but if the data wasn't first COMMITTED then it won't exist for any other session.

    Later the data is committed (some tools will auto commit when you exit) and you rerun your test and 'voila' it suddenly works.
    >
    Is there a way to access, if it's useful, more information from Oracle logs when this happens, or capture more detail in Java somehow?
    >
    For Oracle there is no 'other' information. Oracle doesn't really care if your queries find any data except for this case when you give instructions to store the data and there isn't any to store.

    Oracle procedures should have an exception handler that logs relevant information to a log table (using a logging package/procedure defined as AUTONOMOUS_TRANSACTION). The exception handler should then re RAISE the exception so that the client application receives it.

    For your case the procedure's exception handler should specifically trap the ORA-01403 exception and log the 'my_var1' value that was used.

    Then when you get that exception in your Java app you or the DBA can check the log table to see what parameter value was being used.

    You could also log the parameter values in the Java app but that logging should really be in the procedure itself since that is where the exception arises.

    See the examples in the PL/SQL Language doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm
    >
    Predefined PL/SQL Exceptions
    An internal exception is raised automatically if your PL/SQL program violates a database rule or exceeds a system-dependent limit. PL/SQL predefines some common ORA-n errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
    . . .
    NO_DATA_FOUND

    A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.

    Using Locator Variables to Identify Exception Locations
    Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you must know which statement failed, you can use a locator variable, as in Example 11-14.

    Example 11-14 Using a Locator Variable to Identify the Location of an Exception

    CREATE OR REPLACE PROCEDURE loc_var AS
    stmt_no NUMBER;
    name VARCHAR2(100);
    BEGIN
    stmt_no := 1; -- designates 1st SELECT statement
    SELECT table_name INTO name
    FROM user_tables
    WHERE table_name LIKE 'ABC%';
    stmt_no := 2; -- designates 2nd SELECT statement
    SELECT table_name INTO name
    FROM user_tables
    WHERE table_name LIKE 'XYZ%';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE
    ('Table name not found in query ' || stmt_no);
    END;
    /
    CALL loc_var();
  • 6. Re: intermittent error "no data found" accessing SPROC in oracle database
    tem Newbie
    Currently Being Moderated
    Hi rp0428,
    Oracle procedures should have an exception handler that logs relevant information to a log table (using a logging package/procedure defined as AUTONOMOUS_TRANSACTION). The exception handler should then re RAISE the exception so that the client application receives it.
    Good point, I should try to do this. How?

    The examples in the link you provided above always use DBMS_OUTPUT.PUT_LINE. If I use this, will the output go to the Java POJO and get stored in the error log? If not, where to does this output go that I may view it later?

    Here's example 11-2:

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#BABFBHGA
    DECLARE
    emp_column VARCHAR2(30) := 'last_name';
    table_name VARCHAR2(30) := 'emp';
    > temp_var VARCHAR2(30);
    BEGIN
    temp_var := emp_column;
    > SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS
    > WHERE TABLE_NAME = 'EMPLOYEES'
    > AND COLUMN_NAME = UPPER(emp_column);
    -- processing here
    temp_var := table_name;
    SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name)
    AND OBJECT_TYPE = 'TABLE';
    -- processing here
    EXCEPTION
    -- Catches all 'no data found' errors
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE
    ('No Data found for SELECT on ' || temp_var);
    END;
    Can you modify this example to show how to log an exception in a table using AUTONOMOUS_TRANSACTION?
  • 7. Re: intermittent error "no data found" accessing SPROC in oracle database
    rp0428 Guru
    Currently Being Moderated
    This is a Java forum - not SQL.

    You need to focus on a Java solution or find a resource in your company that can provide those changes.
  • 8. Re: intermittent error "no data found" accessing SPROC in oracle database
    tem Newbie
    Currently Being Moderated
    Sorry, I got confused which thread I was working on.

    Try:

    Re: debugging intermittent "no data found" error
  • 9. Re: intermittent error "no data found" accessing SPROC in oracle database
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    SELECT a_column_name INTO out_number_of_pts FROM my_table WHERE user_name = myVar1
    Presuming that this is the proc that is running then Oracle is telling you that the match isn't occurring.

    So the easiest solution is to add a conditional test before that line, in the proc, to see whether the value exists first. If it doesn't exist the return an error and as part of that error return the value of myVar1 and surround it with a delimiter so you can see if there is white space.

    That would result in a java exception which would have the text of the message in it and the value.
  • 10. Re: intermittent error "no data found" accessing SPROC in oracle database
    tem Newbie
    Currently Being Moderated
    Turns out it was related to sorting a datagrid on a client before sending the request to Java/Oracle db. The sort disturbed the order the data was sent compared to non-sort and the database sproc was only designed to work with non-sorted data. Thanks for the help.

Legend

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