This discussion is archived
7 Replies Latest reply: Apr 25, 2012 10:28 PM by 930602 RSS

PL/SQL Code check error.

930602 Newbie
Currently Being Moderated
Hi Guys

I'm Having trouble competing the following code, I think I've got the logic right but my coding is so bad I need some help. The objective of the code is I have two tables and I have to go through the first table's columns and insert in the correct columns in the second table. Please let me know how this can be fixed.

Loop through a column called "street1" in the table "test_data" (table and column already created in the database) to check each columns string and place or split the string into a second table called new_data with two column called Newstreetname and Newstreetnumber (table and column already created in the database) .


If

"Crn" is found in the String

Then

Everything after Crn is added into Newstreetname. Newstreetnumber is null, end.

Else if

"PO Box" is found in the street1 string

Then

Everything is added into the Newstreetname. Newstreetnumber 1 is null, end

Else if

No numbers are found in the street1 string

Then

Everything added into Newstreetname. Newstreetnumber is null, end

Else

If number is found in street1 string

Then

Add street numbers to Newstreetnumber and street name to Newstreetname (I think this part is right.)



Code



DECLARE

cursor c1 is
SELECT street1
from helens_data;
r1 c1%ROWTYPE;

BEGIN

FOR r1 IN c1 LOOP

If

Instr(r1.street1, ‘Cnr’, 1) >= 1

Then

Newstreetname := Substr(r1.street1, Instr(r1.street1, ‘Cnr’, 1)+3);

Else if

Instr(r1.street1, ‘PO Box’, 1) >= 1

Then
Newstreetname:= Substr(r1.street1, Instr(r1.street1, ‘PO Box’, 1));

Else if

REGEXP_ Instr (r1.street1, [\d], 1) = 0     

Then

Newstreetname:= r1.street1;

Elses

REGEXP_ Instr (r1.street1, [\d], 1) >= 1

Then

Newstreetnumber:= regexp_substr(r1.street1, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?');
Newstreetname:= regexp_substr(r1.street1, '(\w+\s\w+)$');

END LOOP;
END;
  • 1. Re: PL/SQL Code check error.
    sb92075 Guru
    Currently Being Moderated
    post CREATE TABLE statements for both tables
  • 2. Re: PL/SQL Code check error.
    Etbin Guru
    Currently Being Moderated
    You could do it using SQL (disregarding street names might contain digits too: 5th Avenue ...)
    insert into new_data
    select <preceeding columns>,
           case when instr(upper(street1),'CNR') > 0
                then ltrim(substr(street1,instr(upper(street1),'CNR') + 3))
                when instr(upper(street1),'PO BOX') > 0
                  or regexp_ instr(street1,[\d], 1) = 0     
                then street1
                else regexp_substr(r1.street1,'(\w+\s\w+)$')
           end newstreetname,
           case when instr(upper(street1),'CNR') > 0
                  or instr(upper(street1),'PO BOX') > 0
                  or regexp_ instr(street1,[\d], 1) = 0     
                then null
                else regexp_substr(r1.street1,'\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?')
           end newstreetnumber,
           <following columns>
      from test_data
    Regards

    Etbin
  • 3. Re: PL/SQL Code check error.
    930602 Newbie
    Currently Being Moderated
    Hi

    Thanks for the reply but there is a problem with the following line.

    or regexp_ instr(street1,[\d], 1) = 0     
    then street1

    This line should just check the street1 string and if there is no digit then whole street1 string is inserted in to streetname column. I tried testing this part on is own it don't seem to work. Can Any one help ?

    SELECT
    case
    WHEN REGEXP_Instr(street1, '[:digit:]')=0 THEN
    Substr(street1, 1)

    end streetname


    FROM helens_data
    ;
  • 4. Re: PL/SQL Code check error.
    Etbin Guru
    Currently Being Moderated
    Sorry, I just tried to suggest trying to use SQL first (see Tom Kyte's [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73891904732164]mantra) so I just copied your expressions ;)
    I'm not very used to regular expressions and I don't have a Database at hand to test it (we're on a Forum) so don't take my word for it.
    Maybe
    or regexp_like(street1,'\D')
    Regards

    Etbin
  • 5. Re: PL/SQL Code check error.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    The following condition will be
    TRUE if street1 is made up entirely of letters, punctution, and/or any other characters except the 10 digits ('0' through '9')
    FALSE if street1 contains any digits
    UNKNOWN if street1 is NULL
    WHERE     street1 = TRANSLATE ( street1
                       , 'A0123456789'
                       , 'A'
                       )
    When using TRANSLATE to remove characters, the 2nd argument should start with one of the characters that you don't want to eliminate, and the 3rd argument should be that same character (alone). I used 'A' above. It doesnt matter if this character ever actaully appears in any string in your table.

    Regular expressions can get the same results, perhaps more clearly, but they will be less efficient.
    One example:
    WHERE     NOT REGEXP_LIKE ( street1
                   , '\d'
                   )
    The 2nd argument is a string, so it's enclosed in single-quotes. Square brackets and POSIX expressions (starting with a backslash, like '\d') are alternate (and mutually exclusive) ways fo referring to sets of chararacters (in this case, the set of digits.) If you're using square brackets, you wouldn't use '\d', and if you're using '\d', you wouldn't use square brackets.

     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using.
  • 6. Re: PL/SQL Code check error.
    930602 Newbie
    Currently Being Moderated
    Thanks! I'll give that a go.
  • 7. Re: PL/SQL Code check error.
    930602 Newbie
    Currently Being Moderated
    Thanks guys!

Legend

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