Dynamic SQL and bulk bind 1

[Dagon]

Can someone confirm what I fear is the case. From Oracle 9 onwards, you can use dynamic SQL with bulk operations but for queries only i.e. you can do:

index_by_table is table of something indexed by binary_integer;
...
execute immediate 'sql-statement' bulk collect into index-by-table;

But there is no corresponding mechanism to use an index-by table in an insert or update statement. If I try to do something like:

execute immediate 'update_statement' using index_by_var;

I get the error "PLS-00457: expressions have to be of SQL types"

I know that, if you're not using dynamic SQL, the syntax of a bulk update statement is to use "forall". But this syntax doesn't seem to be available for dynamic insert or update statements.

 

[LKBrwnDBA]

Did you try:

FORALL i IN 1..index-by_var.count
    EXECUTE IMMEDIATE 'update statement" using index_by_var(i);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb