oracle, accessing to a member function from a view

Question

Firstly, I have this object created by the system :

create or replace 
type type_client (
  num int ,
  username varchar(30),
  balance int,
  ta table_achat,
  ref_admin ref type_admin,

  member function get_prix_achat_total return int
);

And I have a table of this object created by the system:

create table table_client of type_client

And a view also created by the system (it gives each user to select his proper line from table_client):

create view one_line_client as 
select * from table_client
where username = (select user from dual);

Then I give a user called c##client1 -for example- a privilege to access to this view.

My problem is, the user c##client1 can access all data of type type_client except for the member function ;

When I do

select balance from system.one_line_client;

or

select num from system.one_line_client;

then I get the correct result.

But when I do

select olc.get_prix_achat_total() from system.one_line_client olc;

then I get the error :

ORA-00904: "OOO"."GET_PRIX_ACHAT_TOTAL": invalid identifier
00904. 00000 - "%s: invalid identifier"

Answer 1

The asterisk does not mean everything, it is the "all-column wildcard". Member functions must be explicitly listed as a column expression in order to show up in the view:

create view one_line_client as 
select t.*, 
       t.get_prix_achat_total() as get_prix_achat_total 
from table_client t
where username = (select user from dual);

That column expression will indirectly call the function. It must not use parentheses when it is selected from the view:

select olc.get_prix_achat_total from one_line_client olc;