RE: ** test a SP with table input at SQL prompt
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: Bill Coulam <bill.coulam@xxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 8 Dec 2004 10:38:52 -0800 (PST)
Hi Bill : Thanks for your help with detailed example. It is greatly
appreciated. You are great.
Bill Coulam <bill.coulam@xxxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of A Joshi
Sent: Wednesday, December 08, 2004 9:56 AM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
