How to restrict IN parameter length in Procedure [message #670161] |
Tue, 12 June 2018 16:06 |
|
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 #670167 is a reply to message #670166] |
Wed, 13 June 2018 01:20 |
|
Michel Cadot
Messages: 68693 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
|
|
|
|