天道酬勤,学无止境

pipelined-function

SELECT * FROM TABLE(流水线函数):我可以确定结果中行的顺序吗?(SELECT * FROM TABLE(pipelined function): can I be sure of the order of the rows in the result?)

问题 在下面的例子中,我会总是得到“1, 2”,还是有可能得到“2, 1”,你能告诉我你在文档中的哪个地方看到了保证是否存在? 如果答案是肯定的,则意味着在没有ORDER BY和ORDER SIBLINGS有一种方法可以确定 SELECT 语句中的结果集顺序。 CREATE TYPE temp_row IS OBJECT(x number); / CREATE TYPE temp_table IS TABLE OF temp_row; / CREATE FUNCTION temp_func RETURN temp_table PIPELINED IS BEGIN PIPE ROW(temp_row(1)); PIPE ROW(temp_row(2)); END; / SELECT * FROM table(temp_func()); 谢谢你。 回答1 我认为文档中没有任何地方可以保证数据返回的顺序。 2003 年有一个旧的 Tom Kyte 线程(所以可能已经过时),它指出依赖隐式顺序是不可取的,原因与您不依赖普通 SQL 中的顺序相同。 第一:从 SQL 语句中的表函数返回的行的顺序是否与条目“管道”到内部集合中的顺序完全相同(因此不需要 order by 子句)? ... 跟进 2003 年 5 月 18 日 - 世界标准时间上午 10 点: 1)也许,也许不是,我不会指望它。

2021-07-12 14:11:29    分类:技术分享    oracle   plsql   pipelined-function

是否允许在流水线 PL/SQL 表函数中使用 SELECT?(Is using a SELECT inside a pipelined PL/SQL table function allowed?)

问题 流水线函数的文档说,在 SQL 语句(通常是SELECT )中使用 DML 时不允许使用 DML,并且在大多数示例中,流水线函数用于数据生成或转换(接受 custor 作为参数),但不发出任何 DML 语句。 现在,从技术上讲,可以使用 SELECTs 而没有来自 Oracle 的任何错误(不会发生 ORA 14551)。 但是,我遇到了可重复的选择奇怪行为; 即使没有使用PRAGMA AUTONOMOUS_TRANSACTION , SELECT检索的行似乎并不总是考虑当前的本地事务,这对我来说就像一个错误。 更令人不安的是,当使用分布式事务(例如通过 ORAMTS 而不是本地事务)时,会使用事务。 编辑:事实证明,奇怪的效果似乎与查询中的一些 WITH 语句有关,这些语句有时有效,有时无效(取决于 Oracle 优化器的当前心情,至少在 10g 中)。 在某些情况下,我得到一个 ORA-32036,然后它又不会发生,根本不更改代码。 现在看起来好像有时会因 ORA-32036 失败的查询也无法使用正确的事务,并且它可能与流水线功能无关。 所以我的具体问题是: 是否有任何(最好是官方的)声明是否允许流水线表函数中的SELECT以及它们的事务上下文是什么? 是否有另一种方法可以模块化可在 SQL 语句中使用的常用查询(就像表函数可以使用TABLE() )?

2021-07-11 16:30:40    分类:技术分享    sql   oracle   plsql   dml   pipelined-function

PL/SQL - 在流水线函数中立即执行(PL/SQL - execute immediate in pipelined function)

问题 我想在我的流水线函数中执行动态查询并返回此查询的结果。 是否有可能做到这一点? 流水线函数方便我为我的应用程序实现良好的接口,因为它的行为就像一个表。 功能: CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS v_query VARCHAR2(1000); BEGIN v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function EXECUTE IMMEDIATE v_query; --Results of the v_query are compatible with MyTableType's row type. But how to return them from pipelined function? END; 回答1 可以结合动态 SQL 和流水线函数,但返回类型不会是动态的:返回的列的数量和类型将是固定的。 您可以使用 EXECUTE IMMEDIATE 和BULK COLLECT (感谢@be here now)、动态游标或 DBMS_SQL 来返回多于一行

2021-07-10 09:53:59    分类:技术分享    oracle   plsql   execute-immediate   pipelined-function

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

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

2021-06-26 07:53:13    分类:问答    sql   oracle   plsql   dml   pipelined-function

Oracle Pipelined function

I'm trying to create a function which returns an object that can be used in the FROM Clause. According to research on the oracle documentation I've found that a PIPELINED function is what I need. I have this code: CREATE TYPE type_struct AS OBJECT ( i NUMBER ); CREATE TYPE tp_struct AS TABLE OF type_struct; CREATE OR REPLACE FUNCTION gen_nums (na NUMBER, nb NUMBER) RETURN tp_struct PIPELINED IS rec type_struct; counter NUMBER; BEGIN counter := na; WHILE (counter <= nb) LOOP SELECT counter INTO rec FROM dual; counter := counter + 1; PIPE ROW (rec); END LOOP; RETURN; END gen_nums; / The intended

2021-06-22 07:24:22    分类:问答    oracle   plsql   pipelined-function

PL/SQL - execute immediate in pipelined function

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table. The function: CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS v_query VARCHAR2(1000); BEGIN v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function EXECUTE IMMEDIATE v_query; --Results of the v_query are compatible with MyTableType's row type. But how to

2021-06-11 13:42:10    分类:问答    oracle   plsql   execute-immediate   pipelined-function

SELECT * FROM TABLE(pipelined function): can I be sure of the order of the rows in the result?

In the following example, will I always get “1, 2”, or is it possible to get “2, 1” and can you tell me where in the documentation you see that guarantee if it exists? If the answer is yes, it means that without ORDER BY nor ORDER SIBLINGS there is a way to be sure of the result set order in a SELECT statement. CREATE TYPE temp_row IS OBJECT(x number); / CREATE TYPE temp_table IS TABLE OF temp_row; / CREATE FUNCTION temp_func RETURN temp_table PIPELINED IS BEGIN PIPE ROW(temp_row(1)); PIPE ROW(temp_row(2)); END; / SELECT * FROM table(temp_func()); Thank you.

2021-06-09 12:06:53    分类:问答    oracle   plsql   pipelined-function

表函数和流水线函数之间的区别?(Difference between Table Function and Pipelined Function?)

问题 CREATE OR REPLACE TYPE ty_1 AS OBJECT ( fn VARCHAR2(100), sl NUMBER, hd DATE ); CREATE OR REPLACE TYPE ty_1_table AS TABLE OF ty_1; CREATE OR REPLACE FUNCTION FN_RET_COL RETURN ty_1_table AS c ty_1_table := TY_1_TABLE(); BEGIN c.extend; C(1) := TY_1('A', 1, '10-JUN-2013'); c.extend; C(2) := TY_1('B', 2, '11-JUN-2013'); c.extend; C(3) := TY_1('C', 3, '12-JUN-2013'); RETURN c; END; CREATE OR REPLACE FUNCTION FN_RET_PIPE RETURN ty_1_table PIPELINED IS BEGIN PIPE ROW (TY_1('A', 1, '10-JUN-2013')); PIPE ROW (TY_1('B', 2, '11-JUN-2013')); PIPE ROW (TY_1('C', 3, '12-JUN-2013')); END; SELECT * FROM

2021-04-29 23:55:46    分类:技术分享    oracle   plsql   pipelined-function

Difference between Table Function and Pipelined Function?

CREATE OR REPLACE TYPE ty_1 AS OBJECT ( fn VARCHAR2(100), sl NUMBER, hd DATE ); CREATE OR REPLACE TYPE ty_1_table AS TABLE OF ty_1; CREATE OR REPLACE FUNCTION FN_RET_COL RETURN ty_1_table AS c ty_1_table := TY_1_TABLE(); BEGIN c.extend; C(1) := TY_1('A', 1, '10-JUN-2013'); c.extend; C(2) := TY_1('B', 2, '11-JUN-2013'); c.extend; C(3) := TY_1('C', 3, '12-JUN-2013'); RETURN c; END; CREATE OR REPLACE FUNCTION FN_RET_PIPE RETURN ty_1_table PIPELINED IS BEGIN PIPE ROW (TY_1('A', 1, '10-JUN-2013')); PIPE ROW (TY_1('B', 2, '11-JUN-2013')); PIPE ROW (TY_1('C', 3, '12-JUN-2013')); END; SELECT * FROM

2021-04-10 23:30:38    分类:问答    oracle   plsql   pipelined-function