Dropping a routine in a local or remote database

After you create an SPL routine, you cannot change the body of the routine. Instead, you need to drop the routine and re-create it. Before you drop the routine, however, make sure that you have a copy of its text somewhere outside the database.

In general, use DROP PROCEDURE with an SPL procedure name and DROP FUNCTION with an SPL function name, as the following figure shows.
Figure 1: DROP PROCEDURE and DROP FUNCTION.
DROP PROCEDURE raise_prices;
DROP FUNCTION read_address;
Tip: You can also use DROP PROCEDURE with a function name to drop an SPL function. However, it is recommended that you use DROP PROCEDURE only with procedure names and DROP FUNCTION only with function names.
If the database has other routines of the same name (overloaded routines), you cannot drop the SPL routine by its routine name alone. To drop a routine that has been overloaded, you must specify either its signature or its specific name. The following figure shows two ways that you might drop a routine that is overloaded.
Figure 2: Drop a routine that is overloaded.
DROP FUNCTION calculate(INT, INT, INT);
   -- this is a signature

DROP SPECIFIC FUNCTION calc1;
   -- this is a specific name
If you do not know the type of a routine (function or procedure), you can use the DROP ROUTINE statement to drop it. DROP ROUTINE works with either functions or procedures. DROP ROUTINE also has a SPECIFIC keyword, as the following figure shows.
Figure 3: The DROP ROUTINE statement.
DROP ROUTINE calculate;
DROP SPECIFIC ROUTINE calc1;

Before you drop an SPL routine stored on a remote database server, be aware of the following restriction. You can drop an SPL routine with a fully qualified routine name in the form database@dbservername:owner.routinename only if the routine name alone, without its arguments, is enough to identify the routine.

Restrictions on data types in distributed operations

SPL routines that access tables in databases of non-local database servers, or that are invoked as UDRs of a database of another database server, can only have non-opaque built-in data types as their arguments or returned values.

If the tables or the UDR resides on another database of the same HCL OneDB™ instance, however, the arguments and returned values of routines written in SPL (or in external languages that HCL OneDB supports) can be the built-in opaque data types BLOB, BOOLEAN, CLOB, and LVARCHAR. They can also be UDTs or DISTINCT data types if the following conditions are true:
  • The remote database has the same server as the current database.
  • The UDT arguments are explicitly cast to a built-in data type.
  • The DISTINCT types are based on built-in types and are explicitly cast to built-in types.
  • The SPL routine and all the casts are defined in all participating databases.