What is the best method to declare SP variable ?

ypc,

Method 2, where you use the EMP.NAME%TYPE is highly advised by Steven Feuerstein in his books on PL/SQL programming. The slight disadvantage is that a check on the dictionary has to be done at compile time which makes compilation take that little bit longer.

At run time, and if EMP.NAME changed since compilation - say from VARCHAR2(30) to VARCHAR2(50) - then the SP will be flagged as invalid and autocompiled before its first run since the columns was modified.

So, regardless of the method you use, you still need a recompilation after the column gets modified. In method 1, YOU have to carry out the compilation because oracle wont know about the dependency (unless there are others in the code) but in method 2, Oracle does know about the dependecy, so can do it for you.

In addition, Method 2 allows the SP to be noted as a dependency of the EMP table. This allows you to check for and get an accurate listing back of all dependencies for EMP, should you ever change EMP. (select * from user_dependencies where referenced_name = 'EMP';)

My advice, stick with method 2.

HTH Regards,
Norman.

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------

-----Original Message-----

From: yewpc [mailto:yewpc]On Behalf Of yewpc Posted At: Thursday, March 14, 2002 12:12 PM Posted To: server
Conversation: What is the best method to declare SP variable ? Subject: What is the best method to declare SP variable ?

Hi all, There is 2 well known way to declare SP input, output or in out argument. Example i have a input argument call NAME which is of VARCHAR2(30) To declare this variable : way 1. NAME IN VARCHAR way 2. NAME IN EMP.NAME%TYPE In term of coding, i know that way 2 is better as if the datatype change you don't have to change the SP code.

But will this impact performace may be during compilation or SP execution compare to way 1?

Or is there are bad point for using way 2?

Hope to hear the answer from you guys soon.

Thank you

Regards, ypc

--
Posted via dBforums
http://dbforums.com