procedure in unix

[ancys]

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

[Stuart Browne]

What SQL server are you using here? I can't tell.