Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 4: Topics [Table of Contents] Appendix A: Using sqledit

DirectConnect for Oracle Installation/Reference for Windows NT

[-] Chapter 5: RPC Handling

Chapter 5

RPC Handling

Remote Procedure Calls (RPCs) are accepted by the DirectConnect for Oracle RPC event handler. The RPC is either interpreted locally or passed to Oracle for processing. RPCs specific to DirectConnect for Oracle are documented in this chapter.

RPCs Interpreted by DirectConnect for Oracle

The following RPCs are processed by DirectConnect for Oracle:

RPCs Passed to Oracle

RPCs that are not interpreted by DirectConnect for Oracle are passed directly to Oracle for execution.

Oracle stored procedures are executed using PL/SQL which is Oracle's procedural extension to the SQL language. Stored procedures are executed using anonymous PL/SQL blocks. When DirectConnect for Oracle executes a stored program, it associates the actual and formal parameters by position. Therefore, when calling a subprogram using DirectConnect for Oracle, the parameters have to be passed using positional notation. Named notation and mixed notation are not supported.

Creating Stored Procedures

Stored procedures can be created using Oracle's SQL*PLUS utility or using the passthrough mode in ASE or OmniConnect. The syntax for the create procedure SQL command can be found in Oracle SQL language reference documentation.

Stored Procedures with Input Parameters

If only input parameters are present a stored procedure is created against Oracle using SQL*PLUS as follows:

create PROCEDURE create_newdept (
    new_deptno in number, 
    new_dname in CHAR, 
    new_loc in CHAR) as 
BEGIN 
    INSERT INTO DEPT VALUES (    new_deptno,
                            new_dname, 
                            new_loc);
END create_newdept;

Once this procedure has been created in Oracle, it can be invoked from DirectConnect for Oracle. The above procedure inserts new rows into the DEPT table. The user who intends to execute the procedure has to be given appropriate privileges. Once the privileges have been granted, this user can execute the Oracle stored program from OmniConnect using the following syntax:

ORACLE_DCO...create_newdept 10, "MIS", "BLDG_7"

Here ORACLE_DCO refers to the server name in OmniConnect that has been defined as an Oracle access server.

When the DirectConnect for Oracle gets an RPC request from OmniConnect to execute this procedure, DirectConnect for Oracle first checks if this procedure is one of the supported catalog procedures (sp_tables, sp_columns, etc). If it is not one of them, DirectConnect for Oracle assumes that the procedure is a an Oracle procedure and executes it using an anonymous PL/SQL block. DirectConnect for Oracle converts the RPC request to a PL/SQL block, as shown in the following, and executes it:

begin 
    create_newdept (10, 'MIS', 'BLDG_7'); 
end;

Stored Procedures with Input and Output Parameters

A stored procedure in Oracle with input and output parameters is created as shown in the following example:

create or replace procedure get_sal_info( 
    name in emp.ename%type, 
    salary out emp.sal%type) as
begin
    select sal into salary from emp 
        where ename = name;
    insert into tempsal values (name, salary); 
end get_sal_info;

This procedure assumes the existence of two tables, tempsal and emp, which have been created using the following SQL command:

create table tempsal (            col1 VARCHAR2(10), 
                    col2 NUMBER(7,2))
create table emp (empno number(4) not null,
ename char(10), job char(9), mgr number(4),
hiredate date, sal number(7,2),comm number(7,2),
deptno number(2))

To execute this stored procedure from OmniConnect, you need to have one parameter defined as an input parameter and one as an output parameter. This is done from OmniConnect as shown in the following example:

declare @emp_name varchar(20)
declare @salary int
select @emp_name='FORD' 
exec ORACLE_DCO...get_sal_info @emp_name, 
                              @salary out

Two variables (@emp_name and @salary) are defined, and one of them is defined as an output parameter. OmniConnect passes this RPC request to DirectConnect for Oracle. DirectConnect for Oracle converts this RPC request to an anonymous PL/SQL block and executes it. Numbered placeholders are used to hold the input and output variables. The results (return parameters) are sent back to the client.

Restrictions

When handling RPC parameters of datatype tinyint, only valid values (between 0 and 255) can be passed back. If the backend database returns a negative value, it is passed to the user as a NULL value. We recommend the smallint datatype because it can handle negative numbers correctly.

Oracle stored procedures with long and long raw datatypes are not supported with output parameters. long raw input parameters are not supported.

Executing Procedures Within Packages with Input and Output Parameters

If a procedure is a part of a package, the procedure has to be explicitly defined using the package name as the prefix.

Assume the existence of a package that has been created using the following command in SQL*PLUS:

create or replace package personnel as 
    procedure fetch_employee( 
        dept_no             in         number,
        found             in out         number, 
        done             out         number, 
        emp_name             out        emp.ename%type, 
        job_title             out        emp.job%type); 
end personnel;

The procedure fetch_employee is created using SQL*PLUS, as shown in the following example:

create or replace package body personnel as 
    cursor get_empname (dept_no integer) is 
        select ename, job from emp 
            where deptno = dept_no; 
procedure fetch_employee( 
    dept_no                 in         number,
    found                 in out         number, 
    done                out         number, 
    emp_name                 out         emp.ename%type, 
    job_title                 out         emp.job%type) is 
begin 
    if not get_empname%ISOPEN then 
    open get_empname(dept_no); 
end if; 
done := 0; 
found := 0; 
fetch get_empname into emp_name, job_title; 
if get_empname%notfound then 
    done := 1; 
else 
    found := found + 1; 
end if; 

close get_empname;
end fetch_employee;
end personnel;

To execute the fetch_employee procedure belonging to the personnel package, the following command has to be executed:

declare @dept_no                         numeric 
declare @found                         numeric 
declare @done                         numeric 
declare @emp_name                         varchar(20) 
declare @job_title                         varchar (20)
select @dept_no = 10 
exec ORACLE_DCO.personnel..fetch_employee @dept_no,
                        @found out,
                        @done out, 
                        @emp_name out, 
                        @job_title out

Here the database portion of the RPC request is mapped to the package name. DirectConnect for Oracle converts this RPC request to the following PL/SQL block:

begin
    personnel.fetch_employee(:1, :2, :3,                                 :4, :5);
end;

Parameters are bound to the placeholders and the OUTPUT parameters are returned to the client.

Views on Tables

DirectConnect for Oracle uses Oracle's data dictionary views when executing the following stored procedures:

Table 5-1: Data dictionary views with stored procedures

Stored Procedure

Oracle Data Dictionary View

sp_stored_procedures

all_objects

sp_statistics

all_indexes, all_ind_columns

sp_columns

all_tab_columns

sp_tables

all_catalog

sp_capabilities

Function

Determines the capabilities of the DirectConnect for Oracle.

Syntax

sp_capabilities

Parameters

None.

Comments

None.

Returns

None.

Messages

Results

The following table lists the format of the result set:

Table 5-2: sp_capabilities result set

ID

Capability Name

Value

101

SQL syntax

1 = sybase

102

join handling

3 = full join support (See note following this table)

103

aggregate handling

2 = all functions

104

AND predicates

1 = supported

105

OR predicates

1 = supported

106

LIKE predicates

1 = ANSI-style supported

107

bulk insert handling

0 = unsupported

108

text/image handling

2 = text, textptr

109

transaction handling

1 = local

110

text pattern handling

0 = unsupported

111

order by

1 = supported

112

group by

1 = supported

113

net password encryption

0 = unsupported

114

object name case sensitivity

0 = case insensitive

115

distinct

0 = unsupported

Outer join Handling

Sybase and Oracle handle outer joins differently when there are conditions on the outer join. As a result outer joins may return different results, when using Oracle tables and Sybase tables, even though the data in the tables is identical.

For example, the following query, when executed on Sybase, will return all rows from table A. For any rows not meeting the condition c1 > 6 the columns from table A will contain null.

select A.c1, B.c2 from A, B where A.c1 *= B.c1 and A.c1 > 6

On Oracle, only rows from table A meeting the condition c1 > 6 will be returned.

sp_columns

Function

Return column information for a single object that can be queried in the current DBMS environment. The returned columns belong to either a table or a view.

Syntax

sp_columns table_name [, table_owner] 
[, table_qualifier] [, column_name]

Parameters

table_name - The name of the table or view. Wildcard pattern matching must be supported. Support of this parameter is mandatory.

table_owner - The name of the table owner or view owner. Wildcard pattern matching must be supported. If the parameter is not specified, sp_columns should use the default rules of the underlying DBMS to determine which table's columns to return. Support of this parameter is optional.

table_qualifier - A qualifier that restricts results sets. See sp_tables. Support of this parameter is optional.

column_name - Restrains the output to the column named in the parameter. Wildcard pattern matching must be supported. Support of this parameter is optional.

Comments

sp_shutdown

Function

Shuts down DirectConnect for Oracle.

Syntax

exec sp_shutdown [nowait]

Parameters

nowait - This option causes an immediate shutdown. Without the nowait option, currently active queries will be canceled cleanly before a shutdown.

sp_statistics

Function

Returns a list of all indexes on a single table, determined by the table_qualifier, table_owner, and table_name parameters.

Syntax

sp_statistics table_name [, table_owner] 
[, table_qualifier] [, index_name] [, is_unique]

Parameters

table_name - The name of the table. No wildcard pattern matching is supported. Support of the parameter is mandatory.

table_owner - The name of the table owner. No wildcard pattern matching is supported. If the parameter is not specified, sp_statistics uses the default rules of the underlying DBMS to determine which table's columns to return. Support of this parameter is optional.

table_qualifier - A qualifier that restricts the results sets. See sp_tables. Support of this parameter is optional.

index_name - The index name. No wildcard pattern matching is supported. Support of this parameter is optional.

is_unique - Indexes to be returned. Enter "y" if only unique indexes are to be returned. Support of this parameter is optional.

Comments

sp_stored_procedures

Function

Returns a list of stored procedures in the current DBMS environment.

Syntax

sp_stored_procedures [sp_name] [, sp_owner]
[, sp_qualifier]

Parameters

sp_name - The stored procedure name. The underlying DBMS supports wildcard matching.

sp_owner - The stored procedure owner. The underlying DBMS supports wildcard matching.

sp_qualifier - The stored procedure qualifier that controls the retrieval of information about stored procedures that are uniquely identified and executable, using a three-part naming scheme.

If this parameter is not given a value, or is a null string, the gateway returns the stored procedures in the default database environment. This default database environment depends upon the gateway. For example, the default database environment for OmniConnect is the current database context. In OmniConnect, there is no way to switch the current database from within a stored procedure, so the only values that are acceptable for this parameter are NULL or the name of the current database.

Comments

sp_tables

Function

Returns a list of objects that can be queried in the current DBMS environment, that is, any object that can appear in a from clause. The output of sp_tables depends upon the Oracle user's privileges.

Syntax

sp_tables [table_name] [, table_owner] 
[, table_qualifier][, table_type]

Parameters

table_name - The name of the table. Wildcard matching must be supported. Support of this parameter is optional.

table_owner - The table owner. Wildcard matching must be supported. Support of this parameter is optional.

table_qualifier - The control over the retrieval of information about tables. These tables are uniquely identified and can be queried using a three-part naming scheme. Note that there are no standard semantics for what the first part of a three-part name represents.

Support of this parameter is optional.

table_type - A list of values, separated by commas, giving information about all tables of the table type(s) specified, including the following:

"'TABLE', 'SYSTEM TABLE', 'VIEW'"

Support of this parameter is optional.

Note: Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be entered in uppercase letters.

Comments

sp_users

Function

Displays information on clients of DirectConnect for Oracle.

Syntax

exec sp_users

Returns

One row describing each currently connected client. The following is an example of data returned by sp_users.

spid

status

loginame

hostname

process id

4

local rpc

system

palm

27449

5

idle

system

LIVEOAK

27450

6

clientio

system

LIVEOAK

27451

spid - The internal client identification.

status - Current state of the client. Possible states are:

Special RPCs

For compatibility with Oracle Gateway, two additional RPCs are recognized: rpctest and oraexec. These should only be used for compatibility purposes. They will not be supported in future releases.

rpctest

rpctest accepts any number of parameters (up to the maximum allowed by the server, currently 255). The RPC name and parameter attributes are echoed back to the client application by way of messages. These messages have a message number of 0, thus they appear to the client as if they were sent via the Transact-SQL print statement. The first message returned contains the RPC name (always rpctest) and number of parameters passed. For each parameter, two messages are created: the first contains the parameter name, type, length and status, and the second contains its value.

oraexec

oraexec accepts any number of parameters (up to the maximum allowed by the server, currently 255), and builds a single buffer by concatenating the parameter values. Non-character values are first converted to datatype srvchar. The resulting buffer contains what is assumed to be one or more SQL statements separated by semicolons. Each statement is passed to Oracle for execution, and the results are returned to the client.

Chapter 4: Topics [Table of Contents] Appendix A: Using sqledit