This section journeys into the “Oracle Style Package” for LightDB. A package, by very definition, is an object or a group of objects packed together. In terms of databases, this translates into a named schema object that packages within itself a logically grouped collection of procedures, functions, variables, cursors, user-defined record types, and reference records.
Like similar constructs in various other programming languages, there are good reasons for using packages with SQL. In this section, we are going to cover a few.
Reliability and Reusability of Code Packages provide the ability to create modular objects that encapsulate code. This makes the overall design and implementation simpler. The ability to encapsulate variables, related types, stored procedures/functions, and cursors, essentially allows creating a self-contained module that is simple and easy to understand, maintain and use. Encapsulation comes into play through the exposure of a package interface, rather than its implementation details, i.e., package body. This, therefore, benefits in many ways. Furthermore it allows applications and users to refer to a consistent interface and not worry about the contents of its body. Also, it prevents users from making any decisions based on code implementation as that’s never exposed to them.
Ease of Use The ability to create a consistent functional interface in LightDB helps simplify application development by allowing the compilation of packages without their bodies. Beyond the development phase, the package allows a user to manage access control on the entire package rather than individual objects. This is rather valuable especially if the package contains lots of schema objects.
Performance Packages are loaded into memory for maintenance and therefore utilize minimal I/O resources. Recompilation is simple and only limited to object(s) changed; dependent objects are not recompiled.
Additional Features In addition to performance and ease of use, packages offer session-wide persistence for variables and cursors. This means variables and cursors have the same lifetime as a database session and are destroyed when the session is destroyed.
Package consists of two components. Package specification and Package body.
Package Specification Any object within the package that is to be used from the outside is specified in the package specification section. This is the publicly accessible interface we have been referring to in earlier sections. It does not contain the definition or implementation of them, i.e. the functions and the procedures. It only has their headers defined without the body definitions. The variables can be initialized. The following is the list of objects that can be listed in the package specification:
Functions
Procedures
Cursors
Types
Variables
Record types
Associative Arrays
Nested Tables
Package Body the body contains all the implementation code of a package, including the public interfaces and the private objects. A package body is optional if the specification does not contain any subprogram or cursor.
It must contain the definition of the subprograms declared in specification and the corresponding definitions must match.
A package body can contain its own subprogram and type declarations of any internal objects not specified in the specifications. These objects are then considered private. Private objects cannot be accessed from outside the package.
If the guc parameter 'lightdb_oracle_enble_drop_package_body_equal_drop_package'
is not set, the guc parameter defaults to true
and the package specification will be deleted when the package body is deleted.
In addition to subprogram definitions, it can optionally contain a initializer block that initializes the variables declared in specification and is executed only once when the first call to the package is made in a session.
CREATE PACKAGE —— Define a new package specification.
Syntax
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] PACKAGE [schema.]package_name [IS | AS]
item_list[, item_list ...]
END [package_name];
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
function_declaration:
FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype;
procedure_declaration:
PROCEDURE procedure_name [(parameter_declaration[, ...])]
type_definition:
record_type_definition |
ref_cursor_type_definition
cursor_declaration:
CURSOR name [(cur_param_decl[, ...])] RETURN rowtype;
item_declaration:
cursor_declaration |
cursor_variable_declaration |
record_variable_declaration |
variable_declaration |
record_type_definition:
TYPE record_type IS RECORD ( variable_declaration [, variable_declaration]... ) ;
ref_cursor_type_definition:
TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];
cursor_variable_declaration:
curvar curtype;
record_variable_declaration:
recvar { record_type | rowtype_attribute | record_type%TYPE };
variable_declaration:
varname datatype [ [ NOT NULL ] := expr ]
parameter_declaration:
parameter_name [IN] datatype [[:= | DEFAULT] expr]
Description
Creates the package specification that contains public declarations. The declared items in the package specification are accessible from anywhere in the package and to any other subprograms in the same database.
CREATE PACKAGE defines a new package. CREATE OR REPLACE PACKAGE will either create a new package or replace an existing definition.
If a schema name is included, then the package is created in the specified schema. Otherwise, it is created in the current schema. The name of the new package must be unique within the schema.
When CREATE OR REPLACE PACKAGE is used to replace an existing package, the ownership and permissions of the package do not change. All other package properties are assigned the values specified or implied in the command. Only the owner and member of the owning roles are allowed to replace the packages.
The user that creates the package becomes the owner of the package.
CREATE PACKAGE BODY —— Define a new package definition.
Syntax
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] PACKAGE BODY [schema.]package_name [IS | AS]
[item_list[, item_list ...]] |
item_list_2 [, item_list_2 ...]
[initialize_section]
END [package_name];
initialize_section:
BEGIN statement[, ...]
item_list:
[
function_declaration |
procedure_declaration |
type_definition |
cursor_declaration |
item_declaration
]
item_list_2:
[
function_declaration
function_definition
procedure_declaration
procedure_definition
cursor_definition
]
function_definition:
FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype [IS | AS]
[declare_section] body;
procedure_definition:
PROCEDURE procedure_name [(parameter_declaration[, ...])] [IS | AS]
[declare_section] body;
cursor_definition:
CURSOR name [(cur_param_decl[, ...])] RETURN rowtype IS select_statement;
body:
BEGIN statement[, ...] END [name];
statement:
[LABEL] pl_statments[, ...];
Description
CREATE PACKAGE BODY defines the package body for a package. CREATE OR REPLACE PACKAGE body will either create a new package body for the package or replace an existing package body definition. Package specification must be created first to create the package body. The package body contains the implementation of every cursor and subprogram declared in the package specification created through “CREATE PACKAGE”. objects defined in a package body are only accessible to outside the package if their specification is listed in the package specification. For all objects that are only defined in the package body and are not included in the package specification, they become private members to the package and are not accessible outside of the package. Both the package and its body must be created in the same schema.
EDITIONABLE,NONEDITIONABLE Use these two attributes to specify whether the package is
an editioned or noneditioned object if editioning is enabled for the schema object type FUNCTION in schema.
Currently, only syntax support is provided, with the functionality not yet implemented.
This syntax is only supported in Oracle compatibility mode.
package_name The name (optionally schema-qualified) of the package to create.
item_list This is the list of items that can be part of a package.
procedure_declaration The procedure signature, i.e. procedure_name(argument_list ).
procedure_declaration can appear in both package specification and package body.
Procedure declarations listed in the Package specification makes the procedure
public and accessible from outside of the package. While the procedure declared
in the package body is considered as a forward declaration and becomes
a private member to the package.
procedure_definition Implementation/definition of the package procedure.
procedure_definition can only be provided in the package body.
Procedure access specifier is determined by procedure declaration
and the procedures defined in the package body without corresponding
declaration automatically becomes private to the package.
function_declaration The function signature and it’s return type,
i.e. function_name(argument_list) RETURN return_type;.
function_declaration can appear in both package specification and package body. Function declarations listed in the Package specification makes the function public and accessible from outside the package. While the function declaration in the package body is considered as a forward declaration and becomes a private member to the package.
function_definition Implementation/definition of the package function.
function_definition can only be provided in the package body.
Function access specifier is determined by function declaration
and the function defined in the package body without corresponding
declaration automatically becomes private to the package.
type_definition Either a RECORD, or CURSOR type definition.
cursor_declaration CURSOR declaration along with its arguments and return type as the desired ROWTYPE.
item_declaration Allows declaration of: Cursors, Cursor variables, Record variable, Variables.
parameter_declaration Defines the syntax for declaring a parameter.
The keyword “IN” if specified indicates that this is an input parameter.
The DEFAULT keyword followed by an expression (or value) may only be specific
for an input parameter.
declare_section This contains all the elements that are local to the function
or procedure and can be referenced within its body.
body The body consists of the SQL statements or PL control structures
that are supported by pl/sql language.
In the previous sections, we have gone through the syntax that dictates the structure of a package. In this section, we are going to take this a step further by understanding the construction process of a package and how we can access its public elements.
As a package is created, LightDB will compile it and report any issues it may find. Once the package is successfully compiled, it becomes ready for use.
A package is instantiated and initialized when it is referenced for the first time in a session. The following actions are performed in the same order during this process:
Assignment of initial values to public variables
Execution of the initializer block of the package
There are several ways to access package elements:
Package functions can be utilized just as any other function in a SELECT statement or from other PL blocks
Package procedure can be invoked directly using CALL or from other PL blocks
Package variables can be directly read and written using the package name qualification in a PL block.
Direct Access Using Dot Notation: In the dot notation, elements can be accessed in the following manner:
package_name.func('foo');
package_name.proc('foo');
package_name.variable;
These statements can be used from inside a PL block or in a SELECT statement if the element is a function.
SQL Call Statement: Another way is to use the CALL statement. The CALL statement executes a standalone procedure in a type or package.
CALL package_name.proc('foo');
The scope of variables declared in a pl/sql block is limited to that block. If it has nested blocks, then it will be a global variable to the nested blocks.
Similarly, if both blocks declare the same name variable, then inside of the nested block, its own declared variable is visible and the parent one becomes invisible. To access the parent variable, that variable must be fully qualified.
Consider the following code snippet.
Example: Visibility and Qualifying Variable Names
-- blk_1
DECLARE
x INT;
y INT;
BEGIN
-- both blk_1.x and blk_1.y are visible
-- blk_2
DECLARE
x INT;
z INT;
BEGIN
-- blk_2.x, y and z are visible
-- to access blk_1.x it has to be a qualified name. blk_1.x := 0; NULL;
NULL;
END;
-- both x and y are visible
END;
/
The above example shows how you must fully qualify a variable name in case a nested package contains a variable with the same name.
Variable name qualification helps in resolving possible confusion that gets introduced by scope precedence in the following scenarios:
Package and nested packages variables: without qualification, nested takes precedence
Package variable and column names: without qualification, column name takes precedence
Function or procedure variable and package variable: without qualification, package variable takes precedence.
create package example.
CREATE TABLE test(x INT, y VARCHAR2(100)); create table rectype(a int,b varchar2(100)); INSERT INTO test VALUES (1, 'One'); INSERT INTO test VALUES (2, 'Two'); INSERT INTO test VALUES (3, 'Three'); CREATE OR REPLACE PACKAGE example AS -- Declare type, cursor: CURSOR curtype RETURN rectype%rowtype; rec rectype%rowtype; -- Declare subprograms: FUNCTION somefunc (v int) RETURN NUMBER; -- Overload preceding subprogram: PROCEDURE xfunc (emp_id NUMBER); PROCEDURE xfunc (emp_email VARCHAR2); END; /
create package body example.
CREATE OR REPLACE PACKAGE BODY example
AS
nelems NUMBER; -- variable in this package
-- Define cursor declared in package specification:
CURSOR curtype RETURN rectype%rowtype IS SELECT x, y FROM test ORDER BY x;
-- Define subprograms declared in package specification:
FUNCTION somefunc (v int) RETURN NUMBER
IS
id NUMBER := 0;
BEGIN
OPEN curtype;
FETCH curtype INTO rec;
RETURN rec.a;
END;
PROCEDURE xfunc (emp_id NUMBER) IS
BEGIN
NULL;
END;
PROCEDURE xfunc (emp_email VARCHAR2) IS
BEGIN
NULL;
END;
END;
/
Record types are supported as package variables, however they can only be used within package elements i.e., Package function/procedure can utilize them. They can not be accessed outside the package.
It is not recommended to use package types for table building and function/procedure statements(external to the package), as this may lead to some issues.
Table and package with the same name cannot exist in the same schema. Same as oracle.
lightdb@postgres=# create table t1(key1 int);
CREATE TABLE
lightdb@postgres=# create package t1 IS
key1 real;
end;
/
ERROR: relation "t1" already exists
HINT: A relation has the same name, so you must use a name that doesn't conflict with any existing relation.
lightdb@postgres=# drop table t1;
DROP TABLE
lightdb@postgres=# create package t1 IS
key1 real;
end;
/
CREATE PACKAGE
lightdb@postgres=# create table t1(key1 int);
ERROR: package "t1" already exists
HINT: A package has the same name, so you must use a name that doesn't conflict with any existing package.
lightdb@postgres=#
Schema and package cannot have the same name.
lightdb@postgres=# create package t1 IS
key1 real;
end;
/
CREATE PACKAGE
lightdb@postgres=# create schema t1;
ERROR: package name "t1" exists, schema name is forbid same with package name.
lightdb@postgres=# drop package t1;
DROP PACKAGE
lightdb@postgres=# create schema t1;
CREATE SCHEMA
lightdb@postgres=# create package t1 IS
key1 real;
end;
/
ERROR: schema name "t1" exists, package name is forbid same with schema name.
lightdb@postgres=#
A "package" is a group of features, brought together by schemas, that have a single functionality, and are used by calling from PL/pgSQL.
The following packages are supported:
DBMS_ALERT
DBMS_ASSERT
DBMS_DATAPUMP
DBMS_LOB
DBMS_OUTPUT
DBMS_PIPE
DBMS_RANDOM
DBMS_UTILITY
DBMS_JOB
DBMS_LOCK
DBMS_METADATA
DBMS_OBFUSCATION_TOOLKIT
DBMS_SNAPSHOT
DBMS_SQL
DBMS_STATS
UTL_FILE
UTL_RAW
UTL_URL
UTL_ENCODE
To call the different functionalities from PL/pgSQL, use the PERFORM statement or SELECT statement, using the package name to qualify the name of the functionality. Refer to the explanations for each of the package functionalities for information on the format for calling.
Overview
The DBMS_ALERT package sends alerts from a PL/pgSQL session to multiple other PL/pgSQL sessions.
This package can be used when processing 1:N, such as when notifying alerts from a given PL/pgSQL session to another PL/pgSQL session at the same time.
Table 41.2. DBMS_ALERT Features
| Feature | Description |
|---|---|
| REGISTER | Registers the specified alert. |
| REMOVE | Removes the specified alert. |
| REMOVEALL | Removes all alerts from a session. |
| SIGNAL | Notifies alerts. |
| WAITANY | Waits for notification of any alerts for which a session is registered. |
| WAITONE | Waits for notification of a specific alert for which a session is registered. |
Syntax
This section explains each feature of DBMS_ALERT.
REGISTER
REGISTER registers the specified alert to a session. By registering alerts to a session, SIGNAL notifications can be received.
Specify the name of the alert.
Alerts are case-sensitive.
Multiple alerts can be registered within a single session. If registering multiple alerts, call REGISTER for each alert.
Example
PERFORM DBMS_ALERT.REGISTER('sample_alert');
REMOVE
REMOVE removes the specified alert from a session.
Specify the name of the alert.
Alerts are case-sensitive.
The message left by the alert will be removed.
Example
PERFORM DBMS_ALERT.REMOVE('sample_alert');
REMOVEALL
REMOVEALL removes all alerts registered within a session.
All messages left by the alerts will be removed.
Example
PERFORM DBMS_ALERT.REMOVEALL();
SIGNAL
SIGNAL sends a message notification for the specified alert.
Specify the name of the alert for which message notifications are sent.
Alerts are case-sensitive.
In the message, specify the alert message for notifications.
Message notifications are not complete at the stage when SIGNAL is executed. Message notifications are sent upon committing the transaction. Message notifications are discarded if a rollback is performed after SIGNAL is executed.
If message notifications are sent for the same alert from multiple sessions, the messages will be accumulated without being removed.
Example
PERFORM DBMS_ALERT.SIGNAL('ALERT001','message001');
Note
If SIGNAL is issued continuously and the accumulated messages exceed a certain amount, an insufficient memory error may be output. If the memory becomes insufficient, call AITANY or WAITONE to receive an alert, and reduce the accumulated messages.
WAITANY
WAITANY waits for notification of any alerts registered for a session.
Specify the maximum wait time timeout in seconds to wait for an alert.
Use a SELECT statement to obtain the notified information, which is stored in the name, message and status columns.
The name column stores the alert names. The data type of name is TEXT.
The message column stores the messages of notified alerts. The data type of message is TEXT.
The status column stores the status code returned by the operation: 0-an alert occurred; 1-a timeout occurred. The data type of status is INTEGER.
Example
DECLARE
alert_name TEXT := 'sample_alert';
alert_message TEXT;
alert_status INTEGER;
BEGIN
SELECT name,message,status INTO alert_name,alert_message,alert_status FROM DBMS_ALERT.WAITANY(60);
WAITONE
WAITONE waits for notification of the specified alert.
Specify the name of the alert to wait for.
Alerts are case-sensitive.
Specify the maximum wait time timeout in seconds to wait for the alert.
Use a SELECT statement to obtain the notified information, which is stored in the message and status columns.
The message column stores the messages of notified alerts. The data type of message is TEXT.
The status column stores the status code returned by the operation: 0-an alert occurred; 1-a timeout occurred. The data type of status is INTEGER.
Example
DECLARE
alert_message TEXT;
alert_status INTEGER;
BEGIN
SELECT message,status INTO alert_message,alert_status FROM DBMS_ALERT.WAITONE('sample_alert', 60);
Below is a usage example of the processing flow of DBMS_ALERT.
DBMS_ALERT flow
Note
The target of message notifications by SIGNAL is sessions for which REGISTER is executed at the time of executing SIGNAL.
On the receiving side, always ensure that REMOVE or REMOVEALL is used to remove alerts as soon as the alerts are no longer needed. If a session is closed without removing the alerts, it may no longer be possible to receive a SIGNAL for alerts of the same name in another session.
DBMS_ALERT and DBMS_PIPE use the same memory environment. Therefore, when insufficient memory is detected for DBMS_PIPE, it is possible that insufficient memory will also be detected for DBMS_ALERT.
Usage example
Sending side
CREATE FUNCTION send_dbms_alert_exe() RETURNS VOID AS $$
BEGIN
PERFORM DBMS_ALERT.SIGNAL('sample_alert','SIGNAL ALERT');
END;
$$ LANGUAGE plpgsql;
SELECT send_dbms_alert_exe();
DROP FUNCTION send_dbms_alert_exe();
Receiving side
CREATE FUNCTION receive_dbms_alert_exe() RETURNS VOID AS $$
DECLARE
alert_name TEXT := 'sample_alert';
alert_message TEXT;
alert_status INTEGER;
BEGIN
PERFORM DBMS_ALERT.REGISTER(alert_name);
SELECT message,status INTO alert_message,alert_status FROM DBMS_ALERT.WAITONE(alert_name,300);
RAISE NOTICE 'Message : %', alert_message;
RAISE NOTICE 'Status : %', alert_status;
PERFORM DBMS_ALERT.REMOVE(alert_name);
END;
$$ LANGUAGE plpgsql;
SELECT receive_dbms_alert_exe();
DROP FUNCTION receive_dbms_alert_exe();
Overview
Performs verification of the properties of input values in PL/pgSQL.
Table 41.3. DBMS_ASSERT Features
| Feature | Description |
|---|---|
| ENQUOTE_LITERAL | Returns the specified string enclosed in single quotation marks. |
| ENQUOTE_NAME | Returns the specified string enclosed in double quotation marks. |
| NOOP | Returns the specified string as is. |
| OBJECT_NAME | Verifies if the specified string is a defined identifier. |
| QUALIFIED_SQL_NAME | Verifies if the specified string is in the appropriate format as an identifier. |
| SCHEMA_NAME | Verifies if the specified string is a defined schema. |
| SIMPLE_SQL_NAME | Verifies if the specified string is in the appropriate format as a single identifier. |
Syntax
This section explains each feature of DBMS_ASSERT.
ENQUOTE_LITERAL
ENQUOTE_LITERAL returns the specified string enclosed in single quotation marks.
Specify a string enclosed in single quotation marks.
The data type of the return value is VARCHAR.
Example
DECLARE
q_literal VARCHAR(256);
BEGIN
q_literal := DBMS_ASSERT.ENQUOTE_LITERAL('literal_word');
ENQUOTE_NAME
ENQUOTE_NAME returns the specified string enclosed in double quotation marks.
Specify a string enclosed in double quotation marks.
For lowercase conversion, specify TRUE or FALSE. Specify TRUE to convert uppercase characters in the string to lowercase. If FALSE is specified, conversion to lowercase will not take place. The default is TRUE.
If all the characters in the string are lowercase, they will not be enclosed in double quotation marks.
The data type of the return value is VARCHAR.
See
Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.
Example
DECLARE
dq_literal VARCHAR(256);
BEGIN
dq_literal := DBMS_ASSERT.ENQUOTE_NAME('TBL001');
NOOP
NOOP returns the specified string as is.
Specify a string.
The data type of the return value is VARCHAR.
Example
DECLARE
literal VARCHAR(256);
BEGIN
literal := DBMS_ASSERT.NOOP('NOOP_WORD');
OBJECT_NAME
OBJECT_NAME verifies if the specified string is a defined identifier.
Specify the identifier for verification. If the identifier has been defined, the specified identifier will be returned. Otherwise, the following error will occur.
ERROR: invalid object name
The data type of the return value is VARCHAR.
Example
DECLARE
object_name VARCHAR(256);
BEGIN
object_name := DBMS_ASSERT.OBJECT_NAME('SCM001.TBL001');
QUALIFIED_SQL_NAME
QUALIFIED_SQL_NAME verifies if the specified string is in the appropriate format as an identifier.
Specify the identifier for verification. If the string can be used as an identifier, the specified identifier will be returned. Otherwise, the following error will occur.
ERROR: string is not qualified SQL name
The data type of the return value is VARCHAR.
See
Refer to "The SQL Language" > "Lexical Structure" > "Identifiers and Key Words" in the LightDB Documentation for information on the formats that can be used as identifiers.
Example
DECLARE
object_name VARCHAR(256);
BEGIN
object_name := DBMS_ASSERT.QUALIFIED_SQL_NAME('SCM002.TBL001');
SCHEMA_NAME
SCHEMA_NAME verifies if the specified string is a defined schema.
Specify a schema name for verification. If the schema has been defined, the specified schema name will be returned. Otherwise, the following error will occur.
ERROR: invalid schema name
The data type of the return value is VARCHAR.
Example
DECLARE
schema_name VARCHAR(256);
BEGIN
schema_name := DBMS_ASSERT.SCHEMA_NAME('SCM001');
SIMPLE_SQL_NAME
SIMPLE_SQL_NAME verifies if the specified string is in the appropriate format as a single identifier.
Specify an identifier for verification. If the specified string can be used as an identifier, the specified identifier will be returned. Otherwise, the following error will occur.
ERROR: string is not qualified SQL name
The data type of the return value is VARCHAR.
See
Refer to "The SQL Language" > "Lexical Structure" > "Identifiers and Key Words" in the LightDB Documentation for information on the formats that can be used as identifiers. Note that an error will occur if an identifier using fullwidth characters is specified. If fullwidth characters are included, specify a quoted identifier.
Example
DECLARE
simple_name VARCHAR(256);
BEGIN
simple_name := DBMS_ASSERT.SIMPLE_SQL_NAME('COL01');
A usage example of DBMS_ASSERT is shown below.
CREATE FUNCTION dbms_assert_exe() RETURNS VOID AS $$
DECLARE
w_schema VARCHAR(20) := 'public';
w_table VARCHAR(20) := 'T1';
w_object VARCHAR(40);
BEGIN
PERFORM DBMS_ASSERT.NOOP(w_schema);
PERFORM DBMS_ASSERT.SIMPLE_SQL_NAME(w_table);
PERFORM DBMS_ASSERT.SCHEMA_NAME(w_schema);
w_object := w_schema || '.' || w_table;
PERFORM DBMS_ASSERT.QUALIFIED_SQL_NAME(w_object);
PERFORM DBMS_ASSERT.OBJECT_NAME(w_object);
RAISE NOTICE 'OBJECT : %', DBMS_ASSERT.ENQUOTE_LITERAL(w_object);
RAISE NOTICE 'TABLE_NAME : %', DBMS_ASSERT.ENQUOTE_NAME(w_table);
END;
$$
LANGUAGE plpgsql;
SELECT dbms_assert_exe();
DROP FUNCTION dbms_assert_exe();
Overview
Provides a set of stored procedures and functions for moving data and metadata between databases.
Table 41.4. DBMS_DATAPUMP Features
| Feature | Description |
|---|---|
| ADD_FILE | Adds dump files to the dump file set for an Export,Import operation. |
| ATTACH | Used to gain access to a Data Pump job. |
| METADATA_FILTER | Provides filters that allow you to restrict the items that are included in a job |
| OPEN | Declares a new job using the Data Pump API. |
| SET_PARALLEL | Adjusts the degree of parallelism within a job. |
| SET_PARAMETER | Specifies job-processing options. |
| START_JOB | Begins or resumes execution of a job. |
| STOP_JOB | Terminates a job, but optionally, preserves the state of the job. |
| WAIT_FOR_JOB | Runs a job until it either completes normally or stops for some other reason.(invalid) |
Syntax
PROCEDURE ADD_FILE(HANDLE IN NUMERIC, FILENAME IN VARCHAR2, DIRECTORY IN VARCHAR2, FILESIZE IN VARCHAR2 DEFAULT NULL, FILETYPE IN NUMERIC DEFAULT 0, REUSEFILE IN NUMERIC DEFAULT NULL);
FUNCTION ATTACH(JOB_NAME IN VARCHAR2 DEFAULT NULL, JOB_OWNER IN VARCHAR2 DEFAULT NULL) RETURN NUMERIC;
PROCEDURE METADATA_FILTER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN VARCHAR2, OBJECT_PATH IN VARCHAR2 DEFAULT NULL);
PROCEDURE METADATA_FILTER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN CLOB, OBJECT_PATH IN VARCHAR2 DEFAULT NULL);
FUNCTION OPEN(OPERATION IN VARCHAR2, JOB_MODE IN VARCHAR2, REMOTE_LINK IN VARCHAR2 DEFAULT NULL, JOB_NAME IN VARCHAR2 DEFAULT NULL, VERSION IN VARCHAR2 DEFAULT 'COMPATIBLE') RETURN NUMERIC;
PROCEDURE SET_PARAMETER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN VARCHAR2);
PROCEDURE SET_PARAMETER(HANDLE IN NUMERIC, NAME IN VARCHAR2, VALUE IN NUMERIC);
PROCEDURE SET_PARALLEL(HANDLE IN NUMERIC, DEGREE IN NUMERIC);
PROCEDURE START_JOB(HANDLE IN NUMERIC, SKIP_CURRENT IN NUMERIC DEFAULT 0, ABORT_STEP IN NUMERIC DEFAULT 0, CLUSTER_OK IN NUMERIC DEFAULT 1, SERVICE_NAME IN VARCHAR2 DEFAULT NULL);
PROCEDURE STOP_JOB(HANDLE IN NUMERIC, IMMEDIATE IN NUMERIC DEFAULT 0, KEEP_MASTER IN NUMERIC DEFAULT NULL, DELAY IN NUMERIC DEFAULT 60);
PROCEDURE WAIT_FOR_JOB(HANDLE IN NUMERIC, JOB_STATE OUT VARCHAR2);
This section explains each feature of DBMS_DATAPUMP.
ADD_FILE
Adds dump files to the dump file set for an Export,Import operation.
FILESIZE,REUSEFILE is not in use currently.
Example
DBMS_DATAPUMP.ADD_FILE(HANDLE => hdl, FILENAME => 'example001.dmp', DIRECTORY => 'DMPDIR');
DBMS_DATAPUMP.ADD_FILE(HANDLE => hdl, FILENAME => 'example001.log', DIRECTORY => 'DMPDIR', FILESIZE => '', FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
ATTACH
Used to gain access to a Data Pump job.
Example
DBMS_DATAPUMP.ATTACH(JOB_NAME => 'example001', JOB_OWNER => 'lightdb')
METADATA_FILTER
Provides filters that allow you to restrict the items that are included in a job.
NAME support options ('SCHEMA_EXPR','NAME_EXPR');VALUE support epxr types (IN,NOT IN,LIKE,NOT LIKE).
OBJECT_PATH is not in use currently.
Example
DBMS_DATAPUMP.METADATA_FILTER(HANDLE => hdl, NAME => 'SCHEMA_EXPR', VALUE => 'LIKE ''%HR%''');
DBMS_DATAPUMP.METADATA_FILTER(HANDLE => hdl, NAME => 'NAME_EXPR', VALUE => 'LIKE ''%EMP%''');
OPEN
Declares a new job using the Data Pump API.
OPERATION support options ('EXPORT','IMPORT');JOB_MODE support options ('SHCEMA','TABLE').
REMOTE_LINK,VERSION is not in use currently.
Example
DBMS_DATAPUMP.OPEN(OPERATION => 'EXPORT', JOB_MODE => 'TABLE', REMOTE_LINK => NULL, JOB_NAME => 'EXAMPLE001', VERSION => 'LATEST');
SET_PARAMETER
Specifies job-processing options.
Example
DBMS_DATAPUMP.SET_PARAMETER(HANDLE => hdl, NAME => 'COMPRESSION', VALUE => 'ALL');
SET_PARALLEL
Adjusts the degree of parallelism within a job.
Example
DBMS_DATAPUMP.SET_PARALLEL(HANDLE => hdl, DEGREE => 2);
START_JOB
Begins or resumes execution of a job.
SKIP_CURRENT,ABORT_STEP,CLUSTER_OK,SERVICE_NAME is not in use currently.
Example
DBMS_DATAPUMP.START_JOB(HANDLE => hdl);
STOP_JOB
Terminates a job, but optionally, preserves the state of the job.
IMMEDIATE,KEEP_MASTER,DELAY is not in use currently.
Example
DBMS_DATAPUMP.STOP_JOB(HANDLE => hdl);
WAIT_FOR_JOB
Runs a job until it either completes normally or stops for some other reason(procedure is empty internally).
Example
DBMS_DATAPUMP.WAIT_FOR_JOB(HANDLE => hdl,JOB_STATE => jbs);
A usage example of DBMS_DATAPUMP is shown below.
--EXPORT JOB
declare
hdl numeric;
jbs varchar2;
begin
hdl := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => hdl, name => 'NAME_EXPR', value => 'LIKE ''%EMP%''');
dbms_datapump.set_parameter(handle => hdl, name => 'COMPRESSION', value => 'ALL');
dbms_datapump.set_parallel(handle => hdl, degree => 2);
dbms_datapump.start_job(handle => hdl);
dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
dbms_datapump.stop_job(handle => hdl);
end;
/
--IMPORT JOB
declare
hdl numeric;
jbs varchar2;
begin
hdl := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', remote_link => NULL, job_name => 'EXAMPLE001', version => 'LATEST');
dbms_datapump.add_file(handle => hdl, filename => 'example001.dmp', directory => 'DMPDIR');
dbms_datapump.add_file(handle => hdl, filename => 'example001.log', directory => 'DMPDIR', filesize => '', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.set_parallel(handle => hdl, degree => 2);
dbms_datapump.start_job(handle => hdl);
dbms_datapump.wait_for_job(handle => hdl,job_state => jbs);
dbms_datapump.stop_job(handle => hdl);
end;
/
Overview
Some functions compatible with DBMS_LOB package.
Table 41.5. DBMS_LOB Features
| Feature | Description |
|---|---|
| INSTR | Returns starting index of specified LOB. |
| SUBSTR | Extracts the substring of LOB. |
| GETLENGTH | Returns the number of characters in the LOB. |
| APPEND | Appends the contents of the source LOB to the destination LOB. |
| CLOSE | Closes a previously opened internal or external LOB.(invalid) |
| COMPARE | Compares two entire LOBs or parts of two LOBs. |
| COPY | Copies all, or part, of the source LOB to the destination LOB. |
| CREATETEMPORAR | Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace.(invalid) |
| ERASE | Erases all or part of a LOB. |
| FREETEMPORARY | Erases all or part of a LOB. |
| OPEN | Frees the temporary BLOB or CLOB in the default temporary tablespace.(invalid) |
| READ | Reads data from the LOB starting at the specified offset. |
| TRIM | Trims the LOB value to the specified shorter length. |
| WRITE | Writes data to the LOB from a specified offset. |
| WRITEAPPEND | Writes a buffer to the end of a LOB. |
| COMPARE | Compares two entire LOBs or parts of two LOBs. |
Syntax
INSTR(STR TEXT, PATT TEXT, START INTEGER DEFAULT 1, NTH INTEGER DEFAULT 1) RETURNS INTEGER
INSTR(STR BLOB, PATT TEXT, START INTEGER DEFAULT 1, NTH INTEGER DEFAULT 1) RETURNS INTEGER
SUBSTR(STR CLOB, AMOUNT INTEGER DEFAULT 32767, START_OFFSET INTEGER DEFAULT 1) RETURNS VARCHAR2
SUBSTR(STR BLOB, AMOUNT INTEGER DEFAULT 32767, START_OFFSET INTEGER DEFAULT 1) RETURNS RAW
GETLENGTH(STR CLOB) RETURNS INTEGER
GETLENGTH(STR BLOB) RETURNS INTEGER
APPEND(dest_lob BLOB,src_lob BLOB)/APPEND(CLOB,CLOB)
COMPARE(lob_1 BLOB,lob_2 BLOB,amount INT4 DEFAULT 2147483647,offset_1 INT4 DEFAULT 1,offset_2 INT4 DEFAULT 1) RETURNS INTEGER
COMPARE(CLOB,CLOB,INT4 DEFAULT 2147483647,INT4 DEFAULT 1,INT4 DEFAULT 1) RETURNS INTEGER
COPY(dest_lob BLOB,src_lob BLOB,amount INT4,dest_offset INT4 DEFAULT 1,src_offset INT4 DEFAULT 1)/COPY(CLOB,CLOB,INT4,INT4 DEFAULT 1,INT4 DEFAULT 1)
ERASE(lob_loc BLOB,amount INT4,p_offset INT4 DEFAULT 1)/ERASE(CLOB,INT4,INT4 DEFAULT 1)
READ(lob_loc BLOB,amount INT4,p_offset INT4,buffer RAW)/READ(CLOB,INT4,INT4,TEXT)
TRIM(lob_loc BLOB,newlen INT4)/TRIM(CLOB,INT4)
WRITE(lob_loc BLOB,amount INT4,p_offset INT4,buffer RAW)/WRITE(CLOB,INT4,INT4,TEXT)
WRITEAPPEND(lob_loc BLOB,amount INT4,buffer RAW)/WRITEAPPEND(CLOB,INT4,TEXT)
Features
This section explains each feature of DBMS_LOB.
INSTR
Offset of the start of the matched pattern, in bytes or characters.It returns 0 if the pattern is not found.
It retruns null if any one or more of the IN parameters was NULL or INVALID.
It returns NULL if offset < 1.
Example
select dbms_lob.INSTR('Thomas', 'om') from dual;
select dbms_lob.INSTR('Thomas'::blob, utl_raw.CAST_TO_RAW('om')) from dual;
SUBSTR
Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified.
It returns NULL if any input parameter is NULL
It returns NULL if amount < 1.
It returns NULL if offset < 1.
Example
select dbms_lob.substr('1234567',1,2) from dual;
select dbms_lob.substr('1234567'::blob,1,2) from dual;
GETLENGTH
The length of the text in bytes or characters as an INTEGER.
NULL is returned if the input str is NULL.
Example
select DBMS_LOB.GETLENGTH('abc') from dual;
APPEND
Appends the contents of the source LOB to the destination LOB.
Example
declare
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
begin
dbms_output.serveroutput(true);
dbms_lob.append(l_blob, 'ext'::blob);
dbms_output.put_line(l_blob::text);
dbms_lob.append(l_clob, 'ext'::clob);
dbms_output.put_line(l_clob);
end;
/
COMPARE
Compares two entire LOBs or parts of two LOBs.
Returns 0 if `lob_1` equals `lob_2`, -1 if `lob_1` is less than `lob_2`, 1 if `lob_1` is greater than `lob_2`.
Returns NULL if any of amount, offset_1 or offset_2 is not a valid LOB offset value. A valid offset is within the range of 1 to LOBMAXSIZE inclusive.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
l_result := dbms_lob.compare('abcd'::blob, 'abcd'::blob);
dbms_output.put_line(l_result);
l_result := dbms_lob.compare('abcd'::clob, 'abcd'::clob);
dbms_output.put_line(l_result);
end;
/
COPY
Copies all, or part, of the source LOB to the destination LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, then zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, then existing data is overwritten.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
dbms_lob.copy(l_blob, 'new_blob'::blob, 100);
dbms_output.put_line(l_blob::text);
dbms_lob.copy(l_blob, 'xnew_blob'::blob, 100, 1, 2);
dbms_output.put_line(l_blob::text);
dbms_lob.copy(l_blob, 'xnew_blob'::blob, 100, 2, 2);
dbms_output.put_line(l_blob::text);
end;
/
ERASE
Erases an entire internal LOB or part of an internal LOB.
When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.
The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
l_blob := 'abcd'::blob;
l_amout := 100;
dbms_output.put_line(l_blob::text);
dbms_lob.erase(l_blob, l_amout);
dbms_output.put_line(l_blob::text);
l_clob := 'abcd'::clob;
l_amout := 4;
dbms_lob.erase(l_clob, l_amout);
dbms_output.put_line(l_clob);
end;
/
READ
This procedure reads a piece of a LOB, and returns the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.
The number of bytes or characters actually read is returned in the amount parameter. If the input offset points past the End of LOB, then amount is set to 0, and a NO_DATA_FOUND exception is raised.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
l_amout := 5;
dbms_lob.read('abcde'::blob, l_amout, 1, l_raw_result);
dbms_output.put_line(l_raw_result);
l_amout := 5;
dbms_lob.read('abcde'::clob, l_amout, 1, l_text_result);
dbms_output.put_line(l_text_result);
end;
/
TRIM
This procedure trims the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
l_blob := 'abcde'::blob;
dbms_lob.trim(l_blob, 1);
dbms_output.put_line(l_blob::text);
l_clob := 'abcde'::clob;
dbms_lob.trim(l_clob, 1);
dbms_output.put_line(l_clob);
end;
/
WRITE
This procedure writes a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.
WRITE replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
l_blob := 'abcde'::blob;
dbms_lob.write(l_blob, pg_catalog.length(utl_raw.CAST_TO_RAW('测试')), 2, utl_raw.CAST_TO_RAW('测试'));
dbms_output.put_line(l_blob::text);
-- write clob
l_clob := 'abcde'::clob;
dbms_lob.write(l_clob, 2, 2,'测试'::text);
dbms_output.put_line(l_clob);
end;
/
WRITEAPPEND
This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.
Example
declare
l_result int;
l_blob blob := 'abcd'::blob;
l_clob clob := 'abcd'::clob;
l_raw_result raw;
l_amout INTEGER := 100;
l_text_result text;
begin
dbms_output.serveroutput(true);
-- writeappend blob
l_blob := 'abcde'::blob;
dbms_lob.writeappend(l_blob, pg_catalog.length(utl_raw.CAST_TO_RAW('测试')), utl_raw.CAST_TO_RAW('测试'));
dbms_output.put_line(l_blob::text);
-- write clob
l_clob := 'abcde'::clob;
dbms_lob.writeappend(l_clob, 2,'测试'::text);
dbms_output.put_line(l_clob);
end;
/
A usage example of DBMS_LOB is shown below.
CREATE PROCEDURE dbms_lob_exe() AS $$
DECLARE
str1 text;
str2 text;
pos INTEGER;
count INTEGER;
BEGIN
str2 := dbms_lob.substr('1234567',2,3);
pos := dbms_lob.instr('Tech on the net', 'e');
count := dbms_lob.getlength('1234567');
raise info 'str2=%,pos=%,count=%', str2, pos, count;
END;
$$ LANGUAGE plpgsql;
call dbms_lob_exe();
DROP PROCEDURE dbms_lob_exe();
Overview
Sends messages to clients such as ltsql from PL/pgSQL.
Features
Table 41.6. DBMS_OUTPUT Features
| Feature | Description |
|---|---|
| ENABLE | Enables features of this package. |
| DISABLE | Disables features of this package. |
| SERVEROUTPUT | Controls whether messages are sent. |
| PUT | Sends messages. |
| PUT_LINE | Sends messages with a newline character appended. |
| NEW_LINE | Sends a newline character. It cannot be used with parameters, Supports two usages: dbms_output.new_line and dbms_output.new_line(). |
| GET_LINE | Retrieves a line from the message buffer. |
| GET_LINES | Retrieves multiple lines from the message buffer. |
Syntax
This section explains each feature of DBMS_OUTPUT.
ENABLE
ENABLE enables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
With multiple executions of ENABLE, the value specified last is the buffer size (in bytes). Specify a buffer size from 2000 to 1000000.
The default value of the buffer size is 20000. If NULL is specified as the buffer size, 1000000 will be used.
If ENABLE has not been executed, PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES are ignored even if they are executed.
Example
PERFORM DBMS_OUTPUT.ENABLE(20000);
DISABLE
DISABLE disables the use of PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES.
Remaining buffer information is discarded.
Example
PERFORM DBMS_OUTPUT.DISABLE();
SERVEROUTPUT
SERVEROUTPUT controls whether messages are sent.
Specify TRUE or FALSE for sendMsgs.
If TRUE is specified, when PUT, PUT_LINE, or NEW_LINE is executed, the message is sent to a client such as ltsql and not stored in the buffer.
If FALSE is specified, when PUT, PUT_LINE, or NEW_LINE is executed, the message is stored in the buffer and not sent to a client such as ltsql.
The default specified is TRUE.
See
Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.
Example
PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
SET SERVEROUTPUT ON/OFF; Equals SELECT DBMS_OUTPUT.SERVEROUTPUT(TRUE/FALSE);.
Example
lightdb@oracle=# SET SERVEROUTPUT ON;
serveroutput
--------------
(1 row)
lightdb@oracle=# SELECT DBMS_OUTPUT.PUT_LINE('my serveroutput');
my serveroutput
put_line
----------
(1 row)
lightdb@oracle=# SET SERVEROUTPUT OFF;
serveroutput
--------------
(1 row)
lightdb@oracle=# SELECT DBMS_OUTPUT.PUT_LINE('my serveroutput');
put_line
----------
(1 row)
lightdb@oracle=#
PUT
PUT sets the message to be sent.
The string is the message to be sent.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.
PUT does not append a newline character. To append a newline character, execute NEW_LINE.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.
Supported output datetime types: date, interval, timestamp without timezone, oracle.date,
and the time format is affected by the parameter nls_date_format.
Example
PERFORM DBMS_OUTPUT.PUT('abc');
PUT_LINE
PUT_LINE sets the message to be sent appended with a newline character.
The string is the message to be sent.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.
If a string longer than the buffer size specified in ENABLE is sent, an error occurs.
Example
PERFORM DBMS_OUTPUT.PUT_LINE('abc');
NEW_LINE
NEW_LINE appends a newline character to the message created with PUT.
When TRUE is specified for SERVEROUTPUT, the messages are sent to clients such as ltsql.
When FALSE is specified for SERVEROUTPUT, the messages are retained in the buffer.
Example
PERFORM DBMS_OUTPUT.NEW_LINE();
GET_LINE
GET_LINE retrieves a line from the message buffer.
Use a SELECT statement to obtain the retrieved line and status code returned by the operation, which are stored in the line and status columns.
The line column stores the line retrieved from the buffer. The data type of line is TEXT.
The status column stores the status code returned by the operation: 0-completed successfully; 1-failed because there are no more lines in the buffer. The data type of status is INTEGER.
If GET_LINE or GET_LINES is executed and then PUT, PUT_LINE or PUT_LINES is executed while messages that have not been retrieved from the buffer still exist, the messages not retrieved from the buffer will be discarded.
Example
DECLARE
buff1 VARCHAR(20);
stts1 INTEGER;
BEGIN
SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();
GET_LINES
GET_LINES retrieves multiple lines from the message buffer.
Specify the number of lines to retrieve from the buffer.
Use a SELECT statement to obtain the retrieved lines and the number of lines retrieved, which are stored in the lines and numlines columns.
The lines column stores the lines retrieved from the buffer. The data type of lines is TEXT.
The numlines column stores the number of lines retrieved from the buffer. If this number is less than the number of lines requested, then there are no more lines in the buffer. The data type of numlines is INTEGER.
If GET_LINE or GET_LINES is executed and then PUT, PUT_LINE, or NEW_LINE is executed while messages that have not been retrieved from the buffer still exist, the messages not retrieved from the buffer will be discarded.
Example
DECLARE
buff VARCHAR(20)[10];
stts INTEGER := 10;
BEGIN
SELECT lines, numlines INTO buff,stts FROM DBMS_OUTPUT.GET_LINES(stts);
A usage example of DBMS_OUTPUT is shown below.
CREATE FUNCTION dbms_output_exe() RETURNS VOID AS $$
DECLARE
buff1 VARCHAR(20);
buff2 VARCHAR(20);
stts1 INTEGER;
stts2 INTEGER;
BEGIN
PERFORM DBMS_OUTPUT.DISABLE();
PERFORM DBMS_OUTPUT.ENABLE();
PERFORM DBMS_OUTPUT.SERVEROUTPUT(FALSE);
PERFORM DBMS_OUTPUT.PUT('DBMS_OUTPUT TEST 1');
PERFORM DBMS_OUTPUT.NEW_LINE();
PERFORM DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT TEST 2');
SELECT line,status INTO buff1,stts1 FROM DBMS_OUTPUT.GET_LINE();
SELECT line,status INTO buff2,stts2 FROM DBMS_OUTPUT.GET_LINE();
PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
PERFORM DBMS_OUTPUT.PUT_LINE(buff1);
PERFORM DBMS_OUTPUT.PUT_LINE(buff2);
END;
$$ LANGUAGE plpgsql;
SELECT dbms_output_exe();
DROP FUNCTION dbms_output_exe();
Overview
Performs communication between sessions that execute PL/pgSQL.
This package can be used for 1:1 communication, such as when data is being exchanged between sessions executing PL/pgSQL.
For pipes, there are explicit pipes and implicit pipes, and furthermore, for explicit pipes, you can select public pipes and private pipes. The characteristics of each type are as follows:
Types of pipes
Table 41.7. Types of pipes
| Type | Characteristics |
|---|---|
| Explicit pipe |
- CREATE_PIPE is used to create a pipe explicitly. - While creating a pipe, you can select between a public pipe and private pipe. - It is necessary to use REMOVE_PIPE to explicitly remove a pipe. |
| Implicit pipe |
- Created automatically when SEND_MESSAGE and RECEIVE_MESSAGE are used. - The pipe that is created becomes a public pipe. - When messages are received using RECEIVE_MESSAGE, if there are no additional messages remaining in the pipe, the pipe will be removed automatically. |
| Public pipe |
- Can be created as an explicit pipe or implicit pipe. - Can also be used by users other than the creator. |
| Private pipe |
- Can only be created as an explicit pipe. - Can only be used by its creator. |
Note
Up to 50 pipes can be used concurrently by a single instance.
In cases where pipes are frequently created and removed repetitively, use public pipes. If you create a private pipe, internal information (the creator of the private pipe) will remain even after the pipe is removed. Thus, repeatedly creating and removing pipes may ultimately cause memory to run out.
If a timeout occurs without receiving a message when an implicit pipe is created by RECEIVE_MESSAGE, the pipe will not be removed.
Features
Table 41.8. DBMS_PIPE Features
| Feature | Description |
|---|---|
| CREATE_PIPE | Creates a public or private pipe. |
| NEXT_ITEM_TYPE | Determines the data type of the next item in the local buffer, and returns that type. |
| PACK_MESSAGE | Sets a message in the local buffer. |
| PURGE | Empties the contents of the specified pipe. |
| RECEIVE_MESSAGE | Sets a received message in the local buffer. |
| REMOVE_PIPE | Removes the specified pipe. |
| RESET_BUFFER | Resets the set position of the local buffer. |
| SEND_MESSAGE | Sends the contents of the local buffer. |
| UNIQUE_SESSION_NAME | Returns a unique session name. |
| UNPACK_MESSAGE_BYTEA | Receives a message in the local buffer in BYTEA type. |
| UNPACK_MESSAGE_DATE | Receives a message in the local buffer in DATE type. |
| UNPACK_MESSAGE_NUMBER | Receives a message in the local buffer in NUMERIC type. |
| UNPACK_MESSAGE_RECORD | Receives a message in the local buffer in RECORD type. |
| UNPACK_MESSAGE_TEXT | Receives a message in the local buffer in TEXT type. |
| UNPACK_MESSAGE_TIMESTAMP | Receives a message in the local buffer in TIMESTAMP type. |
Syntax
This section explains each feature of DBMS_PIPE.
CREATE_PIPE
CREATE_PIPE explicitly creates a pipe environment for data communication.
Specify the name of the pipe to be created.
Pipe names are case-sensitive.
Specify the maximum number of messages that can be sent or received. If omitted, 0 (cannot send messages) will be used. Specify from 1 to 32767.
Specify TRUE or FALSE for private. If TRUE is specified, a private pipe will be created. If FALSE is specified, a public pipe will be created. The default is FALSE.
An error will occur if a pipe of the same name has already been created.
See
Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.
Example
PERFORM DBMS_PIPE.CREATE_PIPE('P01', 100, FALSE);
NEXT_ITEM_TYPE
NEXT_ITEM_TYPE returns the next data type in the local buffer.
The data type of the return value is INTEGER. One of the following values is returned:
Table 41.9. Values returned by NEXT_ITEM_TYPE
| Return value | Data type |
|---|---|
| 9 | NUMERIC type |
| 11 | TEXT type |
| 12 | DATE type |
| 13 | TIMESTAMP type |
| 23 | BYTEA type |
| 24 | RECORD type |
| 0 | No data in the buffer |
Example
DECLARE
i_iType INTEGER;
BEGIN
i_iType := DBMS_PIPE.NEXT_ITEM_TYPE();
PACK_MESSAGE
PACK_MESSAGE sets the specified message in the local buffer.
Specify the data to be set in the local buffer. The following data types can be used:
Character type (*1)
Integer type (*2)
NUMERIC type
DATE type
TIMESTAMP type (*3)
BYTEA type
RECORD type
*1: The character type is converted internally to TEXT type.
*2: The integer type is converted internally to NUMERIC type.
*3: The TIMESTAMP type is converted internally to TIMESTAMP WITH TIME ZONE type.
Each time PACK_MESSAGE is called, a new message is added to the local buffer.
The size of the local buffer is approximately 8 KB. However, each message has overhead, so the total size that can be stored is actually less than 8 KB. To clear the local buffer, send a message (SEND_MESSAGE), or reset the buffer (RESET_BUFFER) to its initial state.
Example
PERFORM DBMS_PIPE.PACK_MESSAGE('Message Test001');
PURGE
PURGE removes the messages in the pipe.
Specify the name of the pipe for which the messages are to be removed.
Pipe names are case-sensitive.
Example
PERFORM DBMS_PIPE.PURGE('P01');
Note
When PURGE is executed, the local buffer is used to remove the messages in the pipe. Therefore, if there are any messages remaining in the pipe, the local buffer will be overwritten by PURGE.
RECEIVE_MESSAGE
RECEIVE_MESSAGE receives messages that exist in the specified pipe, and sets those messages in the local buffer.
Messages are received in the units in which they are sent to the pipe by SEND_MESSAGE. Received messages are removed from the pipe after being set in the local buffer.
Specify the name of the pipe for which the messages are to be received.
Pipe names are case-sensitive.
Specify the maximum wait time timeout in seconds to wait for a message. If omitted, the default is 31536000 seconds (1 year).
The data type of the return value is INTEGER. If a message is received successfully, 0 is returned. If a timeout occurs, 1 is returned.
Example
DECLARE
i_Ret INTEGER;
BEGIN
i_Ret := DBMS_PIPE.RECEIVE_MESSAGE('P01', 60);
REMOVE_PIPE
REMOVE_PIPE removes the specified pipe.
Specify the name of the pipe to be removed.
Pipe names are case-sensitive.
Example
PERFORM DBMS_PIPE.REMOVE_PIPE('P01');
RESET_BUFFER
RESET_BUFFER resets the set position of the local buffer. Any unnecessary data remaining in the local buffer can be discarded using this operation.
Example
PERFORM DBMS_PIPE.RESET_BUFFER();
SEND_MESSAGE
SEND_MESSAGE sends data stored in the local buffer to the specified pipe.
Specify the name of the pipe that the data is to be sent to.
Pipe names are case-sensitive.
Specify the maximum wait time timeout in seconds for sending data stored in the local buffer. If omitted, the default is 31536000 seconds (1 year).
Specify the maximum number of messages that can be sent or received. If omitted, the maximum number of messages set in CREATE_PIPE is used. If omitted in the implicit pipe, the number of messages will be unlimited. Specify from 1 to 32767.
If the maximum number of messages is specified in both SEND_MESSAGE and CREATE_PIPE, the larger of the values will be used.
The data type of the return value is INTEGER. If a message is received successfully, 0 is returned. If a timeout occurs, 1 is returned.
Example
DECLARE
i_Ret INTEGER;
BEGIN
i_Ret := DBMS_PIPE.SEND_MESSAGE('P01', 10, 20);
Note
A timeout will occur during sending if the maximum number of messages is reached, or if the message being sent is too large. If a timeout occurs, use RECEIVE_MESSAGE to receive any messages that are in the pipe.
UNIQUE_SESSION_NAME
UNIQUE_SESSION_NAME returns a name that is unique among all the sessions. This name can be used as the pipe name.
Multiple calls from the same session always return the same name.
The data type of the return value is VARCHAR. Returns a string of up to 30 characters.
Example
DECLARE
p_Name VARCHAR(30);
BEGIN
p_Name := DBMS_PIPE.UNIQUE_SESSION_NAME();
UNPACK_MESSAGE_BYTEA
NPACK_MESSAGE_BYTEA receives BTYEA type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is BYTEA.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
g_Bytea BYTEA;
BEGIN
g_Bytea := DBMS_PIPE.UNPACK_MESSAGE_BYTEA();
UNPACK_MESSAGE_DATE
UNPACK_MESSAGE_DATE receives DATE type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is DATE.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
g_Date DATE;
BEGIN
g_Date := DBMS_PIPE.UNPACK_MESSAGE_DATE();
Note
If the "oracle" schema is set in search_path, the DATE type of orafce will be used, so for receiving data, use UNPACK_MESSAGE_TIMESTAMP. UNPACK_MESSAGE_DATE is the interface for the DATE type of LightDB.
UNPACK_MESSAGE_NUMBER
UNPACK_MESSAGE_NUMBER receives NUMERIC type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is NUMERIC.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
g_Number NUMERIC;
BEGIN
g_Number := DBMS_PIPE.UNPACK_MESSAGE_NUMBER();
UNPACK_MESSAGE_RECORD
UNPACK_MESSAGE_RECORD receives RECORD type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is RECORD.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
msg1 TEXT;
status NUMERIC;
BEGIN
SELECT col1, col2 INTO msg1, status FROM DBMS_PIPE.UNPACK_MESSAGE_RECORD();
UNPACK_MESSAGE_TEXT
UNPACK_MESSAGE_TEXT receives TEXT type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is TEXT.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
g_Text TEXT;
BEGIN
g_Text := DBMS_PIPE.UNPACK_MESSAGE_TEXT();
UNPACK_MESSAGE_TIMESTAMP
UNPACK_MESSAGE_TIMESTAMP receives TIMESTAMP WITH TIME ZONE type messages in the local buffer.
Messages are received in the unit set in the local buffer by PACK_MESSAGE. Received messages are removed from the local buffer.
The data type of the return value is TIMESTAMP WITH TIME ZONE.
If no messages exist in the local buffer, a NULL value is returned.
For the data type, it is necessary to align with the data type set by PACK_MESSAGE. If the data type is different, the following error will occur.
ERROR: datatype mismatch
DETAIL: unpack unexpected type: xx
Example
DECLARE
g_Timestamptz TIMESTAMP WITH TIME ZONE;
BEGIN
g_Timestamptz := DBMS_PIPE.UNPACK_MESSAGE_TIMESTAMP();
Below is a usage example of the processing flow of DBMS_PIPE.
Flow of DBMS_PIPE
Note
When CREATE_PIPE is used to explicitly create a pipe, ensure to use REMOVE_PIPE to remove the pipe. If a pipe is not removed explicitly, once created, it will remain until the instance is stopped.
In the flow diagram, CREATE_PIPE and REMOVE_PIPE are described on the receiving side, however, these can be executed on the sending side. In order to maintain consistency, it is recommended to create and remove pipes on one side.
An error will occur for CREATE_PIPE if a pipe of the same name already exists. Implicitly created pipes are also the target of SEND_MESSAGE and RECEIVE_MESSAGE, so when executing CREATE_PIPE, ensure that SEND_MESSAGE and RECEIVE_MESSAGE are not called beforehand.
DBMS_ALERT and DBMS_PIPE use the same memory environment. Therefore, when insufficient memory is detected for DBMS_ALERT, it is possible that insufficient memory will also be detected for DBMS_PIPE.
Information
The information of pipes that are in use can be viewed in the DBMS_PIPE.DB_PIPES view.
SELECT * from dbms_pipe.db_pipes;
name | items | size | limit | private | owner
------+-------+------+-------+---------+-------
P01 | 1 | 18 | 100 | f |
(1 row)
Usage example
Sending side
CREATE FUNCTION send_dbms_pipe_exe(IN pipe_mess text) RETURNS void AS $$
DECLARE
pipe_name text := 'sample_pipe';
pipe_time timestamp := current_timestamp;
pipe_stat int;
BEGIN
PERFORM DBMS_PIPE.RESET_BUFFER();
PERFORM DBMS_PIPE.PACK_MESSAGE(pipe_mess);
PERFORM DBMS_PIPE.PACK_MESSAGE(pipe_time);
pipe_stat := DBMS_PIPE.SEND_MESSAGE(pipe_name);
RAISE NOTICE 'PIPE_NAME: % SEND Return Value =%', pipe_name, pipe_stat;
END;
$$ LANGUAGE plpgsql;
SELECT send_dbms_pipe_exe('Sample Message.');
DROP FUNCTION send_dbms_pipe_exe(text);
Receiving side
CREATE FUNCTION receive_dbms_pipe_exe() RETURNS void AS $$
DECLARE
pipe_name text := 'sample_pipe';
pipe_text text;
pipe_nume numeric;
pipe_date date;
pipe_time timestamp with time zone;
pipe_byte bytea;
pipe_reco record;
pipe_item int;
pipe_stat int;
BEGIN
pipe_stat := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name,300);
RAISE NOTICE 'Return Value = %', pipe_stat;
LOOP
pipe_item := DBMS_PIPE.NEXT_ITEM_TYPE();
RAISE NOTICE 'Next Item : %', pipe_item;
IF (pipe_item = 9) THEN
pipe_nume := DBMS_PIPE.UNPACK_MESSAGE_NUMBER();
RAISE NOTICE 'Get Message : %' ,pipe_nume;
ELSIF (pipe_item =11) THEN
pipe_text := DBMS_PIPE.UNPACK_MESSAGE_TEXT();
RAISE NOTICE 'Get Message : %' ,pipe_text;
ELSIF (pipe_item = 12) THEN
pipe_date := DBMS_PIPE.UNPACK_MESSAGE_DATE();
RAISE NOTICE 'Get Message : %' ,pipe_date;
ELSIF (pipe_item = 13) THEN
pipe_time := DBMS_PIPE.UNPACK_MESSAGE_TIMESTAMP();
RAISE NOTICE 'Get Message : %' ,pipe_time;
ELSIF (pipe_item = 23) THEN
pipe_byte := DBMS_PIPE.UNPACK_MESSAGE_BYTEA();
RAISE NOTICE 'Get Message : %' ,pipe_byte;
ELSIF (pipe_item = 24) THEN
pipe_reco := DBMS_PIPE.UNPACK_MESSAGE_RECORD();
RAISE NOTICE 'Get Message : %' ,pipe_reco;
ELSE
EXIT;
END IF;
END LOOP;
PERFORM DBMS_PIPE.REMOVE_PIPE(pipe_name);
END;
$$ LANGUAGE plpgsql;
SELECT receive_dbms_pipe_exe();
DROP FUNCTION receive_dbms_pipe_exe();
Overview
Generates random numbers in PL/pgSQL.
Table 41.10. DBMS_RANDOM Features
| Feature | Description |
|---|---|
| INITIALIZE | Initializes the generation of random numbers. |
| NORMAL | Returns a normally distributed random number. |
| RANDOM | Generates a random number. |
| SEED | Resets the seed value. |
| STRING | Generates a random string. |
| TERMINATE | Terminates generation of random numbers. |
| VALUE | Generates a random decimal number between 0 and 1, or between specified values. |
Syntax
This section explains each feature of DBMS_RANDOM.
INITIALIZE
INITIALIZE initializes the generation of random numbers using the specified seed value.
For seedVal, specify a SMALLINT or INTEGER type.
Example
PERFORM DBMS_RANDOM.INITIALIZE(999);
NORMAL
NORMAL generates and returns a normally distributed random number.
The return value type is DOUBLE PRECISION.
Example
DECLARE
d_RunNum DOUBLE PRECISION;
BEGIN
d_RunNum := DBMS_RANDOM.NORMAL();
RANDOM
RANDOM generates and returns a random number.
The data type of the return value is INTEGER.
Example
DECLARE
d_RunInt INTEGER;
BEGIN
d_RunInt := DBMS_RANDOM.RANDOM();
SEED
SEED initializes the generation of a random number using the specified seed value or seed string.
For seedVal, specify a SMALLINT or INTEGER type.
Any string can be specified for the seed string.
Example
PERFORM DBMS_RANDOM.SEED('123');
STRING
STRING generates and returns a random string in accordance with the specified display format and string length.
For the display format fmt, specify any of the following values. An error will occur if any other value is specified.
Table 41.11. Values that can be specified for the display format
| Setting value | Generated string |
|---|---|
| 'u', 'U' | Uppercase letters only |
| 'l', 'L' | Lowercase letters only |
| 'a', 'A' | Mixture of uppercase and lowercase letters |
| 'x', 'X' | Uppercase letters and numbers |
| 'p', 'P' | Any displayable character |
Specify the length of the string to be generated. Specify a SMALLINT or INTEGER type.
The data type of the return value is TEXT.
Example
DECLARE
d_RunStr TEXT;
BEGIN
d_RunStr := DBMS_RANDOM.STRING('a', 20);
TERMINATE
Call TERMINATE to terminate generation of random numbers.
Information
TERMINATE does not do anything, but has been included for compatibility with Oracle databases.
Example
PERFORM DBMS_RANDOM.TERMINATE();
VALUE
VALUE generates and returns a random number within the specified range.
For min and max, specify a numeric data type. A random number between and inclusive of the minimum value and maximum value is generated.
If the minimum value and maximum value are omitted, a random decimal number between 0 and 1 will be generated.
The data type of the return value is DOUBLE PRECISION.
Example
DECLARE
d_RunDbl DOUBLE PRECISION;
BEGIN
d_RunDbl := DBMS_RANDOM.VALUE();
A usage example of DBMS_RANDOM is shown below.
CREATE FUNCTION dbms_random_exe() RETURNS VOID AS $$
DECLARE
w_rkey VARCHAR(10) := 'rnd111';
i_rkey INTEGER := 97310;
BEGIN
PERFORM DBMS_RANDOM.INITIALIZE(i_rkey);
RAISE NOTICE 'RANDOM -> NORMAL : %', DBMS_RANDOM.NORMAL();
RAISE NOTICE 'RANDOM -> RANDOM : %', DBMS_RANDOM.RANDOM();
RAISE NOTICE 'RANDOM -> STRING : %', DBMS_RANDOM.STRING('a',10);
RAISE NOTICE 'RANDOM -> VALUE : %', DBMS_RANDOM.VALUE();
PERFORM DBMS_RANDOM.SEED(w_rkey);
RAISE NOTICE 'RANDOM -> NORMAL : %', DBMS_RANDOM.NORMAL();
RAISE NOTICE 'RANDOM -> RANDOM : %', DBMS_RANDOM.RANDOM();
RAISE NOTICE 'RANDOM -> STRING : %', DBMS_RANDOM.STRING('p',10);
RAISE NOTICE 'RANDOM -> VALUE : %', DBMS_RANDOM.VALUE(1,100);
PERFORM DBMS_RANDOM.TERMINATE();
END;
$$ LANGUAGE plpgsql;
SELECT dbms_random_exe();
DROP FUNCTION dbms_random_exe();
Overview
Provides utilities of PL/pgSQL.
Table 41.12. DBMS_UTILITY Features
| Feature | Description |
|---|---|
| FORMAT_CALL_STACK | Returns the current call stack. |
| GET_HASH_VALUE | A hash value based on the input string. |
| CANONICALIZE | Canonicalizes a given string. |
| COMMA_TO_TABLE | Converts a comma-delimited list of names into a PL/SQL table of names. |
| TABLE_TO_COMMA | Converts a PL/SQL table of names into a comma-delimited list of names. |
| DB_VERSION | Returns version information for the database. |
| EXEC_DDL_STATEMENT | Executes the DDL statement in parse_string. |
| GET_TIME | Finds out the current time in hundredths of a second. |
Syntax
FORMAT_CALL_STACK(fmt text);
FORMAT_CALL_STACK();
GET_HASH_VALUE(name VARCHAR2,base INTEGER,hash_size INTEGER)
CANONICALIZE(name TEXT,canon_name TEXT,canon_len INT4)
COMMA_TO_TABLE(list TEXT,tablen INT4,tab TEXT[])
TABLE_TO_COMMA(tab TEXT[],tablen INT4,list TEXT)
DB_VERSION(version TEXT,compatibility TEXT)
EXEC_DDL_STATEMENT(parse_string TEXT)
This section explains each feature of DBMS_UTILITY.
FORMAT_CALL_STACK
FORMAT_CALL_STACK returns the current call stack of PL/pgSQL.
For the display format fmt, specify any of the following values. An error will occur if any other value is specified.
Table 41.13. Values that can be specified for the display format
| Setting value | Displayed content |
|---|---|
| 'o' | Standard-format call stack display (with header) |
| 's' | Standard-format call stack display (without header) |
| 'p' | Comma-delimited call stack display (without header) |
If the display format is omitted, display format 'o' will be used.
The data type of the return value is TEXT.
Example
DECLARE
s_StackTrace TEXT
BEGIN
s_StackTrace := DBMS_UTILITY.FORMAT_CALL_STACK();
Note
If a locale other than English is specified for the message locale, the call stack result may not be retrieved correctly. To correctly retrieve the call stack result, specify English as the message locale.
GET_HASH_VALUE
A hash value based on the input string where the hash value should be between base and base + hash_size -1.
Example
PERFORM DBMS_UTILITY.GET_HASH_VALUE('abcdef',0,10000);
CANONICALIZE
The procedure handles a single reserved or key word (such as 'table'), and strips off white spaces for a single identifier so that ' table ' becomes TABLE.
Example
declare
l_result text := '';
begin
dbms_output.serveroutput(True);
dbms_utility.canonicalize('taBle', l_result, 100);
dbms_output.put_line(l_result);
end;
/
COMMA_TO_TABLE
These procedures converts a comma-delimited list of names into a PL/SQL table of names.
The list must be a non-empty comma-delimited list: Anything other than a comma-delimited list is rejected. Commas inside double quotes do not count.
Example
declare
tablen int4;
tab text[];
begin
dbms_output.serveroutput(True);
dbms_utility.comma_to_table('table, "Table","Table,122324" , Tablesd', tablen, tab);
dbms_output.put_line(tablen);
dbms_output.put_line(array_to_string(tab,','));
end;
/
TABLE_TO_COMMA
This procedure converts a PL/SQL table of names into a comma-delimited list of names.
Example
declare
tablen int4;
l_str text;
begin
dbms_output.serveroutput(True);
dbms_utility.table_to_comma('{table," \"table\"","\"table.1234567890\" "," tablezx",NULL}'::text[], tablen, l_str);
dbms_output.put_line(tablen);
dbms_output.put_line(l_str);
end;
/
DB_VERSION
This procedure returns version information for the database.
Example
declare
lt_version text;
compatibility text;
begin
dbms_output.serveroutput(True);
dbms_utility.db_version(lt_version, compatibility);
dbms_output.put_line(lt_version);
dbms_output.put_line(oracle.nvl(compatibility,'NULL'));
end;
/
EXEC_DDL_STATEMENT
his procedure executes the DDL statement in parse_string.
Example
declare
l_result text := '';
begin
dbms_output.serveroutput(True);
dbms_utility.exec_ddl_statement('create table if not exists public.test_exec_ddl_stmt (id int, name text);');
end;
/
GET_TIME
This function returns a measure of current time in hundredths of a second. The difference between the times returned from two calls measures the total elapsed time (not just CPU processing time) between those two points.
Example
DO $$
DECLARE
start_time integer;
end_time integer;
BEGIN
start_time := DBMS_UTILITY.GET_TIME();
PERFORM pg_sleep(2);
end_time := DBMS_UTILITY.GET_TIME();
-- clamp long runtime on slow build machines to the 2s the testsuite is expecting
IF end_time BETWEEN start_time + 300 AND start_time + 1000 THEN end_time := start_time + 250; END IF;
RAISE NOTICE 'Execution time: % seconds', trunc((end_time - start_time)::numeric/100);
END
$$;
A usage example of DBMS_UTILITY is shown below.
CREATE FUNCTION dbms_utility1_exe() RETURNS VOID AS $$
DECLARE
s_StackTrace TEXT;
v_hashvalue numeric(38,0);
BEGIN
s_StackTrace := DBMS_UTILITY.FORMAT_CALL_STACK();
RAISE NOTICE '%', s_StackTrace;
v_hashvalue := DBMS_UTILITY.GET_HASH_VALUE('abcdef',0,10000);
RAISE NOTICE 'v_hashvalue=%', v_hashvalue;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION dbms_utility2_exe() RETURNS VOID AS $$
BEGIN
PERFORM dbms_utility1_exe();
END;
$$ LANGUAGE plpgsql;
SELECT dbms_utility2_exe();
DROP FUNCTION dbms_utility2_exe();
DROP FUNCTION dbms_utility1_exe();
declare
l_result text := '';
begin
dbms_output.serveroutput(True);
dbms_utility.exec_ddl_statement('create table if not exists public.test_exec_ddl_stmt (id int, name text);');
l_result := dbms_metadata.get_ddl('table', 'test_exec_ddl_stmt');
dbms_output.put_line(l_result);
dbms_utility.exec_ddl_statement(' truncate table public.test_exec_ddl_stmt');
dbms_output.put_line('truncate SUCCESS');
dbms_utility.exec_ddl_statement('drop table if exists public.test_exec_ddl_stmt ; ');
dbms_output.put_line('drop SUCCESS');
end;
/
Overview
Schedules and manages jobs in the lt_cron.
Table 41.14. DBMS_JOB Features
| Feature | Description |
|---|---|
| BROKEN | Disables job execution.(invalid) |
| CHANGE | Alters any of the user-definable parameters associated with a job. |
| INSTANCE | Assigns a job to be run by a instance.(invalid) |
| INTERVAL | Alters the interval between executions for a specified job. |
| NEXT_DATE | Alters the next execution time for a specified job.(invalid) |
| REMOVE | Removes specified job from the job queue. |
| RUN | Forces a specified job to run.(invalid) |
| SUBMIT | Submits a new job to the job queue. |
| USER_EXPORT | Re-creates a given job for export. |
| WHAT | Alters the job description for a specified job. |
Syntax
CHANGE(job INT8,what TEXT,next_date TIMESTAMP,interval TEXT,instance INT4 default null,force BOOL default 'false')
INTERVAL(job INT8,interval TEXT)
REMOVE(job INT8)
SUBMIT(job INT8,what TEXT,next_date TIMESTAMP default sysdate,interval TEXT default 'null',no_parse BOOL default 'false',instance INT4 default null,force BOOL default 'false')
USER_EXPORT(job INT8,mycall TEXT)
WHAT(job INT8,what TEXT)
This section explains each feature of dbms_job.
CHANGE
This procedure changes any of the fields a user can set in a job.
Example
dbms_job.change(l_job_id, 'select 3', sysdate, '0 11 * * *');
INTERVAL
This procedure changes how often a job runs.
he interval parameter format is lt_cron's format.
Example
dbms_job.interval(l_job_id, '0 11 * * *');
REMOVE
This procedure removes an existing job from the job queue.
Example
dbms_job.remove(2);
SUBMIT
This procedure submits a new job.
The job submited by this procedure will not have name. It's suggested to submit by 'select cron.Schedule(name,xxx)'.
Example
dbms_job.submit(l_job_id, 'select 1',sysdate, '0 10 * * *');
USER_EXPORT
Produces the text of a call to re-create the given job.
Example
dbms_job.user_export(2, l_result);
WHAT
This procedure changes what an existing job does.
Example
dbms_job.what(2, 'select 2');
A usage example of DBMS_JOB is shown below.
declare
l_job_id int8;
l_result text := '';
begin
dbms_output.serveroutput(true);
-- use dbms_job.submit will not have jobname
dbms_job.submit(l_job_id, 'select 1',sysdate, '0 10 * * *');
dbms_output.put_line('submit job, job id:'||l_job_id);
dbms_job.user_export(l_job_id, l_result);
dbms_output.put_line('user_export(job id'||l_job_id||'):'||l_result);
-- update can't be used for job without name
dbms_job.what(l_job_id, 'select 2');
dbms_job.user_export(l_job_id, l_result);
dbms_output.put_line('after dbms_job.what user_export(job id:'||l_job_id||'):'||l_result);
dbms_job.interval(l_job_id, '0 11 * * *');
dbms_job.user_export(l_job_id, l_result);
dbms_output.put_line('after dbms_job.interval user_export(job id:'||l_job_id||'):'||l_result);
dbms_job.change(l_job_id, 'select 3', sysdate, '0 11 * * *');
dbms_job.user_export(l_job_id, l_result);
dbms_output.put_line('after dbms_job.change user_export(job id:'||l_job_id||'):'||l_result);
dbms_job.next_date(l_job_id, sysdate);
dbms_job.broken(l_job_id, True);
dbms_job.instance(l_job_id, 10);
dbms_job.run(l_job_id);
dbms_job.remove(l_job_id);
end;
/
Overview
Provides an interface to Lock Management services.
Table 41.15. DBMS_LOCK Features
| Feature | Description |
|---|---|
| sleep | Puts a session to sleep for a specific time. |
Syntax
SLEEP(pi_seconds FLOAT8)
This section explains each feature of dbms_job.
SLEEP
This procedure suspends the session for a specified period of time.
suspends in seconds, to suspend the session.
Example
dbms_lock.sleep(0.1);
Overview
Provides a way for you to retrieve metadata from the database dictionary.
Table 41.16. DBMS_METADATA Features
| Feature | Description |
|---|---|
| get_ddl | Let you fetch metadata for objects. |
Syntax
GET_DDL(object_type VARCHAR,name VARCHAR,schema VARCHAR DEFAULT current_schema(),VARCHAR DEFAULT 'COMPATIBLE',VARCHAR DEFAULT 'ORACLE',VARCHAR DEFAULT 'DDL',BOOL DEFAULT 'false') RETURNS TEXT
This section explains each feature of dbms_job.
GET_DDL
Let you fetch metadata for objects.
Example
select dbms_metadata.get_ddl('constraint', 'key1_check') from dual;
Overview
Enables an application to encrypt data using either the Data Encryption Standard (DES) or the Triple DES algorithms.
Table 41.17. DBMS_OBFUSCATION_TOOLKIT Features
| Feature | Description |
|---|---|
| md5 | Generates MD5 hashes of data. |
Syntax
MD5(input_string TEXT) RETURNS RAW
This section explains each feature of dbms_job.
MD5
Generates MD5 hashes of data.
Example
select dbms_obfuscation_toolkit.md5(input_string => '测试')::text from dual;
Overview
Enables you to refresh snapshots(MVIEW).
Table 41.18. DBMS_SNAPSHOT Features
| Feature | Description |
|---|---|
| refresh | Refreshes a list of snapshots. |
Syntax
REFRESH(list TEXT,method TEXT default 'C')
This section explains each feature of dbms_job.
REFRESH
This procedure refreshes a list of snapshots(MVIEW).
Example
select dbms_snapshot.refresh('mv_test_dbms_snapshot,mv_test_dbms_snapshot1') from dual;
Overview
Provides a way to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL.
Table 41.19. DBMS_SQL Features
| Feature | Description |
|---|---|
| OPEN_CURSOR | Returns cursor ID number of new cursor. |
| PARSE | Parses given statement. |
| EXECUTE | Executes a given cursor. |
| CLOSE_CURSOR | Closes given cursor and frees memory. |
Syntax
FUNCTION OPEN_CURSOR() RETURN INT;
PROCEDURE PARSE(C INT, STMT VARCHAR2);
PROCEDURE PARSE(C INT, STMT DBMS_SQL.VARCHAR2A, LB INT, UB INT, LFFLG BOOL, LANGUAGE_FLAG INT);
FUNCTION EXECUTE(C INT) RETURN BIGINT;
PROCEDURE CLOSE_CURSOR(C INT);
This section explains each feature of DBMS_SQL.
OPEN_CURSOR
Returns cursor ID number of new cursor.
Example
cursor_sql integer;
cursor_sql := dbms_sql.open_cursor;
PARSE
Parses given statement.
Example
vs_sql varchar2(200);
vs_sql:='create or replace function f1(v int) RETURN int AS begin return v + 1; end;';
dbms_sql.parse(cursor_sql,vs_sql);
vc_prosql dbms_sql.varchar2a;
iIndex integer;
iIndex := 1;
vc_prosql(iIndex) := 'create or replace function f1(v int) RETURN int AS ';
iIndex := iIndex + 1;
vc_prosql(iIndex) := 'begin';
iIndex := iIndex + 1;
vc_prosql(iIndex) := ' return v + 1;';
iIndex := iIndex + 1;
vc_prosql(iIndex) := 'end;';
dbms_sql.parse(cursor_sql, vc_prosql, vc_prosql.first, vc_prosql.last, false, dbms_sql.native);
EXECUTE
Executes a given cursor.
Example
vn_ret int;
vn_ret := dbms_sql.execute(cursor_sql);
CLOSE_CURSOR
Closes given cursor and frees memory.
Example
dbms_sql.close_cursor(cursor_sql);
A usage example of DBMS_SQL is shown below.
DECLARE
vc_prosql dbms_sql.varchar2a;
cursor_sql integer;
iIndex integer;
vn_ret integer;
BEGIN
FOR i IN 1..2 LOOP
cursor_sql := dbms_sql.open_cursor;
vc_prosql.DELETE;
iIndex := 1;
vc_prosql(iIndex) := 'create or replace function f' || i || '(v int) RETURN int AS ';
iIndex := iIndex + 1;
vc_prosql(iIndex) := 'begin';
iIndex := iIndex + 1;
vc_prosql(iIndex) := ' return v + ' || i || ';';
iIndex := iIndex + 1;
vc_prosql(iIndex) := 'end;';
for j in 1..vc_prosql.last loop
raise notice '%',vc_prosql(j);
end loop;
raise notice '';
dbms_sql.parse(cursor_sql, vc_prosql, vc_prosql.first, vc_prosql.last, false, dbms_sql.native);
vn_ret := dbms_sql.execute(cursor_sql);
dbms_sql.close_cursor(cursor_sql);
END LOOP;
END;
/
Overview
Enables you to collect optimizer statistics.
Table 41.20. DBMS_STATS Features
| Feature | Description |
|---|---|
| gather_table_stats | Gathers table, column, and index statistics. |
Syntax
GATHER_TABLE_STATS(
ownname TEXT,
tabname TEXT,
partname TEXT DEFAULT NULL,
estimate_percent NUMERIC DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt TEXT DEFAULT NULL,
degree NUMERIC DEFAULT NULL,
granularity TEXT DEFAULT NULL,
cascade BOOLEAN DEFAULT NULL,
stattab TEXT DEFAULT NULL,
statid TEXT DEFAULT NULL,
statown TEXT DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT NULL,
stattype TEXT DEFAULT NULL,
force BOOLEAN DEFAULT FALSE,
context TEXT DEFAULT NULL,
options TEXT DEFAULT NULL
)
This section explains each feature of dbms_stats.
GATHER_TABLE_STATS
This procedure invokes the ANALYZE statement to gather table statistics.
The arguments 'ownname' and 'tabname' must not be null; they are mandatory.
Currently, except for the 'ownname', 'tabname', and 'partname' parameters, which serve specific functions, the rest of the parameters are included for Oracle compatibility and hold no actual functionality.
After successfully executing GATHER_TABLE_STATS, you can query the statistical results using views such as pg_statistic, pg_stat_all_tables, dba_tab_statistics.
Example
begin
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'dbms_gather_test',
tabname => 'dbms_stats_gather_test_1',
degree => 4
);
end;
/
Overview
Text files can be written and read using PL/pgSQL.
To perform these file operations, the directory for the operation target must be registered in the UTL_FILE.UTL_FILE_DIR table beforehand. Use the INSERT statement as the database administrator or a user who has INSERT privileges to register the directory. Also, if the directory is no longer necessary, delete it from the same table. Refer to "Registering and Deleting Directories" for information on the how to register and delete the directory.
Declare the file handler explained hereafter as follows in PL/pgSQL:
DECLARE f UTL_FILE.FILE_TYPE;
Table 41.21. UTL_FILE Features
| Feature | Description |
|---|---|
| FCLOSE | Closes a file. |
| FCLOSE_ALL | Closes all files open in a session. |
| FCOPY | Copies a whole file or a contiguous portion thereof. |
| FFLUSH | Flushes the buffer. |
| FGETATTR | Retrieves the attributes of a file. |
| FOPEN | Opens a file. |
| FREMOVE | Deletes a file. |
| FRENAME | Renames a file. |
| FSEEK | Adjusts the file pointer forward or backward within the file by the number of bytes specified |
| GET_LINE | Reads a line from a text file. |
| IS_OPEN | Checks if a file is open. |
| NEW_LINE | Writes newline characters. |
| PUT | Writes a string. |
| PUT_LINE | Appends a newline character to a string and writes the string. |
| PUTF | Writes a formatted string. |
Syntax
FCLOSE(file utl_file.file_type)
FCLOSE_ALL()
FCOPY(srcDir text, srcFileName text, destDir text, destFileName text)
FCOPY(srcDir text, srcFileName text, destDir text, destFileName text, startLine integer)
FCOPY(srcDir text, srcFileName text, destDir text, destFileName text, startLine integer, endLine integer)
FFLUSH(file utl_file.file_type)
FGETATTR(dir text, fileName text, OUT fexists boolean, OUT file_length bigint, OUT block_size integer)
FOPEN(dir, fileName text, openMode text) return utl_file.file_type
FOPEN(dir, fileName text, openMode text, maxLineSize integer) return utl_file.file_type
FOPEN(dir, fileName text, openMode text, maxLineSize integer, encoding name) return utl_file.file_type
FREMOVE(dir text, fileName text);
FRENAME(src_location text, src_filename text,dest_location text, dest_filename text)
FRENAME(src_location text, src_filename text,dest_location text, dest_filename text, overwrite boolean)
FSEEK(file utl_file.file_type, absolute_offset bigint default null, relative_offset bigint default null)
GET_LINE(file utl_file.file_type, OUT buffer text)
GET_LINE(file utl_file.file_type, OUT buffer text, len integer)
IS_OPEN(file utl_file.file_type) return boolean
NEW_LINE(file utl_file.file_type) return boolean
NEW_LINE(file utl_file.file_type,lines integer) return boolean
PUT(file utl_file.file_type,str text) return boolean
PUT(file utl_file.file_type,str anyelement) return boolean
PUT_LINE(file utl_file.file_type, str text)
PUT_LINE(file utl_file.file_type, str text, autoflush boolean)
PUT_LINE(file utl_file.file_type, str anyelement)
PUT_LINE(file utl_file.file_type, str anyelement, autoflush boolean)
PUTF(file utl_file.file_type, fmt ) return boolean
PUTF(file utl_file.file_type, fmt, arguments1 ) return boolean
PUTF(file utl_file.file_type, fmt, arguments1, arguments2) return boolean
PUTF(file utl_file.file_type, fmt, arguments1, arguments2, arguments3) return boolean
PUTF(file utl_file.file_type, fmt, arguments1, arguments2, arguments3, arguments4) return boolean
PUTF(file utl_file.file_type, fmt, arguments1, arguments2, arguments3, arguments4, arguments5) return boolean
Registering the directory
1. Check if the directory is already registered (if it is, then step 2 is not necessary).
SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/pgsql';
2. Register the directory.
INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/pgsql');
Deleting the directory
DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/lightdb';
This section explains each feature of UTL_FILE.
FCLOSE
FCLOSE closes a file that is open.
Specify an open file handle.
Example
UTL_FILE.FCLOSE(f);
FCLOSE_ALL
FCLOSE_ALL closes all files open in a session.
Files closed with FCLOSE_ALL can no longer be read or written.
Example
PERFORM UTL_FILE.FCLOSE_ALL();
FCOPY
FCOPY copies a whole file or a contiguous portion thereof. The whole file is copied if startLine and endLine are not specified.
Specify the directory location of the source file.
Specify the source file.
Specify the directory where the destination file will be created.
Specify the name of the destination file.
Specify the line number at which to begin copying. Specify a value greater than 0. If not specified, 1 is used.
Specify the line number at which to stop copying. If not specified, the last line number of the file is used.
Example
PERFORM UTL_FILE.FCOPY('/home/lightdb', 'regress_ltsql.txt', '/home/lightdb', 'regress_ltsql2.txt');
FFLUSH
FFLUSH forcibly writes the buffer data to a file.
Specify an open file handle.
Example
PERFORM UTL_FILE.FFLUSH(f);
FGETATTR
FGETATTR retrieves file attributes: file existence, file size, and information about the block size of the file.
Specify the directory where the file exists.
Specify the relevant file name.
A BOOLEAN for whether or not the file exists. If the file exists, fexists is set to TRUE. If the file does not exist, fexists is set to FALSE. The data type of fexists is BOOLEAN.
Length of the file in bytes. NULL if file does not exist. The data type of file_length is INTEGER.
File system block size in bytes. NULL if the file does not exist. The data type of blocksize is INTEGER.
Example
declare
f utl_file.file_type;
fexists boolean;
file_length integer;
blocksize integer;
begin
utl_file.fgetattr('DATA','LT_VERSION',fexists, file_length, blocksize);
IF fexists then
DBMS_OUTPUT.PUT_LINE('fexists IS: true');
ELSE
DBMS_OUTPUT.PUT_LINE('fexists IS: false');
END IF;
DBMS_OUTPUT.PUT_LINE('file_length IS:'||nvl(to_char(file_length),'[NULL]'));
DBMS_OUTPUT.PUT_LINE('blocksize IS:'||nvl(to_char(blocksize),'[NULL]'));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE IS:'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM IS:'||SQLERRM);
end;
/
FOPEN
FOPEN opens a file.
Specify the directory where the file exists.
Specify the file name.
Specify the mode for opening the file:
r: Read
w: Write
a: Add
Specify the maximum string length (in bytes) that can be processed with one operation. If omitted, the default is 1024. Specify a value from 1 to 32767.
Up to 50 files per session can be open at the same time.
Example
f := UTL_FILE.FOPEN('/home/pgsql','regress_pgsql.txt','r',1024);
FREMOVE
FREMOVE deletes a file.
Specify the directory where the file exists.
Specify the file name.
Example
PERFORM UTL_FILE.FREMOVE('/home/lightdb', 'regress_ltsql.txt');
FRENAME
FRENAME renames a file.
Specify the directory location of the source file.
Specify the source file to be renamed.
Specify the directory where the renamed file will be created.
Specify the new name of the file.
Specify whether to overwrite a file if one exists with the same name and in the same location as the renamed file. If TRUE is specified, the existing file will be overwritten. If FALSE is specified, an error occurs. If omitted, FALSE is set.
See
Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.
Example
UTL_FILE.FRENAME('DATA', 'regress_ltsql.txt', 'DATA', 'regress_ltsql2.txt', TRUE);
FSEEK
FSEEK adjusts the file pointer forward or backward within the file by the number of bytes specified.
Specify an open file handle.
Specify the absolute location to which to seek; default = NULL.
Specify the number of bytes to seek forward or backward; positive = forward, negative integer = backward, zero = current position, default = NULL
If the beginning of the file is reached before the number of bytes specified, then the file pointer is placed at the beginning of the file. If the end of the file is reached before the number of bytes specified, then an INVALID_OFFSET error is raised.
If absolute_offset, the procedure seeks to an absolute location specified in bytes.
See
Example
UTL_FILE.fseek(filehandle, 0);
GET_LINE
GET_LINE reads a line from a file.
Specify the file handle returned by FOPEN using r (read) mode.
Specify the number of bytes to read from the file. If not specified, the maximum string length specified at FOPEN will be used.
Specify the Data buffer to receive the line read from the file.
Newline characters are not loaded to the buffer.
NULL is returned if a blank line is loaded.
Specify the maximum length (in bytes) of the data to be read. Specify a value from 1 to 32767. If not specified, the maximum string length specified at FOPEN is set. If no maximum string length is specified at FOPEN, 1024 is set.
If the line length is greater than the specified number of bytes to read, the remainder of the line is read on the next call.
A NO_DATA_FOUND exception will occur when trying to read past the last line.
Example
UTL_FILE.GET_LINE(f,buff);
IS_OPEN
IS_OPEN checks if a file is open.
Specify the file handle.
The return value is a BOOLEAN type. TRUE represents an open state and FALSE represents a closed state.
See
Refer to "The SQL Language" > "Data Types" > "Boolean Type" in the LightDB Documentation for information on boolean type (TRUE/FALSE) values.
Example
IF UTL_FILE.IS_OPEN(f) THEN
UTL_FILE.FCLOSE(f);
END IF;
NEW_LINE
NEW_LINE writes one or more newline characters.
Specify an open file handle.
Specify the number of newline characters to be written to the file. If omitted, "1" is used.
Example
PERFORM UTL_FILE.NEW_LINE(f, 2);
PUT
PUT writes a string to a file.
Specify the file handle that was opened with FOPEN using w (write) or a (append).
Specify the string to be written to the file.
The maximum length (in bytes) of the string to be written is the maximum string length specified at FOPEN.
PUT does not append a newline character. To append a newline character, execute NEW_LINE.
Example
PERFORM UTL_FILE.PUT(f, 'ABC');
PUT_LINE
PUT_LINE appends a newline character to a string and writes the string.
Specify the file handle that was opened with FOPEN w (write) or a (append).
Specify whether to forcibly write to the file. If TRUE is specified, file writing is forced. If FALSE is specified, file writing is asynchronous. If omitted, FALSE will be set.
The maximum length of the string (in bytes) is the maximum string length specified at FOPEN.
Example
UTL_FILE.PUT_LINE(f, 'ABC', TRUE);
PUTF
PUTF writes a formatted string.
Specify the file handle that was opened with FOPEN w (write) or a (append).
Specify the format, which is a string that includes the formatting characters \n and %s.
The \n in the format is code for a newline character.
Specify the same number of input values as there are %s in the format. Up to a maximum of five input values can be specified. The %s in the format are replaced with the corresponding input characters. If an input value corresponding to %s is not specified, it is replaced with an empty string.
Example
PERFORM UTL_FILE.PUTF(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]\n', '1', '2', '3', '4', '5');
The procedure when using UTL_FILE, and a usage example, are shown below.
1. Preparation
Before starting a new job that uses UTL_FILE, register the directory in the UTL_FILE.UTL_FILE_DIR table.
Refer to "Registering and Deleting Directories" for information on how to register the directory.
2. Performing a job
Perform a job that uses UTL_FILE. The example is shown below.
CREATE OR REPLACE procedure gen_file(mydir TEXT, infile TEXT, outfile TEXT, copyfile TEXT) as
v1 VARCHAR(32767);
inf UTL_FILE.FILE_TYPE;
otf UTL_FILE.FILE_TYPE;
BEGIN
inf := UTL_FILE.FOPEN(mydir, infile,'r',256);
otf := UTL_FILE.FOPEN(mydir, outfile,'w');
UTL_FILE.GET_LINE(inf, v1, 256);
UTL_FILE.PUT_LINE(otf,v1,TRUE);
UTL_FILE.GET_LINE(inf, v1, 256);
PERFORM UTL_FILE.PUTF(otf,'%s\n',v1);
UTL_FILE.GET_LINE(inf, v1, 256);
PERFORM UTL_FILE.PUT(otf,v1);
PERFORM UTL_FILE.NEW_LINE(otf);
PERFORM UTL_FILE.FFLUSH(otf);
UTL_FILE.FCLOSE(inf);
UTL_FILE.FCLOSE(otf);
PERFORM UTL_FILE.FCOPY(mydir, outfile, mydir, copyfile, 2, 3);
UTL_FILE.FRENAME(mydir, outfile, mydir, 'rename.txt');
END;
/
BEGIN
gen_file('DATA', 'input.txt', 'output.txt', 'copyfile.txt');
END;
/
3. Post-processing
If you remove a job that uses UTL_FILE, delete the directory information from the UTL_FILE.UTL_FILE_DIR table. Ensure that the directory information is not being used by another job before deleting it.
Refer to "Registering and Deleting Directories" for information on how to delete the directory.
Overview
Provides SQL functions for manipulating RAW datatypes.
Table 41.22. UTL_RAW Features
| Feature | Description |
|---|---|
| BIT_AND | Performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW. |
| BIT_COMPLEMENT | Performs bitwise logical "complement" of the values in RAW r and returns the "complement'ed" result RAW. |
| BIT_OR | Performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the "or'd" result RAW. |
| BIT_XOR | Performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the "xor'd" result RAW. |
| CAST_FROM_BINARY_INTEGER | Returns the RAW binary representation of a BINARY_INTEGER value. |
| CAST_FROM_NUMBER | Returns the RAW binary representation of a NUMBER value. |
| CAST_TO_BINARY_INTEGER | Casts the RAW binary representation of a BINARY_INTEGER into a BINARY_INTEGER. |
| CAST_TO_NUMBER | Casts the RAW binary representation of a NUMBER into a NUMBER. |
| CAST_TO_RAW | Converts a text value into a RAW value. |
| CAST_TO_VARCHAR2 | Converts a RAW value into a text value. |
| COMPARE | Compares RAW r1 against RAW r2. |
| CONCAT | Concatenates up to 12 RAWs into a single RAW. |
| CONVERT | Converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW. |
| COPIES | Returns n copies of r concatenated together. |
| LENGTH | Returns the length in bytes of a RAW r. |
| OVERLAY | Overlays the specified portion of target RAW with overlay RAW, starting from byte position pos of target and proceeding for len bytes. |
| REVERSE | Reverses a byte sequence in RAW r from end to end. |
| TRANSLITERATE | Translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set. |
| TRANSLATE | Converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set. |
| XRANGE | Reverses a byte sequence in RAW r from end to end. |
| REVERSE | Returns a RAW containing all valid 1-byte encodings in succession, beginning with the value start_byte and ending with the value end_byte. |
Syntax
CAST_TO_VARCHAR2(r raw) RETURNS TEXT
CAST_TO_RAW(c TEXT) RETURNS RAW
CONCATr1 RAW,r2 RAW,r3 RAW,r4 RAW,r5 RAW,r6 RAW,r7 RAW,r8 RAW,r9 RAW,r10 RAW,r11 RAW,r12 RAW) RETURNS RAW
LENGTH(r RAW) RETURNS INT4
SUBSTR(r RAW,pos INT4,len INT4 DEFAULT null) RETURNS RAW
TRANSLITERATE(r RAW,to_set RAW DEFAULT ''::raw ,from_set RAW DEFAULT ''::raw,pad RAW DEFAULT '00'::raw) RETURNS RAW
TRANSLATE(r RAW,from_set RAW,to_set RAW) RETURNS RAW
COPIES(r RAW,n INT8) RETURNS RAW
OVERLAY(overlay_str RAW,target RAW,pos INT4 DEFAULT 1,len INT4 DEFAULT NULL,pad RAW DEFAULT '00'::raw) RETURNS RAW
XRANGE(start_byte RAW,end_byte RAW) RETURNS RAW
REVERSE(r RAW) RETURNS RAW
COMPARE(r1 RAW,r2 RAW,pad RAW) RETURNS INT4
CONVERT(r RAW,to_charset TEXT,from_charset TEXT) RETURNS RAW
BIT_AND(r1 RAW,r2 RAW) RETURNS RAW
BIT_OR(r1 RAW,r2 RAW) RETURNS RAW
BIT_XOR(r1 RAW,r2 RAW) RETURNS RAW
BIT_COMPLEMENT(r RAW) RETURNS RAW
CAST_TO_NUMBER(r RAW)RETURNS NUMERIC
CAST_FROM_NUMBER(n NUMERIC) RETURNS RAW
CAST_TO_BINARY_INTEGER(r RAW,endianess INT)RETURNS INT4
CAST_FROM_BINARY_INTEGER(n INT4,endianess INT) RETURNS RAW
This section explains each feature of dbms_job.
CAST_TO_VARCHAR2
This function converts a RAW value represented using some number of data bytes into a text value with that number of data bytes.
Example
select utl_raw.cast_to_varchar2('43616D65726F6E') from dual;
CAST_TO_RAW
This function converts a text value represented using some number of data bytes into a RAW value with that number of data bytes. The data itself is not modified in any way, but its datatype is recast to a RAW datatype.
Example
select utl_raw.cast_to_raw('测试') from dual;
CONCAT
This function concatenates up to 12 RAWs into a single RAW.
Example
select utl_raw.concat( '1', '0102', 'f', '1a2b' ) from dual;
LENGTH
This function returns the length in bytes of a RAW r.
Example
select UTL_RAW.length('FFFF'::raw) from dual;
SUBSTR
If pos is positive, then SUBSTR counts from the beginning of r to find the first byte. If pos is negative, then SUBSTR counts backward from the end of the r. The value pos can be 0.
Example
select utl_raw.substr( '0102030405', 3, 2 ) from dual;
TRANSLITERATE
This function converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set. Successive bytes in r are looked up in the from_set, and, if not found, copied unaltered to the result RAW. If found, then they are replaced in the result RAW by either corresponding bytes in the to_set, or the pad byte when no correspondence exists.
Example
select utl_raw.transliterate( '010203040502', '0709', '01020304', 'ff' ) from dual;
TRANSLATE
This function translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set. If a byte in r has a matching byte in from_set, then it is replaced by the byte in the corresponding position in to_set, or deleted.
Example
select utl_raw.translate( '0102030405', '0304', '09' ) from dual;
COPIES
This function returns n copies of r concatenated together.
Example
select utl_raw.copies( '010203', 3 ) from dual;
OVERLAY
This function overlays the specified portion of target RAW with overlay_str RAW, starting from byte position pos of target and proceeding for len bytes.
If overlay_str has less than len bytes, then it is extended to len bytes using the pad byte. If overlay_str exceeds len bytes, then the extra bytes in overlay_str are ignored. If len bytes beginning at position pos of target exceeds the length of target, then target is extended to contain the entire length of overlay_str.
Example
select utl_raw.overlay( 'aabb', '010203', 5, 1, 'FF' ) from dual;
XRANGE
This function returns a RAW value containing the succession of one-byte encodings beginning and ending with the specified byte-codes.
If the start_byte value is greater than the end_byte value, then the succession of resulting bytes begins with start_byte, wraps through x'FF' back to x'00', then ends at end_byte.
Example
select utl_raw.xrange( 'FA', '01' ) from dual;
REVERSE
This function reverses a byte sequence in RAW r from end to end. For example, x'0102F3' would be reversed to x'F30201', and 'xyz' would be reversed to 'zyx'. The result length is the same as the input RAW length.
Example
select utl_raw.reverse( '010203040506070809' ) from dual;
COMPARE
This function compares two RAW values. If they differ in length, then the shorter is extended on the right according to the optional pad parameter.
Example
select utl_raw.compare( '010203', '01020304', '04' ) from dual;
CONVERT
This function converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW.
Both from_charset and to_charset must be supported character sets defined to the LightDB server.
Example
select utl_raw.convert(rawout('测试'::BYTEA)::text::raw,'GBK','UTF8') from dual;
BIT_AND
This function performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW. It is a 'strict' function.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).
Example
select utl_raw.bit_and('1234ffdd','fff1234f') from dual;
BIT_OR
This function performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the or'd result RAW. It is a 'strict' function.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).
Example
select utl_raw.bit_or('1234ffdd','fff1234f') from dual;
BIT_XOR
This function performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the xor'd result RAW. It is a 'strict' function.
If r1 and r2 differ in length, the shorter of the two RAWs will extend to same length by add '00'. (it is not same with oracle).
Example
select utl_raw.bit_xor('1234ffdd','1234ffee') from dual;
BIT_COMPLEMENT
This function performs bitwise logical "complement" of the values in RAW r and returns the complement'ed result RAW. The result length equals the input RAW r length. It is a 'strict' function.
Example
select UTL_raw.bit_complement('1122FF') from dual;
CAST_TO_NUMBER
This function casts the RAW binary representation of a NUMBER into a NUMBER.
Example
select utl_raw.cast_TO_number('C10215') from dual;
CAST_FROM_NUMBER
This function returns the RAW binary representation of a NUMBER value.
Example
select UTL_raw.cast_from_number(1.2) from dual;
CAST_TO_BINARY_INTEGER
This function casts the RAW binary representation of a INTEGER into a INTEGER.(oracle is BINARY_INTEGER)
Example
select utl_raw.cast_to_binary_integer('FF00') from dual;
CAST_FROM_BINARY_INTEGER
This function returns the RAW binary representation of a INTEGER value.(oracle is BINARY_INTEGER)
Example
select utl_raw.cast_from_binary_integer(65280) from dual;
Overview
Has two functions that provide escape and unescape mechanisms for URL characters.
Table 41.23. UTL_URL Features
| Feature | Description |
|---|---|
| ESCAPE | Returns a URL with illegal characters (and optionally reserved characters) escaped using the %2-digit-hex-code format. |
| UNESCAPE | Unescapes the escape character sequences to their original forms in a URL. Convert the %XX escape character sequences to the original characters. |
Syntax
ESCAPE(url TEXT,escape_reserved_chars BOOL DEFAULT FALSE,url_charset TEXT DEFAULT 'UTF8') RETURNS TEXT
UNESCAPE(url TEXT,url_charset TEXT DEFAULT 'UTF8') RETURNS TEXT
This section explains each feature of dbms_job.
ESCAPE
This function returns a URL with illegal characters (and optionally reserved characters) escaped using the %2-digit-hex-code format.
Example
select utl_url.escape('http://新年好.com') from dual;
UNESCAPE
This function unescapes the escape character sequences to its original form in a URL, to convert the %XX escape character sequences to the original characters.
Example
select utl_url.unescape('http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com', 'utf8') from dual;
Overview
Provides functions that encode RAW data into a standard encoded format.
Table 41.24. UTL_ENCODE Features
| Feature | Description |
|---|---|
| BASE64_DECODE | Reads the base 64-encoded RAW input string and decodes it to its original RAW value. |
| BASE64_ENCODE | Encodes the binary representation of the RAW value into base 64 elements and returns it in the form of a RAW string. |
| MIMEHEADER_DECODE | Decodes a string from mime header format. |
| MIMEHEADER_ENCODE | Encodes a string into mime header format. |
| QUOTED_PRINTABLE_DECODE | Reads the text quoted printable format input string and decodes it to the corresponding RAW string. |
| QUOTED_PRINTABLE_ENCODE | Reads the RAW input string and encodes it to the corresponding quoted printable format string. |
| TEXT_DECODE | Decodes a character set sensitive text string. |
| TEXT_ENCODE | Encodes a character set sensitive text string. |
| UUDECODE | Reads the RAW uuencode format input string and decodes it to the corresponding RAW string. |
| UUDECODE | Reads the RAW input string and encodes it to the corresponding uuencode format string. |
Syntax
BASE64_DECODE(r RAW) RETURNS RAW
BASE64_ENCODE(r RAW) RETURNS RAW
MIMEHEADER_DECODE(buf TEXT) RETURNS TEXT
MIMEHEADER_ENCODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
QUOTED_PRINTABLE_DECODE(r RAW) RETURNS RAW
QUOTED_PRINTABLE_ENCODE(r RAW) RETURNS RAW
TEXT_DECODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
TEXT_ENCODE(buf TEXT,encode_charset TEXT default 'UTF8'::text,encoding INT4 default 2::int4) RETURNS TEXT
UUDECODE(r RAW) RETURNS RAW
UUENCODE(r RAW,type INT default 1::int,filename TEXT default 'uuencode.txt',permission TEXT default '0') RETURNS RAW
This section explains each feature of dbms_job.
BASE64_DECODE
This function reads the base 64-encoded RAW input string and decodes it to its original RAW value.
Example
select utl_encode.base64_decode('3572574C364B2B56') from dual;
BASE64_ENCODE
This function encodes the binary representation of the RAW value into base 64 elements and returns it in the form of a RAW string.
Example
select utl_encode.base64_encode(rawtohex('测试')::raw) from dual;
MIMEHEADER_DECODE
Decodes a string from mime header format('=?<charset> ?<encoding> ?<encoded text>?= ').
Example
select UTL_ENCODE.mimeheader_decode('=?UTF-8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?=') from dual;
MIMEHEADER_ENCODE
This function produces as output an "encoded word" of the form('=?<charset> ?<encoding> ?<encoded text>?= ').
The ENCODING input parameter accepts as valid values UTL_ENCODE.QUOTED_PRINTABLE or UTL_ENCODE.BASE64 or NULL. If NULL, quoted-printable encoding is selected as a default value.
The <charset> value is specified as the input parameter encode_charset.
Example
select utl_encode.MIMEHEADER_ENCODE('What is the date 王 =20 / \?', encode_charset =>'UTF8', encoding => 1 ) from dual;
QUOTED_PRINTABLE_DECODE
This function reads the raw quoted printable format input string and decodes it to the corresponding RAW string.
Example
select utl_encode.quoted_printable_decode('3D45353D42453D3830313233345C61732064663D33444142') from dual;
QUOTED_PRINTABLE_ENCODE
This function reads the RAW input string and encodes it to the corresponding quoted printable format string.
Example
select utl_encode.quoted_printable_decode('3D45353D42453D3830313233345C61732064663D33444142') from dual;
TEXT_DECODE
This function converts the input text to the target character set as specified by the encode_charset parameter, if not NULL. The encoded text is converted to the base character set of database.
You can decode from either quoted-printable or base64 format, with regard to each encoding parameter. 1 for base64, 2 for quoted-printable, default is quoted-printable format.
Example
select utl_encode.text_decode('=CD=F91234\as df=3DAB',encode_charset => 'GBK',encoding => 2) from dual;
TEXT_ENCODE
This function converts the input text to the target character set as specified by the encode_charset parameter, if not NULL. The text is encoded to either base64 or quoted-printable format, as specified by the encoding parameter.
Example
select utl_encode.text_encode('往12\as df=AB',encode_charset => 'GBK', encoding => 1) from dual;
UUDECODE
This function reads the RAW uuencode format input string and decodes it to the corresponding RAW string.
Example
select UTL_ENCODE.uudecode(rawtohex(',6[C9&5FA$R,S0`')::raw) from dual;
UUENCODE
This function reads the RAW input string and encodes it to the corresponding uuencode format string.
Example
select UTL_ENCODE.uuencode('5BB8D91959A113233340'::raw) from dual;