Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Issue (Oracle 11g)
PL/SQL Issue [message #561094] Wed, 18 July 2012 15:09 Go to next message
Messages: 41
Registered: July 2012
Location: Argentina
Hi Guys!

I will apreciate if someone can help me with my issue, I've got a logic problem, here is the situation, I have to write a function that receive as parameter a datatype varchar2 and validate if the letter is less than "A" or greater than "Z", it must print do not correct, or correct depending on the situation, no problem so far, the only problem I've got is: If the symbol is ";" is valid, but i do not know how to write it, It does not depend on the PL/SQl languaje but depends on the logic itself.


v_nombre    varchar2(20):='john;%';
vv          varchar2(1);


for i in 1..length(v_nombre) loop


IF (vv < 'a' or vv > 'z') then

dbms_output.put_line('do not correct' || ' ' || vv);


dbms_output.put_line('correct' || ' ' || vv);

end if;

end loop;


Re: PL/SQL Issue [message #561098 is a reply to message #561094] Wed, 18 July 2012 15:29 Go to previous messageGo to next message
Messages: 4766
Registered: February 2005
Location: East Coast USA
Senior Member
you said A and Z, but your code does a and z. these are different.
why not use the ascii code for the range?
pl/sql runs within the database and is not interactive. having it display messages is ok for debugging while running at a sql prompt, but an application will not be bale to display any messages.

oh, and ascii code for semicolon is 59, so you would just an *or* clause.

[Updated on: Wed, 18 July 2012 15:31]

Report message to a moderator

Re: PL/SQL Issue [message #561104 is a reply to message #561098] Wed, 18 July 2012 16:51 Go to previous message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood which values are valid and which are not, but OK - here's one option. See if you can adjust it.

Here's how you can find out characters and their ASCII codes:
Click here

And the query ("valid" would be characters between A and Z, as you originally said):
SQL> select ascii('A') "A",
  2         ascii('a') "a",
  3         ascii('Z') "Z",
  4         ascii('z') "z"
  5  from dual;

         A          a          Z          z
---------- ---------- ---------- ----------
        65         97         90        122

SQL> with test as
  2    (select '&value' col from dual)
  3  select
  4    col,
  5    substr(col, level, 1) one_char,
  6    case when ascii(substr(col, level, 1)) between 65 and 90 then 'Valid'
  7         else 'Invalid'
  8    end status
  9  from test
 10  connect by level <= length(col);
Enter value for value: JoHN;/#
old   2:   (select '&value' col from dual)
new   2:   (select 'JoHN;/#' col from dual)

------- ---- -------
JoHN;/# J    Valid
JoHN;/# o    Invalid
JoHN;/# H    Valid
JoHN;/# N    Valid
JoHN;/# ;    Invalid
JoHN;/# /    Invalid
JoHN;/# #    Invalid

7 rows selected.

SQL> /
Enter value for value: MIke
old   2:   (select '&value' col from dual)
new   2:   (select 'MIke' col from dual)

---- ---- -------
MIke M    Valid
MIke I    Valid
MIke k    Invalid
MIke e    Invalid


[Updated on: Wed, 18 July 2012 16:54]

Report message to a moderator

Previous Topic: Loading data from Oracle into Excel
Next Topic: 2nd Decimal Place not appearing
Goto Forum:

Current Time: Sun Aug 20 01:09:49 CDT 2017

Total time taken to generate the page: 0.26498 seconds