Sybernet / Supplied Procedures Reference
Release 3.00
Oct 14, 2002

HTTP.SP_RPC

sp_rpc attempts to address the problem of issuing remote procedure calls between Sybase and Oracle. In Sybase, remote procedure calls allow you to execute a procedure on a server different from the one where the procedure is invoked; for example, from HRPAY I can issue a remote procedure on SRIMIS if I am a remote user on that server.

exec SRIMIS.guardian.dbo.sp_update_vehicle '2DZC543','GREEN'

Here is how I would do the same thing using sp_rpc:

exec sp_rpc

    @PROCNAME   = "exec guardian.dbo.sp_update_vehicle '2DZC543','GREEN'"
,   @DESTSERVER = "SRIMIS"

To execute the same procedure on ORADEV I simply change the PROCNAME syntax to be compatible with Oracle and set the DESTSERVER to ORADEV:

exec sp_rpc

    @PROCNAME   = "begin guardian.sp_update_vehicle('2DZC543','GREEN'); end;"
,   @DESTSERVER = "ORADEV"

It should be obvious enough that PROCNAME can contain any DDL or DML command as long as it fits inside a varchar(255) and as long as your syntax is compatible with the destination server. Here's another example:

exec sp_rpc

    @PROCNAME   = "select 'Hello World' from dual"
,   @DESTSERVER = "ORADEV"

There isn't a lot you can do in a varchar(255), and you do not want to use this facility to make hundreds or thousands of calls to overcome this limitation. sp_csv provides a more desirable and a more natural approach to this problem.

PROCNAME can also be a stored procedure! In this case, your stored procedure is executed at the source server, and its results are what is sent to the destination server. sp_csv determines this is what you are doing by determing the object_id of the first name in your string. If not NULL, the rest of your string (if any) is interpreted as a comma separated list of string parameters to that procedure.

exec sp_rpc

    @PROCNAME   = "sybsystemprocs.dbo.RPC_TEST '2DZC543',GREEN"
,   @DESTSERVER = "ORADEV"

In this example sp_rpc is executed from Sybase (at HRPAY), executes the procedure residing in database sybsystemprocs called RPC_TEST. That procedure creates PL/SQL code which is then sent to ORADEV where it is invoked by Syberload.

Here is what the procedure RPC_TEST looks like. Note, this is a Sybase stored procedure that is creating PL/SQL code.

create procedure RPC_TEST (@LICENSE varchar(7), @COLOR varchar(30))
as
    select "DECLARE SUCCESSFUL BOOLEAN := TRUE;"
    ,      char(10) + "BEGIN"
    ,      char(10) + "    HTTP.WRITE('<h3>UPDATING GUARDIAN.VEHICLE...</h3>');"
    ,      char(10) + "    BEGIN"
    ,      char(10) + "        UPDATE GUARDIAN.VEHICLE"
    ,      char(10) + "        SET    COLOR = '" + @COLOR + "'"
    ,      char(10) + "        WHERE  LICENSE = '" + @LICENSE + "';"
    ,      char(10) + "        EXCEPTION WHEN OTHERS THEN SUCCESSFUL:=FALSE;"
    ,      char(10) + "    END;"
    ,      char(10) + "    IF (SQL%ROWCOUNT = 0 OR NOT SUCCESSFUL) THEN"
    ,      char(10) + "        HTTP.WRITE('<font color=red>Oops!</font>');"
    ,      char(10) + "    ELSE"
    ,      char(10) + "        HTTP.WRITE('<font color=blue>Success!</font>');"
    ,      char(10) + "    END IF;"
    ,      char(10) + "END;"
    return

It's a silly example because it doesn't do very much; however, it does illustrate how a stored procedure on Sybase can overcome the 255 limit of PROCNAME.

Invoking sp_rpc

Here is the complete list of parameters that sp_rpc recognizes:

Parameter	Comments
BUTTON	BUTTON should be NULL.
PROCNAME	PROCNAME can be either a DDL or DML statement that is executed at DESTSERVER or a stored procedure that is executed at SRCESERVER. This is how sp_csv determines which is which: sp_csv examines the first name in PROCNAME. If it is a valid object_id at SRCESERVER, the rest of the string (if any) is interpreted as a comma separated list of parameters and the procedure is executed at SRCESERVER. If the name is not a valid object_id, sp_csv assumes it is a DDL or DML statement and invokes that command at DESTSERVER. All parameters are assumed to be varchars (whether quoted or not). You may not pass numeric values to your stored procedure using PROCNAME. Consider the following examples for PROCNAME: guardian.dbo.sp_update_vehicle '2DZC543','GREEN' guardian.dbo.sp_update_vehicle certainly looks like a procedure name (but so does XYZ). If its object_id at SRCESERVER is not null, sp_csv will execute it. exec guardian.dbo.sp_update_vehicle '2DZC543','GREEN' exec doesn't look like a procedure name and it isn't because object_id("exec") is null. In this case, this command is invoked at the destination server. select 'Hello World' from dual Like above, the word select is not a valid object_id so this command would be invoked at the destination server. Obviously, this is a command suitable for Oracle. Because there is a small possibility that the object ID is valid in both Sybase and Oracle, it is a good idea to fully qualify your procedure name.
SRCESERVER	SRCESERVER determines the server name where sp_rpc is executed from. By default, SRCESERVER is the server name that you executed sp_rpc from. This is a little confusing. Normally, SRCESERVER will be NULL. When not NULL, however, you have the ability to invoke sp_rpc from one server (the server you are logged into) and tell it to run sp_rpc from another procedure passing all of your parameters to it as though you were logged into that server. You must be assigned as a remote user to specify anything other than the current server.
SRCEDATABASE	SRCEDATABASE determines the database "context" that you want sp_rpc to run in. If PROCNAME is fully qualified, however, there is no reason to specify SRCEDATABASE.
SRCEOWNER	SRCEOWNER should be NULL unless you are calling sp_rpc with a BUTTON value of CSV.
SRCENAME	SRCENAME should be NULL. sp_csv creates a unix disk file composed of SRCESERVER, SRCEDATABASE, SRCEOWNER and SRCENAME. A typical example is something like this: DEVPAY.SYBSYSTEMPROCS.DBO.20020308135559.ddl This file is then sent to ./csv which submits it to the appropiate destination server (DESTSERVER). If SRCENAME is NULL, sp_rpc creates a unique name for this node. If you specify your own SRCENAME, then you run the risk of overwriting a filename that has not been processed.
DESTSERVER	DESTSERVER is the name of the server where your results are sent and applied. If not specified, DESTSERVER defaults to the current server name (@@servername or http.servername). I can't imagine any condition when DESTSERVER should be NULL.
MAILTO	MAILTO represents the e-mail address where your results are sent. If MAILTO is NULL, sp_rpc will attempt to determine this automatically. If it cannot, you will have to supply this value yourself.

sp_rpc on Oracle

Nearly identical to the Sybase version except it resides in the HTTP schema. Obviously, you have to use PL/SQL syntax when invoking it.

Here is how to display "Hello World" on the same server:

HTTP.SP_RPC
(
    BUTTON                        => NULL
,   PROCNAME                      => 'select ''hello world'' from dual'
,   SRCESERVER                    => NULL
,   SRCEDATABASE                  => NULL
,   SRCEOWNER                     => NULL
,   SRCENAME                      => NULL
,   DESTSERVER                    => NULL
,   MAILTO                        => NULL
)

Here is how to display "Hello World" at a Sybase server:

HTTP.SP_RPC
(
    BUTTON                        => NULL
,   PROCNAME                      => 'select ''hello world'''
,   SRCESERVER                    => NULL
,   SRCEDATABASE                  => NULL
,   SRCEOWNER                     => NULL
,   SRCENAME                      => NULL
,   DESTSERVER                    => 'HRPAY'
,   MAILTO                        => NULL
)

Remember our example called RPC_TEST that resides on DEVPAY? Here is how to invoke that from Oracle. Since this procedure creates PL/SQL text, we don't want to run the results in Sybase. Instead, we want to run the results in Oracle.

HTTP.SP_RPC
(
    BUTTON                        => NULL
,   PROCNAME                      => 'exec sybsystemprocs.dbo.sp_rpc NULL,"RPC_TEST ''2DZC543'',''GREEN''","DEVPAY","sybsystemprocs","dbo",NULL,"ORADEV","nospam@nospams.com"'
,   SRCESERVER                    => NULL
,   SRCEDATABASE                  => NULL
,   SRCEOWNER                     => NULL
,   SRCENAME                      => NULL
,   DESTSERVER                    => 'DEVPAY'
,   MAILTO                        => NULL
)

Since the parameters are passed by position and cannot be named, the value of BUTTON was explicitly passed as NULL.

Security

You must have SA_ROLE to invoke sp_rpc.