Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Stored Procedure Problem

    I am using this SQL to get some data

    I have defined the Variable as
    v_old_insurance_analysis_id web_insurance_analysis.INSURANCE_ANALYSIS_ID%TYPE: =NULL;

    SELECT insurance_analysis_id into v_old_insurance_analysis_id from
    web_fp_plan_Detail where plan_id = a_plan_id;

    I can have a situation where this value can be null,
    If this is null, then i am getting NO_DATA_FOUND error.

    any idea on how to clear this up.
    thanks
    shankar

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Select ** Into **
    will always raised an exception (no_data_found) when no rows are retrived by the select query.
    U can handle this in the exception handler i.e
    PHP Code:
    Declare
    Begin
    -----
      
    Begin
       SELECT insurance_analysis_id into v_old_insurance_analysis_id from
         web_fp_plan_Detail where plan_id 
    a_plan_id;

      
    Exception
      When No_data_found then
      
    -----
      
    End;

    End
    Thanks
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Null

    Hi,

    Why dont you try..

    select nvl(field,0) into var from table where x=y;

    regs
    Sree

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sreeraj
    Why dont you try..

    select nvl(field,0) into var from table where x=y;
    But that will still raise NO_DATA_FOUND if there is no row in table with x=y.

  5. #5
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Ooooooooops

    Hi,

    Sorry i was a bit fast at the qstn

    You are right it will still through a no data found execption.

    So easier solution is checking the existence first....


    Sorryy

    Regs
    Sree

  6. #6
    Join Date
    Jun 2004
    Posts
    2

    Select Max(....

    Hi!

    There is a workaround for the NO_DATA_FOUND-exception.
    It can be used on SELECT-statements where the result set contains only a single row; for example a statement where the WHERE-clause contains the primary key or an unique key of the table.

    It seems that your statement is one that selects the field of one single row by querying by the primary key.

    So you can try the following:

    SELECT MAX (insurance_...) INTO ... FROM ... WHERE ...

    In any case you catch no exception.
    The value of the variable is in case of an existing row the value of the column. In case of no existing row the value of the variable is NULL.

    hope that helps
    regards
    klaus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •