Solved

is there a way i define constant globally in oracle

Posted on 2014-01-08
11
547 Views
Last Modified: 2014-01-27
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
Comment
Question by:ajaybelde
11 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39765878
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39765925
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
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 39765950
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39765977
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
 

Author Comment

by:ajaybelde
ID: 39766147
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39766153
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 39766164
>>> 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
 

Assisted Solution

by:ajaybelde
ajaybelde earned 0 total points
ID: 39766239
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
 

Author Comment

by:ajaybelde
ID: 39766360
and also can we use Types here?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 39766381
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
 

Author Closing Comment

by:ajaybelde
ID: 39811504
your's suggestion was helpful and i was able to help appteam
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now