• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

is there a way i define constant globally in oracle

I want to define a constant or some macro defined value  in oracle db and use it globally or at schema level. is that possible by any means.
Here was a macro defined for IPNEWVAL address lengths we want  use in various places (db definitions and SPs).

is there a way i can define a value to IPNEWVAL=say some value 120 and use the defined IPNEWVAL in SP,Trigger or constarints

ex: instead of giving constant value 120. i want pas IPNEWVAL which is defined already using that my contsraint can get the value.
CHECK (name NOT MATCHES '' AND LENGTH(name) <= IPNEWVAL)
0
ajaybelde
Asked:
ajaybelde
6 Solutions
 
slightwv (䄆 Netminder) Commented:
This is the way I've seen it done for everything except table check constraints:
create or replace package mypkg
is
	IPNEWVAL number := 120;
	function myfunc return number;
end;
/

show errors

create or replace package  body mypkg
is
	function myfunc return number
	is
	begin
		return IPNEWVAL;
	end;
end;
/

show errors

select mypkg.myfunc from dual;

Open in new window


You cannot use a user defined function in a check constraint.  I don't think you can do it with a check constraint.  Possibly with other mechanisms like triggers.
0
 
sdstuberCommented:
you can use a global context which is usable inside sql, pl/sql and constraints.

just make sure you have the context set before you do your sql or pl/sql.
If you go this route, I recommend putting the "refresh" in a system trigger that fires at db startup.


SQL> CREATE TABLE testn (n NUMBER);

Table created.

SQL> ALTER TABLE testn ADD (CONSTRAINT chk_n_less_than_global_limit CHECK(n <= SYS_CONTEXT('mycontext','ipnewval')));

Table altered.

SQL> CREATE OR REPLACE CONTEXT mycontext USING context_test_pkg ACCESSED GLOBALLY;

Context created.

SQL> CREATE OR REPLACE PACKAGE context_test_pkg
  2  AS
  3      PROCEDURE refresh_context;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY context_test_pkg
  2  AS
  3      PROCEDURE refresh_context
  4      IS
  5      BEGIN
  6          DBMS_SESSION.set_context('mycontext', 'ipnewval', 10);
  7      END;
  8  END;
  9  /

Package body created.

SQL> begin
  2      context_test_pkg.refresh_context;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> insert into testn values(5);

1 row created.

SQL> insert into testn values(15);
insert into testn values(15)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.CHK_N_LESS_THAN_GLOBAL_LIMIT) violated


SQL> select * from testn;

         N
----------
         5

SQL>

Open in new window



The actual value "10" or whatever, should probably be declared as a constant
0
 
johnsoneSenior Oracle DBACommented:
In slightwv's solution, the function is not necessary (and neither is the package body, all you need is the package header).  You can just reference the variable directly as MYPKG.IPRENEWAL.  I have done it that way many times in the past.  You would only need the function if you needed to retrieve the value in a SQL statement.  If your references are through PL/SQL only, then you shouldn't need the function.

If you were to go with slightwv's solution, I would move the variable into the package body and hide the actual variable.  If the only access is through the function, then the variable itself doesn't need to be public.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sdstuberCommented:
in 12c you can reference the package variable in sql without needing the pl/sql function.
You might want to declare it as a constant though instead of just another variable.
0
 
ajaybeldeAuthor Commented:
sdstuber: how would i call the context in trigger. and also i just want to use the conext has a fixed constant like number 100. and should be able to call it in trigger and SP
0
 
sdstuberCommented:
any time you need to find the value you use

SYS_CONTEXT('mycontext','ipnewval')


whether in a trigger, sql statement, constraint, pl/sql function, etc
0
 
sdstuberCommented:
>>> and should be able to call it in trigger and SP

if you ONLY need to call it in a trigger or SP then just use a package constant, or a package function as shown above - easier to maintain and no need to "seed" the value at startup

use the constraint if you need the value to be usable in other criteria as well (like sql or constraints)
0
 
ajaybeldeAuthor Commented:
Is there user defined constants for numbers and error strings that can be defined and brought into
the SP, like SYS_CONTEXT but defined by us?
0
 
ajaybeldeAuthor Commented:
and also can we use Types here?
0
 
sdstuberCommented:
in a stored procedure yes - you can create functions, constants, procedures ,etc   - slightwv's example shows those.

the problem is you have asked for something that can be used in multiple places.

stored procedures
triggers
sql
constraints

no user defined function can be used inside a constraint.  nor can you reference a pl/sql package variable in a constraint or

if your version is 11gR2 or less you can't reference package variables from within sql either.

The only thing that is available for use in procedures,triggers, functions, packages, sql and constraints is sys_context

No - user defined types won't help with this
0
 
ajaybeldeAuthor Commented:
your's suggestion was helpful and i was able to help appteam
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now