An issue with the UTL_FILE and file permissions

Configuration in which only local user is able to write to indicated directory through UTL_FILE running sqlplus, and for any remote access or access by other users, the code will not work properly Some time ago we met with a little weird behavior of a test database. The code in a package did some processing and wrote to specified directory. However it worked only if the user was logged on the database host as him. Calling the code remotely or locally from a different user (also the database owner) ended always with error about wrong operation on file (the writes were issued through UTL_FILE).
The os directory, which was aliased in the database, had mask 0777, so though not owned by oracle, should be accessible by the database.

The explanation is the following:
While the aliased directory was accessible to anybody, the parent directory to it (ie. home directory of the user) was accessible only to him and his group. Thus if any user tried to use the code, the database can not have written to the destination as it requires at least rX permissions on every directory level. To make the code run was to add the oracle user to the local user group or change permissions on the local user home or anything like that.
But why the local user was able to perform the code successfully? This is bound with the way the connection to the database is handed to a user client. When it is done through listener simplifying it prepares a process/dispatcher, then forwards the connection information to the client. If dealing with connection locally it is done differently - the database process becomes a child of the local user session and inherits user' permissions and thus is able to write to the otherwise inaccessible file.

Conditions in UPDATE statements

This is kind of a fix to common developer misuse. I noticed that sometimes see the solution for searching more complex constructs as this:

[..] where f1||f2||f3 in (select f1||f2||f3 from t1)

While in SELECT this is simply weird, because one may simply join 2 tables and specify conditions with f1=f1 etc., it may be considered when we play with DML. Of course this means the optimizer can not use indexes (unless there is some rather complex index on expression), but developers forget about very easy construct:

[..] where (f1, f2, f3) in (select f1, f2, f3 from t1)

Now plans look much better and we still may create condition on several fields at once.

Confusion at partition maintenance

Here an anecdote rather than a hint, but sometimes helps ;-). Once I have read about our "built-in" inability to find own errors and this is exactly about such case.

Lately I tried to add a new partition for 2013 year to some table. It was subpartitioned, so the syntax little more complex (below - please note, this is with error).

alter table schema1.tab1
add partition p1301 VALUES less than (201302) TABLESPACE tbs1 (
SUBPARTITION p1301_1 VALUES(1), 
SUBPARTITION p1301_2 VALUES(2),
SUBPARTITION p1301_3 VALUES(3), 
SUBPARTITION p1301_4 VALUES(4)));

I constructed the statement and tried to execute. But on and on I have got the same error:

ORA-14048: a partition maintenance operation may not be combined with other operations

. And it was not as complex as I may miss an additional operation. So I stuck. After several minutes I ask a colleague of mine for help. He came to me and I have explained him the issue. At the same moment I realize what was wrong - additional parenthesis.
If it would be missing, the error message would be correct

ORA-00907: missing right parenthesis

but with additional parenthesis the statement syntax suggests to the RDBMS something completely different, thus returning more enigmatic error, which in turn puzzled the RDBMS user - in this case me.

Some notes on materialized views

Few loosely notes on materialized views.

It is possible to build a mview on a pre-built table. I like this feature mainly because one is able to drop such view and recreate it with slightly changed definition, while the data is preserved, which is very valuable especially when dealing with large source tables.

Another observation is that sometimes the casting to right types is necessary. We met several times the situation when on Linux some mview definition worked properly while on AIX it failed due to not strong type casting, when the solution was to use CAST with precision to type and length (usually the problem core was longer VARCHAR2 than expected).