如何动态更改PL/SQL包中全局变量的值? [英] How to change the value of a global variable in a PL/SQL package dynamically?

问题描述

在我们的Oracle数据库中的一个PL/SQL软件包中，有一个全局变量g_file_path，它指向系统上将存储某些文件的位置:

In one of the PL/SQL packages in our Oracle database, there's a global variable, g_file_path, that points to a location on the system where certain files will be stored:

create or replace
PACKAGE xyz_package
AS

...

   g_file_path     VARCHAR2 (80) := '/usr/tmp';

...

此变量在整个软件包的各种UTL_FILE操作中使用.

This variable is used in various UTL_FILE operations throughout the package.

不幸的是，选择的路径不足，我需要弄清楚如何根据数据库运行的环境动态设置路径，例如因此路径变为/opt/ENVDB/xyz，其中ENVDB随环境而变化.

Unfortunately, the path chosen is inadequate, and I need to figure out how to set a path dynamically depending on the environment where the database is running, e.g. so the path becomes /opt/ENVDB/xyz, where ENVDB changes depending on the env.

一个想法是模拟shell脚本的行为:

One idea is to emulate the behavior of the shell script:

>echo $XYZ_DB_TOP

指向一个可以存储文件的合适文件夹.我想不出一个合适的PL/SQL函数来模拟这种行为.有什么聪明/简单的解决方案可以解决这个问题吗?感谢您的帮助！

That points to a suitable folder where the files can be stored. I can't think of a suitable PL/SQL function that emulates this behavior though. Any smart/simple solution to this problem? Any help is appreciated!

推荐答案

如果您使用的是Oracle 9i或更高版本，则应使用目录对象.这样比较安全，因为它只允许使用完整的路径(不允许使用通配符).与在init.ora文件中使用UTL_FILE_DIR不同，它也不需要重新启动数据库.而且它更加安全，因为我们可以将每个目录的特权授予特定的个人用户.

If you're using Oracle 9i or higher you should use a directory object instead. This is safer, because it only permits complete paths (no wildcards). It also doesn't require a database restart, unlike using UTL_FILE_DIR in the init.ora file. And it is far more secure because we can grant privileges on each directory to specific individual users.

但是，现在最让您感兴趣的方面是目录对象的抽象使更改实际OS路径变得很困难，因此在每个环境中它都可以有所不同.就是这样:

But the aspect that will interest you the most right now is the that the abstraction of the directory object makes it a cinch to change the actual OS path, so it can be different in each environment. Just like this:

alter directory temp_data as '/home/oracle/tmp';

了解更多信息.

这篇关于如何动态更改PL/SQL包中全局变量的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！