RE: ** test a SP with table input at SQL prompt

From: Bill Coulam <bill.coulam_at_scgo.com>
Date: Wed, 8 Dec 2004 10:40:05 -0600
Message-ID: <FJEOLLFBELDPHAFFABKMCEOGCCAA.bill.coulam@scgo.com>

Try something like this:

CREATE TYPE obj1 AS OBJECT
(

   emp_no NUMBER,
   emp_name VARCHAR2(20)
)
;

CREATE TYPE tab_obj1 AS TABLE OF obj1;

CREATE OR REPLACE PROCEDURE proc1
(

   in_objs IN tab_obj1
) AS
BEGIN
   IF (in_objs.COUNT > 0 AND in_objs IS NOT NULL) THEN

      FOR i IN in_objs.FIRST .. in_objs.LAST LOOP
         DBMS_OUTPUT.put_line(in_objs(i).emp_no || ': ' ||
in_objs(i).emp_name);
      END LOOP;

   END IF;
END proc1;

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
   l_nto tab_obj1 := tab_obj1();
BEGIN

• several ways to assign values in collections, this is one of them l_nto.EXTEND(2); l_nto(1) := obj1(112,'JO'); l_nto(2) := obj1(114,'RA');

   proc1(l_nto);
END;
/

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of A Joshi Sent: Wednesday, December 08, 2004 9:56 AM To: oracle-l_at_freelists.org
Subject: ** test a SP with table input at SQL prompt

Hi,
  I have a stored procedure which has a input parameter of type table of a object. I am trying to test it at SQL level with PL/SQL block. I am able to assign values to other data types but I do not know how to do it for type table. Can some one help? Thanks

I have :

CREATE TYPE obj1 AS OBJECT
(emp_no number,

 emp_name varchar2(20));

CREATE TYPE tab_obj1 AS TABLE OF obj1;

PROCEDURE proc1 (

               in_objs        tab_obj1,
               dept            varchar2(10),
               flag       out number);

I am testing procedure proc1 at SQL level. Iwould like to set the values emp_no= 112, emp_name = 'JO', emp_no=114, emp_name = 'RA'. Thanks for your help.

---

Do you Yahoo!?
 Yahoo! Mail - Helps protect you from nasty viruses.

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 08 2004 - 10:40:02 CST