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
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;
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)
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)
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)
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)
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
