SQL & PL/SQL

Hi,

I have one table which has columns like COMP_PART_NUMBER_1 , COMP_PART_NUMBER_2 up to COMP_PART_NUMBER_50

Need to validate columns as shown below,I want to know how to make create dynamic script for the below validation so that there is no need to add same logic for 50 times.

IF rec_SCR_assy.COMP_PART_NUMBER_1 IS NOT NULL THEN
IF LENGTH(rec_SCR_assy.COMP_PART_NUMBER_1) > 8 THEN
v_error_description := NVL(v_error_description,'') || 'The Component 1 Part Number length should not be greater than 8| ';
ELSE
v_upd_script := ' COMP_PART_NUMBER_1 = ''' || rec_SCR_assy.COMP_PART_NUMBER_1 || '''';
END IF;
END IF;



I have tried to create pl sql block as :-


FOR vcnt IN 1..50
LOOP
VFIELD_COMP_PART := 'COMP_PART_NUMBER_' || VCNT;

v_exec_query:=' begin
IF rec_SCR_assy.'||VFIELD_COMP_PART ||' IS NOT NULL THEN
IF LENGTH(rec_SCR_assy.'||VFIELD_COMP_PART||') > 8 THEN
:v_error_description := NVL(:v_error_description,'''') || ''The Component 1 Part Number length should not be greater than 8| '';
ELSE
:v_upd_script := '' rec_SCR_assy.'||VFIELD_COMP_PART||' = '''''' ||rec_SCR_assy.'||VFIELD_COMP_PART ||'||'''''''';
end if;
END IF; end;';

execute immediate V_EXEC_QUERY using v_error_description,v_upd_script;

This code is giving an error as it is not possible to create a bind variable to the cursor in dynamic script.


Please help me to find the solution for it.Thanks in advance.