forall in oracle

I don't believe you can use FORALL.

I believe it is a simple FOR loop.

FOR x IN 1..l_test_tab.COUNT LOOP
--do some stuff
END LOOP;

Seems to me like FORALL would work:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#BABFHGHI


Why do you want/need to use execute immediate?

declare
p_stmt varchar2(1000);
type test_tab is table of test_ap@xx%rowtype index by binary_integer;
l_test_tab test_tab;
 
begin
 
l_test_tab(1).col1 := 'AAA';
l_test_tab(1).col2 := 'BBB';
 
l_test_tab(2).col1 := 'CCC';
l_test_tab(2).col2 := 'DDD';
 
l_test_tab(3).col1 := 'EEE';
l_test_tab(3).col2 := 'FFF';

FORALL x IN 1..l_test_tab.COUNT
  INSERT INTO xxcfi_test@xx  VALUES l_test_tab(x);

commit;

exception
when others then
dbms_output.put_line(SQLERRM);
end;

Select allOpen in new window

Here is the modified code, just in place of db link I created the same table 1st in my current schema but still the error is in  :

DECLARE
p_stmt varchar2(1000);

TYPE test_tab IS TABLE OF
test_ap%rowtype INDEX BY BINARY_INTEGER;
l_test_tab test_tab;
l_test_tab_index BINARY_INTEGER;
BEGIN
l_test_tab(1).col1 := 'AAA';
l_test_tab(1).col2 := 'BBB';

l_test_tab(2).col1 := 'CCC';
l_test_tab(2).col2 := 'DDD';
 
l_test_tab(3).col1 := 'EEE';
l_test_tab(3).col2 := 'FFF';

FOR x IN 1..l_test_tab.COUNT
loop
--p_stmt := 'INSERT INTO xxcfi_test@xx  VALUES l_test_tab(x);' ; 
p_stmt := 'INSERT INTO test_ap  VALUES l_test_tab(x);' ; 
dbms_output.put_line(p_stmt);
end loop;
execute immediate p_stmt;
commit;
exception
when others then
dbms_output.put_line(SQLERRM);
end;

Select allOpen in new window

result as dbms output :

INSERT INTO test_ap  VALUES l_test_tab(x);
INSERT INTO test_ap  VALUES l_test_tab(x);
INSERT INTO test_ap  VALUES l_test_tab(x);
ORA-00911: invalid character

The statement that you are executing should not have a semi colon in it.

p_stmt := 'INSERT INTO test_ap  VALUES l_test_tab(x);' ;

should be

p_stmt := 'INSERT INTO test_ap  VALUES l_test_tab(x)' ;


That is where your invalid character error comes from.

However, once you fix that, you are going to have a problem because execute immediate has no idea what l_test_tab(x) is.

It should be more like:


p_stmt := 'INSERT INTO test_ap  VALUES (' || l_test_tab(x) || ')';


Or really best would be:



p_stmt := 'INSERT INTO test_ap  VALUES (:x)';
execute immediate p_stmt using l_test_tab(x);

@johnsone yes I want to use execute immediate and the bulk collect concept .. if possible.

I'm still not sure why you need to use execute immediate.

Put it all together and you should see this:  

declare
p_stmt varchar2(1000);
type test_tab is table of test_ap@xx%rowtype index by binary_integer;
l_test_tab test_tab;
 
begin
 
l_test_tab(1).col1 := 'AAA';
l_test_tab(1).col2 := 'BBB';
 
l_test_tab(2).col1 := 'CCC';
l_test_tab(2).col2 := 'DDD';
 
l_test_tab(3).col1 := 'EEE';
l_test_tab(3).col2 := 'FFF';

p_stmt := 'INSERT INTO xxcfi_test@xx  VALUES (:x)' ; 

FORALL x IN 1..l_test_tab.COUNT
execute immediate p_stmt using l_test_tab(x);

commit;

exception
when others then
dbms_output.put_line(SQLERRM);
end;

Select allOpen in new window

EXECUTE IMMEDIATE is just going to slow down your code.  You should only use it when you absolutely cannot do it another way.

getting error .. I created the table on my schema

DECLARE
  p_stmt VARCHAR2(1000);
type test_tab
IS
  TABLE OF test_ap%rowtype INDEX BY binary_integer;
  l_test_tab test_tab;
BEGIN
  l_test_tab(1).col1 := 'AAA';
  l_test_tab(1).col2 := 'BBB';
  l_test_tab(2).col1 := 'CCC';
  l_test_tab(2).col2 := 'DDD';
  l_test_tab(3).col1 := 'EEE';
  l_test_tab(3).col2 := 'FFF';
  p_stmt             := 'INSERT INTO test_ap  VALUES (:x)' ;
  FORALL x           IN 1..l_test_tab.COUNT
  EXECUTE immediate p_stmt USING l_test_tab(x); --- getting error expressions have to be of SQL types
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;

Select allOpen in new window

l_test_tab(x) is a rowtype.  You probably need to list out each field.

I haven't done anything like this in a very very long time.  I would suggest the documentation as the place to go for this, if you really want to do it this way.  I always figured it out from the doc.

Thanks all for your inputs but my problem is in Execute immediate ...

As my table is in remote db , where the db link is  created .. which exe imm... need to be implemented.

 

i understand that your table is located in remote db, though you can use the code in above comments without execute immediate even...

instead of code written in above code block

FORALL i IN l_test_tab.FIRST .. l_test_tab.LAST
     INSERT INTO test_ap VALUES l_test_tab(i);  

use this

FORALL i IN l_test_tab.FIRST .. l_test_tab.LAST
     INSERT INTO test_ap@dblink VALUES l_test_tab(i);  

this would do the insert in remote table.

i really didn't understand why do you need to use execute immediate..!!! when your purpose is being served even without using it...

any ways.. all the best for your search..!!

Thanks for all your inputs.