Nested Tables as PL/SQL parameters (Very Long Post!)

From: Phil Singer <psinger1_at_chartermi.net>
Date: Sat, 01 Dec 2001 14:26:37 -0500
Message-ID: <3C092EED.5C6DBB50@chartermi.net>

Be warned: this is a long post; but I don't know how to explain things any more concisely.

Here is my problem: I have to implement some kind of n-tier authentication using PL/SQL on a Sequent, using 8.1.6. C external routines are not allowed. Moving to 9i is not allowed. I must do it the hard way.

I have a table, Tbl_A in database A. I have other tables in other databases, on a 2nd server. These tables all contain a column whose domain is common with Tbl_A. I have a database link in database B, link2A, which (remarkably!) links over to database A.

Start with this problem: to find those rows in database B which have columns which match those in Tbl_A.

As stated, this is trivial. I can write a query such as

   SELECT ... FROM TBL_A_at_link2A a, TBL_B b

     WHERE tbl_a_id = tbl_b_id
       AND ... (other predicates belonging
         to object in database B).

Now, lets make life more
difficult: the criteria are about to depend on other items than just tbl-a-id. In fact, I am going to have to hide them from database B. Therefore, instead of a predicate in a query, I will have to use a function instead. Now I could rewrite the query as something like

  SELECT ... FROM TBL_A_at_link2A a, TBL_B b

     WHERE func(tbl_a_id, tbl_b_id) = 'TRUE'
       AND ... (other predicates belonging
         to object in database B).

But when I try that the run time increases from 2.5 seconds to 2.5 minutes.

So I got the bright idea to try to write a function which has an IN parameter of a nested table (which contains all the IDs in TBL_B), and which returns a second nested table of all the matching IDs in TBL_A. This actually works:

Create or replact type id_type as table of varchar2(8);

create or replace function test_9(p_ids id_type,

                                     p_code number) 

 return id_type
 AS
 errtxt varchar2(512);
 reSult id_type := id_type();
 v_kount number;
 BEGIN
 select cast(multiset(select tbl_a_id from tbl_a_at_link2A.world

    where a_code = p_code

       and tbl_a_id in 
                (select * from table(cast(p_ids as id_type))) 
                ) as id_type) into reSult from dual; 

  return reSult;
  exception
    when others then

      errtxt := sqlerrm; 
      dbms_output.put_line (errtxt); 
  END; 
  /                             

[First Question: Why do I have to cast(p_ids as id_type)?
p_ids already is of type id_type. If I leave off this clause, the function compiles O.K., but does not execute correctly]

Being of the olde schoole, I cannot stop here, I must test my function. So I wrote another function:

  Create or replace function testIt
    return id_type
  AS
   o_ids id_type := id_type();
   i_ids id_type := id_type();
   errtxt varchar2(512);
   p_code number := 734;
  BEGIN
    select cast(multiset(select tbl_b_idfrom tbl_b     where some_code = '002') as id_type)     INTO i_ids from dual;
  o_ids := test_9(i_ids, p_code);
  return o_ids;
 exception
   when others then
    errtxt := sqlerrm;
    dbms_output.put_line(errtxt);
 end;
 /

  Finally, this can be executed by entering:

  SQL> select * from table(cast(testIt() as id_type));

  Jxxxxxx
  Syyyyyy

  Elapsed: 00:00:01.41
  SQL>
[Second question: is there some way to

test this from SQL*Plus?]

I am almost there: I have a function which interrogates Tbl_A as quickly as coding the join in-line in the calling SQL. However, the function is in the calling database, and this is going to have to be done from multiple remote databases. So, I would like to have the function in database A. The natural way to do this is to take out the database link in function test_9, and compile it in database A. This works OK.

But, when I change my testing function testIt to call it:

   o_ids := test_9_at_link2A(i_ids, p_code);

I get at compile time:

LINE/COL
ERROR

---

---

10/3     PL/SQL: Statement
ignored                                              
10/12    PLS-00306: wrong number or types of arguments in call
to               
         'TEST_9'                                                       
                            

If I do something similar with 'normal' parameters this syntax works perfectly.

Can anyone give me a clue as to what is wrong (and where this is documented?)

If someone thinks DDL and sample data would help, I can post it; I did not because this post is way too long already.

Thank you for reading this far.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!

Received on Sat Dec 01 2001 - 13:26:37 CST