Home » SQL & PL/SQL » SQL & PL/SQL » Problem with cursor (Oracle 9i)
Problem with cursor [message #573306] Wed, 26 December 2012 09:39 Go to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Hello!

I'm writting to you guys because we have an issue with our database, the problem we are facing is that we are having some problems with names in a filed which are not letters but strange simbols inserted by a webservice which by the way has been corrected, but we have the discrepancy on out DB, so I've dedided to write a PL/SQL procedure to correct them.

Here is the example of an incorrect record.

4047254| STEVE; ROVINSON    (THE INCORRECT SYMBOL IS CHR(32), I do not know why the forum doesn't show it)

So, if you look there's a symbol that is not interpreted by the DB and my original idea was to extract them and correct then with another procedure. I have originally wrote a procedure with the logic if there's a symbol that is not beween currect letters (from A to Z including semicolon (;)) extract them and that's it. So, the big question is, How I tranfer the control from the second loop to the cursor loop in order to analice the next record. Hope you understood me.

here is the code which hasn't been completed yet.


DECLARE

CURSOR get_nombre IS

SELECT CNTA_NOM_ABRV 
FROM CUENTA;

v_letra     VARCHAR2(10);

BEGIN

    FOR i IN get_nombre LOOP 
    
        FOR j in 1..length(i.CNTA_NOM_ABRV) LOOP 
        
        v_letra := substr(i.CNTA_NOM_ABRV,j,1);
        
        IF v_letra != 'A' OR

        
        
        END LOOP;
        
    END LOOP;
    
END;



[EDITED by LF: disabled smilies in this message]

[Updated on: Wed, 26 December 2012 10:02] by Moderator

Report message to a moderator

Re: Problem with cursor [message #573309 is a reply to message #573306] Wed, 26 December 2012 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
SELECT dump(CNTA_NOM_ABRV) FROM CUENTA;

what does SQL above produce?
On my ASCII chart CHR(32) is a space character.
Re: Problem with cursor [message #573311 is a reply to message #573309] Wed, 26 December 2012 10:04 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
So, the big question is, How I tranfer the control from the second loop to the cursor loop in order to analice the next record

Oracle does that for you. As soon as inner loop ends, the next record is fetched from a cursor (anyway, that's what your code suggests).
Re: Problem with cursor [message #573312 is a reply to message #573309] Wed, 26 December 2012 10:05 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Here is the incorrect record which I am trying to extract them

http://i50.tinypic.com/11j7j3c.png
Re: Problem with cursor [message #573313 is a reply to message #573311] Wed, 26 December 2012 10:09 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
What I meant when I said transfer the control to the next loop is.

If there's a extrange symbol no matter what the position is, print the entire name previusly fetched i.CNTA_NOM_ABRV stop looping because the record is wrong and transfer the control to the next record to be analized.
Re: Problem with cursor [message #573314 is a reply to message #573312] Wed, 26 December 2012 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
design FLAW!
only unprofessionals & fools store multiple values in a single column
Re: Problem with cursor [message #573315 is a reply to message #573314] Wed, 26 December 2012 10:16 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Yes Sir, the database design is totally unprofessional but we have the problem and we're trying to fix it because the application doesn't recognice the symbol and produce an error.
Re: Problem with cursor [message #573316 is a reply to message #573315] Wed, 26 December 2012 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
REPLACE() function could provide relief if properly applied.
Re: Problem with cursor [message #573318 is a reply to message #573316] Wed, 26 December 2012 11:25 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you asked for it (i.e. how to exit the loop if certain condition is met), here you are: EXIT is the statement you are looking for.
SQL> declare
  2    cursor get_nombre is
  3      select 'STEVE #ROBINSON' cnta_nom_abrv from dual union
  4      select 'LITTLE FOOT' from dual;
  5  begin
  6    for i in get_nombre loop
  7      dbms_output.put_line('Testing this string: ' || i.cnta_nom_abrv);
  8      for j in 1 .. length(i.cnta_nom_abrv) loop
  9        -- There's a "special" character (#) in the first string and none of them
 10        -- in the second. I'll EXIT the inner loop as soon as I find the special
 11        -- character, i.e. anything but uppercase A - Z, space and semi-colon
 12        if ascii(substr(i.cnta_nom_abrv, j, 1)) not between ascii('A') and ascii('Z')
 13           and ascii(substr(i.cnta_nom_abrv, j, 1)) not in (ascii(' '), ascii(';'))
 14        then
 15           -- I found a special character - print the whole string and exit the inner loop!
 16           dbms_output.put_line('Error: ' || i.cnta_nom_abrv ||
 17                                ' - special character found at position ' || j);
 18           exit;     --> this is what you are looking for
 19        end if;
 20      end loop j;
 21    end loop i;
 22  end;
 23  /
Testing this string: LITTLE FOOT
Testing this string: STEVE #ROBINSON
Error: STEVE #ROBINSON - special character found at position 7

PL/SQL procedure successfully completed.

SQL>

However, there are better ways to check that because the above example does that row-by-row (some people here call it "slow-by-slow"); even worse, you're checking every character, one-by-one (slower-by-slower). Here's one example - a regular expression.
SQL> set null <null>
SQL> with test as
  2    (select 'STEVE #ROBINSON' cnta_nom_abrv from dual union
  3     select 'LITTLE FOOT' from dual union
  4     select 'THREE_INVALID$CHARACTERS)' from dual
  5    )
  6  select cnta_nom_abrv,
  7    regexp_replace(cnta_nom_abrv, '[A-Z]| |;', '') repl,
  8    length(regexp_replace(cnta_nom_abrv, '[A-Z]| |;', '')) len
  9  from test;

CNTA_NOM_ABRV             REPL                        LEN
------------------------- -------------------- ----------
LITTLE FOOT               <null>               <null>
STEVE #ROBINSON           #                             1
THREE_INVALID$CHARACTERS) _$)                           3

SQL>

As you can see, the first string's "replacement string" is an empty string and its length is null (which means that it is OK). The second one's length is 1 (i.e. one invalid character), and so on. Therefore, your final query might look like this:
SQL> with test as
  2    (select 'STEVE #ROBINSON' cnta_nom_abrv from dual union
  3     select 'LITTLE FOOT' from dual union
  4     select 'THREE_INVALID$CHARACTERS)' from dual
  5    )
  6  select cnta_nom_abrv
  7  from test
  8  where length(regexp_replace(cnta_nom_abrv, '[A-Z]| |;', '')) > 0;

CNTA_NOM_ABRV
-------------------------
STEVE #ROBINSON
THREE_INVALID$CHARACTERS)

SQL>

You'd, of course, remove the WITH factoring clause and use something like
select cnta_nom_abrv
from cuenta
where length(regexp_replace(cnta_nom_abrv, '[A-Z]| |;', '')) > 0;
Re: Problem with cursor [message #573377 is a reply to message #573318] Thu, 27 December 2012 09:38 Go to previous message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Thanks a lot! It worked properly and we have the DB under control again!
Previous Topic: Document contains too many nodes error message select query search all columns for a string
Next Topic: DBMS_CRYPTO hash a BLOB in a union over dblink
Goto Forum:
  


Current Time: Fri Aug 29 09:24:00 CDT 2014

Total time taken to generate the page: 0.15252 seconds