Home » SQL & PL/SQL » SQL & PL/SQL » Alternative to avoid ORA-01795 Error
Alternative to avoid ORA-01795 Error [message #109516] Fri, 25 February 2005 04:11 Go to next message
ksst
Messages: 10
Registered: February 2005
Junior Member
Hi,

I have a PL/SQL procedure that takes an in parameter of type varchar2. I am sending values to this parameter in this format
(val 1, val 2, val 3, ......). When I pass more than 1000 values I get this error.

ORA-01795 : Maximum number of expressions in a list is 1000.

Could anyone suggest as how this error can be avoided. I do not want to spilt the no. of values in the list. More over the vaules come from a java front-end framework.

Is there another datatype that can accept more values in a list at a time? OR Is this error standard for all datatype.
Any help would be really appriciated.

Thanks & regards,
Sri Sai Krishna
Re: Alternative to avoid ORA-01795 Error [message #109548 is a reply to message #109516] Fri, 25 February 2005 12:50 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Here is an approach using a collection type that many of us use with great success. You could easily substitute a StringTable since your list is a bunch of strings.

You can pass the array of strings in from Java, or pass in a delimited list of strings and use a function like f_number_table (below, or the equivalent f_string_table) to turn the list into a collection (array).

Once you have the array, you can use the TABLE function to treat that collection as a table and use it in an IN list and not worry about the limitation you are hitting now.

If the number of elements is always over 1000, you might also consider dumping the array contents into a global temporary table and then using the GTT in the query.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:712625135727

sql>create or replace type NumberTable as table of number;
  2  / 
 
Type created.
 
sql>CREATE OR REPLACE function f_number_table(
  2    p_list       in  varchar2,
  3    p_delimiter  in  varchar2 default ',')
  4    return numbertable
  5  is
  6    v_string  long := p_list || p_delimiter;
  7    v_pos     pls_integer;
  8    v_data    numbertable := numbertable();
  9  begin
 10    loop
 11      v_pos := instr(v_string, p_delimiter);
 12      exit when (nvl(v_pos, 0) = 0);
 13      v_data.extend;
 14      v_data(v_data.count) := trim(substr(v_string, 1, v_pos - 1));
 15      v_string := substr(v_string, v_pos + 1);
 16    end loop;
 17    return (v_data);
 18  end f_number_table;
 19  / 
 
Function created.
 
sql>create or replace procedure p_update_emp
  2  (p_emp_list in varchar2, p_sal in emp.sal%type)
  3  is
  4  begin
  5  update emp set sal = p_sal where empno in (select * from table(cast(f_number_table(p_emp_list) 
as NumberTable)));
  6  end;
  7  / 
 
Procedure created.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369       800
     7499      1600
     7521      1250
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.
 
sql>exec p_update_emp('7369,7499,7521', 10000)
 
PL/SQL procedure successfully completed.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369     10000
     7499     10000
     7521     10000
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.
Re: Alternative to avoid ORA-01795 Error [message #207348 is a reply to message #109548] Tue, 05 December 2006 04:07 Go to previous messageGo to next message
vivek79
Messages: 1
Registered: December 2006
Junior Member
Hi

After following your suggestion to solve this problem in IN clause, I created the function and type in the DB.
Now when i call the function from my java code which looks following

String sqlQuery = "select * from vivektest where Not amount in " +
"(select * from table(cast(temp('"+inString+"',',')as NumberTable)))";


inString contains the very big string seperated by comma. I get the following error while executing the code

select * from vivektest where Not amount in (select * from table(cast(temp('0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41, 42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,8 6,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122, 123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,1 56,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,18 9,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222 ,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255, 256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,2 89,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,32 2,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355 ,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388, 389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,4 22,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,45 5,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488 ,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521, 522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,5 55,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,58 8,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621 ,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654, 655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,6 88,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,72 1,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754 ,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787, 788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,8 21,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,85 4,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887 ,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920, 921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,9 54,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,98 7,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,101 6,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1 043,1044,1045,1046,1047,1048,1049,1010',',')as NumberTable)))
java.sql.SQLException: ORA-01704: string literal too long

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_describe(T4CStatement.java:440)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:979)
at oracle.jdbc.driver.T4CStatement.execute_maybe_describe(T4CStatement.java:483)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1096)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276)
at com.csc.dbtest.DBTEST.main(DBTEST.java:40)

Can you please suggest , what should i do

thanks in advance

vg
Re: Alternative to avoid ORA-01795 Error [message #207367 is a reply to message #207348] Tue, 05 December 2006 04:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Either:
1) Load the numbers into an array first, and then bind the array into the query, or
2) Insert all these values into a Global Temporary Table, and just query from the table.
Previous Topic: Problem with trigger and NULLS
Next Topic: how to perform query based on data in excel file
Goto Forum:
  


Current Time: Thu Dec 08 12:48:08 CST 2016

Total time taken to generate the page: 0.06871 seconds