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

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?

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;
/

Select allOpen in new window

correct