forall in oracle

I have a table called test_ap  in a remote database and I have the DB link created below is the table structure:

CREATE TABLE test_ap  
(
col1 varchar2(20),
col2 varchar2(20)
);

Open in new window


I want to pass the values from one DB to other remote database which should insert the records by using forall in ... like :



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

-- here I want to use execute immediate to be used 

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

execute immediate p_stmt;

commit;

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

Open in new window

LVL 17
Swadhin Ray Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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;
0
johnsoneSenior Oracle DBACommented:
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;

Open in new window

0
Swadhin Ray Author Commented:
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;

Open 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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
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);
0
Swadhin Ray Author Commented:
@johnsone yes I want to use execute immediate and the bulk collect concept .. if possible.

0
johnsoneSenior Oracle DBACommented:
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;

Open in new window

0
johnsoneSenior Oracle DBACommented:
EXECUTE IMMEDIATE is just going to slow down your code.  You should only use it when you absolutely cannot do it another way.
0
Swadhin Ray Author Commented:
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;

Open in new window

0
johnsoneSenior Oracle DBACommented:
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.
0
Wasim Akram ShaikCommented:
i am not sure how to do this with execute immediate, but you can use for all , this code would work in case of normal dml

i tried to execute using execute immediate but with no luck..!!!
/* Formatted on 2011/12/22 12:24 (Formatter Plus v4.8.8) */
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 l_test_tab(:x)';
   DBMS_OUTPUT.put_line ('sql:' || p_stmt);
   FORALL i IN l_test_tab.FIRST .. l_test_tab.LAST
     INSERT INTO test_ap VALUES l_test_tab(i);   
   
   COMMIT;
   DBMS_OUTPUT.put_line ('control here;');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Open in new window

0
Swadhin Ray Author Commented:
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.

 
0
Wasim Akram ShaikCommented:
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..!!
0
johnsoneSenior Oracle DBACommented:
Again, I still have to wonder why you need to use execute immediate.  You are overcomplicating and slowing down something that is very simple.

As I stated earlier, one approach is to separate out the columns and put in the full statement.  I don't see the difficulty in doing that, but here it is.
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  (col1, col2) VALUES (:x, :y)' ; 

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

commit;

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin Ray Author Commented:
Thanks for all your inputs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.