oracle - Getting error when calling any function in SQL query in a package without declaring function in package specification

Question

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

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.

Answer 1

Nothing to do with forward declaration at all.

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.

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.

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.