[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# quick PL\SQL  question

Posted on 2004-11-23
Medium Priority
484 Views
Hi Guys,
I need to write a PL\SQL function that would take a string input and tell me if it contains just numbers. It should return true if just numbers (negative ok) and false if its not a number... pretty easy not no clue..

Thanks!
0
Question by:maxb

LVL 29

Expert Comment

ID: 12657596
Create or Replace Function Is_Number(n varchar2)
Is
V_test Number;
Begin
V_Test:=n;
Return 1;
Exceptions
When INVALID_NUMBER Then
Return 0;
End;
/
0

Author Comment

ID: 12657609
can you walk me through that? comments?
0

LVL 29

Expert Comment

ID: 12657614

Create or Replace Function Is_Number(n varchar2)
Return Number Is
V_test Number;
Begin
V_Test:=n;
Return 1;
Exceptions
When INVALID_NUMBER Then
Return 0;
End;
/
Where 1 = TRUE and 0 = FALSE.

0

LVL 3

Expert Comment

ID: 12657678
create or replace function isNumeric(string IN varchar2)
return boolean
as
temp number;

begin
temp:=to_number(string);
return true;
exception
when others then
dbms_output.put_line(sqlerrm);
return false;
end;

To test it:

declare
testa varchar2(100) := 'jklfsa';
testb varchar2(100) := '1234';
res boolean;

begin
res := isNumeric(testa);
if res = true then
dbms_output.put_line('testa is a number');
else
dbms_output.put_line('testa is not a number');
end if;

res := isNumeric(testb);
if res = true then
dbms_output.put_line('testb is a number');
else
dbms_output.put_line('testb is not a number');
end if;
end;

output:

testa is not a number
testb is a number

HTH
tim
0

LVL 29

Accepted Solution

MikeOM_DBA earned 200 total points
ID: 12657701
Ok what you actually need is to test for VALUE_ERROR:

Create or Replace Function Is_Number(n varchar2)
Return Number Is
V_test Number;
Begin
V_Test:=n;
Return 1;
Exception
When VALUE_ERROR Then
Return 0;
End;
/

0

LVL 3

Expert Comment

ID: 12657759
Or:

CREATE OR REPLACE FUNCTION is_a_number(p_number VARCHAR2) RETURN BOOLEAN AS

-- VARIABLES

l_sql_error_message VARCHAR2(500);

l_test_number NUMBER;

BEGIN

l_test_number := p_number;
RETURN(TRUE);

EXCEPTION

WHEN VALUE_ERROR THEN

RETURN(FALSE);

WHEN OTHERS THEN

l_sql_error_message := SQLERRM(SQLCODE);
DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
RETURN(FALSE);

END is_a_number;
/
0

LVL 3

Expert Comment

ID: 12657762
Then test it:

DECLARE

x BOOLEAN;

BEGIN

x := is_a_number('a');

DBMS_OUTPUT.ENABLE(5000);

IF(x) THEN
DBMS_OUTPUT.PUT_LINE('It is a number');
ELSE
DBMS_OUTPUT.PUT_LINE('It is not a number');
END IF;

END;
/
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any othā¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
###### Suggested Courses
Course of the Month17 days, 17 hours left to enroll