CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldeveloper
Jānis
Jānis

Reputation: 1812

PL/SQL Package Function that returns table querying

I am trying to: Make package that has function that takes number and returns table from another table. And then query this. I have compiled all types with no errors, but when querying I get invalid data type error. I apologise for non-English words in code.

I have object table "Pasutijumi" of type T_Pasutijums:

Create or Replace Type T_Pasutijums As Object(
Pasutijums_ID integer,
Klients_ID integer,
Soferis_ID integer,
Prece varchar2(20),
Cena number,
Daudzums integer,
Statuss varchar2(12));

Then there is package specification:

Create or Replace package Pakete_1 As
 Type Pasutijums_filtrets Is Record(
 Pasutijums_ID integer,
 Klients_ID integer,
 Soferis_ID integer,
 Prece varchar2(20),
 Cena number,
 Daudzums integer,
 Statuss varchar2(12),
 Gala_Cena number);

 Type Pasutijumi_filtreti Is Table Of Pasutijums_filtrets;
 Function Filtret_Pasutijumus(Gala_Cena number) return Pasutijumi_filtreti;

 End Pakete_1;

And package body:

create or replace
Package Body Pakete_1 As
 Function Filtret_Pasutijumus(Gala_Cena number) return Pasutijumi_filtreti Is
  Cursor Kursors1 Is
  Select A.Pasutijums_ID, A.Klients_ID, A.Soferis_ID, A.Prece, A.Cena,
      A.Daudzums, A.Statuss, A.Cena*A.Daudzums
    From Pasutijumi A
    Where A.Statuss!='NORAIDITS' And A.Cena*A.Daudzums>=Gala_Cena;

  Pasutijumi_table Pasutijumi_filtreti;
  Begin
  Open Kursors1;
    Loop
      Fetch Kursors1 Bulk Collect Into Pasutijumi_table;
      Exit When Kursors1%NOTFOUND;
    End Loop;
    Close Kursors1;
    return Pasutijumi_table;
  End;
End;

But when I try to query:

Select *
From table(pakete_1.filtret_pasutijumus(20));

I get SQL Error: ORA-00902: invalid datatype. What is the problem here?

Upvotes: 1

Views: 159

Answers (1)

Allan
Allan

Reputation: 17429

In order for SQL to have access to the record and table types, they must be database objects, rather that package declarations.

create or replace Type Pasutijums_filtrets Is Object(
 Pasutijums_ID integer,
 Klients_ID integer,
 Soferis_ID integer,
 Prece varchar2(20),
 Cena number,
 Daudzums integer,
 Statuss varchar2(12),
 Gala_Cena number);

create or replace Type Pasutijumi_filtreti Is Table Of Pasutijums_filtrets;

Create or Replace package Pakete_1 As
 Function Filtret_Pasutijumus(Gala_Cena number) return Pasutijumi_filtreti;
End Pakete_1;

Upvotes: 3

Related Questions