How to define a type within a with block?-CodePudding

This code works:

WITH
    FUNCTION a (a IN INTEGER)
        RETURN INTEGER
    IS
    BEGIN
        RETURN a   1;
    END;
    b(v) AS (SELECT column_value FROM sys.ODCINUMBERLIST(1,2,3)) 

SELECT a (v) FROM  b;

But I would like to define a type in this with statement. Later I want to reuse this type in order to use a pipelined function. Therefore I will need a type which is a table of a record. And the type must be defined outside of the function (not inside the function ) because the type will be returned by the function

I tried with this simple type w.

WITH
    type w is record(w1 integer);
    FUNCTION f (a in integer)
        RETURN INTEGER
    IS
        ret integer;
    BEGIN
        return 2;
    END;
B(b1) as (select 1 from dual)
select f(3) from dual;

It doesn't work:

[Error] Compilation (3: 5): ORA-00900: invalid SQL statement

Is it possible to define a type within a with statement and how can I do that?

CodePudding user response：

The return data type of a function used in a SQL context (as opposed to PL/SQL) must be either a native SQL type or a schema-level user-defined type (a type defined on its own, with the create type statement, rather than defined in a package).

This restriction was not lifted when support for functions defined in the with clause was added in Oracle 12.1 - not even for the data type of a function so defined. In particular, the return data type can't be defined in the with clause of a SQL statement.

Then, records are supported only in PL/SQL; at the schema level, you will need to create an object type, rather than a record type.

NOTE: A pipelined function returning a collection of records (with the record type defined in a package) can be used in a SQL context; the reason is that Oracle defines a corresponding object type, at the schema level, behind the scenes, and takes care of the conversion. Why Oracle doesn't do the same for all functions is something only Oracle can explain. Of course, as you are finding out in another thread you started, pipelined table functions in the with clause are not supported (even though non-pipelined table functions are!)

We don't know the actual problem you are trying to solve, but it seems unlikely that you will be able to do everything you are trying to do in the with clause.

CodePudding user response：

You cannot.

If you look at the SELECT documentation, particularly the syntax diagrams:

with_clause::=

plsql_declarations::=

You can see that you can only declare a function or a procedure.

If you try:

WITH FUNCTION f (a in integer)
 RETURN w
IS
  type w is record(w1 integer);
BEGIN
  return w(2);
END;
SELECT f(3)
FROM   DUAL;

You get the error:

ORA-06553: PLS-313: 'F' not declared in this scope
ORA-06552: PL/SQL: Item ignored
ORA-06553: PLS-498: illegal use of a type before its declaration

You cannot fix that error but, if you magically could (which you cannot using only local declarations), you would get a second error as a RECORD is a PL/SQL only data type and you are then trying to use it in an SQL scope.

For example, if you declared the type globally in a PL/SQL package:

CREATE PACKAGE pkg AS
  type w is record(w1 integer);
END;
/

WITH FUNCTION f (a in integer)
 RETURN pkg.w
IS
BEGIN
  return pkg.w(2);
END;
SELECT f(3).w1
FROM   DUAL;

The query gives the error:

ORA-00902: invalid datatype

You would need to use an SQL OBJECT type and declare it in the global SQL scope before running your query.

For example:

CREATE TYPE w IS OBJECT(w1 INTEGER);

WITH FUNCTION f (a in integer)
 RETURN w
IS
BEGIN
  return w(2);
END;
SELECT f(3).w1
FROM   DUAL;

Outputs:

F(3).W1

2

F(3).W1
2

To use a RECORD, you need to declare the type in a PL/SQL package or in a PL/SQL anonymous block and then use it only in PL/SQL.

For example, if you just want to run your function using a locally declared PL/SQL type then you can do it entirely in a PL/SQL anonymous block:

DECLARE
  TYPE w IS record(w1 integer);
  v_w w;
  
  FUNCTION f (a in integer)
    RETURN w
  IS
  BEGIN
    return w(2);
  END f;
BEGIN
  v_w := f(3);
  
  DBMS_OUTPUT.PUT_LINE(v_w.w1);
END;
/

Outputs:

db<>fiddle here

CodePudding user response：

Your code will work if you define the type within the function like so:

WITH
    FUNCTION f (a in integer)
        RETURN INTEGER
    IS
        ret integer;
        type w is record(w1 integer);
    BEGIN
        return 2;
    END;
B(b1) as (select 1 from dual)
select f(3) from dual;