View Poll Results: pat

Voters
0. You may not vote on this poll
  • oracle

    0 0%
  • pl/sql

    0 0%
Results 1 to 15 of 15

Thread: pl/sql Errors

  1. #1
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Unanswered: pl/sql Errors

    hi Guys!

    i couldn't solve this...

    create or replace function get_addrNO (p_fulladdr IN varchar2)
    RETURN VARCHAR2
    IS
    v_fulladdr VARCHAR2(1000) := p_fulladdr;
    v_ascii NUMBER;
    v_number VARCHAR2(1000);
    v_count NUMBER;

    BEGIN
    v_fulladdr := UPPER(p_fulladdr);

    FOR i IN 1 .. LENGTH(v_fulladdr) LOOP
    v_count := i;
    v_ascii := ASCII(SUBSTR(v_fulladdr, v_count , 1));


    IF v_ascii < 58 AND v_ascii > 47 OR v_ascii = ASCII('/') OR v_ascii = ASCII('-') THEN
    --number or '/'
    v_number := v_number || chr(v_ascii);
    ELSE
    EXIT;
    END IF;
    END LOOP;

    IF INSTR(v_number,'/') > 2 OR INSTR(v_number, '-') > 2 THEN
    -- # remove last / character
    NULL;
    END IF;

    v_ascii := ASCII(SUBSTR(v_fulladdr,v_count,1));
    IF CHR(v_ascii) = ' ' THEN
    IF INSTR(v_fulladdr,' ',v_count,2) - v_count = 2 THEN
    --add_log(v_count || SUBSTR(v_fulladdr,v_count,3));
    v_number := v_number || SUBSTR(v_fulladdr,v_count,3);
    END IF;

    ELSIF v_ascii >= 65 AND v_ascii <= 90 THEN
    IF SUBSTR(v_fulladdr,v_count + 1,1) = ' ' THEN
    v_number := v_number || CHR(v_ascii);
    END IF;

    ELSE
    --#raise application error
    NULL;
    END IF;
    END;

    ERROR====
    select get_addrno(street1) from helens_data
    *

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "DP02132049.GET_ADDRNO", line 12

    i got this when i try to get some result..


    help me, pls!!! ^^**

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pl/sql Errors

    Which is line 12?

    select text from user_source where name='GET_ADDRNO' and line=12;

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    line 12

    i think this is line 12

    FOR i IN 1 .. LENGTH(v_fulladdr) LOOP


    thx, so much...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: line 12

    You think it is line 12? Why can you not be certain? :-

    select text from user_source where name='GET_ADDRNO' and line=12;

    That will tell you for sure.

    I suspect it is more likely to be this line:

    v_fulladdr := UPPER(p_fulladdr);

    How big is the column helens_data.street1 ?

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    thx..

    i got the solutiong for this ..

    but, first of 3 rows data are null...\
    i can't handle it..
    i think i need to put if condition for null

    if v_count is 'null then v_count :=1;
    else exit;
    end if

    but it doesn't work... -_-;;;

    HELP ME, PLS!!!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: thx..

    Why not just put this right at the top:

    if p_fulladdr is null then return null; end if;

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Talking I GOT IT!!!

    THX SO MUCH!!!!

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Exclamation AGIAN.!!!!

    HI,,,

    I am splitting the street column which include street number and street name... i want to split the this one column to 2 columns

    but, there are some different startting point something like "shop, unit, lot and level" in the old column ..

    i just got the number part before.. now it is my problem..
    i am thinking to use
    IF INSTR(F_street,'SHOP') >= 1 THEN
    v_NAME := 'SHOP';
    ELSIF INSTR(F_street,'UNIT') >= 1 THEN
    v_NAME := 'UNIT';
    ELSIF INSTR(F_street,'LOT') >= 1 THEN
    v_NAME := 'LOT';
    ELSIF INSTR(F_street,'LEVEL') >= 1 THEN
    v_NAME := 'LEVEL';
    END IF;
    RETURN V_NAME;

    in the beginning part........ pls

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: AGIAN.!!!!

    That works up to a point, but may also get "false positives" like "BISHOP'S STREET" and "CAMELOT DRIVE". You could improve by looking for isolated words like this:

    IF INSTR(' '||F_street||' ',' SHOP ') >= 1 THEN ...

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Re: AGIAN.!!!!

    Thx andwerst,

    i don't know exactly what are the different between
    IF INSTR(F_street,'SHOP') >= 1 THEN
    v_NAME := 'SHOP';

    and

    IF INSTR(' '||F_street||' ',' SHOP ') >= 1 THEN ...

    it doesn't work...
    i will show the part of my code

    CREATE OR REPLACE Function GET_SNUMBER (F_STREET IN varchar2)
    RETURN VARCHAR2
    IS
    v_STREET VARCHAR2(100);
    v_TO_ASCII NUMBER;
    v_RETURN_NUMBER VARCHAR2(100);
    v_count NUMBER :=1;
    v_name varchar2(10);

    BEGIN
    IF F_STREET IS NULL
    THEN RETURN NULL;
    END IF;

    IF INSTR(''||F_street||'','SHOP') >= 1 THEN v_NAME := 'SHOP';
    ELSIF INSTR(''||F_street||'','UNIT') >= 1 THEN v_NAME := 'UNIT';
    ELSIF INSTR(''||F_street||'','LOT') >= 1 THEN v_NAME := 'LOT';
    ELSIF INSTR(''||F_street||'','LEVEL') >= 1 THEN v_NAME := 'LEVEL';
    END IF;
    RETURN V_NAME;
    v_count := v_name;


    v_STREET := UPPER(F_STREET);

    FOR i IN 1..LENGTH(v_STREET)
    LOOP
    v_count := i;

    v_TO_ascii := ASCII(SUBSTR(V_STREET, v_count , 1));


    IF v_TO_ascii < 58 AND v_TO_ascii > 47 OR v_TO_ascii = ASCII('/') OR v_TO_ascii = ASCII('-')
    THEN
    v_RETURN_number := v_RETURN_number || chr(v_TO_ascii);
    ELSE
    EXIT;


    regard,

    jehee

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: AGIAN.!!!!

    The difference is that my version will recognise a difference between 'SHOP' as a word and the same four letters in the middle of 'BISHOPSGATE', which should reduce errors.

    In your code you have omitted the spaces between the quotes e.g. here:

    IF INSTR(''||F_street||'','SHOP') >= 1 THEN v_NAME := 'SHOP';

    should be:

    IF INSTR(' '||F_street||' ',' SHOP ') >= 1 THEN v_NAME := 'SHOP';

    Also, your function will never get past this line:

    RETURN V_NAME;

    so all the code after that line is never executed.

  12. #12
    Join Date
    Apr 2004
    Posts
    2

    Re: pl/sql Errors

    Originally posted by andrewst
    Which is line 12?

    select text from user_source where name='GET_ADDRNO' and line=12;
    Hi ,

    I have compiled the code and passed character type of data ,fucntion is working fine.
    Please check the street1 variable datatype and there is no return value in the function.Please add the return value in the function.

    Thnaks,
    Karthik

  13. #13
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Talking thx... ^^**

    i already solved this problem..

    i got another problem... there are data which like 'shop 2 xxxxst'
    i have to divide this 1)shop 2
    2)xxx st.

    this is same as above... i got the starting number one... but not of this part...

    pls ,,,, help me???

    i am trying to use exception for this...

    thx...

  14. #14
    Join Date
    Apr 2004
    Posts
    2
    Hi

    The following gives the second part.

    SELECT SUBSTR('shop 2 xxxxst',INSTR('shop 2 xxxxst','2')+2) FROM dual --xxxxst

    Thanks,
    karthik

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    8

    Talking

    thx... so much...!!!


    u know i want to get 'shop 2' not 'xxx st'
    i have to think about column... in the coulmn shop, unit, lot, level
    starting from those letters, not from normal street number..
    i am doing to split the column (old) to 2 columns one for
    street number and the other one is only store steert name....

    i already got the starting number one ... look at above...

    thanks,,,

Posting Permissions

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