Solved

ora-01795: maximum number of expression in a list is 1000

Posted on 2015-01-13
5
203 Views
Last Modified: 2015-01-24
hi i have the following query

select * from cal_obj
where obj_name in ('obj1','obj3' etc) I have more tha 1000+ obj how can I work around this after I get this values I also what to delete tham am in 11gr2
0
Comment
Question by:chalie001
  • 2
  • 2
5 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40547368
Break them up into several lists:

where
(
obj_name in ('obj1','obj3' etc)
or
obj_name in ('obj1001','obj1003' etc)
)
...

or

Place all the objects in a temp table then do:
where obj_name in (select tmp_object_name from tmp_object_table) ...


There are a couple of other ways but those are probably the most straight forward.

>> I also what to delete tham am in 11gr2

Change the outer most select to a delete?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 40547439
put them into a collection object
collections have no limit on members

select * from cal_obj
where obj_name in (select * from table(your_collection));

simple example...

create or replace type vcarray as table of varchar2(20);

select * from cal_obj
where obj_name in (select * from table(vcarray('obj1','obj3')));
0
 

Author Comment

by:chalie001
ID: 40548582
how can i do this in better way

CREATE OR REPLACE TYPE         "VARCHAR2TABLE"                                          AS TABLE OF VARCHAR2(50)

CREATE OR REPLACE FUNCTION SMS01.F_VARCHAR2_TABLE (ccode_list       IN  VARCHAR2,
     p_delimiter  IN  VARCHAR2 DEFAULT ',')
RETURN varchar2Table
PIPELINED
   IS
      v_string  LONG := ccode_list || p_delimiter;
      v_pos     PLS_INTEGER;
      v_data      varchar2Table :=  varchar2Table();
    BEGIN
     LOOP
       v_pos := INSTR(v_string, p_delimiter);
       EXIT WHEN (NVL(v_pos, 0) = 0);
       v_data.extend;
       v_data(v_data.COUNT) := trim(SUBSTR(v_string, 1, v_pos - 1));
       v_string := SUBSTR(v_string, v_pos + 1);
     END LOOP;
     RETURN ;
   END F_varchar2_Table;
/


CREATE OR REPLACE PROCEDURE SMS01.SMS_SUSPEND_BYCCODE (p_ccode VARCHAR)
IS
--procedure to suspend by ccode and exclude  from a pay run
CURSOR get_agree IS
SELECT a.id aid, a.ssch_id, a.contract_no, b.id ayea_id, b.asta_id, b.status_date, b.comments
FROM SMS_AGREEMENTS a,
SMS_AGREEMENT_YEARS b,
SMS_PARTIES par
WHERE a.id = b.agr_id
AND b.fyea_year = 2011
AND par.id = a.par_id
AND  par.c_code IN (SELECT *  FROM TABLE(cast(F_Varchar2_Table_Pipe(p_ccode)AS VARCHAR2TABLE)));

cnt INTEGER;

BEGIN

cnt := 0;

SELECT COUNT(*) INTO cnt FROM SMS_AGREEMENTS a,
SMS_AGREEMENT_YEARS b,
SMS_PARTIES par
WHERE a.id = b.agr_id
AND b.fyea_year = 2011
AND par.id = a.par_id
AND par.C_CODE IN (SELECT *  FROM TABLE(cast(F_Varchar2_Table_Pipe(p_ccode)AS VARCHAR2TABLE)));

cnt := 0 ;


    FOR agree_rec IN get_agree LOOP
	cnt := cnt + 1;

        UPDATE SMS_AGREEMENT_YEARS
        SET asta_id = 2444,
        --comments = 'UNSUSPEND ON REQUEST BY MICHAEL'
		 comments = 'SUSPEND ON REQUEST BY MICHAEL'
        WHERE id = agree_rec.ayea_id;
    END LOOP;
	COMMIT;

	dbms_output.put_line('data records updated ' || cnt);


END Sms_Suspend_Byccode;
/

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40548778
your function doesn't ever return the collection.

try one of these, one pipelined, one not

CREATE OR REPLACE FUNCTION f_varchar2_table(
    ccode_list    IN VARCHAR2,
    p_delimiter   IN VARCHAR2 DEFAULT ','
)
    RETURN varchar2table
    PIPELINED
IS
    v_string LONG := ccode_list || p_delimiter;
    v_pos    PLS_INTEGER;
BEGIN
    LOOP
        v_pos := INSTR(v_string, p_delimiter);
        EXIT WHEN (NVL(v_pos, 0) = 0);

        PIPE ROW (TRIM(SUBSTR(v_string, 1, v_pos - 1)));
        v_string := SUBSTR(v_string, v_pos + 1);
    END LOOP;

    RETURN;
END f_varchar2_table;
/



CREATE OR REPLACE FUNCTION f_varchar2_table(
    ccode_list    IN VARCHAR2,
    p_delimiter   IN VARCHAR2 DEFAULT ','
)
    RETURN varchar2table
IS
    v_string LONG := ccode_list || p_delimiter;
    v_pos    PLS_INTEGER;
    v_data   varchar2table := varchar2table();
BEGIN
    LOOP
        v_pos := INSTR(v_string, p_delimiter);
        EXIT WHEN (NVL(v_pos, 0) = 0);
        v_data.EXTEND;
        v_data(v_data.COUNT) := TRIM(SUBSTR(v_string, 1, v_pos - 1));
        v_string := SUBSTR(v_string, v_pos + 1);
    END LOOP;

    RETURN v_data;
END f_varchar2_table;
/
0
 

Author Closing Comment

by:chalie001
ID: 40568352
correct
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question