| oracle 9i [message #73647] |
Fri, 11 June 2004 02:15  |
people
Messages: 1
Registered: June 2004
|
Junior Member |
|
|
Hi All,
When I run some reports I get this error message. I have oracle 9.2
maximum no. of expression in a list 1000
Please Help.
Thanks
|
|
|
|
| Re: oracle 9i [message #73655 is a reply to message #73647] |
Sun, 13 June 2004 10:43  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When posting such a question, it helps if you provide more information, such as the error number, the code that produced the erorr, and any other releveant data, such as table structure. Also, this question does not belong in the Utilities forum and should have been posted in the Newbies forum for SQL and PL/SQL.
I am going to guess that you received ORA-01795 and that you were using an IN list. Oracle only allows up to 1000 values in an IN list. If you try to use more than 1000, then you get the error message that you got. Please see the examples below that demonstrate that an IN list works with 1000 variables (expressions) and does not work with 1001. Then, below that I have included an example of a workaround. If the variables are already in a table, then just select from that table. Otherwise, you can use an object or collection or some such thing.
scott@ORA92> -- with 1000 expressions in list:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
2 v_sql VARCHAR2(32767);
3 v_result NUMBER;
4 v_prefix VARCHAR2(1) := '(';
5 BEGIN
6 v_sql := 'SELECT MIN(deptno) FROM dept WHERE deptno IN ';
7 FOR i IN 1 .. 1000 LOOP
8 v_sql := v_sql || v_prefix || i;
9 v_prefix := ',';
10 END LOOP;
11 v_sql := v_sql || ')';
12 EXECUTE IMMEDIATE v_sql INTO v_result;
13 DBMS_OUTPUT.PUT_LINE (v_result);
14 END;
15 /
10
PL/SQL procedure successfully completed.
scott@ORA92> -- with 1001 expressions in list:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
2 v_sql VARCHAR2(32767);
3 v_result NUMBER;
4 v_prefix VARCHAR2(1) := '(';
5 BEGIN
6 v_sql := 'SELECT MIN(deptno) FROM dept WHERE deptno IN ';
7 FOR i IN 1 .. 1001 LOOP
8 v_sql := v_sql || v_prefix || i;
9 v_prefix := ',';
10 END LOOP;
11 v_sql := v_sql || ')';
12 EXECUTE IMMEDIATE v_sql INTO v_result;
13 DBMS_OUTPUT.PUT_LINE (v_result);
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at line 12
scott@ORA92> -- possible workaround
scott@ORA92> -- (best method if the values for the select list are already in a table):
scott@ORA92> CREATE TABLE your_table
2 (col1 NUMBER)
3 /
Table created.
scott@ORA92> BEGIN
2 FOR i IN 1 .. 1001 LOOP
3 INSERT INTO your_table (col1) VALUES (i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
2 v_result NUMBER;
3 BEGIN
4 SELECT MIN(deptno)
5 INTO v_result
6 FROM dept WHERE deptno IN
7 (SELECT col1 FROM your_table);
8 DBMS_OUTPUT.PUT_LINE (v_result);
9 END;
10 /
10
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
