sa_make_object system procedure

Ensures that a skeletal instance of an object exists before executing an ALTER statement.

Syntax

sa_make_object( 
objtype
, objname
[, owner
[, tabname ]  ] 
)

objtype:
'procedure' 
| 'function' 
| 'view' 
| 'trigger' 
| 'service'
| 'event'

Arguments

objtype – Use this CHAR(30) parameter to specify the type of object being created. If objtype is 'trigger', this argument specifies the owner of the table on which the trigger is to be created.
objname – Use this CHAR(128) parameter to specify the name of the object to be created.
owner – Use this optional CHAR(128) parameter to specify the owner of the object to be created. The default is NULL.
tabname – This CHAR(128) parameter is required only if objtype is 'trigger', in which case you use it to specify the name of the table on which the trigger is to be created. The default is NULL.

Remarks

This procedure is useful in scripts that are run repeatedly to create or modify a database schema. A common problem in such scripts is that the first time they are run, a CREATE statement must be executed, but subsequent times an ALTER statement must be executed. This procedure avoids the necessity of querying the system views to find out whether the object exists.

For procedures, functions, views, triggers, you can now use the OR REPLACE clause instead of this system procedure.

To use the procedure, follow it by an ALTER statement that contains the entire object definition.

Privileges

You must have the required privileges as follows:

Procedures or functions owned by the invoker – CREATE PROCEDURE, CREATE ANY PROCEDURE, or CREATE ANY OBJECT system privilege
Procedures or functions owned by other users – CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege
Services – MANAGE ANY WEB SERVICE system privilege
Events – MANAGE ANY EVENT or CREATE ANY OBJECT system privilege
Views owned by the invoker – CREATE VIEW, CREATE ANY VIEW, or CREATE ANY OBJECT system privilege
Views owned by other users – CREATE ANY VIEW or CREATE ANY OBJECT system privilege
Triggers – If the trigger is on a table owned by you, you must have either the CREATE ANY TRIGGER or CREATE ANY OBJECT system privilege.
If the trigger is on a table owned by another user, you must have either the CREATE ANY TRIGGER or the CREATE ANY OBJECT system privilege. Additionally, you must have one of the following:
- ALTER ANY TABLE privilege
- ALTER ANY OBJECT system privilege
- ALTER permission on the table on which the trigger is being created.

Side effects

Automatic commit

Examples

The following statements ensure that a skeleton procedure definition is created, define the procedure, and grant privileges on it. A script file containing these instructions could be run repeatedly against a database without error.

CALL sa_make_object( 'procedure', 'myproc' );
ALTER PROCEDURE myproc( in p1 INT, in p2 CHAR(30) )
BEGIN
    // ...
END;
GRANT EXECUTE ON myproc TO public;

The following example uses the sa_make_object system procedure to add a skeleton web service.

CALL sa_make_object( 'service', 'my_web_service' );