whale

Introduction

whale is an Oracle compatibility extension package of MogDB, in which 15 Oracle functions and 7 packages are added. The functions include instrb, nls_charset_id, nls_charset_name, nls_lower, etc. The oracle packages include dbms_random, dbms_output, dbms_lock, dbms_application_info, dbms_metadata, dbms_job, and dbms_utility.

Note: The whale extension cannot be together used with the orafce extension. This issue will be resolved in the subsequent patch version.

whale Installation

For details, see gs_install_plugin or gs_install_plugin_local.

whale Usage

Create the whale extension.

CREATE EXTENSION whale;

Note: After the extension is created and submitted, all existing connections that need to be reconnected can find whale functions without specifying the whale schema.

Function

The Oracle packages currently supported include instrb, nls_charset_id, nls_charset_name, nls_lower, nls_upper, ora_hash, remainder, replace, show, show_parameter, to_timestamp, to_yminterval, tz_offset, nullif, and ratio_to_report.

Package

Functions commonly used by users are stored in packages. To call certain functions, you can only specify a package to use them.

Package is usually used in stored procedure. The following introduces dbms_random, dbms_output, dbms_lock, dbms_application_info, dbms_metadata, dbms_job, and dbms_utility.

DBMS_RANDOM

DBMS_RANDOM provides a built-in random data generator, including the following built-in interfaces:

• DBMS_RANDOM.INITIALIZE(val IN BINARY_INTEGER): initializes a package.

  select DBMS_RANDOM.INITIALIZE(101);

• DBMS_RANDOM.NORMAL: returns random data in standard normal distribution.

  select DBMS_RANDOM.NORMAL();

• DBMS_RANDOM.RANDOM: generates and returns a random integer ranging from -2^31 to 2^31.

  select DBMS_RANDOM.RANDOM();s

• DBMS_RANDOM.SEED: generates the seed of random data.

  select DBMS_RANDOM.SEED(1);

• DBMS_RANDOM.STRING(opt IN CHAR,len IN NUMBER)RETURN VARCHAR2: generates and returns a random string.

  select DBMS_RANDOM.STRING('A', 10);
  select DBMS_RANDOM.STRING('x', 10);

  This function generates a random string in a specified mode. The optional modes are as follows:

  'u' or 'U': returns only uppercase letters. 'l' or 'L': returns only lowercase letters. 'a' or 'A': returns a mixed string with uppercase and lowercase letters. 'x' or 'X': returns a mixed string of uppercase letters and digits. 'p' or 'P': returns any string that can be displayed.

• DBMS_RANDOM.TERMINATE: This function has been discarded. It is supported but you are not advised to use it. It will be called after a package is finished.

• DBMS_RANDOM.VALUE RETURN NUMBER: returns random data greater than or equal to 0 and less than 1. There are 15 places to the right of the decimal point.

DBMS_OUTPUT

DBMS_OUTPUT allows you to send information from a stored procedure, package, and trigger. DBMS_OUTPUT includes the following built-in interfaces:

Note:

• DBMS_OUTPUT applies to only stored procedures and does not apply to gsql.

• set serveroutput on is not supported.

• set serveroutput off is not supported.

• DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000): The greatest value of buff_size is 1000000 and the smallest value is 2000. Before using DBMS_OUTPUT, DBMS_OUTPUT.ENABLE must be executed.

• DBMS_OUTPUT.GET_LINE(line INOUT text, status INOUT INTEGER): retrieves row arrays from the cache area.

• DBMS_OUTPUT.GET_LINES(lines INOUT text[], numlines INOUT INTEGER): retrieves row arrays from the cache area.

  Note: After executing DBMS_OUTPUT.GET_LINE and DBMS_OUTPUT.GET_LINES, clear the cache area.

• DBMS_OUTPUT.NEW_LINE: puts a space.

• DBMS_OUTPUT.PUT(item IN VARCHAR2): puts some rows in the cache area.

• DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2): puts one row in the cache area.

• DBMS_OUTPUT.disable (): clears the requested space.

• DBMS_OUTPUT.data type: DBMS_OUTPUT package includes a built-in CHARARR data type. You can call DBMS_OUTPUT.CHARARR to use the data type. DBMS_OUTPUT package does not support the DBMSOUTPUT_LINESARRAY data type.

DBMS_LOCK

DBMS_LOCK provides an interface for the Oracle lock management service.

dbms_lock.sleep:pg_sleep()

DBMS_APPLICATION_INFO

DBMS_APPLICATION_INFO is used for recording the name of a module or transaction being executed in a database so that the module performance can be traced and the module can be used in debugging.

DBMS_APPLICATION_INFO includes the following built-in interfaces:

• DBMS_APPLICATION_INFO.READ_CLIENT_INFO (client_info OUT VARCHAR2): reads the value of client_infor of the current session.

• DBMS_APPLICATION_INFO.READ_MODULE (module_name OUT VARCHAR2, action_name OUT VARCHAR2): reads the module and the value of the related operation field of the current session.

• DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info IN VARCHAR2): provides additional information related to client applications.

• DBMS_APPLICATION_INFO.SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2): sets the name of the current application or module.

• DBMS_APPLICATION_INFO.SET_ACTION (action_name IN VARCHAR2): sets the name of the current action under the current module.

DBMS_METADATA

DBMS_METADATA provides a method of creating and submitting a XML file including metadata retrieved from the database dictionary or creating DDL to recreate an object.

dbms_metadata.get_ddl()

DBMS_JOB

DBMS_JOB calls and manages jobs in a job queue.

DBMS_JOB includes the following built-in interfaces:

• DBMS_JOB.BROKEN( job int8, broken bool, next_date timestamp default sysdate): sets the break marker. Broken indicates whether to terminate a job. To terminate a job, set its status (job_status) to d.

• DBMS_JOB.CHANGE( job int8, what text, next_date timestamp, "interval" text, instance int4 default null, force bool default false): modifies any fields that can be set in a job.

  • At least one variable of what, next_date, and interval of CHANGE is not empty. If the parameter is not empty, it will not be modified.
  • instance: is useless in dbms_job.
  • force: is useless in dbms_job.

• DBMS_JOB.INSTANCE( job int8, instance int4, force bool default false)(): does not perform any operation.

• DBMS_JOB.INTERVAL( job int8, "interval" text): modifies the running frequency of a job.

• DBMS_JOB.NEXT_DATE( job int8, next_date timestamp): modifies the running time of a job next time.

  Note: next_date is usually set to a time later than the current system time. If it is set to the current system time, the task will be executed at once and next_date will be set to the current system time plus interval.

• DBMS_JOB.REMOVE(job int8): deletes a job from a job queue.

• DBMS_JOB.RUN( job int8, force boolean default false): runs a job. The force parameter is useless.

• DBMS_JOB.SUBMIT( job out int8, what in text, next_date in timestamp default sysdate, "interval" in text default null, no_parse in bool default false, instance in int4 default null, force in bool default false): submits a new job. Parameters no_parse, instance, and force are not used. To use them, a prompt will be displayed.

• DBMS_JOB.USER_EXPORT(job IN int8, mycall OUT text): generates a call text to recreate a specified job.

• DBMS_JOB.WHAT( job int8, what text): modifies the job function and updates its environment. If what exists, an error will be reported.

DBMS_UTILITY

DBMS_UTILITY processes and calculates data types. It includes the following built-in interfaces:

• DBMS_UTILITY.CANONICALIZE( name IN text, canon_name OUT text, canon_len IN int4): standardizes a given string. It processes a single reserved word or keyword, such as table, and clear the space of a single marker so that table will be changed to TABLE.

• DBMS_UTILITY.COMMA_TO_TABLE( list IN text, tablen OUT int4, tab OUT text[]): replaces the list of names separated with commas with the PL/SQL table of the names.

• DBMS_UTILITY.TABLE_TO_COMMA(tab IN text[], tablen OUT int4, val OUT text): converts the PL/SQL table of the names to the list of the names separated with commas.

• DBMS_UTILITY.DB_VERSION(INOUT version text, INOUT compatibility text): returns the database version.

• DBMS_UTILITY.EXEC_DDL_STATEMENT(IN parse_string text): executes the DDL statements in parse_string.

The DBMS_UTILITY package supports the INSTANCE_RECORD, DBLINK_ARRAY, INDEX_TABLE_TYPE, INSTANCE_TABLE, LNAME_ARRAY, NAME_ARRAY, NUMBER_ARRAY, and UNCL_ARRAY data types.

Example

Function

• INSTRB

  MogDB=# select INSTRB('123456123', '123', 4);
   instrb 
  --------
        7
  (1 row)

• NLS_CHARSET_ID

  MogDB=# SELECT NLS_CHARSET_ID('gbk');
   nls_charset_id 
  ----------------
                6
  (1 row)

• dbms_random provides built-in random-number generator.

  MogDB=# select DBMS_RANDOM.VALUE(1, '100');
        value       
  ------------------
   92.4730090592057

package

The following uses dbms_random as an example.

MogDB=# select DBMS_RANDOM.VALUE();
       value       
-------------------
 0.482205999083817
(1 row)