ARRAY DML

[jabarreras]

MUST ALL variables declared in the query used with ArrayDML be varArray?

Imagine you want to change the location of several departments to London (in the scott/tiger tables) or you want to raise the salary, with the same amount, to 1000 employees of a total of 5000.
Do I have to declare a varArray with 1000 elements for the salary increase with 250$ for all, for example?

thanks...

 

[Marco Kalter]

All bind variables that you use for Array DML must be an array of equal size. You can additionally use substitution variables or literals for values that are the same for all records. For example:

update dept
   set sal = 2000
 where deptno = :deptno_array

or

update dept
   set sal = :newsal
 where deptno = :deptno_array

In this case :deptno_array is an array of department numbers, and :newsal is a substitution variable with a single new salary value.

------------------
Marco Kalter
Allround Automations

 

[jabarreras]

update dept
   set sal = 2000
 where deptno = :deptno_array

or

update dept
   set sal = :newsal
 where deptno = :deptno_array

In this case :deptno_array is an array of department numbers, and :newsal is a substitution variable with a single new salary value.

[/b][/QUOTE]

update detp
set    loc = :Location
where  deptno = :ArrayDept

where Location is a bind variable and ArrayDept is the array of Dep numbers

In Dephi code we have:

procedure TForm1.Button1Click(Sender: TObject);
var a: variant;
begin
  a:=VarArrayCreate([0,MAX], varVariant);
  a[0]:='10';
  a[1]:='20';
  Qry1.Setvariable('Loc', Edit1.Text);
  Qry1.SetVariable('deptno', a);
  Qry1.Execute;
  OracleSession1.Commit;
end;

But I receive "All arrays must be of equal size for Array DML"

If I declare another varray with the same value for all its elements, it works:

procedure TForm1.Button1Click(Sender: TObject);
var a,b: variant;
begin
  a:=VarArrayCreate([0,MAX], varVariant);
  b:=VarArrayCreate([0,MAX], varVariant);
  a[0]:='10';
  a[1]:='20';
  b[0]:='London';
  b[1]:='London';
  Qry1.SetVariable('Loc',    b);
  Qry1.SetVariable('deptno', a);
  Qry1.Execute;
  OracleSession1.Commit;
end;

[/b][/QUOTE]

[This message has been edited by jabarreras (edited 02 November 2001).]

 

[Marco Kalter]

This will only work if you declare :Location as s substitution variable, and include the quotes in the variable value ('''London''').

Note that substitution variables are replaced in the SQL text with the value before it is sent to the Oracle Server. All other (bind) variables must be arrays of equal size.

------------------
Marco Kalter
Allround Automations