Path: news.easynews.com!easynews!news.he.net!newsfeed.direct.ca!look.ca!news.noc.cabal.int!resurrector!guidorepost!not-for-mail
From: richard.limanowski@t-online.de (Richard Limanowski)
Subject: REPOST: How to (cheap) reference an object
Newsgroups: comp.databases.oracle.misc
X-Repost-Date: 2 Jan 2002 21:25:19 GMT
Message-ID: <3$--$$-$-%-____%_$@news.noc.cabal.int>
X-Original-Path: sn-us!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail
X-Original-Message-ID: <a77e1f06.0201011247.7f1e8dc3@posting.google.com>
X-Original-NNTP-Posting-Host: 217.234.19.200
Date: 1 Jan 2002 12:47:45 -0800
Organization: http://groups.google.com/
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
Sender: resurrector@mindspring.com (Guido the Resurrector)
X-Reposted-By: resurrector@mindspring.com (Guido the Resurrector)
X-Comments: GtR Repost: The following Usenet article was cancelled, more
X-Comments: than likely by someone other than the original poster.  Please
X-Comments: see the end of this posting for a copy of the cancel.
X-Comments: Guido the Resurrector can be contacted at
X-Comments: resurrector@mindspring.com.
Lines: 85
Xref: easynews comp.databases.oracle.misc:74373
X-Received-Date: Thu, 03 Jan 2002 09:50:31 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

========= WAS CANCELLED BY =======:
Path: news.uni-stuttgart.de!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!t-online.de!newsfeed.easynews.net!easynews.net!newsfeed.freenet.de!news2.euro.net!uunet!sac.uu.net!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r
From: richard.limanowski@t-online.de (Richard Limanowski)
Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.misc
Subject: cmsg cancel <a77e1f06.0201011247.7f1e8dc3@posting.google.com>
Control: cancel <a77e1f06.0201011247.7f1e8dc3@posting.google.com>
Date: Wed, 2 Jan 2002 00:43:28 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.a77e1f06.0201011247.7f1e8dc3@posting.google.com>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009943276 19888 166.102.15.34 (2 Jan 2002 03:47:56 GMT)
X-Complaints-To: abuse@navix.net
NNTP-Posting-Date: 2 Jan 2002 03:47:56 GMT
X-No-Archive: yes
Comment: Dude, where's my NewsAgent?
Xref: news.uni-stuttgart.de control:40276110

autocancel