This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 18, 2013 10:01 AM by rp0428 Go to original post RSS
  • 15. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    thanks Purvesh. Should I get rid of bulk operation with execute immediate over db_link because it is not possible at all wihthin the combination I'm asking about?
  • 16. Re: forall + execute immediate + remote database?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Answered in {message:id=10800196}.

    But I still do not see why 2 separate operations are needed:
    - pull data from remote db into local collection
    - send local collection to remote db

    I would rather ran both these steps as a single remote SQL (or PL/SQL) statement.
  • 17. Re: forall + execute immediate + remote database?
    Purvesh K Guru
    Currently Being Moderated
    943276 wrote:
    thanks Purvesh. Should I get rid of bulk operation with execute immediate over db_link because it is not possible at all wihthin the combination I'm asking about?
    Exactly. This is same what everybody else has been trying to convey. Bulk SQL will never perform better than Plain SQL. Plus you are wasting your expensive PGA in doing so and degrading your performance.

    I would prefer, use of MViews and a process as below:
    1. Create an Updatable MView for each of the tables.
    2. Code to Perform the DML on MViews.
    3. Fast Refresh to Push the data from Local DB to Remote (Master Site) DB

    You negate, the Dynamic SQL and the Bulk operations.
  • 18. Re: forall + execute immediate + remote database?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Example as dynamic code supporting run-time specification of database link. Pushing the local connection into a data type that the remote cursor interface (DBMS_SQL) supports, is not particularly to my liking, as the local collection is duplicated locally in the PGA.
    SQL> --// on remote db
    SQL> create table testtab( id number );
    
    Table created.
    SQL> --// on local database
    SQL> 
    SQL> --// local SQL collection type used for bulk collection
    SQL> create or replace type TIdList is table of number;
      2  /
    
    Type created.
    
    SQL> --// procedure that constructs and executes a remote cursor dynamically
    SQL> create or replace procedure RemoteInsert( dbLink varchar2, idList TIdList ) is
      2          INSERT_TEMPLATE constant varchar2(32767) :=
      3  'declare
      4          idList  DBMS_SQL.number_table@$LINK;
      5          cur     integer;
      6          rc      integer;
      7  begin
      8          select column_value bulk collect into idList from TABLE(:idList);
      9          cur := DBMS_SQL.Open_Cursor@$LINK( cur );
     10          DBMS_SQL.parse@$LINK( cur, ''insert into testtab(id) values( :id )'', DBMS_SQL.Native );
     11          DBMS_SQL.Bind_Array@$LINK( cur, ''id'', idList );
     12          rc := DBMS_SQL.Execute@$LINK( cur );
     13          DBMS_SQL.Close_Cursor@$LINK( cur );
     14  exception when OTHERS then
     15          if DBMS_SQL.Is_Open@$LINK( cur ) then
     16                  DBMS_SQL.Close_Cursor@$LINK( cur );
     17          end if;
     18          raise;
     19  end;';
     20  
     21          plBlock varchar2(32767);
     22  begin
     23          plBlock := replace( INSERT_TEMPLATE, '$LINK', dbLink );
     24          execute immediate plBlock using in idList;
     25  end;
     26  /
    
    Procedure created.
    
    SQL> --// inserting a local collection via a remote bulk bind into remote table
    SQL> exec RemoteInsert( 'dev2', TIdList(0,1,2,3,4,5,6,7,8,9) );
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select count(*) from testtab@dev2;
    
      COUNT(*)
    ----------
            10
    
    SQL> 
    You however need to consider how much more effective this is performance wise, versus a normal remote insert FOR loop ran by local PL/SQL code. The entire local collection needs to be pushed across - and that happens irrespective of the method used to get the local data into the remote database process.

    The question is, does using this interface speed up the transfer of the collection? What it does using a bulk bind on the remote database for the remote insert - for data already on that remote database.

    The data is transferred (in an implicit) fashion by the PL/SQL statement that calls the remote DBMS_SQL interface.

    So all the above "tricks" to get the remote insert to run as a bulk process, may not address the actual transfer of local data to the remote process.

    Instrumentation and analysis are important to determine if the above approach is actually more optimal. It is less flexible though, given the need for dynamic PL/SQL code.
  • 19. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    @Purvesh, @Billy: I was thinking about bulk operations as an advantage over ordinary for loop, not pure SQL. Some pointed me to ordinary for loop solutions, which I know and can use, but I was wondering if there is possibility to take an advantage of bulk with the execute immediate (over db_link) instead. unfortunately I couldn't find simple "no, you cannot do this the way you suggest". Now when I'm clear about it I will consider other possibilities.

    Thank you, I treat your opinion as great help. Along with Billy's answer you both made my day. I have to digest Billy's example now and consider his remarks about performance. Thanks to both of you!
  • 20. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    I was wondering if there is possibility to take an advantage of bulk with the execute immediate (over db_link) instead. unfortunately I couldn't find simple "no, you cannot do this the way you suggest".
    >
    WRONG!

    I provided a link which gave you your 'simple' answer. You just chose to either not read the article or didn't understand it.

    If you read the article fully and carefully you will see this line in it:
    >
    ERROR at line 18: ORA-06550: line 18, column 4: PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables
    >
    That is about as clear as it can be - directly from Oracle.

    It is your own laziness and stubborness that delayed your enlightenment.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points