No Data found error

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Naren
    #1

    No Data found error

    Hi,

    I am a newbie to Oracle and stored procedures. Hence this question
    comes up. The complete scenario is like this. We have a multithreaded
    application using pthreads on HP-UX machine. We are connecting to
    Oracle database 8.1.7. The application calls stored procedures on
    database using OCI calls. It is also ensured that the our application
    is thread safe.

    When a particular procedure is called, a SQL select statment returns
    an exception NO DATA FOUND. However when we connect though SQL and try
    executing the same SQL select query , it is found that the data is
    present. Also other SQL select statements in the same procedure prior
    to this one work fine. However one difference is there. The flow of
    the SQL in the proc is as follows
    BEGIN
    SELECT
    a,
    b,
    INTO
    d_a,
    d_b,
    FROM X
    WHERE condition1 = 10
    AND condition2= 3;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    raise_applicati on_error (-20033, '99991, no data found --
    ');
    END;

    BEGIN
    SELECT m INTO d_m
    FROM Y
    WHERE condition3= d_a
    AND condition4 = d_b;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    raise_applicati on_error (-20033, '99992, no data found ');
    END; --problem occurs here

    Could anyone please tell me what can be cause of this error.

    Thanks in advance
    Naren
    Tags: None
    • Frank
      #2
      Re: No Data found error

      Naren wrote:
      Hi,
      >
      I am a newbie to Oracle and stored procedures. Hence this question
      comes up. The complete scenario is like this. We have a multithreaded
      application using pthreads on HP-UX machine. We are connecting to
      Oracle database 8.1.7. The application calls stored procedures on
      database using OCI calls. It is also ensured that the our application
      is thread safe.
      >
      When a particular procedure is called, a SQL select statment returns
      an exception NO DATA FOUND. However when we connect though SQL and try
      executing the same SQL select query , it is found that the data is
      present. Also other SQL select statements in the same procedure prior
      to this one work fine. However one difference is there. The flow of
      the SQL in the proc is as follows
      BEGIN
      SELECT
      a,
      b,
      INTO
      d_a,
      d_b,
      FROM X
      WHERE condition1 = 10
      AND condition2= 3;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      raise_applicati on_error (-20033, '99991, no data found --
      ');
      END;
      >
      BEGIN
      SELECT m INTO d_m
      FROM Y
      WHERE condition3= d_a
      AND condition4 = d_b;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      raise_applicati on_error (-20033, '99992, no data found ');
      END; --problem occurs here
      >
      Could anyone please tell me what can be cause of this error.
      >
      Thanks in advance
      Naren
      what are the values of d_a and d_b?
      I don't see a declarion like
      d_a number := 12;
      d_b number := 4;
      begin
      ....

      --
      Regards, Frank van Bortel

        Comment

        • sybrandb@yahoo.com
          #3
          Re: No Data found error

          naren_on_net@ho tmail.com (Naren) wrote in message news:<825c72b.0 311280533.772e8 448@posting.goo gle.com>...
          Hi,
          >
          I am a newbie to Oracle and stored procedures. Hence this question
          comes up. The complete scenario is like this. We have a multithreaded
          application using pthreads on HP-UX machine. We are connecting to
          Oracle database 8.1.7. The application calls stored procedures on
          database using OCI calls. It is also ensured that the our application
          is thread safe.
          >
          When a particular procedure is called, a SQL select statment returns
          an exception NO DATA FOUND. However when we connect though SQL and try
          executing the same SQL select query , it is found that the data is
          present. Also other SQL select statements in the same procedure prior
          to this one work fine. However one difference is there. The flow of
          the SQL in the proc is as follows
          BEGIN
          SELECT
          a,
          b,
          INTO
          d_a,
          d_b,
          FROM X
          WHERE condition1 = 10
          AND condition2= 3;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
          raise_applicati on_error (-20033, '99991, no data found --
          ');
          END;
          >
          BEGIN
          SELECT m INTO d_m
          FROM Y
          WHERE condition3= d_a
          AND condition4 = d_b;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
          raise_applicati on_error (-20033, '99992, no data found ');
          END; --problem occurs here
          >
          Could anyone please tell me what can be cause of this error.
          >
          Thanks in advance
          Naren

          The second block will be executed regardless of the outcome in the
          first block. If you have no_data_found in the first block, you handle
          this situation (which is good), but you'll need to set up a boolean in
          the block and conditionally execute the second block. Otherwise if
          there is nothing sensible in d_a and d_b you'll end up with
          no_data_found in the second block.

          Sybrand Bakker
          Senior Oracle DBA

            Comment

            Previous template Next
            Working...