我不能在SO中找到它,并认为在这里找到它可能是值得的,因为oracle文档没有指定它。
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm
I couln't find this in SO and thought it might be worth finding it here as the oracle documentation doesn't specify it.
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm
最满意答案
在Oracle 10g上测试过。
DECLARE cur SYS_REFCURSOR; vQuery VARCHAR2(32000) := 'SELECT COALESCE(:NULLS) FROM dual'; vNulls VARCHAR2(32000) := '1, 1'; i PLS_INTEGER := 2; BEGIN LOOP OPEN cur FOR REPLACE (vQuery, ':NULLS', vNulls); CLOSE cur; i := i + 1; vNulls := vNulls || ', 1'; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(i || ' ' || SQLERRM); END; 256 ORA-00939: too many arguments for function所以,答案是256
编辑:正如Bob Duell所建议的那样:
SELECT func_id, name, minargs, maxargs FROM V$SQLFN_METADATA WHERE name = 'COALESCE' FUNC_ID NAME MINARGS MAXARGS 387 COALESCE 2 0:(
编辑:
11.2.0.3的限制似乎是65,535。
上面的脚本不适用于那么多参数。 您可以通过创建和运行非常大的SQL语句来测试限制,使用如下所示的脚本。
--Create a COALESCE with 65,536 functions. --It will fail with: ORA-00939: too many arguments for function --But if you remove the last argument it will work, at least on 11.2.0.3. --WARNING: Sending this much data through DBMS_OUTPUT may freeze some tools. begin dbms_output.put_line('select coalesce('); for i in 1 .. 6553 loop dbms_output.put_line('1,2,3,4,5,6,7,8,9,0,'); end loop; dbms_output.put_line('1,2,3,4,5,6'); dbms_output.put_line(') from dual;'); end; /Tested on Oracle 10g.
DECLARE cur SYS_REFCURSOR; vQuery VARCHAR2(32000) := 'SELECT COALESCE(:NULLS) FROM dual'; vNulls VARCHAR2(32000) := '1, 1'; i PLS_INTEGER := 2; BEGIN LOOP OPEN cur FOR REPLACE (vQuery, ':NULLS', vNulls); CLOSE cur; i := i + 1; vNulls := vNulls || ', 1'; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(i || ' ' || SQLERRM); END; 256 ORA-00939: too many arguments for functionSo, the answer is 256
EDIT: As suggested by Bob Duell:
SELECT func_id, name, minargs, maxargs FROM V$SQLFN_METADATA WHERE name = 'COALESCE' FUNC_ID NAME MINARGS MAXARGS 387 COALESCE 2 0:(
EDIT:
The limit on 11.2.0.3 appears to be 65,535.
The above script will not work with that many arguments. You can test the limit by creating and running a very large SQL statement, with a script like the one below.
--Create a COALESCE with 65,536 functions. --It will fail with: ORA-00939: too many arguments for function --But if you remove the last argument it will work, at least on 11.2.0.3. --WARNING: Sending this much data through DBMS_OUTPUT may freeze some tools. begin dbms_output.put_line('select coalesce('); for i in 1 .. 6553 loop dbms_output.put_line('1,2,3,4,5,6,7,8,9,0,'); end loop; dbms_output.put_line('1,2,3,4,5,6'); dbms_output.put_line(') from dual;'); end; /更多推荐
发布评论