Solved

is there a way i define constant globally in oracle

Posted on 2014-01-08
11
645 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 77

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 74

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 35

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 74

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
 
LVL 74

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 74

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 74

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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 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