Monday, September 28, 2015

Who is your hero in the Oracle community?

I have my mental list of people in the Oracle community I look up to. They are people who help through social networks, emails, presentations, books, blog posts, or in a friendly conversation. Now, Oracle has recognised that there are many such individuals in the community, people that know a lot and spend much time sharing it with others.

In fact they have asked us to vote on our developer heroes, and they will award them during a big celebration at Oracle Open World. Sure you have met one SQL guru, or a PL/SQL programmer, or someone really concerned about database design? Since I am a DBA I think that we should support our database developers since they are doing their part to make sure that the database, and database application that we will support later is as good as possible.

It starts with a good design, then hopefully as much as possible of the hard work will be done in SQL or PL/SQL close to the data, and finally presented beautifully in a web application done in APEX, possibly delivered through  Oracle REST Data Services to make it extra secure.

What are the alternatives? A bunch of coders that starts out without any planning or previous knowledge of the database (because it is just a persistent storage they don't want to relate to).  They also want to do as much as possible in their own app far away, so they happily offload half the database to do so, and blame it on the network if it does not perform. The end result is a a mess that does not perform or scale, but you get to manage it from release until eternity.

Here is what you need to give something back before it is too late:

Sunday, September 27, 2015

Function to let user B see all tables of user A

In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A:

create type str_set as table of varchar2(30);

create or replace function a_tables return str_set pipelined is
l_str varchar2(30);
for l_str in (select table_name from user_tables)
pipe row(l_str.table_name);
end loop;
grant execute on a_tables to B;

Then user B can see the list of A's table with:

select * from table(a.a_tables);

Trying to solve this with a view in schema A that selects on USER_TABLES does not work, but prove me wrong, please.