CODEWITHSUNDEEP
sqloracle-databaseplsqlplsql-package
Si7ius
Si7ius

Reputation: 748

Why Plsql package variables and constants cannot be used in Sql?

Create some package:

create or replace package my_package is
  some_var number := 10;
end;

and use it in Sql:

select my_package.some_var from dual

So it gives error PLS-221.

P.S. I know that I can use wrapper function. My question is why this is not allowed. Can anyone point to the documentation where the reason?

Upvotes: 3

Views: 1355

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I am going to jump and try to answer your question. It is one I heard of sometimes and indeed has been for a while as a Enhancement Request ( Base ER: ENH 6525013 ), as it was very well pointed by @Anum Kumar.

Why is not possible ? Well, I think the Oracle developers of PL/SQL though about Packages merely as collections of logical subprograms and routines. So make available variables directly to external APIs ( OCI or Java ) was never the intention of the concept.

You don't really have a document link telling you that is not possible, as far as I know, but if you read the documentation of the concept itself, you might get some insights:

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. A package always has a specification, which declares the public items that can be referenced from outside the package. In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram by using a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts.

That is the key, you can map any subprogram of a package to any external Java, C routine; but you can't reference a constant variable directly without the corresponding subprogram ( in your case a function ).

However, you can't use it on SQL, but you can in PL/SQL. Keep in mind that Oracle contains different areas in the Library cache to handle SQL and PLSQL. The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to as program units.

Example

CREATE OR REPLACE PACKAGE pkg IS
n NUMBER := 5;
END pkg;
/  

Package created.

SQL> CREATE OR REPLACE PACKAGE sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  n NUMBER := 5;
END sr_pkg;
/  

Package created.

SQL> select sr_pkg.n from dual ;
select sr_pkg.n from dual
       *
ERROR at line 1:
ORA-06553: PLS-221: 'N' is not a procedure or is undefined


SQL> set serveroutput on
SQL> BEGIN
  pkg.n := 10;
  sr_pkg.n := 10;
END;
/  

PL/SQL procedure successfully completed.

SQL> BEGIN
  DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n);
  DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n);
END;
/  
pkg.n: 10
sr_pkg.n: 5

PL/SQL procedure successfully completed.

In the example above, I can't use the SQL engine to reference a constant variable, as I would need a subprogram or routine ( this is an OCI call, but it might very well be a Java program, ProC, etc. I specifically used one of the packages as serially_reusable that you can check how the variable does not change even if I try to.

However, if I don't use the SQL engine, I can use it without any problem within PL/SQL.

I hope it clarifies.

Upvotes: 3

Related Questions