Is SELECT used inside a PL / SQL table pipelined function?
The docs for pipelined functions say that DML is not allowed if used in an SQL statement (usually SELECT
), and in most examples pipelined functions are used to generate or transform data (taking a custor parameter as a parameter), but do not issue any DML statements.
Now it is technically possible to use SELECT without any error from Oracle ( ORA 14551 will not happen). However, I have experience of reproducible strange selection behavior; even if PRAGMA AUTONOMOUS_TRANSACTION
not used, the rows retrieved with SELECT
do not always respect the current local transaction, which seems like a bug to me. Even more disturbing is the fact that when using a distributed transaction (for example, via ORAMTS instead of a local transaction), the transaction is used.
Edit: As it turns out, a strange effect seems to be related to some WITH clauses in the query, which sometimes work and sometimes don't (depending on the current Oracle optimizer sentiment, at least in 10g). In some cases I get ORA-32036 and then again it doesn't, with no code change at all. Now it looks like the requests that sometimes fail with ORA-32036 are the ones that also cannot use the correct transaction and may not be related to the pipelined function.
So my specific questions are:
-
Is there, preferably an official, assertion whether
SELECT
tables are allowed in pipelined functions and what is their transactional context? -
Is there another way to modulate commonly used queries that can be used in SQL statements (just like table functions can be with
TABLE()
)? -
Has anyone also experienced this behavior and perhaps knows more about it? I looked at metalink, but unfortunately I didn't find anything specific in this thread.
source to share
-
Usually DML restrictions only apply to modification statements (UPDATE, DELETE ...), so SELECT should be ok. I will try to find a specific expression from Oracle.
-
Views will be your first tool for modulating frequently used queries.
-
Functions have a drawback over views: if they are called from another SELECT, they are not executed at the same time as the main SELECT. Each SELECT call is consistent, but because the SELECT is in the function code and not in the main SQL, you might return inconsistent results. This is not possible with browsing and subselecting: if a large statement calls a view, the view is created at the same point in time as the main query.
Update : regarding your comment about parameterized queries
You can create parameterized views, that is, views that depend on variables set before execution. Here is an example from AskTom showing how you could do this with userenv('client_info')
or dbms_session.set_context
.
source to share