Home » SQL & PL/SQL » SQL & PL/SQL » How to restrict IN parameter length in Procedure (Oracle 11g DB, Windows 7)
How to restrict IN parameter length in Procedure [message #670161] Tue, 12 June 2018 16:06 Go to next message
bala.b@outlook.com
Messages: 4
Registered: December 2017
Junior Member
Hi,

I have created a procedure with IN parameter and this parameter datatype is referring column empname of emp table. Empname datatype is VARCHAR2(10). But while executing the procedure it is allowing more than 10 characters. How to restrict it?

Thanks in Advance.

Desc Emp;
Empno NUMBER(5)
Empname VARCHAR2(10)

Create procedure P1 (p_name emp.empname%TYPE) IS
begin
 Dbms_output.put_line ('Name :'||p_name);
Exception 
   When others then
          Raise_application_error (-20999,'Error in P1 :'||sqlerrm);

End;

EXEC P1('1234567891011231345');
Result=> Name :1234567891011231345

It is accepting morethan 10 chars..
Re: How to restrict IN parameter length in Procedure [message #670162 is a reply to message #670161] Tue, 12 June 2018 17:38 Go to previous messageGo to next message
BlackSwan
Messages: 26033
Registered: January 2009
Location: SoCal
Senior Member
bala.b@outlook.com wrote on Tue, 12 June 2018 14:06
Hi,

I have created a procedure with IN parameter and this parameter datatype is referring column empname of emp table. Empname datatype is VARCHAR2(10). But while executing the procedure it is allowing more than 10 characters. How to restrict it?

Thanks in Advance.

It is accepting morethan 10 chars..
The Calling Procedure determines & controls the length of the VARCHAR2 datatype that is passed to the Called Procedure.
The Called Procedure could check the length of the parameter & throw error when it exceeds 10.
Re: How to restrict IN parameter length in Procedure [message #670165 is a reply to message #670161] Wed, 13 June 2018 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's one way:
SQL> Create or replace procedure P1 (p_name emp.ename%TYPE) IS
  2    subtype mytype is varchar2(10);
  3    l_name mytype := p_name;
  4  begin
  5   Dbms_output.put_line ('Name :'||p_name);
  6  end;
  7  /

Procedure created.

SQL> exec p1('1234567890')
Name :1234567890

PL/SQL procedure successfully completed.

SQL> exec p1('1234567890X')
BEGIN p1('1234567890X'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MICHEL.P1", line 3
ORA-06512: at line 1

I suggest you read WHEN OTHERS to prevent for many problems in your professional life.


Re: How to restrict IN parameter length in Procedure [message #670166 is a reply to message #670165] Wed, 13 June 2018 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or if you want to return your error message:
SQL> Create or replace procedure P1 (p_name emp.ename%TYPE) IS
  2    subtype mytype is varchar2(10);
  3    l_name mytype;
  4  begin
  5   l_name := p_name;
  6   Dbms_output.put_line ('Name :'||p_name);
  7  exception
  8    when VALUE_ERROR then
  9      Raise_application_error (-20999,'Maximum name length 10 (actual: '||length(p_name)||')');
 10  end;
 11  /

Procedure created.

SQL> exec p1('1234567890X')
BEGIN p1('1234567890X'); END;

*
ERROR at line 1:
ORA-20999: Maximum name length 10 (actual: 11)
ORA-06512: at "MICHEL.P1", line 9
ORA-06512: at line 1
Re: How to restrict IN parameter length in Procedure [message #670167 is a reply to message #670166] Wed, 13 June 2018 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can also create your own type but in this case you have to type your input:
SQL> create or replace type mytype is object (ename varchar2(10))
  2  /

Type created.

SQL> Create or replace procedure P1 (p_name mytype) IS
  2  begin
  3   Dbms_output.put_line ('Name :'||p_name.ename);
  4  end;
  5  /

Procedure created.

SQL> exec p1(mytype('1234567890'))
Name :1234567890

PL/SQL procedure successfully completed.

SQL> exec p1(mytype('1234567890X'))
BEGIN p1(mytype('1234567890X')); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

[Updated on: Wed, 13 June 2018 01:36]

Report message to a moderator

Re: How to restrict IN parameter length in Procedure [message #670168 is a reply to message #670167] Wed, 13 June 2018 02:04 Go to previous message
bala.b@outlook.com
Messages: 4
Registered: December 2017
Junior Member
Thanks Michel&Swan..

I will go for object type. so that i can use same type in different objects throughout the application.
Previous Topic: Want to execute 900 query's, is there any faster way to execute
Next Topic: USERENV, and OS_USER
Goto Forum:
  


Current Time: Mon Jun 25 07:28:40 CDT 2018