更改 nls_length_semantics 后重新编译包
我有一个数据库，其中 nls_length_semantics 值已从字节更改为字符。是否有必要重新编译所有具有外部可访问的基于 varchar2 的数据类型（例如 dbms_sql）的包？
I have a database where the nls_length_semantics value has been changed from byte to char. Is it necessary to recompile all the packages that have externally accessible varchar2 based data types (for example dbms_sql)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为@GWu 是错误的。至少，他在精神上是错的。这不是想要的问题，而是需要的问题。然而，他所说的也是正确的：这取决于。
如果数据库中的基础数据是多字节的，您确实需要重新编译包。
今天我遇到了问题，尽管我的表中的基础数据是 Unicode 格式的，但包是以 BYTE 格式编译的。这些表已明确定义为（例如）VARCHAR2(20 CHAR)，而我的 PL/SQL 包中的代码仅定义为 VARCHAR2(20)。
这意味着数据库中定义长度为 50 的列并不总是适合定义为长度 50 的 PL/SQL 变量。因此，我的用户报告了很多“字符缓冲区太小”类型的错误。
如果你和我有同样的情况，需要重新编译软件包。
然而，我真正想知道的是，我如何知道包是否以 BYTE 格式编译。这样我就可以准确地知道哪些包需要重新编译。在不知道这一点的情况下，我没有办法主动去重新编译它们。我只需要等待用户报告问题即可。
我想我当然可以重新编译它们，但这会锁定我的数据库，而用户仍在其中。所以我必须再次安排一些停机时间来解决整个问题:-(
--编辑--
在发布之前应该阅读@AlexPoole 的评论。视图
dba_plsql_object_settings给出我需要的信息！@GWu is wrong in my opinion. At least, he is wrong in spirit. It's not a matter of WANT, it's a matter of NEED. However, he is also correct in the sense that he says: it depends.
You do NEED to recompile your packages, if the underlying data in the database is multi-byte.
I've had problems today where the packages were compiled in BYTE format, although the underlying data in my tables is in Unicode. The tables have been defined explicitly as being (for example) VARCHAR2(20 CHAR), while the code in my PL/SQL packages have only been defined as VARCHAR2(20).
This means that a column in the database defined with length 50, does not always fit into a PL/SQL variable defined as being length 50. My users have been reporting a lot of "character buffer too small" kinds of errors as a result.
A recompile of the packages is required if you have the same case as me.
However, what I'd really like to know, is how do I know if a package was compiled in a BYTE format. This way I would know exactly which of my packages need to be recompiled. Without knowing this, I don't have a method of pro-actively going out there to re-compile them. I just have to wait for the user to report the problem.
I suppose I could re-compile them all of course, but that would lock my database while users are still in it. So I have to schedule some downtime again to fix the whole problem :-(
--EDIT--
Should have read comment from @AlexPoole before posting that. The view
dba_plsql_object_settingsgives the information I need!简短的回答：这取决于:-)
如果您希望您的现有包使用 char 语义，则需要重新编译。否则它们将保留编译时的设置。从技术角度来看，不需要重新编译。
并且绝对不需要重新编译 SYS 拥有的包（如 dbms_sql）。
Short answer: it depends :-)
If you want your existing packages to use char semantics, you need to recompile. Otherwise they will stay with the setting they had at compile time. From a technical point of view, there is no need to recompile.
And there is definitely no need to recompile SYS owned packages (like dbms_sql).