1. Cloud Control Monitoring Guide
  2. Appendix
  3. Executing SQL via REST API

Executing SQL via REST API

Enterprise Manager has a rich set of monitoring data collected in its repository that can be extracted via REST API. You can use your own SQL scripts and Enterprise Manager's REST endpoints to extract repository data.

Enterprise Manager repository data can be extracted and used for a variety of purposes such as building custom dashboards, or Key Performance Indicator (KPI) reports. You can easily extract repository information via SQL by using Enterprise Manager's HTTP-based REST endpoints.

In addition to extracting data from the Enterprise Manager repository, the REST API also allows you to use some of the REST endpoints to extract data from any database target that is monitored/managed by Enterprise Manager.

With Enterprise Manager you can run a SQLScript job against a database target to automate data extraction. This job type requires both host and database credentials for job execution. For situations where the use of host credentials are not permitted, you can run an Execute SQL job, which requires only database credentials.

Refer to the following tables for REST endpoints:

  • Table -2: REST Endpoints for Repository Operations
  • Table -3: REST Endpoints for Target Database Operations

REST Endpoints Accessibility

Because the repository is a critical component of the Enterprise Manager framework, specific protections must be implemented to ensure that the repository database is secure.

Repository-related REST endpoints are protected by the following:

  • The OMS property oracle.sysman.db.restfulapi.executesql.repository.query.enable must be set to true using emctl.

    Example:

    
emctl set property -name oracle.sysman.db.restfulapi.executesql.repository.query.enable -value true -sysman_pwd “sysman”

  • Authorization Header: Enterprise Manager User Credentials need to be passed as part of header.

For specific repository-related REST operations:

In addition to the above endpoint protection settings, you also need to set the following:

  • Set the oracle.sysman.db.restfulapi.executesql.repository.update.enable OMS property to true using emctl for /repository/update REST method invocation on the repository database.

    Example:

    
emctl set property -name oracle.sysman.db.restfulapi.executesql.repository.update.enable -value true -sysman_pwd “sysman”

  • Set the oracle.sysman.db.restfulapi.executesql.repository.plsql.enable OMS property to true using emctl for /repository/plsql method invocation on the repository database. 

    
emctl set property -name oracle.sysman.db.restfulapi.executesql.repository.plsql.enable -value true -sysman_pwd “sysman”
  • The Enterprise Manager user running a SELECT query REST operation (i.e., /repository/query REST) must be granted any out-of-box Enterprise Manager roles (in addition to the EM_USER role), otherwise Fine Grained Auditing (FGA) will restrict the result of the SQL query to no rows.

Database target-related REST endpoints are protected by the following:

In addition to the aforementioned roles, the Enterprise Manager user should also have the following Target Privileges:

  • Connect target
  • Run any sql on Database

The oracle.sysman.db.restfulapi.executesql.target.query.enable OMS property must be set to true using emctl to enable REST operations on the database target. 


emctl set property -name oracle.sysman.db.restfulapi.executesql.target.query.enable -value true -sysman_pwd “sysman”

For specific database target-related REST operations:

In addition to the above settings, the following OMS properties must be set to true.

  • The oracle.sysman.db.restfulapi.executesql.target.update.enable OMS property must be set to true using emctl for /target/update REST method invocation on the target database:

    Example:

    emctl set property -name oracle.sysman.db.restfulapi.executesql.target.update.enable -value true -sysman_pwd “sysman”
  • The oracle.sysman.db.restfulapi.executesql.target.plsql.enable OMS property must be set to true using emctl for /target/plsql REST method invocation on the target database:

    Example:

    emctl set property -name oracle.sysman.db.restfulapi.executesql.target.plsql.enable -value true -sysman_pwd “sysman”

Query Result Limitation

To prevent the repository/target database from being overloaded, flood control mechanisms that limit the number of returned rows and columns by REST SQL queries have been implemented via the following OMS properties:

  • oracle.sysman.db.httpsql.numrows : If no value has been specified, then by default 1000 rows will be returned in the resultset.

  • oracle.sysman.db.httpsql.numcols : If no value has been specified, then by default only 20 columns will be returned in the resultset.

REST Endpoints

The following tables list available Enterprise Manager repository and target database REST endpoints.

Table -2 REST Endpoints for Repository Operations

REST Endpoint Sample Payload HTTP Method Comments Sample Output
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/query/v1
 { "sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "maxRowLimit": 2, "maxColumnLimit": 4 } POST

Executes the given SELECT query on the repository DB.

maxRowLimit and maxColumnLimit are optional.

 {"Result" : [{"target_name":"Management_Servers","target_type":"oracle_emsvrs_sys","target_guid":" [B@2f99ae59","metric_name":"Response"}, {"target_name":"\/EMGC_EMGC_DOMAIN\/EMGC_DOMAIN\/EMGC_ADMINSERVER\/mds-owsm","target_type":"metadata_repository","target_guid":" [B@12856d79","metric_name":"MDS_REPOSITORY_ROLLUP"}]}
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/plsql/v1 Example #1: executing a PL/SQL block which does not have any data to return.

{ "sqlStatement": " begin execute immediate 'create table test_sql_t1(col1 number(10))'; end; " }

 POST

Executes the given PL/SQL block on the repository DB.

 { "Result":"PLSQL block executed successfully" }
. Example #2: executing a PL/SQL block which have multiple outputs.

{ "sqlStatement": " DECLARE v_target_guid RAW(16); v_status NUMBER; v_sub_status NUMBER; BEGIN SELECT target_guid INTO v_target_guid FROM sysman.EM_TARGETS WHERE target_type = 'oracle_database' AND target_name = 'Oemrep_Database'; SYSMAN.MGMT_AVAIL.get_avail_state(v_target_guid,?,?); SYSMAN.EMD_MAINT_UTIL.get_em_db_sessions_cursor(?); END; ", "sqlParameters":[ {"type":"INTEGER","isOutparameter":true}, {"type":"INTEGER","isOutparameter":true}, {"type":"CURSOR","isOutparameter":true} ], "maxRowLimit": 2, "maxColumnLimit": 2 }

 POST

Executes the given PL/SQL block on the target DB using the DB user ID and password.

 {"Result":[{"OutParameter3":[{"INST_ID":1,"SID":10},{"INST_ID":1,"SID":11}]},{"OutParameter1":1},{"OutParameter2":99}]}
. Example #3: excuting PL/SQL blocks having both input and output parameters.

{ "sqlStatement": " DECLARE BEGIN SYSMAN.EM_TARGET.GET_AGENT_VERSION_FOR_TARGET(?,?,?); END; ", "sqlParameters":[ {"type":"STRING","value":"Oemrep_Database"}, {"type":"STRING","value":"oracle_database"}, {"type":"STRING","isOutparameter":true} ] }

 POST

Executes the given PLSQL block on the target and returns results, if any.

{"Result":[{"OutParameter3":"13.4.0.0.0"}]}
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/repository/update/v1 { "sqlStatement": "CREATE TABLE test_SQL_T3 AS (SELECT * FROM sysman.ADP_EVENT_J2EE where 1<>1)" } POST

Executes the given DML query on the repository DB.

 { "Result":0 }

Table -3 REST Endpoints for Target Database Operations

REST Endpoint Sample Payload HTTP Method Comments Sample Output
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/target/query/v1 {"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS", "credential": {"DBCredsMonitoring":"testcred"}, "maxRowLimit": 3, "maxColumnLimit": 2} POST

Executes the given SELECT query on a target DB using named DB credentials referred by property DBCredsMonitoring in the payload.

maxRowLimit and maxColumnLimit are optional. If maxRowLimit/ maxColumnLimit value is -1, then all rows/columns will be returned in the result set.

{"Result" : [{"target_name":"Management_Servers", "target_type":"oracle_emsvrs_sys"}, {"target_name":"\/EMGC_EMGC_DOMAIN\/EMGC_DOMAIN\/EMGC_ADMINSERVER\/mds-owsm", "target_type":"metadata_repository"}, {"target_name":"\/EMGC_EMGC_DOMAIN\/EMGC_DOMAIN\/EMGC_ADMINSERVER\/mds-owsm", "target_type":"metadata_repository"}]}
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/target/query/v1 {"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": "SELECT * FROM sysman.MGMT$TARGET_METRIC_SETTINGS where target_name=? and warning_operator=?", "sqlParameters":[ {"type":"STRING","value":"Management_Servers"}, {"type":"INTEGER","value":"1"} ], "credential": { "DBCredsMonitoring":"testcred" }, "maxRowLimit": 2, "maxColumnLimit": 2 }

sqlParameters is required only when the sqlStatement contains a question mark "?".

Type can be one of the following

  • STRING
  • INTEGER
  • DATE
  • BYTE
  • BOOLEAN
  • CURSOR (only in case of PL/SQL kind of statements)
 POST

Executes the given SELECT query on a target DB using specified named DB credentials (refer to property DBCredsMonitoring in the payload).

maxRowLimit and maxColumnLimit are optional.

 {"Result" : [{"target_name":"Management_Servers","target_type":"oracle_emsvrs_sys"}]}
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/target/plsql/v1 Example #1: executing a PL/SQL block which does not have any data to return.

{"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": " begin execute immediate 'create table test_sql_t1(col1 number(10))'; end; ", "credential": { "DBCredsMonitoring":"testcred" } }

 POST Executes the given PL/SQL block on a target DB using specifed named DB credentials (refer to property DBCredsMonitoring in the payload). {""Result"": "PLSQL Block executed successfully"}
. Example #2: executing a PL/SQL block which have multiple outputs.

{"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": " DECLARE v_target_guid RAW(16); v_status NUMBER; v_sub_status NUMBER; BEGIN SELECT target_guid INTO v_target_guid FROM sysman.EM_TARGETS WHERE target_type = 'oracle_database' AND target_name = 'Oemrep_Database'; SYSMAN.MGMT_AVAIL.get_avail_state(v_target_guid,?,?); SYSMAN.EMD_MAINT_UTIL.get_em_db_sessions_cursor(?); END; ", "sqlParameters":[ {"type":"INTEGER","isOutparameter":true}, {"type":"INTEGER","isOutparameter":true}, {"type":"CURSOR","isOutparameter":true} ], "credential": {"DBCredsMonitoring":"testcred"}, "maxRowLimit": 2, "maxColumnLimit": 2 }

 POST Executes the given PL/SQL block on a target DB using specified named DB credentials (refer property DBCredsMonitoring in the payload).

Executes the given SELECT query on a target DB using named DB credentials referred by property DBCredsMonitoring in the payload.

{"Result":[{"OutParameter3":[{"INST_ID":1,"SID":10},{"INST_ID":1,"SID":11}]}, {"OutParameter1":1},{"OutParameter2":99}]}
. Example #3: excuting PL/SQL blocks having both input and output parameters.

{"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": " DECLARE BEGIN SYSMAN.EM_TARGET.GET_AGENT_VERSION_FOR_TARGET(?,?,?); END; ", "credential": {"DBCredsMonitoring":"testcred"}, "sqlParameters":[ {"type":"STRING","value":"Oemrep_Database"}, {"type":"STRING","value":"oracle_database"}, {"type":"STRING","isOutparameter":true} ] }

 POST Executes the given PL/SQL block on a target DB using specified named DB credentials (refer to property DBCredsMonitoring in the payload) and returns results, if any. {"Result":[{"OutParameter3":"13.4.0.0.0"}]}
https://<EM_HOST>:<PORT>/em/websvcs/restful/emws/oracle.sysman.db/executesql/target/update/v1 {"targetName":"Oemrep_Database", "targetType": "oracle_database", "sqlStatement": "DELETE FROM mytable where empId<2000 and empId > 1998", "credential": { "DBCredsMonitoring":"testcred" } } POST

Executes the given DML query on a target DB using specified named DB credentials (refer to the property DBCredsMonitoring in the payload).

For most of the DML statement, the result will have a number of rows affected by that DML.

 { "Result": 2 }

Note:

Only Named Credentials are accepted in the payload for target database endpoints. Ensure that Enterprise Manager administrators executing the REST endpoint operations have already created a valid named credential for the database target specified in the payload.