Re: EXECUTE IMMEDIATE + BULK COLLECT without knowing in advance the number of columns fetched ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 14 Sep 2007 02:26:19 -0700
Message-ID: <1189761971.370450@bubbleator.drizzle.com>

codadilupo wrote:
> DA Morgan wrote:
>

>> http://www.psoug.org/reference/array_processing.html

>
> There are wrong links in the "Related Topics" section at the end of this
> page: "Array Size", "DBMS_SQL" and "Loops".
>
> Unfortunately, I'm not able to see an answer to my question in the page
> you suggested me.
>
> Perhaps I found it in another page:
>
> http://www.psoug.org/reference/dbms_sql.html (see the DEFINE_ARRAY section)
>
> Is this the way to go?
>
> Thank you. Kind regards,

Your question was about EXECUTE IMMEDIATE and BULK COLLECT.

I just went to the page and ... quickly found:CREATE TABLE tmp_target AS SELECT rownum ID, table_name, num_rows
FROM all_tables
WHERE rownum < 101;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  rownos NumList;

  TYPE NameList IS TABLE OF VARCHAR2(30);   tnames NameList;
BEGIN
   rownos := NumList(2,4,6,8,16);

   FORALL i IN 1..5
   EXECUTE IMMEDIATE 'UPDATE tmp_target SET id = id * 1.1    WHERE id = :1
   RETURNING table_name INTO :2'
   USING rownos(i) RETURNING BULK COLLECT INTO tnames;

   FOR j IN 1..5
   LOOP
     dbms_output.put_line(tnames(j));
   END LOOP;
END;
/

Why couldn't you? The links were unnecessary.

But thanks for the pointer ... the links are now fixed.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Sep 14 2007 - 04:26:19 CDT