13. Altibase System-defined Stored Packages

---

This chapter discusses system-defined stored packages provided by Altibase.

System-defined Stored Packages

The system-defined Stored packages are the fundamental packages provided by Altibase, and they are owned by the SYS user

Types of System-difined Stored Packages

Altibase provides the system-defined Stored packages as follows.

Packages	Description
DBMS_APPLICATION_INFO	Configures the performance view in order to manage information of client application.
DBMS_ALERT	Notifies other users of events that occur in the database.
DBMS_CONCURRENT_EXEC	Allows procedures to be concurrently executed.
DBMS_LOCK	Offers an interface in which the user can request lock or unlock.
DBMS_METADATA	Provides the ability to extract object creation DDL statements or privileged GRANT statements from the database dictionary.
DBMS_OUPUT	allows the user to print a character string stored in buffer to a client.
DBMS_RANDOM	Creates arbitrary numbers.
DBMS_RECYCLEBIN	Can completely purge the tables which has been dropped and managed in the recycle bin.
DBMS_SQL	Provides procedures and functions utilizing dynamic SQL.
DBMS_SQL_PLAN_CACHE	Provides two stored procedures which keeps or removes the specified execution plan in SQL Plan Cache.
DBMS_STANDARD	Provides various default sub programs
DBMS_STATS	Package views and modifies the stats information
DBMS_UTILITY	Provides various utility subprograms.
STANDARD	In addition to the basic data types, it defines the types that can be used without declaration in PSM.
SYS_SPATIAL	Provides subprograms related to GEOMETRY.
UTL_COPYSWAP	Online DDL is supported by COPY & SWAP method
UTL_FILE	Can read and write text files managed by an operating system.
UTL_RAW	Can modify or alter RAW(VARBYTE) type data into a different type.
UTL_SMTP	Executes SMTP to send E-mail on SMTP server.
UTL_TCP	Controls TCP access in a stored procedure.

DBMS_APPLICATION_INFO

The DBMS_APPLICATION_INFO package tracks and manages the performance of the application by setting or getting values for the V\$SESSION performance view.

The procedures and functions which are comprised of the DBMS_APPLICATION_INFO package are listed in the following table below.

Procedures/Functions	Description
READ_CLIENT_INFO	Imports MODULE and ACTION values specified in V$SESSION.
READ_MODULE	Imports MODULE and ACTION values specified in V$SESSION.
SET_ACTION	Configures values of ACTION in V$SESSION.
SET_CLIENT_INFO	Configures values of CLIENT_INFO in V$SESSION.
SET_MODULE	Configures MODULE and ACTION values of V$SESSION.

READ_CLIENT_INFO

The READ_CLIENT_INFO imports application information of client accessed to the current session.

Syntax

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info OUT VARCHAR(128));

Parameter

Name	Input/Output	Data Type	Description
client_info	OUT	VARCHAR(128)	Information of configured client applicaiton

Return Value

The number of records processed by executing a cursor are returned.

Exception

There is no exception.

Example

Import values of currently executing client information in the current session then print.

iSQL> var v1 varchar(128);
iSQL> EXEC DBMS_APPLICATION_INFO.READ_CLIENT_INFO(:v1);
iSQL> EXEC PRINTLN(:v1);

READ_MODULE

The READ_MODULE imports values of MODULE and ACTION specified in the V$SESSION performance view.

Syntax

DBMS_APPLICATION_INFO.READ_MODULE(module_name OUT VARCHAR(128), action_name OUT VARCHAR(128));

Parameter

Name	In/Output	Data Type	Description
module_name	OUT	VARCHAR(128)	Specified values in the module.
action_name	OUT	VARCHAR(128)	Specified action values.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

Import module name and action value of the procedure which is currently being executed then print.

iSQL> var v1 varchar(128);
iSQL> var v2 varchar(128)
iSQL> EXEC DBMS_APPLICATION_INFO.READ_MODEUL(:v1, :v2);
iSQL> EXEC PRINTLN(:v1);
iSQL> EXEC PRINTLN(:v2);

SET_ACTION

The SET_ACTION is a procedure configuring values of the ACTION column in V$SESSION performance view.

Syntax

DBMS_APPLICATION_INFO.SET_ACTION (action_name VARCHAR(128));

Parameter

Nameq	In/Output	Data Type	Description
action_name	IN	VARCHAR(128)	The values of ACTION column that will be specified.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

The SET_ACTION sets the status of currently operating procedure to stop.

iSQL> EXEC DBMS_APPLICATION_INFO.SET_ACTION( 'stop');

SET_CLIENT_INFO

The SET_CLIENT_INFO configures the client information which is accessed to V$SESSION performance view.

Syntax

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info VARCHAR(128));

Parameter

Name	In/Output	Data Type	Description
client_info	IN	VARCHAR(128)	Client application information

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

This sets the client information to test_application.

iSQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('test_application');

SET_MODULE

The SET_MODULE procedure configures MODULE and values of ACTION column(s) in V$SESSION performance view.

Syntax

DBMS_APPLICATION_INFO.SET_MODULE(module_name VARCHAR(128), action_name
VARCHAR(128));

Parameters

Name	In/Output	Data Type	Description
module_name	IN	VARCHAR(128)	The module values which will be configured
action_name	IN	VARCHAR(128)	The value of ACTION column which will be configured

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

The SET_MODULE procedure modifies the module name of currently running procedure to altibase_module, and sets the status to be running.

iSQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('altibase_module', 'running');

DBMS_ALERT

The DBMS_ALERT package informs and provides an alert to other users with the support of an interface form in regards to various database events.

The DBMS_ALERT package consists of the following procedures and functions.

Procedures/Fucntions	Description
REGISTER	Registers for an alert
REMOVE_EVENT	Removes a specific alert
REMOVEALL	Removes all the alerts
SET_DEFAULTS	Configures the standby time of an alert
SIGNAL	Delivers signals to alerts
WAITANY	Stands by for all the alerts
WAITONE	Awaits a certain alert

REGISTER

알람을 등록한다.

Syntax

DBMS_ALERT.REGISTER (name);

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	The alert name

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_ALERT.REGISTER (‘S1’);

REMOVE_EVENT

This procedure removes a specific alert. The unregistered alert cannot be able to receive signals.

Syntax

DBMS_ALERT.REMOVE_EVENT( name );

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	The alert name

Return Value

None

Exception

There is no exception.

Example

iSQL> EXEC DBMS_ALERT.REMOVE_EVENT (‘S1’);

REMOVEALL

This procedure removes all the alerts which have been already registered. The unregistered alerts cannot be able to receive signals.

Syntax

DBMS_ALERT.REMOVEALL();

Parameter

None

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

EXEC DBMS_ALERT.REMOVEALL ();

SET_DEFAULTS

This procedure sets the standby time for an alert.

Syntax

DBMS_ALERT.SET_DEFAULTS( poll_interval );

Parameter

Name	In/Output	Data Type	Description
poll_interval	IN	INTEGER	The standby time for an alert (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

EXEC DBMS_ALERT.SET_DEFAULTS (5);

SIGNAL

This procedure sends a message included signal to an alert, and multiple signals can be sent; however, only the registered alerts can receive the signals.

Syntax

DBMS_ALERT.SIGNAL( name, message );

Parameters

Name	In/Output	Data Type	Description
name	IN	VARCHAR2(30)	An alert name
message	IN	VARCHAR2(1800)	Message

Return Value

None

Exception

There is no exception.

Example

EXEC DBMS_ALERT.SIGNAL (‘S1’, 'MESSAGE 001’);

WAITANY

This procedure is called to await signals. Only the registered alerts are able to receive the signals, and the procedure is terminated after a certain time(timeout) has passed in a condition of not being received signals.

Syntax

DBMS_ALERT.WAITANY( name, message, status, timeout );

Parameters

Name	In/Output	Data Type	Description
name	OUT	VARCHAR2(30)	An alert name
message	OUT	VARCHAR2(1800)	Message
status	OUT	INTEGER	Status (Success: 0, Fail: 1)
timeout	IN	INTEGER	The standby time for an alert(Timeout) (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

VAR MESSAGE VARCHAR (1800);
VAR STATUS INTEGER;
EXEC DBMS_ALERT.WAITANY ( :NAME, :MESSAGE, :STATUS, 5 );

WAITONE

This procedure awaits a certain alert, and only the registered alerts can receive signals.

Syntax

DBMS_ALERT.WAITONE( name, message, status, timeout );

Parameters

Name	In/Output	Data Type	Description
name	OUT	VARCHAR2(30)	An alert name
message	OUT	VARCHAR2(1800)	Message
status	OUT	INTEGER	Status (Success: 0, Fail: 1)
timeout	IN	INTEGER	The standby time for an alert(Timeout) (Unit: seconds)

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

VAR NAME VARCHAR (30);
VAR MESSAGE VARCHAR (1800);
VAR STATUS INTEGER;
EXEC :name := 'S1';
EXEC DBMS_ALERT.WAITONE ( :NAME, :MESSAGE, :STATUS, 5 );

DBMS_CONCURRENT_EXEC Package

The DBMS_CONCURRENT_EXEC package allows the concurrent execution of procedures. This is a system-defined package.

DBMS_CONCURRENT_EXEC Procedures and Functions

The DBMS_CONCURRENT_EXEC package consists of the following procedures and functions.

Procedures/Functions	Description
INITIALIZE	Initializes the DBMS_CONCURRENT_EXEC package and specifies the number of procedures that can be executed concurrently.
REQUEST	Requests the DBMS_CONCURRENT_EXEC package to run a procedure.
WAIT_ALL	Waits for the execution of all procedures, that were requested by the DBMS_CONCURRENT_EXEC package, to finish.
WAIT_REQ	Waits for the procedure corresponding to Request ID to finish.
GET_ERROR_COUNT	Returns the number of errors that occurred on the requested procedure.
GET_ERROR	Fetches the syntax, error code, and error message of the procedure corresponding to Request ID.
PRINT_ERROR	Prints the syntax, error code, and error message of the procedure corresponding to Request ID.
GET_LAST_REQ_ID	Returns the most recently executed Request ID that was successful.
GET_REQ_TEXT	Returns the procedure syntax corresponding to Request ID.
FINALIZE	Frees the memory that executed the DBMS_CONCURRENT_EXEC package, and initializes the package.

Related Properties

Properties related to the DBMS_CONCURRENT_EXEC package can be set in the altibase.properties file.

• CONCURRENT_EXEC_DEGREE_MAX

• CONCURRENT_EXEC_DEGREE_DEFAULT

• CONCURRENT_EXEC_WAIT_INTERVAL

For more detailed information, please refer to the General Reference.

Restrictions

The DBMS_CONCURRENT_EXEC package has the following restrictions.

• Only procedures that do not return results can be executed; functions that do not return results cannot be executed.
• Procedures with output parameters cannot be executed.
• Procedures or functions cannot make recursive calls. If a recursive call is made, the RECURSIVE_CALL_IS_NOT_ALLOWED exception is raised.
• Cannot be used in parallel queries.
• Executed procedures cannot be printed to the screen with PRINT or PRINTLN. Logs are written in $ALTIBASE_HOME/trc/altibase_qp.log.

INITIALIZE

INITIALIZE initializes the DBMS_CONCURRENT_EXEC package and sets the number of procedures allowed to be executed in parallel. On omission, the value set for the CONCURRENT_EXEC_DEGREE_DEFAULT property is applied.

The maximum number of procedures allowed to be executed in parallel cannot exceed the value set for the CONCURRENT_EXEC_DEGREE_MAX property. If a number of procedures corresponding to CONCURRENT_EXEC_DEGREE_MAX is being executed in another session, 0 is returned and the function does not execute.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.INITIALIZE (in_degree INTEGER DEFAULT NULL );

Parameter

Name	In/Output	Data Type	Description
in_dgree	IN	INTEGER	The number of procedures to be executed in parallel

Return Value

If successful, the number of procedures (DEGREE) that were set is returned. If the server failed to allocate resources, 0 is returned.

Exception

The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests INTIALIZE.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

Initialize the DBMS_CONCURRENT_EXEC package and set the number of procedures to be executed in parallel to 4.

VARIABLE OUT_DEGREE INTEGER;
EXEC :OUT_DEGREE := DBMS_CONCURRENT_EXEC.INITIALIZE(4);

REQUEST

REQUEST requests the DBMS_CONCURRENT_EXEC package to execute a procedure.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.REQUEST(text VARCHAR(8192) );

Parameter

Name	In/Output	Data Type	Description
text	IN	VARCHAR(8192)	The procedure and procedure arguments

Return Value

If successful, Request ID is returned. Request ID is managed in the DBMS_CONCURRENT_EXEC package.

If unsuccessful, -1 is returned. However, it is still possible to fetch Request ID, and errors can be checked with the GET_ERROR function.

Exception

The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

Request procedures in the DBMS_CONCURRENT_EXEC package to be executed in parallel.

VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');

WAIT_ALL

WAIT_ALL waits until the execution of procedures to be executed in parallel are finished.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.WAIT_ALL(  );

Return Value

If successful, 1 is returned. If unsuccessful, -1 is returned.

Exception

The following exception can occur when requesting WAIT_ALL from a procedure executed by the DBMS_CONCURRENT_EXEC package.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

The following is an example of waiting for all the procedures requested by the DBMS_CONCURRENT_EXEC package to complete.

VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL( );

WAIT_REQ

This procedure waits until the operation of a specific procedure being processed in parallel to be completed in the DBMS_CONCURRENT_EXEC package.

Syntax

INTERGER variable :=  
  DBMS_CONCURRENT_EXEC.WAIT_REQ( req_id INTEGER);

Parameter

Name	In/Output	Data Type	Description
req_id	IN	INTEGER	The Request ID corresponding to the procedure executed by packages.

Return Value

1 is returned when successfully executed.

If a request ID does not exist, -1 is returned.

Exception

If a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function, the following exception can be occurred.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

This is an example of waiting until the procedure requested by 'REQ_ID1' to be completed in the DBMS_CONCURRENT_EXEC package.

VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_REQ(:REQ_ID1);

GET_ERROR_COUNT

GET_ERROR_COUNT returns the number of errors that occurred during the execution of a requested procedure. To get an accurate count, call WAIT_ALL and then GET_ERROR_COUNT.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.GET_ERROR_COUNT( );

Return Value

If successful, the number of errors is returned.

0 means that the execution of all requested procedures was successful.

Example

The following exception may occur when a procedure executed in DBMS_CONCURRENT_EXEC package requests this functon.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

Get the number of errors that occurred during the execution of a procedure.

VARIABLE ERR_COUNT INTEGER;
VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL( );
EXEC :ERR_COUNT := DBMS_CONCURRENT_EXEC.GET_ERROR_COUNT( );

GET_ERROR

GET_ERROR fetches the syntax, error code, and error message of the procedure corresponding to Request ID. To get accurate information, call WAIT_ALL, and then GET_ERROR.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.GET_ERROR(
     req_id IN INTEGER,
     text OUT VARCHAR(8192),
     err_code OUT INTEGER,
     err_msg OUT VARCHAR(8192));

Parameters

Name	In/Output	Data Type	Description
req_id	IN	INTEGER	The Request ID corresponding to the procedure for which error information is to be fetched
text	OUT	VARCHAR(8192)	The syntax of the procedure
err_code	OUT	INTEGER	The error code
err_msg	OUT	VARCHAR(8192)	The error message

Return Value

If successful, Request ID is returned.

If neither Request ID exists nor an error occurred, -1 is returned.

Exception

The following exception may occur when a procedure executed in DBMS_CONCURRENT_EXEC package requests this functon.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

Fetch the error that occurred during the execution of a procedure.

VARIABLE RC INTEGER;
VARIABLE TEXT VARCHAR(8192);
VARIABLE ERR_CODE INTEGER;
VARIABLE ERR_MSG VARCHAR(8192);
VARIABLE REQ_ID INTEGER;
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_REQ(:REQ_ID);
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.GET_ERROR( :REQ_ID, :TEXT, :ERR_CODE, :ERR_MSG);

PRINT_ERROR

PRINT_ERROR prints the syntax, error code, and error message of the procedure corresponding to Request ID.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.PRINT_ERROR(req_id IN INTEGER);

Parameter

Name	In/Out	Data Type	Description
req_id	IN	INTEGER	The Request ID corresponding to the procedure to be printed

Example

The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

Print an error that occurred during the execution of a procedure.

VARIABLE RC INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :RC := DBMS_CONCURRENT_EXEC.WAIT_ALL();
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID1);
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID2)
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID3);
EXEC DBMS_CONCURRENT_EXEC.PRINT_ERROR(:REQ_ID4);

GET_LAST_REQ_ID

GET_LAST_REQ_ID returns the most recently executed Request ID that was successful.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.GET_LAST_REQ_ID( );

Return Value

If successful, the most recently executed Request ID is returned.

Exception

There is no exception.

Example

The following is an example of obtaining the ID of the procedure operation last requested through the DBMS_CONCURRENT_EXEC package.

VARIABLE LAST_REQ_ID INTEGER;
VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC :LAST_REQ_ID := DBMS_CONCURRENT_EXEC.GET_LAST_REQ_ID( );

GET_REQ_TEXT

GET_REQ_TEXT returns the syntax of the requested procedure.

Syntax

VARCHAR(8192) variable :=
  DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(req_id IN INTEGER);

Parameter

Name	In/Out	Data Type	Description
req_id	IN	INTEGER	The Request ID corresponding to theprocedure for which syntax is to be returned

Return Value

If successful, the syntax of the procedure is returned.

If the Request ID does not exist, NULL is returned.

Exception

There is no exception.

Example

The following is an example of obtaining the procedure operation syntax requested through the DBMS_CONCURRENT_EXEC package.

VARIABLE REQ_ID1 INTEGER;
VARIABLE REQ_ID2 INTEGER;
VARIABLE REQ_ID3 INTEGER;
VARIABLE REQ_ID4 INTEGER;
EXEC :REQ_ID1 := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :REQ_ID2 := DBMS_CONCURRENT_EXEC.REQUEST('PROC2(1, 1, 3)');
EXEC :REQ_ID3 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''ABC'', 3, 3)');
EXEC :REQ_ID4 := DBMS_CONCURRENT_EXEC.REQUEST('PROC3(''DEF'', 3, 3)');
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID1));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID2));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID3));
EXEC PRINTLN(DBMS_CONCURRENT_EXEC.GET_REQ_TEXT(:REQ_ID4));

FINALIZE

FINALIZE initializes the DBMS_CONCURRENT_EXEC package and frees used resources.

Syntax

INTERGER variable :=
  DBMS_CONCURRENT_EXEC.FINALIZE( );

Return Value

If successful, 1 is returned.

Exception

The following exception may occur when a procedure executed in the DBMS_CONCURRENT_EXEC package requests this function.

RECURSIVE_CALL_IS_NOT_ALLOWED

Example

The following is an example of initializing the DBMS_CONCURRENT_EXEC package and returning the used system resources.

VARIABLE RC INTEGER;
VARIABLE REQ_ID INTEGER;
EXEC :REQ_ID := DBMS_CONCURRENT_EXEC.REQUEST('PROC1');
EXEC :RC := DBMS_CONCURRENT_EXEC.FINALIZE( );

DBMS_LOCK

The DBMS_LOCK package provides an interface managing lock and unlock which can be requested.

The following table demonstrates the procedures and functions comprised of the DBMS_LOCK package.

Procedures/Functions	Description
RELEASE	Unlocks the user.
REQUEST	Requests the user lock.
SLEEP	Makes the session to rest for a certain period of time as it is set.
SLEEP2	Makes the session to rest for a certain period of time as it is set.

Related Properties

DBMS_LOCK properties can be set in altibase.properties.

• USER_LOCK_POOL_INIT_SIZE

• USER_LOCK_REQUEST_CHECK_INTERVAL

• USER_LOCK_REQUEST_LIMIT

• USER_LOCK_REQUEST_TIMEOUT

For more detailed information, please refer to the General Reference.

RELEASE

The RELEASE is a function which unlocks the user account.

Syntax

INTEGER variable :=
  DBMS_LOCK.RELEASE(id IN INTEGER);

Parameters

Name	In/Output	Data Type	Description
id	IN	INTEGER	Lock ID 0 \~ 1073741823

Result Values

The result values are as follows.

• 0 : Success

• 3 : Parameter error

• 4: Already own lock specified by id

Exception

There is no exception in this function; however, if it fails, other values, rather than 0, are returned.

Example

Unlocks ID which is 0.

iSQL> var v1 integer;
iSQL> v1 := dbsm_lock.release(0);

REQUEST

The REQUEST is a function requesting the user lock.

Syntax

INTEGER variable :=
  DBMS_LOCK.REQUEST(
    id IN INTEGER,
    lockmode IN INTEGER DEFAULT x_mode,
    timeout IN INTEGER DEFAULT MAXWAIT,
    release_on_commit IN BOOLEAN DEFAULT FALSE);

Parameters

Name	In/Output	Data Type	Description
id	IN	INTEGER	Lock ID 0 \~ 1073741823
lockmode	IN	INTEGER	This is the parameter only for compatibility. x_mode (exclusive lock) is supported.
timeout	IN	INTEGER	This is the parameter only for compatibility. The default value is MAXWAIT.
release_on_commit	IN	INTEGER	This is a parameter only for compatibility. The default value is FALSE.

Result Values

The result values are as follows.

• 0 : Success

• 1 : Timeout

• 3 : Parameter error

• 4: Already own lock specified by id

Exception

There is no exception in this function; however, if it fails, other values, rather than 0, are returned.

Example

Requests lock on the ID which is 0.

iSQL> var v1 integer;
iSQL> v1 := dbsm_lock.request(0);

SLEEP

The SLEEP is a procedure putting the procedure into to sleep for a specific time.

Syntax

DBMS_LOCK.SLEEP(seconds IN INTEGER);

Parameter

Name	In/Output	Data Type	Description
second	IN	INTEGER	Sleep for a specific seconds. There is no maximum.

Result Value

Because it is a stored procedure, there is no result value.

Exception

There is no exception.

SLEEP2

The SLEEP is a procedure putting the procedure into to sleep for a specific time.

Syntax

DBMS_LOCK.SLEEP2(seconds IN INTEGER, microseconds IN INTEGER);

Parameter

Name	In/Output	Data Type	Description
seconds	IN	INTEGER	Sleep for a specific seconds. There is no maximum.
microseconds	IN	INTEGER	Maximum amount of time, in microseconds, that a session is idle is 999999

Result Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

DBMS_METADATA

The DBMS_METADATA package provides the ability to extract object creation DDL statements or privileged GRANT statements from the database dictionary. The following table shows the procedures and functions that make up the DBMS_METADATA package.

Procedures/Functions	Description
GET_DDL	Returns DDL statement for specified object
GET_DEPENDENT_DDL	Returns DDL statement for objects that depend on the specified object
GET_GRANTED_DDL	Returns GRANT statement for privileges granted to specified user
SET_TRANSFORM_PARAM	Whether to include specific items in the returned DDL statement
SHOW_TRANSFORM_PARAMS	Outputs the currently set transform parameter value.

GET_DDL

This returns DDL statement for specified object.

Syntax

DBMS_METADATA.GET_DDL (
   object_type     IN VARCHAR(20),
   object_name     IN VARCHAR(128),
   schema          IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameter

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
object_name	IN	VARCHAR(128)	Object name (case sensitive)
schema	IN	VARCHAR(128)	Object owner (case sensitive) If object_type is a schema object, the default value is the currently connected user; if it is a non-schema object, the default value is NULL.

object_type

Schema objects

• CONSTRAINT
• DB_LINK
• FUNCTION
• INDEX
• LIBRARY
• MATERIALIZED_VIEW
• PACKAGE
• PACKAGE_SPEC
• PACKAGE_BODY
• PROCEDURE
• QUEUE
• REF_CONSTRAINT
• SEQUENCE
• SYNONYM
• TABLE
• TRIGGER
• TYPESET
• VIEW

Non-schema objects

• DIRECTORY
• JOB
• REPLICATION
• ROLE
• TABLESPACE: Memory system tablespaces do not return DDL statements, and disk system tablespaces return ALTER statements.
• USER

Return Value

DDL Statement

Exception

invalid_argval not_supported_obj_type schema_not_found object_not_found not_supported_ddl

Example

The following example shows how to create the DDL statement for all tables owned by the connection user.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_ddl('TABLE', table_name, null)) as ddl
FROM system_.sys_tables_
WHERE table_type = 'T' AND user_id = user_id()
ORDER BY table_name;

GET_DEPENDENT_DDL

This returns DDL statement for objects that depend on the specified object.

Syntax

DBMS_METADATA.GET_DEPENDENT_DDL (
   object_type          IN VARCHAR(20),
   base_object_name     IN VARCHAR(128),
   base_object_schema   IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameters

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
base_object_name	IN	VARCHAR(128)	Base object name (case sensitive)
base_object_schema	IN	VARCHAR(128)	Base object owner (case sensitive). Default is the currently connected user.

object_type

• COMMENT
• CONSTRAINT
• INDEX
• OBJECT_GRANT
• REF_CONSTRAINT
• TRIGGER

Return Value

DDL statement

Exceptions

invalid_argval not_supported_obj_type schema_not_found object_not_found

Example

The following example shows how to get all object privileges for the T1 table of the connecting user.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'T1')) as ddl
FROM dual;

GET_GRANTED_DDL

This returns the DDL statement for creating privileges granted to the specified user.

Syntax

DBMS_METADATA.GET_GRANTED_DDL (
   object_type          IN VARCHAR(20),
   grantee              IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameters

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
grantee	IN	VARCHAR(128)	grantee (case sensitive). Default is the current user.

object_type

• OBJECT_GRANT
• ROLE_GRANT
• SYSTEM_GRANT

Return Value

DDL statement

Exceptions

invalid_argval not_supported_obj_type grantee_not_found object_not_found

Example

This example shows how to get all system privileges granted to user USER1.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'USER1')) as ddl
FROM dual;

SET_TRANSFORM_PARAM

Option to include specific items in the returned DDL statement. Parameter settings apply only within the same session.

Syntax

DBMS_METADATA.SET_TRANSFORM_PARAM (
   name          IN VARCHAR(40),
   value         IN CHAR(1));

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR(40)	Parameter name
value	IN	CHAR(1)	Value

Applied Parameters by Object Type

Object Type	Name	Description	Default
ALL	SQLTERMINATOR	Specifies whether to append an SQL terminator to the DDL statement. T: appends an SQL terminator F: does not append an SQL terminator	F
TABLE INDEX CONSTRAINT	SEGMENT_ATTRIBUTES	Specifies whether segment attributes (physical attributes, storage clause, tablespace, logging) are included. T: With F: Without	T
	STORAGE	pecifies whether or not to include. T: With F: Without	T
	TABLESPACE	Specifies whether the storage clause is included. T: With F: Without	T
TABLE	CONSTRAINTS	Specifies whether to include constraint (primary key, unique, check) except foreign key. T: With F: Without	T
	REF_CONSTRAINTS	Specifies whether or not to include a foreign key. T: With F: Without

Return Value

None

Exception

invalid_argval

Example

This example configures the SQL terminator to be appended to the returned DDL statement.

exec dbms_metadata.set_transform_param('SQLTERMINATOR', 'T');

SHOW_TRANSFORM_PARAMS

This outputs the currently set transform parameter value.

Syntax

DBMS_METADATA.SHOW_TRANSFORM_PARAMS;

Return Value

None

Exception

There is no exception.

DBMS_OUTPUT

The DBMS_OUTPUT package provides an interface in which the user can print the stored character strings in the buffer to clients.

The procedure and functions comprised of the DBMS_OUTPUT package are as shown in the following table.

Procedures/Functions	Description
NEW_LINE	Prints a character string stored in the buffer along with new-line characters.
PUT	Stores a character string in the buffer.
PUT_LINE	Prints a character string stored in the buffer.

NEW_LINE

The NEW-LINE procedure prints new-line characters( \n for Unix).

Syntax

DBMS_OUTPUT.NEWLINE;

Parameter

None

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

PUT

The PUT is a function storing a characteristic string in the buffer.

Syntax

DBMS_OUTPUT.PUT(str IN VARCHAR(65534));

Parameter

Name	In/Output	Data Type	Description
str	IN	VARCHAR(65534)	The buffer in which the character string to store read from a file

Result Values

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

PUT_LINE

The PUT_LINE is function which outputs by attaching the new-line characters ( \n for Unix) to the character strings printed in the buffer.

Syntax

DBMS_OUTPUT.PUT_LINE(str IN VARCHAR(65533));

Parameter

Name	In/Output	Data Type	Description
str	IN	VARCHAR(65534)	The buffer in which the character string to store read from a file.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

DBMS_RANDOM

The DBMS_RANDOM packages enables creating arbitrarily numbers.

The following table explicates the procedures and functions that are comprised of the DBMS_RANDOM package.

Procedures/Functions	Description
INITIALIZE	Executes initialization of the DBMS_RANDOM package.
SEED	Sets given values or a character string to seed.
STRING	The STRING procedure creates arbitrary numbers.
VALUE	Procedure creates arbitrary values within a specific range.
RANDOM	Generates a random number

INITIALIZE

The INITIALIZE is a procedure which initializes the DBMS_RANDOM package.

Syntax

DBMS_RANDOM.INITIALIZE(val IN INTEGER);

Parameter

Name	In/Output	Data Type	Description
val	IN	INTEGER	The value specified by seed.

Return Values

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

SEED

The SEED is a procedure creating values for arbitrary sequence by setting given values or a character string to seed.

Syntax

DBMS_RANDOM.SEED(seedval IN INTEGER);
DBMS_RANDOM.SEED(seedval IN VARCHAR(2000));

Parameters

Name	In/Output	Data Type	Description
seedval	IN	INTEGER or VARCHAR(200 0)	The character string or values that will be specified seed.

Return Values

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

STRING

The STRING procedure creates an arbitrary character string.

Syntax

DBMS_RANDOM.STRING(opt IN CHAR, len IN NUMBER);

Parameter

Name	In/Output	Data Type	Description
opt	IN	CHAR	The character string which will be created.
len	IN	NUMBER	The length of the character string which will be created.

Description

opt can specifies one of the following parameters listed as below.

• 'u', 'U': Create arbitrary capital letters of alphabet.
• 'l', 'L' : Create arbitrary small letters of alphabet.
• 'a', 'A' :Create alphabet letters regardless of capital or small letters.
• 'x', 'X' : Create capital letters of alphabet and numbers
• 'p', 'P' : Create all the character strings that can be printable.

len(gth) indicates the length of an arbitrary character string and available input rages from 0 to 4000.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

VALUE

The VALUE is a procedure which creates arbitrary values within a specified range. If the range is not specified, arbitrary numbers from 0 to 1is returned.

Syntax

NUMBER variable := DBMS_RANDOM.VALUE(low IN NUMBER, high IN NUMBER);

Parameters

Name	In/Output	Data Type	Description
low	IN	NUMBER	The minimum value of the range for creating arbitrary values.
high	IN	NUMBER	The maximum value of the range for creating arbitrary values.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

RANDOM

This function is a procedure for generating arbitrary integer values.

Syntax

DBMS_RANDOM. RANDOM();

Parameter

None

Return Value

On successful execution, it returns a random integer value.

Exception

There is no exception.

Example

Output a random number.

iSQL> select dbms_random.random() from dual;

DBMS_RECYCLEBIN Package

The DBMS_RECYCLEBIN package allows the user to completely eliminate a table that was dropped and moved to the recycle bin. This feature is provided as a system-defined stored package.

DBMS_RECYCLEBIN Procedures and Functions

The DBMS_RECYCLEBIN package consists of the following procedures and functions.

Procedures/Functions	Description
PURGE_USER_RECYCLEBIN	Drops tables in the recycle bin from the system, for each user.
PURGE_ALL_RECYCLEBIN	Drops all tables in the recycle bin.
PURGE_TABLESPACE	Drops all tables from the specified tablespace.
PURGE_ORIGINAL_NAME	Drop all duplicate tables in the recycle bin, by the name they had before they were moved.

Related Properties

DBMS_RECYCLEBIN properties can be set in altibase.properties.

• RECYCLEBIN_DISK_MAX_SIZE

• RECYCLEBIN_MEM_MAX_SIZE

• RECYCLEBIN_ENABLE

For more detailed information, please refer to the General Reference.

PURGE_USER_RECYCLEBIN

PURGE_USER_RECYCLEBIN completely eliminates tables in the recycle bin from the database system, for each user

Syntax

EXEC DBMS_RECYCLEBIN.PURGE_USER_RECYCLEBIN;

Example

Drop the tables in the recycle bin that were moved to the recycle bin by the user who is currently connected.

EXEC DBMS_RECYCLEBIN.PURGE_USER_RECYCLEBIN;

PURGE_ALL_RECYCLEBIN

PURGE_ALL_RECYCLEBIN drops all tables in the recycle bin from the database system.

Syntax

EXEC DBMS_RECYCLEBIN.PURGE_ALL_RECYCLEBIN;

Example

Drop all tables from the recycle bin.

EXEC DBMS_RECYCLEBIN.PURGE_ALL_RECYCLEBIN;

PURGE_TABLESPACE

PURGE_TABLESPACE drops all specified tables in the recycle bin from the system.

Syntax

EXEC DBMS_RECYCLEBIN.PURGE_TABLESPACE(
  tablespace_name IN VARCHAR(64));

Parameter

Name	In/Output	Data Type	Description
tablespace_name	IN	VARCHAR(64)	The tablespace name

Example

Drop the tables that exist in the TBS_DISK_DATA tablespace from the recycle bin.

EXEC DBMS_RECYCLEBIN.PURGE_TABLESPACE('TBS_DISK_DATA');

PURGE_ORIGINAL_NAME

Drops tables from the recycle bin by the names the tables had before they were dropped. Tables with identical names can be dropped several times, and dropped all at once from the recycle bin.

Syntax

EXEC DBMS_RECYCLEBIN.PURGE_ORIGINAL_NAME(
  original_table_name IN VARCHAR(128));

Parameter

Name	In/Output	Data Type	Description
original_table_name	IN	VARCHAR(128)	The name of the table before it was dropped.

Example

Drop all tables that had the name 'TABLE1’ before they were dropped, from the system.

EXEC DBMS_RECYCLEBIN.PURGE_ORIGINAL_NAME('TABLE1');

DBMS_SQL

The DBMS_SQL provides procedures and functions which utilize dynamic SQL as shown in the table below.

Procedures/Functions	Description
OPEN_CURSOR	Opens a cursor. The maximum number of cursors, which can be open, is be specified in the PSM_CURSOR_OPEN_LIMIT property. (Default Value: 32)
IS_OPEN	Checks on the status of cursor to see if it is open or not in order to return the results.
PARSE	Execute parsing SQL statements.
BIND_VARIABLE	Binds variables which are included in the SQL statement.
EXECUTE_CURSOR	Executes the cursor.
DEFINE_COLUMN	Defines the columns which will be fetched in the cursor.
FETCH_ROWS	Imports a row which is supposed to fetch. It is only used in the SELECT statement.
COLUMN_VALUE	Imports the value of a column which is a variable of the cursor. It is only used in the SELECT statement.
CLOSE_CURSOR	Closes the cursor.
LAST_ERROR_POSITION	Returns the location of the error that occurred when parsing.

Related Properties

DBMS_SQL package related properties can be set in altibase.properties.

• PSM_CURSOR_OPEN_LIMIT

For more detailed information, please refer to the General Reference.

BIND_VARIABLE

The BIND_VARIABLE procedure execute binding of variables which are included in the SQL statement.

Syntax

DBMS_SQL.BIND_VARIABLE(c, name, value);

Parameters

Name	In/Output	Data Type	Description
c	IN	INTEGER	The cursor number
name	IN	VARCHAR2(128)	The variable name starting with a colon (;).
value	IN	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	The language option(It is not supported so that it can be neglected regardless of any value).

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

CLOSE_CURSOR

The CLOSE_CURSOR procedure closes a cursor. If the cursor cannot be closed, it is closed when the session is terminated.

Syntax

DBMS_SQL.CLOSE_CURSOR(c);

Parameter

Name	In/Output	Data Type	Descritption
c	IN	INTEGER	Cursor number

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.close_cursor( c );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

COLUMN_VALUE

The COLUMN_VALUE procedure imports the value of a column which is the binding variables of cursor.

Syntax

DBMS_SQL.COLUMN_VALUE(c, position, column_value);

Parameters

Name	In/Output	Data Type	Descritpion
c	IN	INTEGER	The cursor number
position	IN	INTEGER	The relational position when fetching a column. It starts with 1.
column_value	OUT	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	Store the value of a column

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
loop
exit when dbms_sql.fetch_rows( c ) = 0;
dbms_sql.column_value(c, 1, c1);
println( 'fetch -> ' || c1 );
end loop;
end;
/
Create success.

iSQL> exec proc1;
0
fetch -> 999
Execute success.

DEFINE_COLUMN

The DEFINE_COLUMN procedure defines the type of column which will be fetched. It is only used in the SELECT statement.

Syntax

DBMS_SQL.DEFINE_COLUMN(c, position, column_value);

Parameters

Name	In/Output	Data Type	Description
c	IN	INTEGER	Cursor number
position	IN	INTEGER	The location of a column. It starts with 1.
column_value	IN	VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE	The definition of column type

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

EXECUTE_CURSOR

The EXECUTE_CURSOR function implements a cursor.

Syntax

BIGINT variable:=DBMS_SQL.EXECUTE_CURSOR(c);

Parameter

Name	In/Output	Data Type	Description
c	IN	INTEGER	The cursor number

Result Value

This function returns the number of records by executing a cursor.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
println( rc );
end;
/
Create success.

iSQL> exec proc1;
0
1
Execute success.

FETCH_ROWS

The FETCH_ROWS imports the row which will be fetched in a cursor. It is only used in the SELECT statement.

Syntax

INTEGER variable:=DBMS_SQL.FETCH_ROWS(c);

Parameters

Name	In/Output	Data Type	Description
c	IN	INTEGER	The cursor number

Result Value

0 is returned if there is no row to fetch; otherwise, it returns 1.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
rc := dbms_sql.fetch_rows( c );
println( rc );
end;
/
Create success.

iSQL> exec proc1;
0
1
Execute success.

IS_OPEN

The IS_OPEN is a function which returns the result whether the cursor is open or not.

Syntax

BOOLEAN variable:=DBMS_SQL.IS_OPEN(c);

Parameter

Name	In/Output	Data Type	Description
c	IN	INTEGER	Cursor number

Result Value

True is returned when the cursor is open, and FALSE is returned when the cursor is not open.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
if dbms_sql.is_open( c ) = TRUE
then
println( 'cursor opened' );
else
println( 'invalid cursor' );
end if;
end;
/
Create success.

iSQL> exec proc1;
0
cursor opened
Execute success.

LAST_ERROR_POSITION

The LAST_ERROR_POSITION returns the location of error that occurred when parsing

This function should be used immediately after calling the PARSE procedure to get the correct result

Syntax

DBMS_SQL.LAST_ERROR_POSITION;

Result Value

Returns the error locaiton.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1( a varchar(128) )
as
c integer;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse( c, a, dbms_sql.native );
exception
 when others
  then
    println( dbms_sql.last_error_position );
    dbms_sql.close_cursor( c );
end;
/
Create success.
iSQL> exec proc1( 'select empno, ^a from emp' );
14
Execute success.

OPEN_CURSOR

The OPEN_CURSOR opens the cursor.

Syntax

INTEGER variable:=DBMS_SQL.OPEN_CURSOR;

Result Value

If is successfully executed, the number of cursor is returned.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success

PARSE

The PARSE procedure parses SQL statements.

Syntax

DBMS_SQL.PARSE(c, sql, language_flag);

Parameters

Name	In/Output	Data Type	Description
c	IN	INTEGER	The cursor number
sql	IN	VARCHAR2(32000)	SQL which will be parsed
language_flag	IN	INTEGER	The language option(it is not supported so that it is neglected regardless of specifying any values).

Result Value

Since it is a stored procedure, there is no result value.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( 1 )', dbms_sql.native );
end;
/
Create success.

iSQL> exec proc1;
0
Execute success.

DBMS_SQL_PLAN_CACHE

DBMS_SQL_PLAC_CACHE provides the two following stored procedures which keeps or removes the specified execution plan in SQL Plan Cache.

Procedures/Functions	Description
KEEP_PLAN	Keeps the specified execution plan in SQL Plan Cache.
UNKEEP_PLAN	Disables the execution plan registered by KEEP_PLAN stored procedure. The disabled execution plan can be removed from SQL Plan Cache by check-in method.

KEEP_PLAN

This procedure excludes the execution plan received as an input parameter from the SQL Plan Cache replacement target and keeps in the KEEP state in SQL Plan Cache. However, when the execution plan becomes invalid for example due to Rebuild, it is changed to the UNKEEP state. SQL_TEXT_ID of SQL statements that want to keep the execution plan in KEEP state can be found on the SQL_TEXT_ID column and SQL_TEXT column in V$SQL_PLAN_CACHE_SQLTEXT. All Child PCOs which have this SQL_TEXT_ID as Parent PCO maintain a KEEP state. The KEEP status of Parent PCO can be found on the PLAN_CACHE_KEEP column in V$SQL_PLAN_CACHE_SQLTEXT, and for Child PCO, it can be found on the PLAN_CACHE_KEEP column in V$SQL_PLAN_CACHE_PCO. To release the KEEP state of the execution plan, use the UNKEEP_PLAN storage procedure.

Syntax

DBMS_SQL_PLAN_CACHE.KEEP_PLAN(sql_text_id);

Parameter

Name	In/Output	Data Type	Description
sql_text_id	IN	VARCHAR(64)	The identifier of SQL statement in SQL Plan Cache

Return Value

Because it is a stored procedure, there is no return value.

Exception

Does not occur any exceptions.

Example

iSQL> SELECT SQL_TEXT_ID FROM V$SQL_PLAN_CACHE_SQLTEXT WHERE SQL_TEXT LIKE 'select count%';
SQL_TEXT_ID
------------------------
00510
1 rows selected.

iSQL> EXEC DBMS_SQL_PLAN_CACHE.KEEP_PLAN('00510');
Execute success.

UNKEEP_PLAN

This procedure release the KEEP state of the execution plan received as an input parameter. The released execution plan can be deleted from SQL Plan Cache according to the SQL Plan Cache management policy and check-in method.

Syntax

DBMS_SQL_PLAN_CACHE.UNKEEP_PLAN(sql_text_id);

Parameter

Name	In/Output	Data Type	Description
sql_text_id	IN	VARCHAR(64)	The identifier of SQL statement in SQL Plan Cache

Return Value

Because it is a stored procedure, there is no return value.

Exception

Does not occur any exceptions.

Example

iSQL> SELECT SQL_TEXT_ID FROM V$SQL_PLAN_CACHE_SQLTEXT WHERE PLAN_CACHE_KEEP = 'KEEP';
SQL_TEXT_ID
------------------------
00510
1 row selected.

iSQL> EXEC DBMS_SQL_PLAN_CACHE.UNKEEP_PLAN('00510');
Execute success.

DBMS_STANDARD

DBMS_STANDARD package provides various sub programs that can be used without specifying the name of the package.

The procedures and functions comprising the DBMS_STANDARD package are in the following table below.

Procedures/Functions	Description
DELETING	Returns whether the trigger started from DELETE.
INSERTING	Returns whether the trigger started from INSERT.
UPDATING	Returns whether the trigger started from UPDATE.
UPDATING	Returns whether the trigger started from a specific column's UPDATE.

DELETING

Returns whether the trigger started from DELETE.

Syntax

BOOLEAN variable := DBMS_STANDARD.DELETING;
BOOLEAN variable := DELETING;

Result Value

If the trigger started from DELETE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP ( C1 VARCHAR(10) );
INSERT INTO T1 VALUES(1);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE DELETE ON T1
FOR EACH ROW
BEGIN
 IF DELETING THEN
  INSERT INTO TMP VALUES ('DELETE');
 END IF;
END;
/

iSQL> DELETE FROM T1;
1 row deleted.
iSQL> SELECT & FROM TMP;
1 row selected.

INSERTING

Returns whether the trigger started from INSERT.

Syntax

BOOLEAN variable := DBMS_STANDARD.INSERTING;
BOOLEAN variable := INSERTING;

Result Value

If the trigger started from INSERT, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

CREATE OR REPLACE TRIGGER TRIG1
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
 IF INSERTING THEN
  INSERT INTO TMP VALUES ('INSERT');
 END IF;
END;
/

iSQL> INSERT INTO T1 VALUES(2);
1 row inserted.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
INSERT
1 row selected.

UPDATING

Returns whether the trigger started from UPDATE.

Syntax

BOOLEAN variable := DBMS_STANDARD.UPDATING;
BOOLEAN variable := UPDATING;

Result Value

If the trigger started from UPDATE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

INSERT INTO T1 VALUES(1);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
 IF UPDATING THEN
  INSERT INTO TMP VALUES ('UPDATE');
 END IF;
END;
/

iSQL> UPDATE T1 SET C1 = 2;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE
1 row selected.

UPDATING (columnName)

Returns whether the trigger started from a specific column's UPDATE.

Syntax

BOOLEAN variable := DBMS_STANDARD.UPDATING(COLNAME IN VARCHAR(128));
BOOLEAN variable := UPDATING(COLNAME IN VARCHAR(128));

Parameter

Name	In/Output	Data Type	Description
COLNAME	IN	VARCHAR(128)	Specifies the name of the column

Result Value

If the trigger started from a specific coulmn's UPDATE, TRUE is returned.

Exception

There is no exception.

Example

CREATE TABLE T1 (C1 INTEGER, C2 INTEGER);
CREATE TABLE TMP (C1 VARCHAR(10));

INSERT INTO T1 VALUES(1, 2);

CREATE OR REPLACE TRIGGER TRIG1
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
 IF UPDATING('C1') THEN
  INSERT INTO TMP VALUES ('UPDATE-C1');
ELSE
 INSERT INTO TMP VALUES ('OTHER');
 END IF;
END;
/

iSQL> UPDATE T1 SET C1 = 2;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE-C1
1 row selected.
iSQL> UPDATE T1 SET C2 = 3;
1 row updated.
iSQL> SELECT * FROM TMP;
TMP.C1
--------------
UPDATE-C1
OTHER
2 rows selected.

DBMS_STATS

The DBMS_STATS package provides an interface which can view and modifies the stats information. By using stored procedures and functions, the stats information can be established and updated, also it can configure or delete the stats information for each column, index, and table or per each system

The procedures and functions comprised of the DBMS_STATS package are in the following table below.

Procedures/Functions	Description
COPY_TABLE_STATS	Copies stats information of a partition to a new partition.
DELETE_COLUMN_STATS	Deletes stats information in column(s) of specific tables.
DELETE_DATABASE_STATS	Deletes stats information of all tables.
DELETE_INDEX_STATS	Used to dlete stats information of specific indexes.
DELETE_TABLE_STATS	Delete stats information of specific tables.
DELETE_SYSTEM_STATS	Deletes stats information of the database system.
GATHER_DATABASE_STATS	Gathers stats information of all tables.
GATHER_INDEX_STATS	Gathers stats information of specific indexes.
GATHER_SYSTEM_STATS	Gathers stats information of database system.
GATHER_TABLE_STATS	Gathers stats information of specific tables.
GET_COLUMN_STATS	Views stats information of column(s) in specific tables.
GET_INDEX_STATS	Views stats information of specific indexes.
GET_SYSTEM_STATS	View stats information of database system.
GET_TABLE_STATS	Views stats information of specific tables.
SET_COLUMN_STATS	Views stats information of column(s) in specific tables.
SET_INDEX_STATS	Alters stats information of specific indexes.
SET_PRIMARY_KEY_STATS	Alters stats information of PRIMARY KEY INDEX of a specific table.
SET_SYSTEM_STATS	Alters stats infomration of the datebase system.
SET_TABLE_STATS	Alters stats information of (a) specific tables.
SET_UNIQUE_KEY_STATS	Alter stats information of UNIQUE KEY INDEX of (a) specific tables.

SET_PRIMARY_KEY_STATS

This procedure alters stats information of PRIMARY KEY INDEX of a specific table.

Syntax

SET_PRIMARY_KEY_STATS (
  ownname       VARCHAR(128),
  tabname       VARCHAR(128),
  keycount      BIGINT DEFAULT NULL,
  numpage       BIGINT DEFAULT NULL,
  numdist       BIGINT DEFAULT NULL,
  clusteringfactor BIGINT DEFAULT NULL,
  indexheight   BIGINT DEFAULT NULL,
  avgslotcnt    BIGINT DEFAULT NULL,
  no_invalidate BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Output	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the index owner
tablename	IN	VARCHAR(128)	Name of the table for which statistics to be changed
keycount	IN	BIGINT	Number of records in the index
numpage	IN	BIGINT	Number of pages in the index
numdist	IN	BIGINT	Number of unique keys in the index
clusteringfactor	IN	BIGINT	Degree to which the data is aligned with the index
indexheight	IN	BIGINT	Depth from the root of the index to the leaf node
avgslotcnt	IN	BIGINT	Average number of records stored in the index leaf node.
no_invalidate	IN	BOOLEAN	Whether to rebuild execution plans for all queries related to the indexes for which statistics were collected. The default is FALSE, which rebuilds the execution plan. If do not want to rebuild, enter TRUE.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.SET_PRIMARY_KEY_STATS( 'SYS', 'T1', 1, 2, 3, 4, 5, 6, TRUE );
__SYS_IDX_ID_148 c integer;
Execute success.

SET_UNIQUE_KEY_STATS

This procedure alters stats information of UNIQUE KEY INDEX

Syntax

SET_UNIQUE_KEY_STATS (
  ownname       VARCHAR(128),
  tabname       VARCHAR(128),
  colnamelist   VARCHAR(32000),
  keycount      BIGINT DEFAULT NULL,
  numpage       BIGINT DEFAULT NULL,
  numdist       BIGINT DEFAULT NULL,
  clusteringfactor BIGINT DEFAULT NULL,
  indexheight   BIGINT DEFAULT NULL,
  avgslotcnt    BIGINT DEFAULT NULL,
  no_invalidate     BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Out	Data Type	Description
ownname	IN	VARCHAR(128)	Name of the index owner
tablename	IN	VARCHAR(128)	Name of the table for which statistics to be changed
colnamelist	IN	VARCHAR(32000)	List of column names to change statistics for. If DESC is specified in a column when creating a UNIQUE KEY INDEX, it must also be specified in uppercase in the colnamelist.
keycount	IN	BIGINT	Number of records in the index
numpage	IN	BIGINT	Number of pages in the index
numdist	IN	BIGINT	Number of unique keys in the index
clusteringfactor	IN	BIGINT	Degree to which the data is aligned with the index
indexheight	IN	BIGINT	Depth from the root of the index to the leaf node
avgslotcnt	IN	BIGINT	Average number of records stored in the index leaf node.
no_invalidate	IN	BIGINT	Whether to rebuild execution plans for all queries related to the indexes for which statistics were collected. The default is FALSE, which rebuilds the execution plan. If do not want to rebuild, enter TRUE.

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> EXEC DBMS_STATS.SET_UNIQUE_KEY_STATS( 'SYS', 'T1', 'C1,C2', 1, 2, 3, 4, 5, 6, TRUE );
__SYS_IDX_ID_149
Execute success.

DBMS_UTILITY

The DBMS_UTILITY package provides diverse utility subprograms.

The procedures and functions organizing the DBMS_UTILITY package are provided as shown in the table below.

Procedures/Functions	Description
FORMAT_CALL_STACK	Calls current stack information.
FORMAT_ERROR_BACKTRACE	Calls the stack information of an error occurring point.
FORMAT_ERROR_STACK	Calls information which is identical with the FORMAT_ERROR_BACKTRACE function.

FORMAT_CALL_STACK

The FORMAT_CALL_STACK is a function which display stack information at the call point and bring it to a character string.

Syntax

VARCHAR variable := DBMS_UTILITY.FORMAT_CALL_STACK;

Result Value

It returns the stack information at the call point.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
is
  a integer;
begin
  a := 1;
  println( dbms_utility.format_call_stack );
end;
/
Create success.

iSQL> create or replace procedure proc2 as begin
  proc1;
end;
/
Create success.

iSQL> exec proc2;
object     line       object
handle     number     name
6261376    6          procedure "SYS.PROC1"
6258720    2          procedure "SYS.PROC2"
Execute success.

FORMAT_ERROR_BACKTRACE

The FORMAT_ERROR_BACKTRACE is a function which retrieves stack information at the point in which an exception was occurred. If no exception had been incurred, NULL value would be returned.

Syntax

VARCHAR variable := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

Result Value

It returns the stack information at the point in which an exception was occurred. If no exception had been incurred, NULL value would be returned.

Exception

There is no exception.

Example

iSQL> create or replace procedure proc1
is
  a integer;
begin
  a := 'aaaaa';
end;
/
Create success.

iSQL> create or replace procedure proc2 as begin
  proc1;
exception
when others then
  println( dbms_utility.format_error_backtrace );
end;
/
Create success.

iSQL> exec proc2;
ERR-21011 : Invalid literal
at "SYS.PROC1", line 5
at "SYS.PROC2", line 2
Execute success.

STANDARD

The STANDARD package defines the types that can be used in the PSM without any additional declarations other than the basic data types. The STANDARD package provides the types listed in the table below.

STANADARD Package Type Name	Type
SYS_REFCURSOR	REF CURSOR

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1 AS
CUR1 SYS_REFCURSOR;
VAR1 INTEGER;
BEGIN
OPEN CUR1 FOR 'SELECT ROWNUM FROM DUAL';
FETCH CUR1 INTO VAR1;
 PRINTLN(VAR1);
CLOSE CUR1;
END;
/

SYS_SPATIAL

SYS_SPATIAL provides subprograms related to Geometry.

Procedures and functions comprising SYS_SPATIAL package is as follows. For more detailed information about each procedure, please refer to Related Stored Procedures in Appendix C. Geometry Reference Tables in Spatial SQL Reference.

Procedures/Functions	Description
ADD_SPATIAL_REF_SYS	Registers Spatial Reference System meta data in the SPATIAL_REF_SYS_ table.
DELETE_SPATIAL_REF_SYS	Deletes the Spatial Reference System meta data from the SPATIAL_REF_SYS_ table.

UTL_COPYSWAP

The UTL_COPYSWAP package provides table schema copy, data replication, and table exchange interfaces.

The procedures and functions that make up the UTL_COPYSWAP package are shown in the table below.

Refer to the the description of CHECK_PRECONDITION for the prerequisites for using UTL_COPYSWAP.

Procedures/Functions	Description
CHECK_PRECONDITION	Checks privileges, session properties, system properties, and replication constraints
COPY_TABLE_SCHEMA	Copies the table schema. Afterwards, execute the DDL the user wants on the copied table.
REPLICATE_TABLE	Replicates the data.
SWAP_TABLE	Swaps the table.
SWAP_TABLE_PARTITION	Swaps the table partition.
FINISH	Cleans up what was generated by COPY_TABLE_SCHEMA_REPLICATE_TABLE.

CHECK_PRECONDITION

This procedure checks prerequisites such as privileges, session properties, system properties, and replication constraints for using UTL_COPYSWAP.

The prerequisites to be examined are:

• Privilege
  Must be the SYS user.

• Session Properties
  The AUTOCOMMIT property must be FALSE.
  The REPLICATION property must be TRUE.

• System Properties
  The REPLICATION_PORT_NO property must not be zero.
  The REPLICATION_DDL_ENABLE property must be 1.
  The REPLICATION_ALLOW_DUPLICATE_HOSTS property must be 1.

• Replication Constraints
  Compressed columns are not supported.
  There should be no related Eager Sender/Receiver thread.

Syntax

UTL_COPYSWAP.CHECK_PRECONDITION(
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128) );

Parameters

Name	In/Output	Data Type	Description
source_user_name	IN	VARCHAR2(128)	Owner name of the source table
source_table_name	IN	VARCHAR2(128)	Name of the source table

Return Value

Because it is a stored procedure, there is no return value.

Exception

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> EXEC UTL_COPYSWAP.CHECK_PRECONDITION( 'SYS', 'T1' );
[SESSION PROPERTY] AUTOCOMMIT property value must be FALSE.
[SYSTEM PROPERTY] REPLICATION_PORT_NO property value must be larger than 0.
[SYSTEM PROPERTY] REPLICATION_DDL_ENABLE property value must be 1.
[SYSTEM PROPERTY] REPLICATION_ALLOW_DUPLICATE_HOSTS property value must be 1.
Execute success.

COPY_TABLE_SCHEMA

The procedure to copy Table Schema. After that, execute the DDL the user want on the copied table. The copy destination is as follows.

• Table basic infromation

• Column

• Index

• Constraint

• Trigger

• Comment

• Partition

Syntax

UTL_COPYSWAP.COPY_TABLE_SCHEMA(
  target_user_name IN VARCHAR(128),
  target_table_name IN VARCHAR(128),
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128) );

Parameters

Name	In/Output	Data Type	Description
target_user_name	IN	VARCHAR2(128)	Owner name of the target table
target_table_name	IN	VARCHAR2(128)	Name of the target table
source_user_name	IN	VARCHAR2(128)	Owner name of the source table
source_table_name	IN	VARCHAR2(128)	Name of the source table

Return Value

Because it is a stored procedure, there is no return value.

Example

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.

REPLICATE_TABLE

The procedure to replicate data using replication.

Syntax

UTL_COPYSWAP.REPLICATE_TABLE(
  replication_name IN VARCHAR(35),
  target_user_name IN VARCHAR(128),
  target_table_name IN VARCHAR(128),
  source_user_name IN VARCHAR(128),
  source_table_name IN VARCHAR(128),
  sync_parallel_factor IN INTEGER DEFAULT 8,
  receiver_applier_count IN INTEGER DEFAULT 8 );

Parameters

Name	In/Output	Data Type	Description
replication_name	IN	VARCHAR2(35)	Name of the replication
target_user_name	IN	VARCHAR2(128)	Owner name of the target table
target_table_name	IN	VARCHAR2(128)	Name of the target table
source_user_name	IN	VARCHAR2(128)	Owner name of the source table
source_table_name	IN	VARCHAR2(128)	Name of the source table
sync_parallel_factor	IN	INTEGER	Parallel factor to apply to initial synchronization
receiver_applier_count	IN	INTEGER	Parallel factor to apply to incremental synchronization

Return Value

Because it is a stored procedure, there is no return value.

Exception

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.

SWAP_TABLE

This is a procedure to complete synchronization using replication and exchange tables.

The exchange target is as follows.

• Table basic information

• Column

• Index

• Constraint

• Trigger

• Comment

• Partition

Syntax

UTL_COPYSWAP.SWAP_TABLE(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  source_user_name IN VARCHAR(128),  
  source_table_name IN VARCHAR(128),  
  force_to_rename_encrypt_column IN BOOLEAN DEFAULT FALSE,  
  ignore_foreign_key_child IN BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Output	Data Type	Description
replication_name	IN	VARCHAR2(35)	Name of the replicaiton
target_user_name	IN	VARCHAR2(128)	Owner name of the target table
target_table_name	IN	VARCHAR2(128)	Name of the target table
source_user_name	IN	VARCHAR2(128)	Owner name of the source table
source_table_name	IN	VARCHAR2(128)	Name of the source table
force_to_rename_encrypt_column	IN	BOOLEAN	Set to TRUE if there is an encryption column and the encryption module supports Rename.
ignore_foreign_key_child	IN	BOOLEAN	Set to TRUE if there is a table referencing the source table.

Result Value

Because it is a stored procedure, there is no result value.

Exception

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 2, 'XYZ' );
1 row inserted.
iSQL> COMMIT;
Commit success.
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.

SWAP_TABLE_PARTITION

The procedure to complete synchronization using replication and exchange table partitions. The exchange target is as follows.

• Partition

Syntax

PROCEDURE swap_table_partition(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  source_user_name IN VARCHAR(128),  
  source_table_name IN VARCHAR(128),  
  table_partition_name IN VARCHAR(128) );

Parameters

Name	In/Output	Data Type	Description
replication_name	IN	VARCHAR2(35)	Name of the replication
target_user_name	IN	VARCHAR2(128)	Owner name of the target table
target_table_name	IN	VARCHAR2(128)	Name of the target table
source_user_name	IN	VARCHAR2(128)	Owner name of the source table
source_table_name	IN	VARCHAR2(128)	Name of the source table
table_partition_name	IN	VARCHAR2(128)	Table partition to be exchanged

Return Value

Because it is a stored procedure, there is no return value.

Exception

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> create table t1 (i1 int, i2 int)
partition by range (i1)
(
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values default
)tablespace sys_tbs_disk_data;
Create success.

iSQL> alter table t1 add constraint pk_t1 primary key(i1) using index local
(
    partition  pk_p1 on p1 tablespace SYS_TBS_DISK_DATA,
    partition  pk_p2 on p2 tablespace SYS_TBS_DISK_DATA,
    partition  pk_p3 on p3 tablespace SYS_TBS_DISK_DATA
);
Alter success.
iSQL> INSERT INTO T1 VALUES ( 15, 15 );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 16, 16 );
1 row inserted.

iSQL> commit ;
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE_PARTITION( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1','P2' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.

FINISH

Cleans up what was generated by COPY_TABLE_SCHEMA_REPLICATE_TABLE.

Syntax

UTL_COPYSWAP.FINISH(  
  replication_name IN VARCHAR(35),  
  target_user_name IN VARCHAR(128),  
  target_table_name IN VARCHAR(128),  
  print_all_errors IN BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Output	Data Type	Description
replication_name	IN	VARCHAR2(35)	Name of the replication
target_user_name	IN	VARCHAR2(128)	Owner name of the target table
target_table_name	IN	VARCHAR2(128)	Name of the target table
print_all_errors	IN	BOOLEAN	Set to TRUE to display replication-related errors.

Result Value

Because it is a stored procedure, there is no return value.

Exception

If a parameter is entered incorrecly, an exception will be occurred.

Example

iSQL> CREATE TABLE T1 ( I1 INTEGER PRIMARY KEY, V1 VARCHAR(1024) );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1, 'ABC' );
1 row inserted.
iSQL> ALTER SESSION SET AUTOCOMMIT = FALSE;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Alter success.
iSQL> ALTER SYSTEM SET REPLICATION_ALLOW_DUPLICATE_HOSTS = 1;
Alter success.
iSQL> EXEC UTL_COPYSWAP.COPY_TABLE_SCHEMA( 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
0    
1 row selected.
iSQL> ALTER TABLE T1_COPY ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> EXEC UTL_COPYSWAP.REPLICATE_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
1    
1 row selected.
iSQL> INSERT INTO T1 VALUES ( 2, 'XYZ' );
1 row inserted.
iSQL> COMMIT;
Commit success.
iSQL> EXEC UTL_COPYSWAP.SWAP_TABLE( 'REP_LOCAL', 'SYS', 'T1_COPY', 'SYS', 'T1' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
COUNT
-----------------------
2    
1 row selected.
iSQL> EXEC UTL_COPYSWAP.FINISH( 'REP_LOCAL', 'SYS', 'T1_COPY' );
Execute success.
iSQL> SELECT COUNT(*) FROM T1_COPY;
[ERR-31031 : Table or view was not found :
0001 : SELECT COUNT(*) FROM T1_COPY
^      ^

Notes

• To replicate data using the REPLICATE_TABLE procedure, free space is required in the tablespace in proportion to the size of the source table. Log files created by the REPLICATE_TABLE procedure are not removed by Checkpoint until the REPLICATE_TABLE procedure is terminated.

• While using the UTL_COPYSWAP package, replication must be able to resolve the DML that applies to the source table. DML that cannot be analyzed in replication may be lost.

  • When executing DML on the source table, the REPLICATION session property must be TRUE.
  • If the source table is replication target table, replication must be stopped at the remote server that corresponds to the source table so that replication does not reflect the data in the source table.

• When dropping a target table using the FINISH procedure, if the RECYCLEBIN_ENABLE property value is 1, then it is moved to the recycle bin.

UTL_FILE

The UTL_FILE package enables writing and reading by accessing the text tiles which are managed by the operation system.

The procedures and functions which are comprised of the UTL_FILE package are listed in the following table below.

Procedures/Functions	Description
FCLOSE	Closes a file
FCLOSE_ALL	Closes all open files in the current session
FCOPY	Copies a file
FFLUSH	Physically archives the data into a file
FOPEN	Opens a file with the object of writing or reading
FREMOVE	Deletes a file
FRENAME	Changes a file name
GET_LINE	Searches for a single line in a file
IS_OPEN	Checks if the file is opened
NEW_LINE	Prints the new-line characters
PUT	Records a character string into a file
PUT_LINE	Records a character string by attaching the new-line characters(= PUT+NEW_LINE)

Refer to File Control in Altibase Stored Procedures manual for in-depth information on each procedure and function pertaining to the UTL_FILE prcedures and packages.

FCLOSE

The FCLOSE is a procedure providing a function of closing and re-initializing the file handle which is opened.

Syntax

UTL_FILE.FCLOSE(file IN OUT FILE_TYPE);

Parameter

Name	In/Output	Data Type	Description
file	IN OUT	FILE_TYPE	File handle

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

FCLOSE_ALL

The FCLOSE_ALL is a procedure providing a function of closing all the file handles that are open in the current session.

Syntax

UTL_FILE.FCLOSE_ALL;

Parameter

None

Return Value

Because it is a stored procedure, there is no return value.

Exception

It always succeeds unless an error occurs during the execution.

FCOPY

The FCOPY is a procedure providing a function of copying a file by line unit. If the result file does not exist in the related directory, the contents of source file is copied when creating a file. If the result file exits, the contents of source file are overwritten.

Syntax

UTL_FILE.FCOPY (
  location IN VARCHAR(40),
  filename IN VARCHAR(256),
  dest_dir IN VARCHAR(40),
  dest_file IN VARCHAR(256),
  start_line IN INTEGER DEFAULT 1,
  end_line IN INTEGER DEFAULT NULL);

Parameters

Name	In/Output	Data Type	Description
location	IN	VARCHAR(40)	The directory name in which the original file, the target of copy, is located.
filename	IN	VARCHAR(256)	The name of the source file
dest_dir	IN	VARCHAR(40)	The directory name in which result files are located
dest_file	IN	VARCHAR(256)	The name of the result file
start_line	IN	INTEGER	The startling line number to copy (Default value: 1)
end_line	IN	INTEGER	The last line number to copy. If it is default value, the file is copied to the end of the file. (Default value: NULL)

Return Value

Because it is a stored procedure, there is no return value.

Exception

The FCOPY might cause the following system-defined exceptions.

• INVALID_PATH

• ACCESS_DENIED

• INVALID_OPERATION

• READ_ERROR

• WRITE_ERROR

FFLUSH

The FFLUSH is a procedure which physically archives the data existing in the buffer into a file.

Syntax

UTL_FILE.FFLUSH(file IN FILE_TYPE);

Parameter

Name	In/Output	Data Type	Description
file	IN	FILE_TYPE	The file handle

Return Value

Because it is a stored procedure, there is no return value.

Exception

The FFLUSH might cause the following system-defined exceptions.

• INVALID_FILEHANDLE

• WRITE_ERROR

FOPEN

The FOPEN procedure opens a file to read or write.

Syntax

UTL_FILE.FOPEN(
  location IN VARCHAR(40),
  filename IN VARCHAR(256),
  open_mode IN VARCHAR(4),
  max_linesize IN INTEGER DEFAULT NULL);

Parameters

Name	In/Output	Data Type	Description
location	IN	VARCHAR(40)	The name of a directory object located in a file
filename	IN	VARCHAR(256)	The file name
open_mode	IN	VARCHAR(4)	The input available options are as follows. r: Read w: Write a: Subsequent writing * Caution: Such options cannot be combined to use. (e.g., rw, wa)
max_linesize	IN	INTEGER	This is the parameter only for Integer compatibility which can be neglected.

Return Value

The file handle with FILE_TYPE data type are returned if successfully executed.

Exception

The FOPEN might cause the following system-defined exceptions.

• INVALID_PATH

• ACCESS_DENIED

• INVALID_OPERATION

• INVALID_MODE

FREMOVE

The FREMOVE is a procedure deleting a file.

Syntax

UTL_FILE.FREMOVE (
  location IN VARCHAR(40),
  filename IN VARCHAR(256));

Parameters

Name	In/Output	Data Type	Description
location	IN	VARCHAR(40)	The directory name in which a file is located
filename	IN	VARCHAR(256)	The file name

Return Value

Because it is a stored procedure, there is no return value.

Exception

The FREMOVE might cause the following system-defined exceptions.

• INVALID_PATH

• ACCESS_DENIED

• DELETE_FAILED

FRENAME

The FRENAME is a stored procedure which can modifies the file name, or transfer the file to a different location. It is the same with UNIX mv command.

Syntax

UTL_FILE.FRENAME (
  location IN VARCHAR(40),
  filename IN VARCHAR(256),
  dest_dir IN VARCHAR(40),
  dest_file IN VARCHAR(256),
  overwrite IN BOOLEAN DEFAULT FALSE );

Parameters

Name	In/Out	Data Type	Description
location	IN	VARCHAR(40)	The directory in which the source file is situated.
filename	IN	VARCHAR(256)	The name of source file.
dest_dir	IN	VARCHAR(40)	The directory in which the result file is situated.
dest_file	IN	VARCHAR(256)	The name of result file.
overwrite	IN	BOOLEAN	Update option when the result file already exists. TRUE: Update as a new file FALSE(Default Value): Not to update.

Return Value

Because it is stored procedure, there is no return value.

Exception

The FRENAME might cause the following system-defined exceptions.

• INVALID_PATH

• ACCESS_DENIED

• RENAME_FAILED

GET_LINE

The GET_LINE is a stored procedure reading every other line from a file.

Syntax

UTL_FILE.GET_LINE(
  file IN FILE_TYPE,  
  buffer OUT VARCHAR(32768),  
  len IN INTEGER DEFAULT NULL);

Parameters

Name	In/Output	Data Type	Description
file	IN	FILE_TYPE	The file handle
buffer	OUT	VARCHAR(32768)	The buffer to store the every other line from a file.
len	IN	INTEGER	The maximum bytes which can read a line form a file. 1024bytes are read unless otherwise specified. Default Value: NULL

Return Value

Because it is a stored procedure, there is no return value.

Exception

The GET_LINE might cause the following system-defined exceptions.

• NO_DATA_FOUND

• READ_ERROR

• INVALID_FILEHANDLE

IS_OPEN

The IS_OPEN function checks on the file whether it is open or not.

Syntax

UTL_FILE.IS_OPEN(file IN FILE_TYPE);

Parameter

Name	In/Out	Data Type	Description
file	IN	FILE_TYPE	The file handle

Return Value

It returns TRUE when it is open, but FALSE is returned when it is closed.

Exception

There is no exception.

NEW_LINE

The NEW_LINE is a procedure which archives the new-line characters to a file(\n for Unix).

Syntax

UTL_FILE.NEW_LINE(
  file IN FILE_TYPE,  
  lines IN INTEGER DEFAULT 1);

Parameters

Name	In/Output	Data Type	Description
file	IN	FILE_TYPE	The file handle
lines	IN	INTEGER	The number of line to record. Default Value: 1

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

PUT

The PUT is a procedure storing a character string which is read from a file in the buffer.

Syntax

UTL_FILE.PUT(
  file IN FILE_TYPE,
  buffer IN VARCHAR(32768));

Parameters

Name	In/Output	Data Type	Description
file	IN	FILE_TYPE	The file handle
buffer	IN	VARCHAR(32768)	The buffer to store a file from the read character strings

Return Value

Because it is a stored procedure, there is no return value.

Exceptions

The PUT might cause the following system-defined exceptions.

• INVALID_FILEHANDLE

• WRITE_ERROR

PUT_LINE

The PUT_LINE is a stored procedure archiving a line including a character string to a file.

Syntax

UTL_FILE.PUT_LINE(
  file IN FILE_TYPE,
  buffer IN VARCHAR(32768)
  autoflush IN BOOLEAN DEFAULT FALSE);

Parameters

Name	In/Output	Data Type	Description
file	IN	FILE_TYPE	The file handle
buffer	IN	VARCHAR(32768)	The buffer storing a character string which is read from a file
autoflush	IN	BOOLEAN	Options to empty the buffer. Default Value: FALSE (Not to empty)

Return Value

Because it is a stored procedure, there is no return value.

Exceptions

The PUT_LINE might cause the following system-defined exceptions.

• INVALID_FILEHANDLE

• WRITE_ERROR

UTL_RAW

The UTL_RAW package is a function which can convert or control RAW(VARBYTE) type data into a different data type.

The procedures and functions which are comprised of the UTL_RAW package are listed in the following table below.

Procedures/Functions	Description
CAST_FROM_BINARY_INTEGER	Converts INTERGER type of data into RAW data type
CAST_FROM_NUMBER	Converts NUMERIC type of data into RAW data type
CAST_TO_BINARY_INTEGER	Converts RAW type of data into BINARY_INTEGER data type
CAST_TO_NUMBER	Converts RAW type of data into NUMERIC data type
CAST_TO_RAW	Converts VARCHAR type of data into RAW type
CAST_TO_VARCHAR2	Converts Raw type of data into VARCHAR data type
CONCAT	Connects RAW type of data
LENGTH	Returns the length of data which has been input
SUBSTR	Returns some of character string data which have been input.

CAST_FROM_BINARY_INTEGER

The CAST_FROM_BINARY_INTEGER is a function converting INTEGER data type into RAW data type.

Syntax

UTL_RAW.CAST_FROM_BINARY_INTEGER(
  n IN INTEGER,
  endianess IN INTEGER DEFAULT 1);

Parameters

Name	In/Output	Data Type	Description
n	IN	INTEGER	The data which will be converted
endianess	IN	INTEGER	This parameter is only for compatibility, and the value of this parameter can be neglected.

Return Value

The entered INTEGER type of data is returned as RAW type.

Exception

There is no exception.

Example

Output 123456, which is INTEGER type by converting into RAW type.

iSQL> select utl_raw.cast_from_binary_integer(123456) from dual;
CAST_FROM_BINARY_INTEGER(123456)  
------------------------------------
40E20100          
1 row selected.

CAST_FROM_NUMBER

The CAST_FROM_NUMBER is a function which returns NEMERIC type of data by converting into RAW type.

Syntax

UTL_RAW.CAST_FROM_NUMBER(n IN NUMBER);

Parameter

Name	In/Output	Data Type	Description
n	IN	NUMBER	The data which will be converted into RAW type

Return Value

The entered NUMERIC type of data is returned by converting into RAW type

Exception

There is no exception.

Example

Output NUMBER type data 1.123456789 by converting into RAW type.

iSQL> select utl_raw.cast_from_number(1.123456789) from dual;
CAST_FROM_NUMBER(1.123456789)
------------------------------------------------------------------------------------
07C1010C22384E5A
1 row selected.

CAST_TO_BINARY_INTEGER

The CAST_TO_BINARY_INTEGER is a function which returns RAW type of data by converting into INTEGER type.

Syntax

UTL_RAW.CAST_TO_BINARY_INTEGER(
  r IN RAW(8),
  endianess IN INTEGER DEFAULT 1);

Parameters

Name	In/Output	Data Type	Description
r	IN	RAW(8)	The data which will be converted as INTEGER type.
endianess	IN	INTEGER	This parameter is only for compatibility, and the value is neglected.

Return Value

The entered RAW type of data is returned as INTEGER type.

Exception

There is no exception.

Example

Output 40E20100, which is RAW type by converting into INTEGER.

iSQL> select utl_raw.cast_to_binary_integer('40E20100') from dual;
CAST_TO_BINARY_INTEGER('40E20100')
-------------------------------------
123456      
1 row selected.

CAST_TO_NUMBER

The CAST_TO_NUMBER is a function which returns RAW type of data by converting it into NUMERIC type.

Syntax

UTL_RAW.CAST_TO_NUMBER(r IN RAW(32767));

Parameter

Name	IN/Output	Data Type	Description
r	IN	RAW(32767)	The data which will be converted into NUMERIC type

Return Value

The entered RAW type of data is returned as NUMERIC type.

Exception

There is no exception.

Example

Output RAW type 07C1010C22384E5A by converting NUMBER.

iSQL> select utl_raw.cast_to_number('07C1010C22384E5A') from dual;
CAST_TO_NUMBER('07C1010C22384E5A')
-------------------------------------
1.12345679  
1 row selected.

CAST_TO_RAW

The CAST_TO_RAW is a function returning VARCHAR type of data by converting into RAW(VARBYTE) type.

Syntax

UTL_RAW.CAST_TO_RAW(c IN VARCHAR(32767));

Parameter

Name	In/Output	Data Type	Description
c	IN	VARCHAR(32767)	The data which will be converted into RAW type.

Return Value

The entered data is returned as RAW type.

Exception

There is no exception.

Emample

Output 'altibase' with RAW.

iSQL> select cast(utl_raw.cast_to_raw('altibase') as raw(10)) from dual;
CAST(UTL_RAW.CAST_TO_RAW('altibase') as RA  
----------------------------------------------
0800616C746962617365  
1 row selected.

CAST_TO_VARCHAR2

The CAST_TO_VARCHAR2 is a function which returns RAW type of data by converting it into VARCHAR type.

Syntax

UTL_RAW.CAST_TO_VARCHAR2(c IN RAW(32767));

Parameter

Name	In/Out	Data Type	Description
c	IN	RAW(32767)	The data which will be converted into VARCHAR type

Return Value

The entered data is returned by converting it into VARCHAR type.

Exception

There is no exception.

Example

Output 0800616C746962617365, the RAW type of data, by converting it into VARCHAR type.

iSQL> select cast(utl_raw.cast_to_varchar2('0800616C746962617365') as varchar(8)) from dual;
CAST(UTL_RAW.CAST_TO_VARCHAR2('0800616C746  
----------------------------------------------
altibase  
1 row selected.

CONCAT

The CONCAT is a function returning the RAW(VARBYTE) data which has been input in parameters by concatenating.

Syntax

UTL_RAW.CONCAT(
  r1 IN RAW(32767) DEFAULT NULL,
  r2 IN RAW(32767) DEFAULT NULL,
  r3 IN RAW(32767) DEFAULT NULL,
  r4 IN RAW(32767) DEFAULT NULL,
  r5 IN RAW(32767) DEFAULT NULL,
  r6 IN RAW(32767) DEFAULT NULL,
  r7 IN RAW(32767) DEFAULT NULL,
  r8 IN RAW(32767) DEFAULT NULL,
  r9 IN RAW(32767) DEFAULT NULL,
  r10 IN RAW(32767) DEFAULT NULL,
  r11 IN RAW(32767) DEFAULT NULL,
  r12 IN RAW(32767) DEFAULT NULL);

Parameter

Name	In/Output	Data Type	Description
r1...r12	IN	RAW(32767)	RAW type data The data can input from r1 to r12.

Return Value

The data connected from r1 to r12 is returned.

Exception

There is no exception.

Example

Output the RAW type by concatenating AA and BB.

iSQL> select cast(utl_raw.concat(raw'aa', raw'bb') as raw(4)) from dual;
CAST(UTL_RAW.CONCAT(RAW'aa', RAW'bb') as R  
----------------------------------------------
AABB      
1 row selected.

LENGTH

The LENGTH is a function returning the length of input RAW type data.

Syntax

UTL_RAW.LENGTH(r IN RAW(32767));

Parameter

Name	In/Output	Data Type	Description
c	IN	RAW(32767)	The RAW type data which will return the length

Return Value

The length of RAW data, which has been input, is returned.

Exception

There is no exception.

Example

Output characteristic 'altibase' with the length of RAW data type.

iSQL> select utl_raw.length(utl_raw.cast_to_raw('altibase')) from dual;
LENGTH(UTL_RAW.CAST_TO_RAW('altibase'))
------------------------------------------
12          
1 row selected.

SUBSTR

The SUBSTR is a function which returns some parts of a character string in RAW type data which has been input.

Syntax

UTL_RAW.SUBSTR(
  r IN RAW(32767),
  pos IN INTEGER,
  len IN INTEGER);

Parameters

Name	In/Output	Data Type	Description
r	IN	RAW(32767)	The input data
pos	IN	INTEGER	The position in which begins returning data. If the value is a positive number, it begins from the front of the input data whereas it begins at the end of data if the value is a negative number.
len	IN	INTEGER	The length of the data which will be returned. If omitted, all the character string is returned to the end of data.

Return Value

The specified length from the beginning point of input data of RAW data is returned

Exception

There is no exception.

Example

Return the length which is the second from the first of 0102030405 RAW type data.

iSQL> select cast(utl_raw.substr('0102030405',1,2) as raw(2)) from dual;
CAST(UTL_RAW.SUBSTR('0102030405',1,2) as R  
----------------------------------------------
0102  

UTL_SMTP

UTL_SMTP can execute SMTP for SMTP server to send an E-mail. Procedures and functions comprising UTL_SMTP is as follows.

Procedures/Functions	Description
OPEN_CONNECTION	Creates TCP socket and connects to SMTP server.
HELO	Sends default command of SMTP, HELO domain.
MAIL	Sends a command of SMTP specifying the sender, MAIL FROM:\
RCPT	Sends a command of SMTP specifying the receiver, MAIL FROM:\
OPEN_DATA	Sends a command of SMTP starting the data transmission, DATA.
WRITE_DATA	Sends data using SMTP.
WRITE_RAW_DATA	Sends RAW data using SMTP.
CLOSE_DATA	Sends a command of SMTP ending the data transmission, \ . \.
QUIT	Sends a command of SMTP ending the connection, QUIT.

OPEN_CONNECTION

Creates TCP socket and connects to SMTP server using the inputted IP and PORT.

Syntax

UTL_SMTP.OPEN_CONNECTION (
  host IN VARCHAR(64),
  port IN INTEGER DEFAULT 25,
  tx_timeout IN INTEGER DEFAULT NULL );

Parameters

Name	In/Output	Data type	Description
host	IN	VARCHAR(64)	IP address of SMTP server
port	IN	INTEGER	Port number of SMTP server
tx_timeout	IN	INTEGER	This parameter is for compatibility, the value of this parameter is ignored.

Return Value

CONNECT_TYPE returns connection handle when successed.

Exception

CONNECT_TYPE returns NULL when failed.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

HELO

Sends HELO domain command to the connected SMTP server to execute reset handshaking.

Syntax

UTL_SMTP.HELO (
  c IN OUT CONNECT_TYPE,
  domain IN VARCHAR(64) );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
domain	IN	VARCHAR(64)	The domain name

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_CONNECTION function has to be called first in order to call the HELO function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

MAIL

Sends MAIL FORM:\ command to the connected SMTP server to specify the sender.

Syntax

UTL_SMTP.MAIL (
  c IN OUT CONNECT_TYPE,
  sender IN VARCHAR(256),
  parameters IN VARCHAR DEFAULT NULL );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
sender	IN	VARCHAR(256)	The sender address
parameters	IN	VARCHAR	This parameter is for compatibility, the value of this parameter is ignored.

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. HELO function has to be called first in order to call the MAIL function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, 'test@test.com', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

RCPT

Sends RCPT TO:\ command to the connected SMTP server to specify the receiver.

Syntax

UTL_SMTP.RCPT (
  c IN OUT CONNECT_TYPE,
  recipient IN VARCHAR(256),
  parameters IN VARCHAR DEFAULT NULL );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
recipient	IN	VARCHAR(256)	The receiver address
parameters	IN	VARCHAR	This parameter is for compatibility, the value of this parameter is ignored.

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. MAIL function has to be called first in order to call the RCPT function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

OPEN_DATA

Sends DATA command to the connected SMTP server to start the data transmission.

Syntax

UTL_SMTP.DATA (
  c IN OUT CONNECT_TYPE,
  body IN VARCHAR DEFAULT NULL );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
body	IN	VARCHAR	This parameter is for compatibility, the value of this parameter is ignored.

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. RCPT function has to be called first in order to call the DATA function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

WRITE_DATA

Transmits the data to the connected SMTP server.

Syntax

UTL_SMTP.WRITE_DATA (
  c IN OUT CONNECT_TYPE,
  data IN VARCHAR(65534) );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
data	IN	VARCHAR(65534)	Data to transmit

Return Value

Returns the length of the transmitted data when succeeded. Returns -1 when failed.

Exception

Exception occurred when it violates the SMTP. OPEN_DATA function has to be called first in order to call the WRITE_DATA function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_DATA( V1, 'test' );
  V2 := CLOSE_CONNECT( V1 );
END;
/

WRITE_RAW_DATA

Transmits RAW data to the connected SMTP server.

Syntax

UTL_SMTP.WRITE_DATA (
  c IN OUT CONNECT_TYPE,
  data IN RAW(65534) );

Parameters

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server
data	IN	RAW(65534)	RAW data to transmit

Return Value

Returns the length of the transmitted data when succeeded. Returns -1 when failed.

Exception

Exception occurred when it violates the SMTP. OPEN_RAW_DATA function has to be called first in order to call the WRITE_RAW_DATA function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V2 := CLOSE_CONNECT( V1 );
END;
/

CLOSE_DATA

Sends \.\ command to the connected SMTP server and ends the data transmission.

Syntax

UTL_SMTP.CLOSE_DATA (
  c IN OUT CONNECT_TYPE );

Parameter

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_DATA function has to be called first in order to call the CLOSE_DATA function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V3 := SMTP.CLOSE_DATA( V1 );
  V2 := CLOSE_CONNECT( V1 );
END;
/

QUIT

Sends QUIT command to the connected SMTP server to end the connection between SMTP session and SMTP server.

Syntax

UTL_SMTP.QUIT (
  c IN OUT CONNECT_TYPE );

Parameter

Name	In/Output	Data Type	Description
c	IN OUT	CONNECT_TYPE	Connection handle of SMTP server

Return Value

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_CONNECTION function has to be called first in order to call the QUIT function.

Example

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['test@test.com](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V3 := SMTP.CLOSE_DATA( V1 );
  V3 := SMTP.QUIT( V1 );
END;
/

UTL_TCP

The UTL_TCP package controls TCP access in a stored procedure.

The procedures and functions which are comprised of the UTL_TCP package are listed in the following table below.

Procedures/Functions	Description
CLOSE_ALL_CONNECTIONS	Procedure closes all the handles connected to the session.
CLOSE_CONNECTION	Closes the access handle which is connected.
IS_CONNECT	Checks on the connection status of access handle.
OPEN_CONNECTION	Accesses to remote server by creating a socket.
WRITE_RAW	Transmits RAW type data to the remote server

CLOSE_ALL_CONNECTIONS

The CLOSE_ALL_CONNECTIONS is a procedure which closes all the connection handles currently being accessed.

Syntax

UTL_TCP.CLOSE_ALL_CONNECTIONS;

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
UTL_TCP.CLOSE_ALL_CONNECTIONS();
END;
/

CLOSE_CONNECTION

The CLOSE_CONNECTION is a procedure closing the accessed connection handle.

Syntax

UTL_TCP.CLOSE_CONNECTION(c IN CONNECT_TYPE);

Parameter

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	Connection handle

Return Value

Because it is a stored procedure, there is no return value.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
 AS
 V1 CONNECT_TYPE;
 V2 INTEGER;
 BEGIN
 V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
 V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
 UTL_TCP.CLOSE_CONNECTION(V1);
 END;
 /

IS_CONNECT

The IS_CONNECT procedure verifies the connection status of connection handle.

Syntax

UTL_TCP.IS_CONNECT(c IN CONNECT_TYPE);

Parameter

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	The connection handle

Return Value

1 is returned If it is successfully executed and when it fails, it returns -1.

Exception

There is no exception.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
    AS
    V1 CONNECT_TYPE;
    V2 INTEGER;
    BEGIN
    V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1000);
    V2 := UTL_TCP.IS_CONNECT(V1);
    IF V2 = 0 THEN
        PRINTLN('CONNECTED');
        V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
        UTL_TCP.CLOSE_CONNECTION(V1);
    ELSE
        PRINTLN('NOT CONNECTD');
    END IF;
    END;
    /

OPEN_CONNECTION

The OPEN_CONNECTION is a procedure which creates a socket in order to access the remote server.1

Syntax

UTL_TCP.OPEN_CONNECTION(
  remote_host IN VARCHAR(64),
  remote_port IN INTEGER,
  local_host IN VARCHAR(64) DEFAULT NULL,
  local_port IN INTEGER DEFAULT NULL,
  in_buffer_size IN INTEGER DEF DEFAULT NULL,
  out_buffer_size IN INTEGER DEF DEFAULT NULL,
  charset IN VARCHAR(16) DEFAULT NULL,
  newline IN VARCHAR(2) DEFAULT CRLF,
  tx_timeout IN INTEGER DEF DEFAULT NULL,
  wallet_path IN VARCHAR(256) DEFAULT NULL,
  wallet_password IN VARCHAR DEFAULT NULL));

Parameters

Name	In/Output	Data Type	Description
remote_host	IN	VARCHAR(64)	The IP address of remote server
remote_port	IN	INTEGER	The port number of remote server
local_host	IN	VARCHAR(64)	This is parameter only for compatibility and it is neglected.
local_port	IN	INTEGER	This is parameter only for compatibility and it is neglected.
in_buffer_size	IN	INTEGER	This is parameter only for compatibility and it is neglected.
out_buffer_size	IN	INTEGER	This parameter sets the size of internal transmission buffer. The minimum value is 2048 bytes whereas 32767 is the maximum value. Null is set to be the minimum value.
charset	IN	VARCHAR(16)	This is parameter only for compatibility and it is neglected.
newline	IN	VARCHAR(2)	This is parameter only for compatibility and it is neglected.
tx_timeout	IN	INTEGER	This is parameter only for compatibility and it is neglected.
wallet_path	IN	VARCHAR(256)	This is parameter only for compatibility and it is neglected.
wallet_password	IN	VARCHAR	This is parameter only for compatibility and it is neglected.

Return Value

If it is successfully executed, the CONNECT_TYPE connection handle is returned.

Exception

If exception occurs, such as network connection failure, NULL value is returned with CONNECT_TYPE. The connection status of access handle can be confirmed through the UTL_TCP.IS_CONNECT()function.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
 AS
 V1 CONNECT_TYPE;
 V2 INTEGER;
 BEGIN
 V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
 V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
 UTL_TCP.CLOSE_CONNECTION(V1);
 END;
 /

WRITE_RAW

The handle accessed to the network transmits the inserted RAW type data to the remote server through the WRITE_RAW function.

Syntax

UTL_TCP.WRITE_RAW(
  c IN CONNECT_TYPE,
  data IN RAW(65534),
  len IN INTEGER DEFAULT NULL);

Parameters

Name	In/Output	Data Type	Description
c	IN	CONNECT_TYPE	The connection handle
data	IN	RAW(65534)	The transmitting data
len	IN	INTEGER	This parameter is only for compatibility, and the value of this parameter can be neglected.

Return Value

If successfully executes, the length of data which has been transmitted to the network is returned. If it fails, -1 is returned.

Exception

The status of accessed connection handle can be checked by using the UTL_TCP.IS_CONNECT() function if the connection of the connection handle is lost.

Example

iSQL> CREATE OR REPLACE PROCEDURE PROC1
    AS
    V1 CONNECT_TYPE;
    V2 INTEGER;
    BEGIN
    V1 := UTL_TCP.OPEN_CONNECTION('127.0.0.1', 22007, NULL, NULL, 1024);
    V2 := UTL_TCP.WRITE_RAW(V1, TO_RAW('MESSAGE'), RAW_SIZEOF('MESSAGE'));
    UTL_TCP.CLOSE_CONNECTION(V1);
    END;
    /