0

I created pl/sql procedure where the arguments are given indirectly through a string. But the update is not making changes in the table as it should. The code I am using is :

 declare
ih varchar2(2000);
begin
ih:=q'<'EC359996',
'39216',
'39220',
'2070706',
'2070710',
'EC521555',
'EC521554',
'1944616',
'1852821',
'2047568',
'1501401',
'4500027246',
'1893240',
'1960896',
'4000003058',
'2022319',
'2051178',
'2022860',
'2023110',
'2023114',
'1848439',
'1848442'>';


update ccex.customer c
     set c.cust_credit_analyst    = 'ss',
         c.cust_secondary_analyst = 'ss1',
         c.cust_collector         = 'ss2'
   where c.subscriber_id = '1020'
   and --select count(*) from ccex.customer where 
   cust_account_number in (ih);

   commit;
   /*DBMS_OUTPUT.PUT_LINE(ih);*/

end;

I tried by copying the exact string content and it is updating as it should.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
user3201928
  • 378
  • 1
  • 6
  • 23
  • 1
    See [**Varying IN list of values in WHERE clause**](http://lalitkumarb.com/2015/01/02/varying-in-list-of-values-in-where-clause/) – Lalit Kumar B Dec 16 '15 at 10:21
  • I have marked the string as ih:=q'<'EC359996', '39216', '39220'>' which should be same as 'EC359996','39216','39220', I also tried the solution given: "select ih from dual", still it doesnt work. – user3201928 Dec 16 '15 at 10:26
  • That's why it is a varying IN list. You cannot do that. Either specify it in the WHERE clause itself, or do as it is explained int he topic marked as duplicate. – Lalit Kumar B Dec 16 '15 at 10:30
  • Why did you remove the `oracle` tag? PL/SQL is Oracle's procedural language. – Lalit Kumar B Dec 16 '15 at 10:44
  • Your `ih` variable is just a string. Why should SQL parse it as a nested table without being told to? [Using a comma-separated list in an SQL query](http://www.williamrobertson.net/documents/comma-separated.html) – William Robertson Dec 16 '15 at 12:11
  • Sorry for the duplicate, was not able to find the question in my search.Thank you very much Lalit for the help. Since my format was different from the specified example had little trouble understanding the solution. – user3201928 Dec 17 '15 at 03:28

0 Answers0