Solved

Stored Procedure Problem

Posted on 2004-04-29
16
2,485 Views
Last Modified: 2007-12-19
I have two tables Poperty_Details and Property. I have been handed over a list of addresses and Post Codes in the Property table, and have been asked to get the corresponding Referral ID's. I wrote this stored procedure but am getting an error. I am at my wit's end! Can someone help?? The Property table is in the schema Reporting and the Property_Details is in the schema WAREHOUSE. I am executing the procedure in the schema Reporting. There are about 300 addresses and pos codes in the Property table. These are being read in! I've confirmed it using a dbms_output.put_line instead of the select statement to echo all the addresses and Post Codes in Property table.

Here's the code and the error message:
CREATE OR REPLACE PROCEDURE temp_proc
AS
      Address      varchar(50);
      PostCode      varchar(15);
                ReferralID  Number;
      CURSOR ref_cur IS
            SELECT ADD1, POSTCODE
            FROM Property;
      BEGIN
         OPEN ref_cur;
         LOOP
                FETCH ref_cur INTO
                  Address, PostCode;
                  Select pd.pd_rs_referral_id INTO ReferralID
                  From   WAREHOUSE.Property_Details pd
                  Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
                  AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;
                  dbms_output.put_line(ReferralID || Address || PostCode);
            Exit when ref_cur%NOTFOUND;
         END LOOP;
         CLOSE ref_cur;
      EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
  END;
/


The following error has occurred:

ORA-20000: Unknown Exception : 100 ORA-01403: no data found
ORA-06512: at "REPORTING.TEMP_PROC", line 25
ORA-06512: at line 2


0
Comment
Question by:anand_007
  • 5
  • 4
  • 4
  • +3
16 Comments
 
LVL 13

Expert Comment

by:riazpk
ID: 10949763
Hay ! Its clear....

This just means that your not getting any data for your query (for some record):

Select pd.pd_rs_referral_id INTO ReferralID
               From   WAREHOUSE.Property_Details pd
               Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
               AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;

Just change your procedure to (handle no_Data_found exception):

CREATE OR REPLACE PROCEDURE temp_proc
AS
     Address     varchar(50);
     PostCode     varchar(15);
                ReferralID  Number;
     CURSOR ref_cur IS
          SELECT ADD1, POSTCODE
          FROM Property;
     BEGIN
        OPEN ref_cur;
        LOOP
              FETCH ref_cur INTO
               Address, PostCode;
              begin
                 Select pd.pd_rs_referral_id INTO ReferralID
                 From   WAREHOUSE.Property_Details pd
                 Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
                 AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;
                 dbms_output.put_line(ReferralID || Address || PostCode);
             exception
                  when no_data_found then null; --or some message
             end;
          Exit when ref_cur%NOTFOUND;
        END LOOP;
        CLOSE ref_cur;
     EXCEPTION
     WHEN OTHERS THEN
        raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
  END;
/

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10949765
well, the message is crystal clear.

it throws NO DATA found error on this line:

              Select pd.pd_rs_referral_id INTO ReferralID
              From   WAREHOUSE.Property_Details pd
              Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
              AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;


Try to run this query in sqlplus or toad , make sure you query is correctly constructed.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10949798
It would appear that you have a 'property' where you don't have a 'warehouse'.  If this is possible, you need to put the inner select statement in it's own code block and handle the 'no data found' exception there.

It would also appear that you should be able to do this in a single select statement w/o PL/SQL something like:

 Select pd.pd_rs_referral_id
               From   WAREHOUSE.Property_Details pd, property p
               Where  pd.pd_address1 LIKE  trim(substr(p.address,1,11)) || '%'
               AND    pd.PD_POST_CODE = p.PostCode;

I haven't tried this to verify but it should be close.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10949910
try to modify your procedure to this:
there is a problem when you try to use select with variabble and single quote, you have to use dynamic sql ...


Just copy and paste the code below to run a test:





CREATE OR REPLACE PROCEDURE temp_proc
AS
    l_Address     varchar2(50);
    l_PostCode     varchar2(15);
    l_ReferralID  Number;
   l_sql varchar2(2000);
    CURSOR ref_cur IS
         SELECT ADD1, POSTCODE
         FROM Property;
    BEGIN
       OPEN ref_cur;
       LOOP
             FETCH ref_cur INTO
              l_Address, l_PostCode;
              l_sql:='Select pd.pd_rs_referral_id INTO l_ReferralID
                         From  WAREHOUSE.Property_Details pd Where  pd.pd_address1        
                         LIKE  ''' || trim(substr(Address,1,11)) || '%' || ''''
              || '  AND   pd.PD_POST_CODE LIKE ''' || PostCode|| '''' ;
              execute immediate l_sql;
              dbms_output.put_line(l_ReferralID || l_Address || l_PostCode);
         Exit when ref_cur%NOTFOUND;
       END LOOP;
       CLOSE ref_cur;
    EXCEPTION
    WHEN OTHERS THEN
       raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
 END;
/






0
 
LVL 15

Expert Comment

by:andrewst
ID: 10950063
I think the problem may be the additional quotes you are appending to the text here:

               Select pd.pd_rs_referral_id INTO ReferralID
               From   WAREHOUSE.Property_Details pd
               Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
               AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;
         
Perhaps you should instead have:

               Select pd.pd_rs_referral_id INTO ReferralID
               From   WAREHOUSE.Property_Details pd
               Where  pd.pd_address1 LIKE  trim(substr(Address,1,11)) || '%'
               AND    pd.PD_POST_CODE LIKE PostCode;
0
 
LVL 2

Author Comment

by:anand_007
ID: 10950290
Thanks for all those comments - I tried what riazpk suggested. This is what I got. Not at all helpful I must say.

ORA-06550: line 2, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   begin declare exit for goto if loop mod null pragma raise
   return select update while <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall
   <a single-quoted SQL string>

Then I tried what andrewst suggested and this is what I got:
ORA-20000: Unknown Exception : -905 ORA-00905: missing keyword
ORA-06512: at "REPORTING.TEMP_PROC", line 27
ORA-06512: at line 2
I am using TOAD - any other suggestions??
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10950346
If you remove the EXCEPTION section you will get a better error message showing the actual line number where the error occured.

Also you could re-post the code.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10950349
anand, take a look at my last post.

I think it will do it for you...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:anand_007
ID: 10950355
Sorry that was seazodiac not andrewst and the error message is so:
ORA-20000: Unknown Exception : -905 ORA-00905: missing keyword
ORA-06512: at "REPORTING.TEMP_PROC", line 27
ORA-06512: at line 2

The first version of my code in the morning started off with what andrewst suggested. Obviously that does not work! Aaargh - stupid computer!!
0
 
LVL 22

Expert Comment

by:earth man2
ID: 10950410
-- I would say you need to exit as soon as cursor is not found
CREATE OR REPLACE PROCEDURE temp_proc
AS
    l_Address    varchar(50);
    l_PostCode  varchar(15);
    l_ReferralID  Number;
    CURSOR ref_cur IS  SELECT ADD1, POSTCODE FROM Property;
BEGIN
       OPEN ref_cur;
       LOOP
             FETCH ref_cur INTO l_Address, l_PostCode;
             Exit when ref_cur%NOTFOUND;
             l_ReferralID := -1;
             begin
                Select pd_rs_referral_id INTO l_ReferralID
                From   WAREHOUSE.Property_Details
                Where  pd_address1 LIKE trim(substr(l_Address,1,11)) || '%'
                AND    PD_POST_CODE = l_PostCode;
            exception
                  when no_data_found then null;
            end;
            dbms_output.put_line( l_ReferralID || l_Address || l_PostCode );
       END LOOP;
       CLOSE ref_cur;
    EXCEPTION
    WHEN OTHERS THEN
       raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
 END;
/
0
 
LVL 2

Author Comment

by:anand_007
ID: 10950503
The code given by riazpk worked after all-  but for some reason I am not getting the corresponding referral id's for ANY of the rows. I tried running a couple of test cases on the Warehouse schema and I am getting the Referral ID's. What could be the problem now?? Here's the code again ..

CREATE OR REPLACE PROCEDURE temp_proc
AS
      Address      varchar(50);
      PostCode      varchar(15);
                ReferralID  Number;
      CURSOR ref_cur IS
            SELECT ADD1, POSTCODE
            FROM Property;
      BEGIN
         OPEN ref_cur;
         LOOP
                FETCH ref_cur INTO
                  Address, PostCode;
         BEGIN
                  Select pd.pd_rs_referral_id INTO ReferralID
                  From   WAREHOUSE.Property_Details pd
                  Where  pd.pd_address1 LIKE  '''' || trim(substr(Address,1,11)) || '%' || ''''
                  AND    pd.PD_POST_CODE LIKE '''' || PostCode|| '''' ;
                  dbms_output.put_line(ReferralID || Address || PostCode);
            EXCEPTION
                  when no_data_found then dbms_output.put_line ('No Ref ID' || Address || PostCode) ;
            END;
            Exit when ref_cur%NOTFOUND;
         END LOOP;
         CLOSE ref_cur;
      EXCEPTION
      WHEN OTHERS THEN
        raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
  END;
Sample outputs:
No Ref ID1 Blackwell Place PL27 7NL
No Ref ID1 Bossiney Road PL34 0AS
No Ref ID1 Cambeak PL35 0DW

Then this is what I ran on the WAREHOUSE:
Select pd.pd_rs_referral_id
From   EAGA_WAREHOUSE.Property_Details pd
Where  pd.pd_address1 LIKE  '1 Blackwell%'
AND    pd.PD_POST_CODE LIKE 'PL27 7NL'

I got the correct referral id. Whats the problem??
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10950506
hmmmm...

I modified it again, please test this:


CREATE OR REPLACE PROCEDURE temp_proc
AS
   l_Address     varchar2(50);
   l_PostCode     varchar2(15);
   l_ReferralID  Number;
  l_sql varchar2(2000);
   CURSOR ref_cur IS
        SELECT ADD1, POSTCODE
        FROM Property;
   BEGIN
      OPEN ref_cur;
      LOOP
            FETCH ref_cur INTO
             l_Address, l_PostCode;
             l_sql := trim(substr(Address,1,11)) || '%' ;
             Select pd.pd_rs_referral_id INTO l_ReferralID
                         From  WAREHOUSE.Property_Details pd Where  pd.pd_address1        
                        LIKE  l_sql AND   pd.PD_POST_CODE LIKE l_PostCode;
             dbms_output.put_line(l_ReferralID || l_Address || l_PostCode);
        Exit when ref_cur%NOTFOUND;
      END LOOP;
      CLOSE ref_cur;
   EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
END;
/
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10950571
I still don't see why you have all those ''''s - surely the data isn't stored with quotes around it like this:

select postcode from property;

POSTCODE
--------
'AB1 2DE'
'CD1 4HG'

?

I think you should remove them:

CREATE OR REPLACE PROCEDURE temp_proc
AS
     Address     varchar(50);
     PostCode     varchar(15);
                ReferralID  Number;
     CURSOR ref_cur IS
          SELECT ADD1, POSTCODE
          FROM Property;
     BEGIN
        OPEN ref_cur;
        LOOP
              FETCH ref_cur INTO
               Address, PostCode;
        BEGIN
               Select pd.pd_rs_referral_id INTO ReferralID
               From   WAREHOUSE.Property_Details pd
               Where  pd.pd_address1 LIKE  trim(substr(Address,1,11)) || '%'
               AND    pd.PD_POST_CODE LIKE  PostCode ;
               dbms_output.put_line(ReferralID || Address || PostCode);
          EXCEPTION
               when no_data_found then dbms_output.put_line ('No Ref ID' || Address || PostCode) ;
          END;
          Exit when ref_cur%NOTFOUND;
        END LOOP;
        CLOSE ref_cur;
     EXCEPTION
     WHEN OTHERS THEN
        raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
  END;

0
 
LVL 15

Accepted Solution

by:
andrewst earned 125 total points
ID: 10950637
Here is a simplified version:

CREATE OR REPLACE PROCEDURE temp_proc
AS
   ReferralID  Number;
   CURSOR ref_cur IS
      SELECT ADD1, POSTCODE
      FROM Property;
BEGIN
   FOR r IN ref_cur LOOP
      BEGIN
         Select pd.pd_rs_referral_id INTO ReferralID
         From   WAREHOUSE.Property_Details pd
         Where  pd.pd_address1 LIKE  trim(substr(r.Address,1,11)) || '%'
         AND    pd.PD_POST_CODE LIKE  r.PostCode ;
         dbms_output.put_line(ReferralID || r.Address || r.PostCode);
      EXCEPTION
         when no_data_found then dbms_output.put_line ('No Ref ID' || r.Address || r.PostCode) ;
      END;
   END LOOP;
END;

if it doesn't find the addresses - well, maybe they aren't there!!!
0
 
LVL 2

Author Comment

by:anand_007
ID: 10950821
Here is the new code in lines of what andrewst suggested. Now I have a new problem :((

ORA-20000: Unknown Exception : -1422 ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "REPORTING.TEMP_PROC", line 21
ORA-06512: at line 2

PROCEDURE temp_proc
AS
   ReferralID  Number;
   CURSOR ref_cur IS
      SELECT ADD1, POSTCODE
      FROM Property;
BEGIN
   FOR r IN ref_cur LOOP
      BEGIN
         Select pd.pd_rs_referral_id INTO ReferralID
         From   Property_Details pd
         Where  pd.pd_address1 LIKE  trim(substr(r.ADD1,1,11)) || '%'
         AND    pd.PD_POST_CODE LIKE  r.PostCode ;
         dbms_output.put_line(ReferralID || r.ADD1 || r.PostCode);
      EXCEPTION
         when no_data_found then dbms_output.put_line ('No Ref ID' || r.ADD1 || r.PostCode) ;
      END;
   END LOOP;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20000, 'Unknown Exception : '||sqlcode||' '||sqlerrm);
END;

It seems some rows are fetching back mulitple Referral ID's - is it possible to capture the first one only?
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 125 total points
ID: 10950841
yes, adding this to your query:

        Select pd.pd_rs_referral_id INTO ReferralID
        From   Property_Details pd
        Where  pd.pd_address1 LIKE  trim(substr(r.ADD1,1,11)) || '%'
        AND    pd.PD_POST_CODE LIKE  r.PostCode and rownum<2 ;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now