Q
Problem solve Get help with specific problems with your technologies, process and projects.

Query sometimes returns ORA-1403

We have a query that sometimes returns ORA-1403. The same query executes successfully from the SQL*Plus prompt and many times from our application. Also, we can access the same data using cursors or an update query. The solution is to restart Oracle which is not acceptable as it is a production database. Please find the PL/SQL code which executes this query.

The following query sometimes returns ORA-1403:

SELECT apptype, appupath, appuname, appvalue 
INTO :type, :upath, :uname, :value 
INDICATOR :value_ind FROM pdm_appdata 
WHERE appuser = '{Global}' AND apppath = 
'/DOCUMENT TYPE/CONCEPT' AND appname = 
'HEARDER' AND apptype = 'VALUE';
We are using Oracle 10.1.0.4. The same query executes successfully from the SQL*Plus prompt and many times from our application. Also, we can access the same data using cursors or an update query. This does not happen every time. There will be only one row fetched by this query. This table does not contain multiple rows for this query at all. The solution is to restart Oracle which is not acceptable as it is a production database. Please find the PL/SQL code which executes this query below. Once the error occurrs we cannot access any data/row of that table through the following routine.

Any help will be appreciated.

int rel_appdata_get_value(char *usr, char *typ, char *pth, char *upth, char *nam, char *unam, char *val)
{
EXEC SQL BEGIN DECLARE SECTION;
     char user[APPUSER_LEN];
     char type[APPTYPE_LEN];
     char path[APPPATH_LEN];
     char upath[APPPATH_LEN];
     char name[APPNAME_LEN];
     char uname[APPNAME_LEN];
     char value[APPVALUE_LEN];
     short value_ind;

EXEC SQL END DECLARE SECTION;
     strcpy(type, "VALUE");
     strcpy(user, usr);
     strcpy(path, pth);
     strcpy(name, nam);

     EXEC SQL
           SELECT
                 apptype,
                 appupath,
                 appuname,
                 appvalue
           INTO
                 :type,
                 :upath,
                 :uname,
                 :value INDICATOR :value_ind
           FROM  pdm_appdata
           WHERE appuser = :user
           AND   apppath = :path
           AND   appname = :name
           AND   apptype = :type;

     if (SQLCODE == 0)
     {
           oraldchar(type, strlen(type));
           strcpy(typ, type);
           oraldchar(upath, strlen(upath));
           strcpy(upth, upath);
           oraldchar(uname, strlen(uname));
           strcpy(unam, uname);
           if (value_ind < 0)
                 value[0] = 0;
           else
           {
                 oraldchar(value, strlen(value));
                 strcpy(val, value);
           }
     }
     return(make_sql_errcode(SQLCODE));
}

I cannot help you with why the query does not return rows. You'll have to figure that one out. However, PL/SQL blocks that contain SELECT...INTO... run the risk of running into the NO DATA FOUND error you are experiencing. This is precisely why Oracle created exception handling in PL/SQL. Have a look at Chapter 10 of the PL/SQL User's Guide, Handling PL/SQL Errors.

This document shows you how to handle the NO_DATA_FOUND exception. There is even an example or two.

This was last published in April 2006

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close