Handle Oracle stored functions that return user defined type

[TomaszGrzmilas]

I have function in ORACLE package Supplier_API:

FUNCTION Get(supplierid IN VARCHAR2) RETURN Public_Rec;

TYPE Public_Rec IS RECORD (supplier_id VARCHAR2(80), "rowid" ROWID, rowversion DATE, rowkey VARCHAR2(200), rowtype VARCHAR2(80), name VARCHAR2(400), creation_date DATE);

How it is possible to execute this function and get returned data?

[mgravell]

Great question! I'm limited here in that I'm not an Oracle person, and don't have access licenses, tools, etc locally; let me turn the question around: if we were talking raw ADO.NET, how would you access the data there? It might already be possible with the existing extension APIs, or it might be something we can add support for if it is missing, but: it is very hard for me to experiment with those APIs, and the docs I can find are lacking in concrete examples.

[TomaszGrzmilas]

I checked and I think ADO.NET, on the start have problem of accessing user types not declared globally (declared in package). I can't find direct solution using ADO.NET. I think there can be a workaround. If I manage to create proof of concept, I will put it here.

[mgravell]

that'd be great, @TomaszGrzmilas - I'm interested in what we can provide here - but I lack the tools to investigate

[TomaszGrzmilas]

My only idea is to wrap everything in PLSQL code and execute it. That will need to do couple of more database query.

Example:

PACKAGE: Invoice_API
TYPE: Public_Rec(company, invoice_id, rowid, rowversion)
FUNCTION: Get (company_ , invoice_id_ )

We can run it like that and bind variables:

DECLARE
    rec_ Invoice_API.Public_Rec;
BEGIN
    rec_ := Invoice_API.Get(:company_, :invoice_id_);
    :company := rec_.company;
    :invoice_id := rec_.invoice_id;
    :rowid:= rec_.rowid;
    :rowversion:= rec_.rowversion;
END;