OraFAQ Forum: SQL & PL/SQL » Using constants

Home » SQL & PL/SQL » SQL & PL/SQL » Using constants

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using constants [message #642534]

Mon, 14 September 2015 02:35

phiroc
Messages: 10
Registered: September 2015

Junior Member

Hello,

I have created a PL/SQL function that contains the below code.

What's the best way to store string constants, such as 'vat_registration,' in PL/SQL?

Should I store them in a table and replace

CASE WHEN (computation_type = 'vat_registration'

by something like

-- create varray containing all values in constant-string-table

CASE WHEN (computation_type = varray(2)

where varray(2) contains 'vat_registration' type

... or is there a better way?

Many thanks.

Phiroc

FUNCTION compute_tax_fn ( vendor_form_id_in IN vendor_form.id%TYPE, computation_type IN vendor_form_tax_comp_types.name%TYPE )
		RETURN VARCHAR2 AS
		political_union VARCHAR2(10);
		country_abbreviation VARCHAR2(3);
		returned_string VARCHAR2(20);
		
		BEGIN
			political_union := vendor_form_political_union_fn(vendor_form_id_in);
			IF (political_union) = 'EU' THEN
				CASE WHEN (computation_type = 'vat_registration'
					OR computation_type = 'vat_reporting_country') THEN
					returned_string := 'FRA';
				WHEN (computation_type = 'vat_default') THEN
					returned_string := 'VAT reporting FRA';
...

Report message to a moderator

Re: Using constants [message #642535 is a reply to message #642534]

Mon, 14 September 2015 02:46

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Consider creating a package (specification) which would contain all the constants you use, and then reference those constants in your PL/SQL code. For example:

create package pkg_global as
  c_computation_type constant varchar2(30) := 'vat_registration';
end;

create function compute_tax_fn ...
  if computation_type = pkg_global.c_computation_type then ...

Report message to a moderator

Re: Using constants [message #642565 is a reply to message #642534]

Mon, 14 September 2015 07:53

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

If the value that is hold in the variable is not going to change over the time then its better to specify it as constant as explained by Littlefoot.
Otherwise its better to use a common paramter table where you can store such variables (not only for the this specific code package but also any other such scenario in your environment).
This looks and and easily maintainable.

Report message to a moderator

Re: Using constants [message #642566 is a reply to message #642565]

Mon, 14 September 2015 08:12

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to take care of the scope you want for your variable value.
If the value is private to a session, it is better to have a package variable.
If the value must be transactional and must see the value changed by other sessions when commited then it is better to have a parameter table.
If the value must be transactional but must not changed during your own transaction then you must change your transaction isolation level.
...

[Updated on: Mon, 14 September 2015 10:15]

Report message to a moderator

Re: Using constants [message #642567 is a reply to message #642566]

Mon, 14 September 2015 09:14

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

Perfect

Report message to a moderator

Re: Using constants [message #642628 is a reply to message #642567]

Tue, 15 September 2015 10:40

phiroc
Messages: 10
Registered: September 2015

Junior Member

Merci, Michel. However, after discussing with cookiemonster in the Dynamic SQL in Cursor thread (http://www.orafaq.com/forum/t/198644/), I have decided to ditch my complicated PL/SQL compute_tax_fn function. Java methods on the server side are much more flexible for my purposes.

[Updated on: Tue, 15 September 2015 10:40]

Report message to a moderator

Previous Topic:	except duplicate record
Next Topic:	Dynamic SQL in cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 19:56:26 CDT 2024