Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** test a SP with table input at SQL prompt

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

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Wed, 8 Dec 2004 10:38:52 -0800 (PST)
Message-ID: <20041208183852.75913.qmail@web60710.mail.yahoo.com>


Hi Bill : Thanks for your help with detailed example. It is greatly appreciated. You are great. Bill Coulam <bill.coulam_at_scgo.com> wrote: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



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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 08 2004 - 12:47:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US