This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 18, 2013 10:01 AM by rp0428 RSS

forall + execute immediate + remote database?

946279 Newbie
Currently Being Moderated
There are two database: DB1 local and DB2 remote, both Oracle 11g R2.
On local database DB1 I collect some data from remote one with the help of the following code:
..
v_acct_id := 999;
..
v_stmt := 'select x, y from some_table@' || db_link_to_DB2 || ' where acct_id = :val';
..
execute immediate v_stmt bulk collect into v_collection using v_acct_id;
..
The code above fills the v_collection with values of x, y properly.
Now I would like to delete remotely from some_other_table based on the values {x} just collected and with use of forall operator (ie. using bulk operation). If I was working on DB2 database what I would like to do could be coded like this:
delete some_other_table
where x in ( select x from some_table where acct_id = 999 );
But is it possible to archive the same result with the help of FORALL operator, EXECUTE IMMEDIATE and v_collection over db link so that this could be invoked on local DB1 database? If so, how this can be coded?

thank you
  • 1. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    If I was working on DB2 database what I would like to do could be coded like this:
    >
    So why don't you code it that way? Why are you collecting data on the first DB if it already exists on the second DB and you want to delete data from a table on the second DB?

    You need to clarify which DB each of the tables is on.
  • 2. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    We don't have direct access, we can only use db_links.

    both tables are on DB2 as db_link states.
  • 3. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    We don't have direct access, we can only use db_links.

    both tables are on DB2 as db_link states.
    >
    The 'db_link' doesn't state any such thing. This query that you said you want to execute doesn't have a db link in it
    delete some_other_table
    where x in ( select x from some_table where acct_id = 999 );
    So there was no way to know what server the 'some_other_table' is on.

    My question is why you don't just add db links to that query?
    delete some_other_table@link1
    where x in ( select x from some_table@link1 where acct_id = 999 );
    Doesn't that 'only use db_links'?
  • 4. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    you completely messed it up. I said "*IF* I was working on DB2 database what I would like to do could be coded like this"

    I'm asking not about "what I would do *IF*..." but I'm asking about how this could be coded as bulk statement with the help of db_link, execute immediate and forall while working on DB1.

    this delete statement would not be hardcoded, it would be created while code is executed: different tables, different db_links etc. this is why I have to use execute immediate. moreover, we do something with this x,y values on DB1, and if we even could execute code directly on DB2 we would still need to send x, y values to DB1 somehow to do things there. since DB2 doens't have db link to DB1 it would mean some file transfer, which we would like to avoid.

    So, the question is, if dynamically built delete statement can be bulk-executed on remote database with those access/create objects restrictions on DB2?
  • 5. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    I'm not the one messing things up. You apparently seem to be withholding information from us.

    We can only go by the information you provide which is:

    1. ALL tables are on DB2.
    2. X,Y come from DB2
    v_stmt := 'select x, y from some_table@' || db_link_to_DB2 || ' where acct_id = :val';
    3. You want to delete data on DB2
    delete some_other_table
    where x in ( select x from some_table where acct_id = 999 );
    So why this?
    >
    if we even could execute code directly on DB2 we would still need to send x, y values to DB1 somehow to do things there
    >
    No you don't - just don't bring X,Y to DB1 to begin with.

    You don't provide any reason at all for bringing X and Y from DB2 to DB1 if you want to use them on DB2 to delete data on DB2.

    Just delete the data on DB2. Go ahead and use execute immediae if you want to.

    Now you seem to be saying something different than anything you posted before
    >
    moreover, we do something with this x,y values on DB1
    >
    That is the FIRST time you mentioned doing ANYTHING on DB1 other than creating a collection to use to delete on DB2.

    Are you now saying that you want to create/update/modify a collection on DB1 that will be used to delete data on DB2 and that some of this data in the collection DID NOT come from DB2?

    So is this the problem you are asking about?
    http://stackoverflow.com/questions/10534390/overcoming-the-restriction-on-bulk-inserts-over-a-database-link
  • 6. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    please explain how more information why I do what I do can influence the syntax I want to get? can you abstract a situation? treat it as a lerning curve: I just would like to know how to do this if it is possible with bulk processing.

    does it make the problem more digestible for you? I would like to know the syntax for bulk processing on remote database with the help of execute immediate if such syntax is possible.

    and no, I'm not looking for using for loop, im looking for answer if using bulk processing with above situation is possible. I just can not imagine that this question is not clear: "But is it possible to archive the same result with the help of *FORALL operator, EXECUTE IMMEDIATE and v_collection over db link* so that this *could be invoked on local DB1* database? If so, how this can be coded?". it it you I let myself go into more details while the problem was clear from the beginning.

    thanks
  • 7. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    . it it you I let myself go into more details while the problem was clear from the beginning.
    >
    NO! The problem wasn't clear from the beginning. You said ALL tables, and by inference all data, was on DB2. Then you appear to say that you want to change some of that data on DB1 before it is used to delete data on DB2.
    >
    "But is it possible to archive the same result with the help of FORALL operator, EXECUTE IMMEDIATE and v_collection over db link so that this could be invoked on local DB1 database? If so, how this can be coded?"
    >
    People are trying to help you. But you aren't acting as if you want help.

    Why aren't you answering the questions you are asked?
    >
    Are you now saying that you want to create/update/modify a collection on DB1 that will be used to delete data on DB2 and that some of this data in the collection DID NOT come from DB2?

    So is this the problem you are asking about?
    http://stackoverflow.com/questions/10534390/overcoming-the-restriction-on-bulk-inserts-over-a-database-link
    >
    Did you look at that blog? It doesn't appear so since it answers the question you are now asking.

    And it's fine to open additional threads but when they are related you need to cross-link them so people can follow the entire chain of issues
    select from collection?
  • 8. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    you assume too much. the problems are not related at all. that's how you interpret it. I appreciate your efforts to help but you try to solve some problem which I'm not asking about. and I realise that there may be many people for whom your asnwer will be very helpful. but not for me. I'm not asking about how to delete records remotely, I'm just asking if it is possible to do this with particular syntax used. the answer I would expect would be (for example): "no, it cannot be done this way. but if you're looking for solution for deleting the rows remotely there are other approaches you can take, for example ....'. but I'm not looking for deleting rows remotely alone, I'm looking for deleting rows remotely with "forall, execute immediate over db_link'.

    thanks
  • 9. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    I'm looking for deleting rows remotely with "forall, execute immediate over db_link'.
    {quote}
    And yet you clearly haven't read the article I gave you the link to that explicity, and unequivocally, answers that question.

    No one on the forums is simply going to fill in the blanks of an answer that you have pre-formatted.

    It is YOUR responsibility to take the portions of the advice that are applicable to your situation and apply them appropriately.

    Editorialisiing about how you would like to have answers structured, and similar off-topic statements are unprofessional and do not contribute to the technical dialogue the forums are meant for.

    I suggest you switch your focus to substance rather than form.
  • 10. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    and I suggest you focus on understanding questions and not assuming too much. I have seen already many pages with for loop solutions, I went through a documentation that forall is not allowed with inserts/delete on remote databases (as it is in this blog). that's why I asked about incorporating execute immediate! and the blog you provided is focused the same way as you are focused when aswering: solve more general situation. your answer would suit me if I wanted to go with ordinary loop, but I don't want, and I said this, untill I know that incorporating execute immediate in some way won't help to utilize bulk processing.
  • 11. Re: forall + execute immediate + remote database?
    rp0428 Guru
    Currently Being Moderated
    >
    and I suggest you focus on understanding questions and not assuming too much
    >
    That is what I'm focused on. That is why I ask you direct, to the point, questions which you have still chosen not to answer.

    And the likely reason for that is because it is YOU who are assuming too much.
  • 12. Re: forall + execute immediate + remote database?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The basic issue seems to be: why pull remote data into a local collection and then attempt to push that local collection back to the remote db via a bulk operation?

    Why not run both these steps as a single SQL statement on the remote db?
  • 13. Re: forall + execute immediate + remote database?
    946279 Newbie
    Currently Being Moderated
    @Billy: I mentioned it earlier:
    a) we have to do something with pulled data on local database based on {x} and {y} set
    b) there is no db_link on remote directing to local
    c) remote is not ours, we just have db_link, we can't create anything there
    d) the statement is not constant, it is dynamic, even it was constant it is not supported by forall
    e) forall is the first attempt to solve because of performance compared to for loop
    f) we cannot login directly, we are not allowed to do this, we have to use db_link

    the question is simple: is is possible to perform bulk operation ( forall ) with the use of dynamically build sql ( execute immediate ) on remote database but invoked locally and using db_link to that database?

    if it is possible please give some example, if it is not possible just please say this explicitly I will assig it as solved and give correct/helpful marks.

    the subject is very clear about the problem, the question was clear about the problem, I cannot understand why rp tries to solve some other problem. I don't need this. rp suggestions are completely useless pointing me to a blog which doesn't mention execute immediate at all! he just doesn't understand what I'm asking about (forall + execute immediate + dblink), he just wants me to read about ordinary loops to solve some problem which he thinks is my problem.
  • 14. Re: forall + execute immediate + remote database?
    Purvesh K Guru
    Currently Being Moderated
    943276 wrote:
    There are two database: DB1 local and DB2 remote, both Oracle 11g R2.
    On local database DB1 I collect some data from remote one with the help of the following code:
    ..
    v_acct_id := 999;
    ..
    v_stmt := 'select x, y from some_table@' || db_link_to_DB2 || ' where acct_id = :val';
    ..
    execute immediate v_stmt bulk collect into v_collection using v_acct_id;
    ..
    The code above fills the v_collection with values of x, y properly.
    Now I would like to delete remotely from some_other_table based on the values {x} just collected and with use of forall operator (ie. using bulk operation). If I was working on DB2 database what I would like to do could be coded like this:
    delete some_other_table
    where x in ( select x from some_table where acct_id = 999 );
    But is it possible to archive the same result with the help of FORALL operator, EXECUTE IMMEDIATE and v_collection over db link so that this could be invoked on local DB1 database? If so, how this can be coded?

    thank you
    Considering your explanation, I would suggest you, as already done by others, to get rid of thought of Bulk Operation. Even the use of Execute Immeditate is debatable, since you should be knowing the Remote Database to be referred to query the data. However, that being besides the point, I will choose not to confront you on that.

    Did you consider the thought of using Updatable Materialized views? Read Here for more information.

    It will not only prevent your Across the Network updates on master table, but also allow you to remove the Execute Immediate code as you will already have an MView created and it will be like accessing it from Local.

    Only care that you will have to take is, scheduling the Refresh of the MView so that the details are pushed back to Master tables. On Commit Refresh could be one solution (Read Here for information).
1 2 Previous Next

Legend

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