Is using a SELECT inside a pipelined PL/SQL table function allowed?

Question

The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements.

Now, technically, it is possible to use SELECTs without any error from Oracle (ORA 14551 will not occur). However, I have experiences reproducible strange behavior of the select; even though PRAGMA AUTONOMOUS_TRANSACTION is not being used, the rows retrieved by the SELECT seem not always taking the current local transaction into account, which feels like a bug to me. Even more disturbing is the fact that, when using a distributed transaction (for instance via ORAMTS instead of a local transaction), the transaction is used.

Edit: As it turns out, the strange effect seems related to some WITH statements in the query, which sometimes work and sometimes not (depending on the current mood of the Oracle optimizer, at least in 10g). In some cases, I get a ORA-32036, then again it doesn't occur, without changing the code at all. Now it looks as if the queries which sometimes fail with the ORA-32036 are the ones which also fail to use the correct transaction, and it may be unrelated to the pipelined function.

So my specific questions are:

• Is there any, preferably official, statement whether SELECTs in pipelined table functions are allowed and what their transactional context is?

• Is there another way of modularizing commonly used queries which can be used in SQL statements (just as table functions can with TABLE())?

• Has anyone also experienced such behavior and does maybe know more about it? I've looked into metalink, but unfortunately I didn't find anything specific on the topic.

Answer 1

Hi Lucero,

1. usually DML restrictions only concern modification (UPDATE, DELETE ...) statements so SELECT should be OK. I'll try to find a specific statement from Oracle.

2. Views would be your first tool to modularize commonly-used queries.

3. Functions have a drawback over views : if they are called from another SELECT they are not executed at the same point-in-time as the main SELECT. Each call to a SELECT is consistent but since the SELECT are in the function code and not in the main SQL you may return inconsistent results. This is not possible with views and sub-select: if a big statement call a view the view is built at the same point-in-time as the main query.

Update: regarding your comment about parameterized queries

You can build parameterized views, that is views that are dependent upon variables set before execution. Here is an example on AskTom showing how you could do it with userenv('client_info') or dbms_session.set_context.