PKG_SERVICE

Table 1 lists all APIs supported by PKG_SERVICE.

• PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE

  This function checks whether a context is registered. This function transfers the ID of the context to be queried. If the context exists, TRUE is returned. Otherwise, FALSE is returned.

  The prototype of the PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE function is as follows:

  1 2 3 4

  PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE( context_id IN INTEGER ) RETURN BOOLEAN;

  Table 2 PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE parameters

  Parameter	Description
  context_id	ID of the context to be queried.

• PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS

  This function cancels all contexts.

  The prototype of the PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS function is as follows:

  1 2 3

  PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS( ) RETURN VOID;

• PKG_SERVICE.SQL_REGISTER_CONTEXT

  This function opens a context, which is the prerequisite for the subsequent operations in the context. This function does not transfer any parameter. It automatically generates context IDs in an ascending order and returns values to INTEGER variables.

  The prototype of the PKG_SERVICE.SQL_REGISTER_CONTEXT function is as follows:

  1 2 3

  DBE_SQL.REGISTER_CONTEXT( ) RETURN INTEGER;

• PKG_SERVICE.SQL_UNREGISTER_CONTEXT

  This function closes a context, which is the end of each operation in the context. If this function is not called when the stored procedure ends, the memory is still occupied by the context. Therefore, remember to close a context when you do not need to use it. If an exception occurs, the stored procedure exits but the context is not closed. Therefore, you are advised to include this API in the exception handling of the stored procedure.

  The prototype of the PKG_SERVICE.SQL_UNREGISTER_CONTEXT function is as follows:

  1 2 3 4

  PKG_SERVICE.SQL_UNREGISTER_CONTEXT( context_id IN INTEGER ) RETURN INTEGER;

  Table 3 PKG_SERVICE.SQL_UNREGISTER_CONTEXT parameters

  Parameter	Description
  context_id	ID of the context to be closed.

• PKG_SERVICE.SQL_SET_SQL

  This function parses the query statement of a given context. The input query statement is executed immediately. Currently, only the SELECT query statement can be parsed. The statement parameters can be transferred only through the TEXT type. The length cannot exceed 1 GB.

  The prototype of the PKG_SERVICE.SQL_SET_SQL function is as follows:

  1 2 3 4 5 6

  PKG_SERVICE.SQL_SET_SQL( context_id IN INTEGER, query_string IN TEXT, language_flag IN INTEGER ) RETURN BOOLEAN;

  Table 4 PKG_SERVICE.SQL_SET_SQL parameters

  Parameter	Description
  context_id	ID of the context whose query statement is to be parsed.
  query_string	Query statement to be parsed.
  language_flag	Version language number, which specifies the behavior of different versions. 1: incompatible version. 2: version in ORA-compatible mode.

• PKG_SERVICE.SQL_RUN

  This function executes a given context. It receives a context ID first, and the data obtained after execution is used for subsequent operations. Currently, only the SELECT query statement can be executed.

  The prototype of the PKG_SERVICE.SQL_RUN function is as follows:

  1 2 3 4

  PKG_SERVICE.SQL_RUN( context_id IN INTEGER, ) RETURN INTEGER;

  Table 5 PKG_SERVICE.SQL_RUN parameters

  Parameter	Description
  context_id	ID of the context whose query statement is to be parsed.

• PKG_SERVICE.SQL_NEXT_ROW

  This function returns the number of data rows returned after the SQL statement is executed. Each time the API is executed, the system obtains a set of new rows until all data is read.

  The prototype of the PKG_SERVICE.SQL_NEXT_ROW function is as follows:

  1 2 3 4

  PKG_SERVICE.SQL_NEXT_ROW( context_id IN INTEGER, ) RETURN INTEGER;

  Table 6 PKG_SERVICE.SQL_NEXT_ROW parameters

  Parameter	Description
  context_id	ID of the context to be executed.

• PKG_SERVICE.SQL_GET_VALUE

  This function returns the context element value in a specified position of a context and accesses the data obtained by PKG_SERVICE.SQL_NEXT_ROW.

  The prototype of the PKG_SERVICE.SQL_GET_VALUE function is as follows:

  1 2 3 4 5 6

  PKG_SERVICE.SQL_GET_VALUE( context_id IN INTEGER, pos IN INTEGER, col_type IN ANYELEMENT ) RETURN ANYELEMENT;

  Table 7 PKG_SERVICE.SQL_GET_VALUE parameters

  Parameter	Description
  context_id	ID of the context to be executed.
  pos	Position of a dynamically defined column in the query.
  col_type	Variable of any type, which defines the return value type of columns.

• PKG_SERVICE.SQL_SET_RESULT_TYPE
  This function defines columns returned from a given context and can be used only for contexts defined by SELECT. The defined columns are identified by the relative positions in the query list. The prototype of PKG_SERVICE.SQL_SET_RESULT_TYPE is as follows:

  1 2 3 4 5 6 7

  PKG_SERVICE.SQL_SET_RESULT_TYPE( context_id IN INTEGER, pos IN INTEGER, coltype_oid IN ANYELEMENT, maxsize IN INTEGER ) RETURN INTEGER;

  Table 8 PKG_SERVICE.SQL_SET_RESULT_TYPE parameters

  Parameter	Description
  context_id	ID of the context to be executed.
  pos	Position of a dynamically defined column in the query.
  coltype_oid	Variable of any type. The OID of the corresponding type can be obtained based on the variable type.
  maxsize	Length of a defined column.

• PKG_SERVICE.JOB_CANCEL

  The stored procedure CANCEL deletes a specified task.

  The prototype of the PKG_SERVICE.JOB_CANCEL function is as follows:

  1 2	PKG_SERVICE.JOB_CANCEL( id IN INTEGER);

  Table 9 PKG_SERVICE.JOB_CANCEL parameters

  Parameter	Type	Input/Output Parameter	Empty or Not	Description
  id	INTEGER	IN	No	Specifies the job ID.

• PKG_SERVICE.JOB_FINISH

  The stored procedure FINISH disables or enables a scheduled task.

  The prototype of the PKG_SERVICE.JOB_FINISH function is as follows:

  1 2 3 4

  PKG_SERVICE.JOB_FINISH( id IN INTEGER, broken IN BOOLEAN, next_time IN TIMESTAMP DEFAULT sysdate);

  Table 10 PKG_SERVICE.JOB_FINISH parameters

  Parameter	Type	Input/Output Parameter	Empty or Not	Description
  id	INTEGER	IN	No	Specifies the job ID.
  broken	BOOLEAN	IN	No	Specifies the status flag, TRUE for broken and FALSE for not broken. The current job is updated based on the parameter value TRUE or FALSE. If the parameter is left empty, the job status remains unchanged.
  next_time	TIMESTAMP	IN	Yes	Specifies the next execution time. The default value is the current system time. If broken is set to TRUE, next_time is updated to '4000-1-1'. If broken is set to FALSE and next_time is not empty, next_time is updated for the job. If next_time is empty, it will not be updated. This parameter can be omitted, and its default value will be used in this case.

• PKG_SERVICE.JOB_SUBMIT

  The stored procedure JOB_SUBMIT commits a scheduled task provided by the system.

  The prototype of the PKG_SERVICE.JOB_SUBMIT function is as follows:

  1 2 3 4 5 6

  PKG_SERVICE.JOB_SUBMIT( id IN BIGINT, content IN TEXT, next_time IN TIMESTAMP DEFAULT sysdate, interval_time IN TEXT DEFAULT 'null', job OUT INTEGER);

  

  When a scheduled task (using JOB) is created, the system binds the current database and the username to the task by default. This function can be called by using call or select. If you call this function by using select, there is no need to specify output parameters. To call this function within a stored procedure, use perform. If the committed SQL statement task uses a non-public schema, specify the schema to a table schema or a function schema, or add set current_schema = xxx before the SQL statement.

  Table 11 PKG_SERVICE.JOB_SUBMIT parameters

  Parameter	Type	Input/Output Parameter	Empty or Not	Description
  id	BIGINT	IN	No	Specifies the job ID. If the input ID is NULL, a job ID is generated internally.
  content	TEXT	IN	No	Specifies the SQL statement to be executed. One or multiple DMLs, anonymous blocks, and statements for calling stored procedures, or all three combined are supported.
  next_time	TIMESTAMP	IN	No	Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is committed.
  interval_time	TEXT	IN	Yes	Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a NUMERIC value, for example, sysdate+1.0/24. If this parameter is left empty or set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  job	INTEGER	OUT	No	Specifies the job ID. The value ranges from 1 to 32767. When pkg_service.job_submit is called using select, this parameter can be omitted.

• PKG_SERVICE.JOB_UPDATE

  The stored procedure UPDATE modifies user-definable attributes of a task, including the task content, next-execution time, and execution interval.

  The prototype of the PKG_SERVICE.JOB_UPDATE function is as follows:

  1 2 3 4 5

  PKG_SERVICE.JOB_UPDATE( id IN BIGINT, next_time IN TIMESTAMP, interval_time IN TEXT, content IN TEXT);

  Table 12 PKG_SERVICE.JOB_UPDATE parameters

  Parameter	Type	Input/Output Parameter	Empty or Not	Description
  id	INTEGER	IN	No	Specifies the job ID.
  next_time	TIMESTAMP	IN	Yes	Specifies the next execution time. If this parameter is left empty, the system does not update the next_time parameter for the specified job. Otherwise, the system updates the next_time parameter for the specified job.
  interval_time	TEXT	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left empty, the system does not update the interval_time parameter for the specified job. Otherwise, the system updates the interval_time parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  content	TEXT	IN	Yes	Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left empty, the system does not update the content parameter for the specified job. Otherwise, the system updates the content parameter for the specified job.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

  CREATE TABLE test_table(a int); CREATE TABLE CREATE OR REPLACE PROCEDURE test_job(a in int) IS BEGIN INSERT INTO test_table VALUES(a); COMMIT; END; / CREATE PROCEDURE --PKG_SERVICE.JOB_SUBMIT SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call test_job(1);', to_date('20180101','yyyymmdd'),'sysdate+1'); job_submit ------------ 28269 (1 row) SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call test_job(1);', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); job_submit ------------ 1506 (1 row) CALL PKG_SERVICE.JOB_SUBMIT(NULL, 'INSERT INTO test_table VALUES(1); call test_job(1); call test_job(1);', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid); job ------- 14131 (1 row) SELECT PKG_SERVICE.JOB_SUBMIT (101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); job_submit ------------ 101 (1 row) --PKG_SERVICE.JOB_UPDATE CALL PKG_SERVICE.JOB_UPDATE(101, sysdate, 'sysdate + 1.0/1440', 'call test_job(1);'); job_update ------------ (1 row) CALL PKG_SERVICE.JOB_UPDATE(101, sysdate, 'sysdate + 1.0/1440', 'insert into test_table values(1);'); job_update ------------ (1 row) --PKG_SERVICE.JOB_FINISH CALL PKG_SERVICE.JOB_FINISH(101,true); job_finish ------------ (1 row) --PKG_SERVICE.JOB_CANCEL CALL PKG_SERVICE.JOB_CANCEL(101); job_cancel ------------ (1 row) DROP TABLE test_table; DROP TABLE

• PKG_SERVICE.SUBMIT_ON_NODES

  The stored procedure SUBMIT_ON_NODES creates a scheduled task on all CNs and DNs. Only users sysadmin and monitor admin have this permission.

  The prototype of the PKG_SERVICE.SUBMIT_ON_NODES function is as follows:

  1 2 3 4 5 6 7

  PKG_SERVICE.SUBMIT_ON_NODES( node_name IN NAME, database IN NAME, what IN TEXT, next_date IN TIMESTAMP WITHOUT TIME ZONE, job_interval IN TEXT, job OUT INTEGER);

  Table 13 PKG_SERVICE.SUBMIT_ON_NODES parameters

  Parameter	Type	Input/Output Parameter	Empty or Not	Description
  node_name	TEXT	IN	No	Specifies the job execution node. Currently, the value can only be ALL_NODE (indicating that the job is executed on all nodes) or CCN (indicating that the job is executed on the central coordinator node).
  database	TEXT	IN	No	Database used by a cluster job. When the node type is ALL_NODE, the value can only be postgres.
  what	TEXT	IN	No	Specifies the SQL statement to be executed. One or multiple DMLs, anonymous blocks, and statements for calling stored procedures, or all three combined are supported.
  nextdate	TIMESTAMP	IN	No	Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is committed.
  job_interval	TEXT	IN	No	Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a NUMERIC value, for example, sysdate+1.0/24. If this parameter is left empty or set to null, the job will be executed only once, and the job status will change to 'd' afterward.
  job	INTEGER	OUT	No	Specifies the job ID. The value ranges from 1 to 32767. When dbms.submit_on_nodes is called using select, this parameter can be omitted.

  Example:

  1 2 3 4 5 6 7 8 9 10 11

  SELECT pkg_service.submit_on_nodes('ALL_NODE', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second'''); submit_on_nodes ----------------- 12068 (1 row) SELECT pkg_service.submit_on_nodes('CCN', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second'''); submit_on_nodes ----------------- 9027 (1 row)

• PKG_SERVICE.ISUBMIT_ON_NODES

  ISUBMIT_ON_NODES has the same syntax function as SUBMIT_ON_NODES, but the first parameter of ISUBMIT_ON_NODES is an input parameter, that is, a specified task ID. In contrast, that last parameter of ISUBMIT_ON_NODES is an output parameter, indicating the task ID automatically generated by the system. Only users sysadmin and monitor admin have this permission.

• PKG_SERVICE.SQL_GET_ARRAY_RESULT

  This function is used to return the value of the bound OUT parameter of the array type and obtain the OUT parameter in a stored procedure.

  The prototype of the PKG_SERVICE.SQL_GET_ARRAY_RESULT function is as follows:

  1 2 3 4 5 6

  PKG_SERVICE.SQL_GET_ARRAY_RESULT( context_id in int, pos in VARCHAR2, column_value inout anyarray, result_type in anyelement );

  Table 14 PKG_SERVICE.SQL_GET_ARRAY_RESULT parameters

  Parameter	Description
  context_id	ID of the context to be queried.
  pos	Name of the bound parameter.
  column_value	Return value.
  result_type	Return type.

• PKG_SERVICE.SQL_GET_VARIABLE_RESULT

  This function is used to return the value of the bound OUT parameter of the non-array type and obtain the OUT parameter in a stored procedure.

  The prototype of the PKG_SERVICE.SQL_GET_VARIABLE_RESULT function is as follows:

  1 2 3 4 5 6

  PKG_SERVICE.SQL_GET_VARIABLE_RESULT( context_id in int, pos in VARCHAR2, result_type in anyelement ) RETURNS anyelement;

  Table 15 PKG_SERVICE.SQL_GET_VARIABLE_RESULT parameters

  Parameter	Description
  context_id	ID of the context to be queried.
  pos	Name of the bound parameter.
  result_type	Return type.