Hi all,
I have a table which has two columns
Criterian no CRT1
UPD_STATEMENT
update table_case set title=:' || CRITERIA_NUMBER || ', support_msg=:' || FILE_NAME || ' where objid=268435458;
update table_case set title=:' || CRITERIA_NUMBER || ', support_msg=:' || FILE_NAME || ' where objid=268435459;
For this CRT1 ther are two update statement so i want the procedure to execute two update stmnts
create or replace procedure proc2(CRITERIA_NUMBER varchar2,FILE_NAME varchar2 ) is
str varchar2(2000);
NOC number(7,2);
pos Number(7,2);
counter Number(7,2);
tempstr varchar2(1000);
cutchar varchar2(1000);
Begin
SELECT upd_statement INTO str FROM cfg_batchimporttool WHERE criteriano=CRITERIA_NUMBER;
tempstr:=str;
IF(LENGTH(str)>0)
THEN
dbms_output.put_line('Total Length ' ||LENGTH(str));
NOC:=LENGTH(str);
counter:=1;
WHILE(counter<=NOC)
LOOP
pos:=INSTR(tempstr,';');
if(pos=0)
THEN
EXIT;
END IF;
cutchar:=SUBSTR(tempstr,1,pos-1);
IF(LENGTH(tempstr)!=0)
THEN
BEGIN
dbms_output.put_line('ORIGINAL ::::'||cutchar);
EXECUTE IMMEDIATE cutchar using CRITERIA_NUMBER,FILE_NAME;
tempstr:=SUBSTR(tempstr,(pos+1),NOC);
END ;
ELSE
EXIT;
END IF;
counter:=(pos+1);
END LOOP;
END IF;
END proc2;
This procedure works fine when i hard code the values for ilename and criteriano directly in update stmnt.But when use variable and try to assign procedure param to these variables using execute immediate it shows an error
EXECUTE IMMEDIATE cutchar using CRITERIA_NUMBER,FILE_NAME in this line
A host/bind variable invalid
Wat change i need to make either in procedure r tin the update stmnt int the table
But this procedure is not executed i need to call procedure in unix
