在程序包中的SQL查询中调用任何函数而在程序包规范中未声明函数时出现错误 [英] Getting error when calling any function in SQL query in a package without declaring function in package specification

问题描述

我创建了一个在包装中返回NUMBER类型的函数，但未在包装规格"中声明此函数.

I have created a function which return NUMBER type in a package, but not declare this function in the Package Specification.

我在同一个程序包主体中的另一个函数的SQL查询中调用此函数.我收到错误消息.

I am calling this function in SQL query in anther function with in same package body. I am getting error.

当我在包装规格"中声明功能时，则其&工作.

When i declare function in Package Specification Then its fine & working.

我想知道背后的原因.请任何人解释.

I want to know reason behind it. Please anybody explain it.

推荐答案

与正向声明完全无关.

这涉及到您使用SQL查询来调用函数的事实.似乎在使用语句调用函数时，您不再位于PL/SQL包的范围之内，因此只能调用公共可用的函数.

This deals with the fact that you are using a SQL query to call the function. It seems like when using a statement to invoke a function, you are no longer inside the scope of the PL/SQL package, thus you can only call publicly available functions.

关于原因，我只能猜测，因此请不要以为然，但是 PL/SQL和SQL具有不同的引擎.因此，在执行sql查询时，即使在您的pl/sql程序包中，您也进入SQL级别，它将根据SQL引擎再次检查权限.因此完全不知道它是从PL/SQL包中执行的，应该允许您调用私有函数.

As for the why, I can only guess, so don't take it as granted, but PL/SQL and SQL have different engines. So, when doing a sql query, even inside your pl/sql package, you go to the level of SQL where it'll check again the permissions according to the SQL engine. So it has no idea it is executed from within a PL/SQL package and you should be allowed to call the private function.

我认为可以轻松检查引擎之间的差异，请尝试使用32000的varchar2，它将在您的pl/sql函数中正常工作.现在，如果调用pl/sql函数返回varchar2(32000)，它将失败.这是我遇到的一个问题，但是我没有数据库可以给您摘要.

I think the difference of engines can be checked easily, try to use a varchar2 of 32000, it'll work within your pl/sql function. Now, if you call your pl/sql function returning a varchar2(32000), it'll fail. Thi is a problem I ran into, but I don't have any databse to give you a snippet.

这篇关于在程序包中的SQL查询中调用任何函数而在程序包规范中未声明函数时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！