Problem solve Get help with specific problems with your technologies, process and projects.

Parsing in Oracle

PL/SQL expert Greg Williams explains how to parse a character string in Oracle.

I am making a program that will ask the user to enter a customer number. All of the customer numbers are numbers only. If the user enters in a customer number with a letter in it by mistake they will receive an ora-06550 error on that line number. How can I create an exception that will allow me to give the user a nice message to retype the customer number instead of all of the ora error lines? Or, how can I write a line of code that will check the user input to make sure it only contains numbers before Oracle sees it as an error, since you can't make an exception for a compilation error?

Here is my code:
set serveroutput on
  v_cust_num number := &Customer_Number;
  v_name varchar2(20);
  v_address varchar2(20);
  v_city varchar2(12);
  v_state varchar2(2);
  v_zip varchar2(5);
  if v_cust_num < 0 then
  raise_application_error (-20000,'Customer number can not be negative');
  select firstname || ' ' || lastname, address, city, state, zip
  into v_name, v_address, v_city, v_state, v_zip
  from customers
  where customer#= v_cust_num;
  dbms_output.put_line(v_city || ', '|| v_state ||' '|| v_zip);  end if;  exception
  when no_data_found then
  dbms_output.put_line('Customer number entered does not exist.');
 when value_error or invalid_number then  dbms_output.put_line(chr(10));  dbms_output.put_line('Customer number not entered correctly.'); end;

here is the error if ddd is input by user:
old 3: v_cust_num number := &Customer_Number; new 3: v_cust_num number := ddd; 

  v_cust_num number := ddd;

ERROR at line 3: 
ORA-06550: line 3, column 24: 
PLS-00201: identifier 'DDD' must be declared
ORA-06550: line 3, column 14: 
PL/SQL: Item ignored
ORA-06550: line 10, column 6: 
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3: 
PL/SQL: Statement ignored
In order to check for characters in the customer number field, you will have to parse the customer number field for characters. As you probably have already found out, Oracle does not have a function that parses a field in 9i. I have created a function called CheckForNumbers (see below). This is something that a lot of shops have or should have. This function accepts a character string and returns a character string delimited by ",". If there are no characters, the function returns the phrase "No Characters". If there are characters, the function returns the characters that are in the string. This function loops thru the string that's passed in and checks to see if the values are between '0' and '9', as follows:

IF to_char(SUBSTR(p_string,i,1)) between '0' and '9' THEN

Notice the single quotes around the 0 and 9. Without the quotes, it would try to compare characters to numbers. This will then generate an error. I hope this solves your problem.

CREATE OR REPLACE function CheckForNumbers (p_string varchar2)
     RETURN varchar2 IS
      v_string_len NUMBER;
      v_string VARCHAR2(2000);      
      v_string_len := LENGTH(p_string);
      FOR  i IN 1..v_string_len LOOP
             IF to_char(SUBSTR(p_string,i,1)) between '0' and '9' THEN     
                v_string := v_string||SUBSTR(p_string, i, 1)||',';    
             end if;    
     END LOOP;
      if v_string is null then
         return ('NO Characters');
         RETURN rtrim(v_string,',');
      end if;
END CheckForNumbers;

Dig Deeper on Oracle development languages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.