| PL/SQL problem [message #38081] |
Mon, 18 March 2002 08:47  |
Anand
Messages: 161
Registered: August 1999
|
Senior Member |
|
|
Hi, I have a problem building the dynamic sql statement
please look at the code below first.
declare
cursor temp_cursor is select * from temp1;
name_val temp_cursor%ROWTYPE;
nop integer(5);
sql_stmt varchar2(300);
angle varchar2(10);
obs varchar2(10);
error varchar2(10);
begin
open temp_cursor;
loop
fetch temp_cursor into name_val;
exit when temp_cursor%NOTFOUND;
for nop in 1..TO_NUMBER(name_val.nop) loop
// angle := 'name_val.angle_' || nop;
// obs := name_val.obs_1;
// error := name_val.error_1;
sql_stmt := 'insert into temp3 values
(temp3_id.nextval,
temp2_id.currval, :angle, :obs, :error)';
EXECUTE IMMEDIATE sql_stmt USING angle, obs, error;
end loop;
end loop;
close temp_cursor;
end;
The problem is in the rows marked with "//".
Based on the value in the "nop" (no of points) variable, I want to generate the column names.
eg. if nop = 2 ,then I want to generate column names,
name_val.angle_1 and name_val.angle_2.
After generating the column names I wanna get the values stored inside the columns for that row(name_val) and assign them to variable "angle".
Same goes for obs and error.
Thanks
Anand
|
|
|
|
| Re: PL/SQL problem [message #38084 is a reply to message #38081] |
Mon, 18 March 2002 17:46  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
Two issues in your code.
1. Size of Those three variable is smaller than size
input data ( 'name_val.angle_' || nop). It should happen concantination of input data.
2. Can't assign number to varchar. The code should be like this ..
angle := 'name_val.angle_' || to_char(nop);
Hope this is helping
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
