In article <YJ=XN5fecTv=LYwUSSUCBOjBTf=c_at_4ax.com>,
tkyte_at_us.oracle.com wrote:
> 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
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Sep 10 1999 - 14:52:51 CDT
