Stored procedures within a package

The following examples illustrate the use of stored procedures. The examples are based on the following Oracle table and data:

example_table
(id_num int,
name varchar(30),
phone varchar(20) null,
birthdate date null)

id_num 		name 						phone					birthdate
1		Carl Finklestein						203-231-0123					NULL
2		Fred P. Body						NULL					NULL
3		Carl Winkerbean						603-231-4123					AUG 09 1947
4		Wanda Finklestein						978-245-6789					JUL 12 1972
5		John Smith						607-789-0123					NOV 09 1963

Oracle package and procedure definitions

CREATE or REPLACE PACKAGE dco_rpc_pkg
AS

TYPE rpc_ex_cur IS REF CURSOR RETURN example_table%ROWTYPE;
PROCEDURE rset_rpc_ex (c1 IN date, c2 IN date, a IN OUT
rpc_ex_cur);
PROCEDURE input_rpc_ex (in_id IN number);
PROCEDURE output_rpc_ex (c1 IN OUT number, c2 OUT varchar);

END dco_rpc_pkg;

CREATE or REPLACE PACKAGE BODY dco_rpc_pkg
AS

PROCEDURE rset_rpc_ex (c1 IN date, c2 IN date, a IN OUT
rpc_ex_cur)
IS
BEGIN
OPEN a FOR select * from example_table where birthdate
between c1 and c2;
END;

PROCEDURE input_rpc_ex (in_id IN number)
IS
BEGIN
delete example_table where id_num = in_id;
END;

PROCEDURE output_rpc_ex (c1 IN OUT number, c2 OUT varchar)
IS
BEGIN
select name into c2 from example_table where id_num = c1;
END;

END dco_rpc_pkg;

Map an RPC that returns a result set as an Adaptive Server table

The following example maps the Oracle procedure rset_rpc_ex as an Adaptive Server table with its input parameters mapped as columns, as described in the ASE documentation:

create existing table rset_rpc_tab
(id_num int,
name varchar(30),
phone varchar(20) null,
birthdate smalldatetime null,
_c1 smalldatetime null,
_c2 smalldatetime null)
external procedure
at 'DCSERVER.dco_rpc_pkg..rset_rpc_ex'


select id_num, name, phone, birthdate from rset_rpc_tab
where _c1 = 'jan 01 1940' and _c2 = 'jan 01 1970'
id_num      name                           phone
birthdate 
----------- ------------------------------ --------------------
--------- 
3 Carl Winkerbean                603-231-4123
Aug  9 1947 12:00AM 
5 John Smith                     607-789-0123
Nov  9 1963 12:00AM 
(2 rows affected)

Execute an RPC that returns a result set

The following example executes an RPC, from ASE through DirectConnect for Oracle 12.6 to Oracle. This RPC takes two input parameters and returns a result set:

DCServer.dco_rpc_pkg..rset_rpc_ex 'jan 01 1940', 'jan 01 1970'

ID_NUM 			NAME 					PHONE 			BIRTHDATE 
------			--------					--------- --------- 
3 			Carl Winkerbean 	603-231-4123 Aug  9 1947 12:00AM
5 			John Smith 					607-789-0123 Nov  9 1963 12:00AM 
(2 rows affected)
(return status = 0)
(0 rows affected)

Execute an RPC with input and output parameters

The following is an example of an RPC from Adaptive Server through DirectConnect for Oracle. Input and output parameters are mapped as Adaptive Server variables:

declare @io_id int
declare @o_name varchar(30)
select @io_id = 3
exec DCSERVER.dco_rpc_pkg..output_rpc_ex
	@io_id, @o_name output

select @io_id, @o_name
(1 row affected)
(return status = 0)
(0 rows affected)
(0 rows affected)

----------- ------------------------------ 
3 Carl Winkerbean                
(1 row affected)

Execute an RPC with an input parameter

The following command executes an RPC from Adaptive Server Enterprise through DirectConnect for Oracle. It passes an input parameter, deleting one row:

DCSERVER.dco_rpc_pkg..input_rpc_ex 2
(return status = 0)
(0 rows affected)

select * from example_table
id_num                              name
phone                birthdate 
-----------------------------------
------------------------------ -------------------- --------- 
1                                   Carl Finklestein
603-231-0123         NULL      
3                                   Carl Winkerbean
603-231-4123         Aug  9 1947 12:00AM 
4                                   Wanda Finklestein
978-245-6789         Jul 12 1972 12:00AM 
5                                   John Smith
607-789-0123         Nov  9 1963 12:00AM 
(4 rows affected)