| variable substitution in stored procedure [message #22853] |
Fri, 01 November 2002 09:41  |
Kathy Kult
Messages: 2
Registered: November 2002
|
Junior Member |
|
|
I am having problems with substituting a string variable into my IN Keyword of my Update statement.
Here is my code (simplified version):
DECLARE
i_model_year varchar2(50) := '2006,2007,2008';
BEGIN
UPDATE POPTABLE
SET RATE = 5
WHERE MODEL_YEAR IN (i_model_year)
END;
The code will not update my table.
I have also tried the following:
i_model_year varchar2 50) := '''2006'',''2007'',''2008''');
The only way I could get this to work is if I hard code the years in the where clause:
where model_year IN (2006,2007,2008)
or
only use one year in the i_model_year variable
i_model_year varchar2(50) := '2006';
Any help in this matter would be greatly appreciated!!!!!!
|
|
|
|
|
|
|
|
| Temp solution [message #22861 is a reply to message #22853] |
Fri, 01 November 2002 11:26  |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Here is a temp solution for you for the given scenario,
DECLARE
v_model_year varchar2(50) := '2006,2007,2008';
v_sub_model varchar2(4) := '';
i number := 1;
v_comma_posn Number := 0;
BEGIN
WHILE v_comma_posn >= 0
LOOP
v_comma_posn := instr(v_model_year, ',', i);
If v_comma_posn = 0 Then
v_sub_model := v_model_year;
v_comma_posn := -1;
Else
v_sub_model := substr(v_model_year, 1, v_comma_posn - 1);
End if;
UPDATE POPTABLE
SET RATE = 5
WHERE MODEL_YEAR = v_sub_model;
v_model_year := substr(v_model_year, v_comma_posn + 1);
i := i + 1;
END LOOP;
END;
/
Thx,
SriDHAR
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
