Re: Basic DECODE Question.....

From: padderz <member633_at_dbforums.com>
Date: Thu, 20 Feb 2003 11:43:39 +0000
Message-ID: <2552881.1045741419@dbforums.com>

In fact I think restriction is 255 arguments total so given that we lose the initial argument it works out to 127 match/value pairs without a default expression or 126 with a default expression. Try (on TEST environment!) the following...

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production With the Partitioning option
JServer Release 8.1.7.2.0 - Production

SQL> set serveroutput on
SQL> DECLARE
  2 v_arg PLS_INTEGER := 3;
  3 v_arg_lst VARCHAR2 (32767) := '1,1,1';   4 BEGIN
  5 LOOP
  6 EXECUTE IMMEDIATE 'SELECT DECODE (' || v_arg_lst || ')   FROM dual';

  7      v_arg := v_arg + 1;
  8      v_arg_lst := v_arg_lst || ',1';

  9 END LOOP;
 10 EXCEPTION
 11 WHEN OTHERS THEN
 12 DBMS_OUTPUT.PUT_LINE (SQLERRM || ' (at ' || v_arg || '  arguments)');
 13 END;
 14 /
ORA-00939: too many arguments for function (at 256 arguments)

PL/SQL procedure successfully completed.

SQL>

--
Posted via http://dbforums.com

Received on Thu Feb 20 2003 - 05:43:39 CST