Home » SQL & PL/SQL » SQL & PL/SQL » formal parameter constraining
formal parameter constraining [message #279787] Sat, 10 November 2007 04:44 Go to next message
raji.s
Messages: 52
Registered: February 2005
Member
case 1

Please go through case 1 and then read case 2 and 3. I have got very much confused , please help me to understand logically.

SQL> create or replace procedure p1 ( a out number) is
2 begin
3 a:=1234567891011;
4 exception
5 when others then
6 dbms_output.put_line(' i am here ' || sqlerrm);
7 end;
8 /

Procedure created.

SQL> declare
2 b number(2);
3 begin
4 p1(b);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4


case 2

As regards to case 1, whey here the formal parameter a is getting created as constrained number(7,2).

sal column of emp table is defined as number(7,2)

SQL> create or replace procedure p1 ( a out emp.sal%type) is
2
3 begin
4 a:=1234567889;
5 exception
6 when others then
7 dbms_output.put_line(' i am here ' || sqlerrm);
8 end;
9 /

Procedure created.

SQL> declare
2 b number(3);
3 begin
4 p1(b);
5 end;
6 /
i am here ORA-06502: PL/SQL: numeric or value error: number precision too large

PL/SQL procedure successfully completed.

case 3

As regards to case 2, why in this case the out parameter a is getting constrained as varchar2(1) ..from actual parameter.

SQL> create table abcd ( a varchar2( 8 ));

Table created.

SQL> create or replace procedure p1 ( a out abcd.a%type) is
2 begin
3 a:='12345678';
4 exception
5 when others then
6 dbms_output.put_line(' i am here ' || sqlerrm);
7 end;
8 /

Procedure created.

SQL> declare
2 b varchar2(1);
3 begin
4 p1(b);
5 end;
6 /
i am here ORA-06502: PL/SQL: numeric or value error: character string buffer too
small

PL/SQL procedure successfully completed.

Re: formal parameter constraining [message #279793 is a reply to message #279787] Sat, 10 November 2007 05:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please read the forum guideline on how to format your posts.

Cases 1 and 3 don't error out in your p1 procedure, but p1 sets b to too large a value. This raises an exception in your anonymous block, so the exception is not caught by your p1-exception handler
Re: formal parameter constraining [message #279794 is a reply to message #279787] Sat, 10 November 2007 05:59 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
In Case 2 and 3, it fails out in procedure p1.

my question is , in case 2 i am creating a procedure with formal parameter as emp.sal%TYPE where column sal of table emp is defined as number(7,2). The anonymous pl/sql block completes successfully as the exception raised in p1 is caught in p1 itself. now, the formal parameter a is getting created as number(7,2) so a is unable the hold the value 1234567889 , exception is raised and caught.

why in case 3 , when i create the formal parameter as abcd.a%type where column a of table abcd is defined as varchar2 ( 8 ),
it should have been able to the hold the value '12345678' , but it got created as varchar2(1) based on actual parameter ..dont know why ...
Re: formal parameter constraining [message #279798 is a reply to message #279787] Sat, 10 November 2007 08:06 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
I am amazed to see this.

When IN mode is used , formal parameter a is unconstrained as i am passing quite a large value which should actually not fit in precison 7, scale 2.

sal number(7,2) .... column of emp table

SQL> create or replace function f1 ( a in emp.sal%type) return number is
2 begin
3 return a;
4 end;
5 /

Function created.

SQL> declare
2 a number:=123456789.12;
3 b number;
4 begin
5 b:=f1(a);
6 dbms_output.put_line('val of b is ' || b);
7 end;
8 /
val of b is 123456789.12

PL/SQL procedure successfully completed.


When IN OUT mode is used, the formal parameter a is constrained,
as the function declaration itself raises an exception which is caught in anonymous block.

SQL> create or replace function f1 ( a in out emp.sal%type) return number is
2 begin
3 return a;
4 end;
5 /

Function created.

SQL> declare
2 a number:=123456789.12;
3 b number;
4 begin
5 b:=f1(a);
6 dbms_output.put_line('val of b is ' || b);
7 exception
8 when others then
9 dbms_output.put_line(' here baby ' || sqlerrm);
10 end;
11 /
here baby ORA-06502: PL/SQL: numeric or value error: number precision too large

PL/SQL procedure successfully completed.


Re: formal parameter constraining [message #279853 is a reply to message #279794] Sun, 11 November 2007 02:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hm.. How did I get to the idea that case 3 errorred out in the calling procedure?
Sorry.
Previous Topic: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code?
Next Topic: Dependency check
Goto Forum:
  


Current Time: Wed Dec 07 18:13:27 CST 2016

Total time taken to generate the page: 0.12918 seconds