PKG_SERVICE

The following table lists all APIs supported by the PKG_SERVICE package.

Table 1 PKG_SERVICE

Interface	Description
PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE	Checks whether a context is registered.
PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS	Deregisters all registered contexts.
PKG_SERVICE.SQL_REGISTER_CONTEXT	Registers a context.
PKG_SERVICE.SQL_UNREGISTER_CONTEXT	Deregisters a context.
PKG_SERVICE.SQL_SET_SQL	Sets a SQL statement for a context. Currently, only the SELECT statement is supported.
PKG_SERVICE.SQL_RUN	Executes the configured SQL statement on a context.
PKG_SERVICE.SQL_NEXT_ROW	Reads the next row of data in a context.
PKG_SERVICE.SQL_GET_VALUE	Reads a dynamically defined column value in a context.
PKG_SERVICE.SQL_SET_RESULT_TYPE	Dynamically defines a column of a context based on the type OID.
PKG_SERVICE.JOB_CANCEL	Removes a scheduled job by job ID.
PKG_SERVICE.JOB_FINISH	Disables or enables scheduled job execution.
PKG_SERVICE.JOB_SUBMIT	Submits a scheduled job. Job ID can be automatically generated by the system or specified manually.
PKG_SERVICE.JOB_UPDATE	Modifies user-definable attributes of a scheduled job, including the job content, next-execution time, and execution interval.
PKG_SERVICE.SUBMIT_ON_NODES	Submits a job to all nodes. The job ID is automatically generated by the system.
PKG_SERVICE.ISUBMIT_ON_NODES	Submits a job to all nodes. The job ID is specified by the user.
PKG_SERVICE.SQL_GET_ARRAY_RESULT	Obtains the array value returned in the context.
PKG_SERVICE.SQL_GET_VARIABLE_RESULT	Obtains the column value returned in the context.

• 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 function prototype of PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE is as follows:

  PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE(
   context_id     IN INTEGER 
  )
  RETURN BOOLEAN;

  Table 2 PKG_SERVICE.SQL_IS_CONTEXT_ACTIVE interface parameters

  Parameter	Description
  context_id	ID of the context to be queried

• PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS

  This function cancels all contexts.

  The function prototype of PKG_SERVICE.SQL_CLEAN_ALL_CONTEXTS is as follows:

  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 function prototype of PKG_SERVICE.SQL_REGISTER_CONTEXT is as follows:

  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 interface in the exception handling of the stored procedure.

  The function prototype of PKG_SERVICE.SQL_UNREGISTER_CONTEXT is as follows:

  PKG_SERVICE.SQL_UNREGISTER_CONTEXT(
   context_id     IN INTEGER 
  )
  RETURN INTEGER;

  Table 3 PKG_SERVICE.SQL_UNREGISTER_CONTEXT interface 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 function prototype of PKG_SERVICE.SQL_SET_SQL is as follows:

  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 interface 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. Currently, only 1 is supported.

• 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 function prototype of PKG_SERVICE.SQL_RUN is as follows:

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

  Table 5 PKG_SERVICE.SQL_RUN interface 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 that meet query conditions. Each time the interface is executed, the system obtains a set of new rows until all data is read.

  The function prototype of PKG_SERVICE.SQL_NEXT_ROW is as follows:

  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 function prototype of PKG_SERVICE.SQL_GET_VALUE is as follows:

  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:

  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 job.

  The function prototype of PKG_SERVICE.JOB_CANCEL is as follows:

  PKG_SERVICE.JOB_CANCEL(
  job  IN  INTEGER);

  Table 9 PKG_SERVICE.JOB_CANCEL parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	integer	IN	No	Specifies the job ID.

  Example:

  CALL PKG_SERVICE.JOB_CANCEL(101);

• PKG_SERVICE.JOB_FINISH

  The stored procedure FINISH disables or enables a scheduled job.

  The function prototype of PKG_SERVICE.JOB_FINISH is as follows:

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

  Table 10 PKG_SERVICE.JOB_FINISH parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  id	integer	IN	No	Specifies the job ID.
  finished	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 submits a scheduled job provided by the system.

  The function prototype of PKG_SERVICE.JOB_SUBMIT is as follows:

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

  NOTE: When a scheduled job is created, the system binds the current database and the username to the job 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 job uses a non-public schema, specify the schema to a job 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	Can Be Empty	Description
  id	bigint	IN	No	Specifies the job ID. If the input ID is NULL, a job ID is generated internally.
  context	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 submitted.
  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.

  Example:

  SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1');
      
  SELECT PKG_SERVICE.JOB_SUBMIT(NULL, 'call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24');
      
  CALL PKG_SERVICE.JOB_SUBMIT(NULL, 'INSERT INTO T_JOB  VALUES(1);  call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid);
      
  SELECT PKG_SERVICE.JOB_SUBMIT (101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24');

• PKG_SERVICE.JOB_UPDATE

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

  The function prototype of PKG_SERVICE.JOB_UPDATE is as follows:

  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	Can Be Empty	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:

  CALL PKG_SERVICE.JOB_UPDATE(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440');
  CALL PKG_SERVICE.JOB_UPDATE(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440');

• PKG_SERVICE.SUBMIT_ON_NODES

  The stored procedure SUBMIT_ON_NODES creates a scheduled job on a node. Only users sysadmin and monitor admin have this permission.

  The function prototype of PKG_SERVICE.SUBMIT_ON_NODES is as follows:

  PKG_SERVICE.SUBMIT_ON_NODES(
  node_name    IN   TEXT,
  database     IN   TEXT
  what         IN   TEXT,
  next_date    IN   TIMESTAMP DEFAULT sysdate,
  job_interval IN   TEXT  DEFAULT 'null',
  job          OUT  INTEGER);

  Table 13 PKG_SERVICE.SUBMIT_ON_NODES parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  node_name	text	IN	No	Specifies the node where a job is executed. Currently, the value can be ALL_NODE (the job is executed on all nodes) or CCN. (Note: CCN is invalid in a centralized or lite deployment environment.)
  database	text	IN	No	Database used by a database instance 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 submitted.
  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:

  select pkg_service.submit_on_nodes('ALL_NODE', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second''');
  select pkg_service.submit_on_nodes('CCN', 'postgres', 'select capture_view_to_json(''dbe_perf.statement'', 0);', sysdate, 'interval ''60 second''');

• 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 job ID. In contrast, that last parameter of ISUBMIT_ON_NODES is an output parameter, indicating the job 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 INT 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:

  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 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:

  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.