• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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

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
chalie001
Asked:
chalie001
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
chalie001Author Commented:
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
 
sdstuberCommented:
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
 
chalie001Author Commented:
correct
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now