This discussion is archived
9 Replies Latest reply: Jan 17, 2013 10:42 PM by BillyVerreynne RSS

select from collection?

946279 Newbie
Currently Being Moderated
there is the following code
declare
  type r is record( rid rowid, acct_id number );
  type t is table of r;
  v t := t();
begin
  ...
  execute immediate v_sql bulk collect into v;
  ...
end;
execute immediate fills collection with some data provided by v_sql statement: rowid of some table and acct_id as a number. Is it possible to use this collection in a way so that it would be possible to write later in this code:
delete ACCOUNTS@db_link_to_remote_database
where acct_id in ( *[here comes some magic subquery that supplies acct_id from collection]* )
If it is possible how this can be done?
  • 1. Re: select from collection?
    rp0428 Guru
    Currently Being Moderated
    >
    execute immediate fills collection with some data provided by v_sql statement: rowid of some table and acct_id as a number. Is it possible to use this collection in a way so that it would be possible to write later in this code:
    >
    That's a much better statement of a question: succinct and to the point. Much better than what you are doing in your other thread:
    Re: forall + execute immediate + remote database?

    Yes - that is possible. Just use a SQL type for the collection and then query from it like it was a table. But you can't use it in a FORALL across a DB link as the blog I pointed you to in your other thread shows.

    The following sample is just trying to show how you can query the collection as a table: 'select deptno from table(tb)'
    -- type to match emp record
    create or replace type emp_scalar_type as object
      (EMPNO NUMBER(4) ,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7, 2),
       COMM NUMBER(7, 2),
       DEPTNO NUMBER(2)
      )
      / 
     
    -- table of emp records
    create or replace type emp_table_type as table of emp_scalar_type
      / 
     
    declare
    tb emp_table_type;
    deptnoList sys.OdciNumberList;
    BEGIN
     
    select emp_scalar_type(empno, ename, job, mgr, hiredate, sal, comm, deptno)
     bulk collect into tb from emp;
     
    SELECT deptno bulk collect
    INTO deptnoList
    FROM dept where deptno not in (select deptno from table(tb));
     
    for i in 1..deptnoList.count loop
    dbms_output.put_Line(deptnoList(i));
    end loop;
    END;
  • 2. Re: select from collection?
    946279 Newbie
    Currently Being Moderated
    "That's a much better statement of a question: succinct and to the point. Much better than what you are doing in your other thread"

    I wish I could say the same about your answers :) I don't want to create additional SQL type (read: we are not allowed). I just asked quite precisely: "Is it possible to use *this collection* in a way so that it would be possible to write later in *this* code"

    I have a lot of "helpful" and "correct" to give, and I wish I could mark my question as answered but... well not this time. please note that the answer: "it is not possible with the code you provided" or "it is possible with the code you provided and can be done like this [ande here comes an example]" is great answer for me :)

    thanks

    Edited by: 943276 on 2013-01-17 23:19
  • 3. Re: select from collection?
    rp0428 Guru
    Currently Being Moderated
    The forum isn't the place to get an answer from a multiple-choice list or provide a simple yes/no answer.

    The purpose of the forum is to help people understand how to solve their problem using industry standard practices.

    That requires understanding the actual problem rather than merely accept as a given that the solution a poster has their heart set on is the one that must be used.

    Many people post questions that seem to indicate they have already chosen the solution that they want to use and want help getting that solution to work.

    Unfortunately, most of the time they have chosen a solution without fully understanding what the real problem is. Worse, the solution they have chosen is often the wrong one to be using.

    You are entitled to focus on your desired solution if you want but any experienced professional is going to first make sure the problem has been fully defined and that all of the possible constraints are known. Then they can determine the list of possible solutions and then, based on some criteria, order those possible solutions for further evaluation and testing.

    So you will get the best, and quickest, help on the forums when you focus on the problem itself and helping the forum volunteers understand the problem: that means answering their questions when there is something about the problem that isn't clear.
  • 4. Re: select from collection?
    946279 Newbie
    Currently Being Moderated
    pleace face the fact that often there are some constraints for the problems. I don't see any advantage of your answers, even I know you would like to help, if they are not appropriate for the situation I'm in. as you like the way I defined the problem now why you still insist on giving me a solution which is not appropriate with my current situation. it doesn't help at all: that's why I asked precise question about this code, not some other code which would do the job. if you like to help it is enough if you say: it is not possible with your code (if it really isn't). and I will be fine with this!
  • 5. Re: select from collection?
    Justin Cave Oracle ACE
    Currently Being Moderated
    No, you cannot use that exact collection in a SQL statement.

    If you could define the collection in SQL, as rp0428 has already suggested, you could use the collection in a SQL statement.
    If you could define the collection in a PL/SQL package, rather than in an anonymous PL/SQL block, you could write a pipelined table function that could be called from SQL. It isn't obvious to me whether one of the restrictions you have is an inability to use named PL/SQL blocks.

    Justin
  • 6. Re: select from collection?
    946279 Newbie
    Currently Being Moderated
    thanks Justin! great answer! and the restictions is: DB2 database is not ours. we cannot create objects there --(as I could do a procedure there with all the logic coded and simply call a procedure on from DB1 over db_link).-- we just have db_link.

    Edited by: 943276 on 2013-01-18 06:46
  • 7. Re: select from collection?
    Justin Cave Oracle ACE
    Currently Being Moderated
    A couple of things to note...

    First, this is the first time that you're mentioning multiple databases in this thread which, I'm guessing, means that this is related to your earlier question. Please don't assume that people answering one question are familiar with every other question that you've asked.

    Second, it's pretty common for people asking questions to have restrictions on the solution. If you tell us the restrictions up front and explain why that restriction exists (i.e. you're running this against a database you don't own where you can't create objects so only anonymous blocks can be used), we can generally provide much better answers. Otherwise, we're left trying to guess whether when you say "this collection" you literally mean "this collection in an anonymous PL/SQL blocks without any supporting SQL or PL/SQL objects" or whether you really mean "a collection like this". My first thought when I read your question was an answer very much like the one you were given about creating a SQL type because the vast majority of people asking a question here would mean "a collection like this". If you intend your question to be read absolutely literally and want to avoid folks suggesting alternatives (or if you do want alternatives so long as they meet whatever restrictions you lay out), let us know that when you ask the question.

    Justin
  • 8. Re: select from collection?
    946279 Newbie
    Currently Being Moderated
    you're right. I can only say that this was my mistake to mention databases here, and this was probably by constant suggestion from rp who insisted on his view of the problem joined to another problem which I also authored. I edited the post to avoid confusion, I hope it is clear now. please note it was him joining the threads, not me.

    on the second issue: I will have this in mind. the problem I formulated was precise and you didn't have any problems understanding it. as far as treating problems literally I don't see the point since database evaluates statements literally and simply do what statement says no matter the intentions of a creator. formulating problems litteraly is a natural way of formulating most (computational) problems at all as it is aligned with how databases generally work. for most of the cases there is no space for "additional interpretation" as rp wants to inject. there was strict question and strict answer. and to be clear about: my intention is not to avoid people from giving suggestions, my intention is to get the answer first. I can listen the sugesstion, sure, but when someone says "i'm not interested" it means "do not bother him". I spent a lot of time on useless talks with rp basically saying "I appreciate your efforts but I don't want what you have, it is not my problem".
  • 9. Re: select from collection?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Basic example of using local collections for remote bulk processing - essentially the same approach that one would use in a C/C++/Pascal/etc client, by using the bulk processing features of the Oracle Call Interface (OCI).
    --// on remote database
    SQL> create table testtab( id number );
    
    Table created.
    --// on local db
    SQL> declare
      2          sqlIns  varchar2(32767);
      3          idList          DBMS_SQL.number_table@dev2;
      4          cur             integer;
      5          rc              integer;
      6  begin
      7          --// populate the local collection
      8          select
      9                  object_id bulk collect into idList
     10          from    user_objects;
     11  
     12          --// dynamic insert SQL to execute on remote db
     13          sqlIns := 'insert into testtab(id) values( :id )';
     14  
     15          cur := DBMS_SQL.Open_Cursor@dev2( cur );
     16          DBMS_SQL.parse@dev2( cur, sqlIns, DBMS_SQL.Native );
     17  
     18          DBMS_SQL.Bind_Array@dev2( cur, 'id', idList );
     19  
     20          rc := DBMS_SQL.Execute@dev2( cur );
     21          DBMS_SQL.Close_Cursor@dev2( cur );
     22  end;
     23  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select count(*) from testtab@dev2;
    
      COUNT(*)
    ----------
           261
    
    SQL> 
    Making the remote database itself dynamic is of course more complex - as the above code will need to be dynamically generated with the applicable database link, and then dynamically executed.

    Do-able, but questionable.

Legend

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