CODEWITHSUNDEEP
oracle-databaseplsqloracle11gsqlplus
kuldarim
kuldarim

Reputation: 1106

SQL*Plus user input exception handling

I have one simple question. I have declared two variables in my code:

v_n NUMBER(3) := &sv_n;
v_m NUMBER(3) := &sv_m;

How could I prevent user to input not a digit simbol? And raise an exception, or something like that.

I was trying to find such example, But with no luck. Also I have tried to write a code which detects if input is digit or not, but the problem is, That if my input is for example 'a' or 'acas' or some other symbols from letters this error raises

Error report:
ORA-06550: line 4, column 20:
PLS-00201: identifier 'A' must be declared
ORA-06550: line 4, column 7:
PL/SQL: Item ignored

And I can`t even check if the input is number or not.

Any solution or advice for that? It would be nice if I could handle this error and raise custom exception.

Upvotes: 2

Views: 1649

Answers (2)

Kirill Leontev
Kirill Leontev

Reputation: 10931

ACCEPT works quite well, no need to create stored objects.

20:19:22 SYSTEM@sandbox> get s:\test\123.sql
  1   accept n number prompt "enter number value: "
  2   declare
  3    myVar number := &n.;
  4   begin
  5    dbms_output.put_line(myVar);
  6*  end;
20:19:30 SYSTEM@sandbox> @s:\test\123.sql
enter number value: adsfadsf
SP2-0425: "adsfadsf" is not a valid NUMBER
enter number value: 12341324
12341324

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

One option would be to define the SQL*Plus script to accept a string rather than a number and then define a function that attempts to convert the input into a number. If you declare a function my_to_number

SQL> ed
Wrote file afiedt.buf

  1  create or replace function my_to_number( p_str in varchar2 )
  2    return number
  3  is
  4    l_num number;
  5  begin
  6    l_num := to_number( p_str );
  7    return l_num;
  8  exception
  9    when others
 10    then
 11      raise_application_error( -20001, p_str || ' is not a number' );
 12* end;
SQL> /

Function created.

Then your SQL*Plus script can look something like this. If the user enters a valid number, the script works as expected. If not, the custom error defined in your function is raised.

SQL> declare
  2    v_n number(3) := my_to_number( '&sv_n' );
  3  begin
  4    dbms_output.put_line( 'The number is ' || v_n );
  5  end;
  6  /
Enter value for sv_n: 123
old   2:   v_n number(3) := my_to_number( '&sv_n' );
new   2:   v_n number(3) := my_to_number( '123' );
The number is 123

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    v_n number(3) := my_to_number( '&sv_n' );
  3  begin
  4    dbms_output.put_line( 'The number is ' || v_n );
  5* end;
SQL> /
Enter value for sv_n: abc
old   2:   v_n number(3) := my_to_number( '&sv_n' );
new   2:   v_n number(3) := my_to_number( 'abc' );
declare
*
ERROR at line 1:
ORA-20001: abc is not a number
ORA-06512: at "SCOTT.MY_TO_NUMBER", line 11
ORA-06512: at line 2

Upvotes: 3

Related Questions