Re: a newbie question about pl/sql table

A copy of this was sent to Johnson Chao <zhao_at_ctc-g.co.jp> (if that email address didn't require changing) On Thu, 09 Sep 1999 01:31:39 GMT, you wrote:

>Hello, :
> I have some questions about the pl/sql table.
>
>1. In order to use a pl/sql table, I have to do like this.
>
>CREATE OR REPLACE PACKAGE RegPlate AS
>-- Define table type
> type VARARRAY is table of VARCHAR2(12) index by BINARY_INTEGER;
>
> FUNCTION LogRepPlate (ArraySize IN INTEGER,ParentPlateID IN VARARRAY)
>RETURN CHAR;
> END RegPlate;
>/
>
>
>CREATE OR REPLACE PACKAGE BODY RegPlate AS
> FUNCTION LogRepPlate (ArraySize IN INTEGER,ParentPlateID IN
>VARARRAY) RETURN CHAR AS
>...
>...
> END LogRepPlate;
>END RegPlate;
>/
>
> It seems that I can only use sql table inside a package. First define
>it in the declaration part, then use it in the package body. Can't I
>use it in a normal procedure or function?
>

Yes you can, just:

create or replace function my_function( x in RegPlate.VARRAY ) return number as
begin

   ....
end;

Just use PACKAGENAME.TYPENAME to refer to it.

>2. How to debug the above function with a table as its parameter? In
>sqlplus, if I want to debug a function with a normal varchar2
>parameter, I can first define a host variable, assign a test value to
>it, then call the fuction with my defined host variable as its
>parameter. If I want to debug the function with a table as its
>paramter, how can I do? I can not define a host variable which is sql
>table type, can I?
>

Use:

SET SERVEROUTPUT ON declare

   test_variable regPlate.VARRAY;
begin

   test_variable(1) := 'some value';
   test_variable(2) := 'something else';    ....
   dbms_output.put_line(

      regPlate.LogRepPlate( test_variable.count, test_variable ) ); end;
/

in sqlplus to test it

Also, you might consider using the builtin .count attribute in LogrepPlate rather then making the caller pass it in.

>thanks .

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 06:55:09 CDT