| Problem with cursor [message #573306] |
Wed, 26 December 2012 09:39  |
 |
akull
Messages: 27
Registered: July 2012
Location: Argentina
|
Junior 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 #573318 is a reply to message #573316] |
Wed, 26 December 2012 11:25  |
 |
Littlefoot
Messages: 16973
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; |
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
