Home » SQL & PL/SQL » SQL & PL/SQL » PlSql Procedure Parameter
PlSql Procedure Parameter [message #589331] Thu, 04 July 2013 07:54 Go to next message
nithinjosephnalloor
Messages: 6
Registered: July 2013
Junior Member
Hi
i would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;

Here colno is a number column and myparam is varchar. I feel its better to change the parameter to number. But i would like to get some advise before considering to change such occurences across my backend codes.
Re: PlSql Procedure Parameter [message #589333 is a reply to message #589331] Thu, 04 July 2013 07:58 Go to previous messageGo to next message
Littlefoot
Messages: 19536
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If datatypes don't match, Oracle tries to perform implicit conversion. Sometimes it succeeds (for example, converting 1 to VARCHAR2) and sometimes it fails (for example, converting 'A' to NUMBER). Therefore, you'd rather take care about it yourself, because nothing prevents you to call the procedure as
begin
  testa ('A');
end;
which is perfectly valid, while its execution will certainly fail.
Re: PlSql Procedure Parameter [message #589334 is a reply to message #589331] Thu, 04 July 2013 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i would like to know if using varchar parameter in sql queries with number column can result in performance degrade.


Yes, definitively.
Not only performances performances but also several kinds of errors.
You MUST use the same datatype of both side of =.

Regards
Michel
Re: PlSql Procedure Parameter [message #589575 is a reply to message #589331] Mon, 08 July 2013 16:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2207
Registered: May 2013
Location: World Wide on the Web
Senior Member
The following doc shows all possible implicit conversions handled by Oracle as and when required:-
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm
Re: PlSql Procedure Parameter [message #589578 is a reply to message #589575] Mon, 08 July 2013 20:04 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 62
Registered: May 2008
Location: bangalore
Member

Always better to use %type while declaring the parameter in procedure

Please see the below example

Procedure testa (myparam table.colno%varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;

Re: PlSql Procedure Parameter [message #589595 is a reply to message #589578] Tue, 09 July 2013 01:46 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
na.dharma@gmail.com wrote on Tue, 09 July 2013 03:04
Always better to use %type while declaring the parameter in procedure

Please see the below example

Procedure testa (myparam table.colno%varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;



And did you by any chance try to compile your above mentioned example in order to see how oracle reacts ??


Regards,
Dariyoosh
Re: PlSql Procedure Parameter [message #589623 is a reply to message #589595] Tue, 09 July 2013 03:29 Go to previous message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
The advise is correct, the example is wrong.
Previous Topic: invalid rowid
Next Topic: Inconsistent execution plans
Goto Forum:
  


Current Time: Mon Sep 01 13:53:13 CDT 2014

Total time taken to generate the page: 0.13960 seconds