We help IT Professionals succeed at work.

Update using CSV in a vachar

darshita00
darshita00 asked
on
446 Views
Last Modified: 2013-12-18
Hi all,

I have a stored procedure that accepts comma separated value in varchar2 variable. I basically want to update all of the records that this csv value holds so for example something like this...

Is this possible? or my only option is to loop through each csv value and then update each at a time? When I try below code out in a stored procedure I get a
ORA-01722: invalid number error.

PROCEDURE QueueStudies
(
      csv_rids      IN Varchar2            -- Comma separated RID Values
)
AS
BEGIN    
       UPDATE sometable
      SET col5 = 'NO',
      col4 = '',
      col3 = 'NO',
      col2 = '',
      col1 = 'YES'
      where rid in (csv_rids);
END QueueStudies;
Comment
Watch Question

Commented:
darshita -
Try string to table fn as described in the link
http://www.oracle.com/technology/oramag/oracle/07-mar/o27asktom.html 
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
and, yes, you need the str2tbl function as indicated above
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
You would either have to loop through the values or use execute immediate (see example).

Are these numeric keys?  If so, this method should work.  If they are anything other than numeric keys, you need to make sure that the comma separated list has the proper quotation marks.
PROCEDURE QueueStudies
(
      csv_rids      IN Varchar2            -- Comma separated RID Values
)
AS
BEGIN    
      EXECUTE IMMEDIATE 'UPDATE sometable SET col5 = ''NO'', col4 = null, col3 = ''NO'', col2 = NULL, col1 = ''YES'' where rid in (' || csv_rids || ')';
END QueueStudies;

Open in new window

SujithData Architect
CERTIFIED EXPERT

Commented:
How frequently do you execute the code?
If it is an "occasionally" executed program, you may try to use dynamic sql.
(bit of a mess with the single quotes, but will do)
PROCEDURE QueueStudies
(
      csv_rids      IN Varchar2            -- Comma separated RID Values
)
AS
BEGIN     
 execute immediate 	
 	'UPDATE sometable '||
 	'SET col5 = ''NO'' , '||
      	'col4 = null , '||
      	'col3 = ''NO'' , '||
      	'col2 = null , '||
      	'col1 = ''YES'' ||
      	'where rid in ('||csv_rids||')';
END QueueStudies;

Open in new window

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I think a split is in order, or give sventhan all of the points.

I explained what the problem was but sventhan pointed you to a solution (actually several of them)

The other posts could work too, but I don't recommend those.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.