Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!nntp.abs.net!feeder.qis.net!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail
From: richard.limanowski@t-online.de (Richard Limanowski)
Newsgroups: comp.databases.oracle.misc
Subject: How to (cheap) reference an object
Date: 1 Jan 2002 12:47:45 -0800
Organization: http://groups.google.com/
Lines: 65
Message-ID: <a77e1f06.0201011247.7f1e8dc3@posting.google.com>
NNTP-Posting-Host: 217.234.19.200
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1009918065 32278 127.0.0.1 (1 Jan 2002 20:47:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 1 Jan 2002 20:47:45 GMT
Xref: easynews comp.databases.oracle.misc:74292
X-Received-Date: Tue, 01 Jan 2002 18:24:20 MST (news.easynews.com)

Hi everybody,

I try to implement a fast two dimensional lookup table in pl/sql
by storing pl/sql tables in another pl/sql table. This works fine
but when I try to retrieve stored tables I get a problem:
Oracle seems to create a copy of the table every time I
try to get the reference to it.
I tried to pass the table to a subroutine 
(nocopy is implicit for in-only parameters)
but this did not work as supposed.
I must be missing something. 
Can anybody help?

create or replace procedure p1(p_cnt int, p_pass boolean)
as
  -- types
  type c is table of number(5) index by binary_integer;
  type cc is table of c index by binary_integer;
  -- variables
  g_c c;
  g_cc cc;
  i binary_integer;
  -- procedures
  procedure p_int(p_tab in c) as
  begin null; end;
  procedure p_int(p_val in int) as
  begin null; end;
begin
  -- init g_c
  for i in 1 .. 1000 loop
    g_c(i) := i;
  end loop;
  -- install g_c
  g_cc(1) := g_c;
  -- call procedure
  if p_pass then
    for i in 1 .. p_cnt loop
      -- assignment and copy cost about the same
      --g_c := g_cc(1);
      p_int(g_cc(1));
    end loop;
  else
    for i in 1 .. p_cnt loop
      p_int(1);
    end loop;
  end if;
end;
/
show errors


SQL> exec p1(10000, false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> exec p1(10000, true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.29

I test with 9i under Linux.

Richard