CODEWITHSUNDEEP
oracle-databaseplsql
waldrumpus
waldrumpus

Reputation: 2590

Unable to call Oracle package function without declaring it in specification

The following example defines two functions, F1 and F2. F1 calls F2 and uses its result. I would like to make only F1 publicly accessible by declaring it in the package specification, and keep F2 private to the package.

CREATE OR REPLACE PACKAGE PBL1 AS     
  TYPE t_record IS RECORD(
         foo NUMBER
    );

    TYPE t_table IS TABLE OF t_record;

    FUNCTION F1 RETURN t_table PIPELINED;
END PBL1;

CREATE OR REPLACE PACKAGE BODY PBL1 AS
  FUNCTION F2 RETURN t_table PIPELINED AS
    r t_record;
  BEGIN
    r.foo := 17;
    PIPE ROW (r);
  END F2;

  FUNCTION F1 RETURN t_table PIPELINED AS
  BEGIN
    FOR x IN (SELECT * FROM F2())
    LOOP
        PIPE ROW (x);
    END LOOP;
  END F1;
END PBL1;

If I try to compile the package body as is, I receive the error message

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00904: invalid ID
PL/SQL: PLS-000231: Function 'F2' may not be be used in SQL

The error disappears if I add F2 to the package specification, which I would like to avoid.

I read in the answer to this question that this might be due to a context switch to the SQL context, which only knows about public package functions, as opposed to the PL/SQL context, which knows about the private package contents. However, the asker of that question didn't provide sample code, and the answerer seemed to retract their answer, so I'd like to pose this question nonetheless.

Is there a way to call package-private functions from other functions without the need to expose them in the package's public specification?

Upvotes: 3

Views: 1253

Answers (2)

APC
APC

Reputation: 146179

There is no way to use a private PL/SQL function in SQL. That remains true even when the SQL statement is embedded in code in the same package body as that private function. The phrase "context-switching" gets bandied around in this sort of discussion (I myself have done some bandying in the past) but it's a gross simplification, not to say a distortion. But the basic principle remains as you say it:

[SQL] only knows about public package functions

So the question becomes: must your (real) F2() function be pipelined? Can't you just populate a nested table? With that implementation F1() could call F2() then iterate around the returned table. Like this:

CREATE OR REPLACE PACKAGE PBL1 AS     
  TYPE t_record IS RECORD(
         foo NUMBER);

    TYPE t_table IS TABLE OF t_record;

    FUNCTION F1 RETURN t_table PIPELINED;

END PBL1;
/
CREATE OR REPLACE PACKAGE BODY PBL1 AS

  FUNCTION F2 RETURN t_table AS
    r t_record;
    t t_table := new t_table() ;
  BEGIN
    r.foo := 17;
    t.extend();
    t(1) := r;

    return t;    
  END F2;

  FUNCTION F1 RETURN t_table PIPELINED AS
    r t_record;    
    t t_table;
  BEGIN
    t := f2;

    FOR x IN 1 .. t.count()
    LOOP
        r := t(x);    
        PIPE ROW (r);
    END LOOP;

  END F1;
END PBL1;
/

Obviously this may not be the solution you're looking for, but it's the best I can do without knowing your underlying requirement.

Upvotes: 4

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

No, there is not. And the answer you read in the other question hits the nail on the head. Private subprograms may ONLY be referenced by other subprograms and the initialization section of the package.

From the SQL layer, any function you call must be either a schema-level function (create or replace function) or listed in the package specification.

Upvotes: 2

Related Questions