10.1.1 Comparison of the Embedded Python Execution APIs

The table below compares the four Embedded Python Execution APIs.

The APIs are:

• Embedded Python Execution API
• REST API for Embedded Python Execution (for use with Oracle Autonomous Database)
• SQL API for Embedded Python Execution with On-Premises Oracle Database.

The APIs share many functions, but they differ in some ways because of the different environments. For example, the APIs available for Autonomous Database provide an API for operating in a web environment.

The procedures and functions are part of the PYQSYS and SYS schemas.

Category	Python API for Embedded Python Execution	REST API for Embedded Python Execution	SQL APIs for Embedded Python Execution
Embedded Python Execution function	oml.do_eval function See Run a User-Defined Python Function.	POST /api/py-scripts/v1/do-eval/{scriptName} See Run a Python Function. POST /api/py-scripts/v1/do-eval/{scriptName}/{ownerName} See Run a Python Function with Script Owner Specified.	pyqEval Function (Autonomous Database) (Autonomous Database) pyqEval Function (On-Premises Database) (on-premises database)
Embedded Python Execution function	oml.table_apply function See Run a User-Defined Python Function on the Specified Data.	POST /api/py-scripts/v1/table-apply/{scriptName} See Run a Python Function on Specified Data. POST /api/py-scripts/v1/table-apply/{scriptName}/{ownerName}} See Run a Python Function on Specified Data with Script Owner Specified.	pyqTableEval Function (Autonomous Database) (Autonomous Database) pyqTableEval Function (On-Premises Database) (on-premises database)
Embedded Python Execution function	oml.group_apply function See Run a Python Function on Data Grouped By Column Values.	POST /api/py-scripts/v1/group-apply/{scriptName} See Run a Python Function on Grouped Data. POST /api/py-scripts/v1/group-apply/{scriptName}/{ownerName} See Run a Python Function on Grouped Data with Script Owner Specified.	pyqGroupEval Function (Autonomous Database) (Autonomous Database) pyqGroupEval Function (On-Premises Database) (on-premises database)
Embedded Python Execution function	oml.row_apply function See Run a User-Defined Python Function on Sets of Rows.	POST /api/py-scripts/v1/row-apply/{scriptName} See Run a Python Function on Chunks of Rows. POST /api/py-scripts/v1/row-apply/{scriptName}/{ownerName} See Run a Python Function on Chunks of Rows with Script Owner Specified.	pyqRowEval Function (Autonomous Database) (Autonomous Database) pyqRowEval Function (On-Premises Database) (on-premises database)
Embedded Python Execution function	oml.index_apply function See Run a User-Defined Python Function Multiple Times.	POST /api/py-scripts/v1/index-apply/{scriptName} See Run a Python Function Multiple Times. POST /api/py-scripts/v1/index-apply/{scriptName}/{ownerName} See Run a Python Function Multiple Times with Script Owner Specified.	pyqIndexEval Function (Autonomous Database) (Autonomous Database) The API for on-premises Oracle Database has no pyqIndexEval function. Use pyqGroupEval Function (On-Premises Database) instead.
Job status API	NA	GET /api/py-scripts/v1/jobs/{jobId} See Retrieve Asynchronous Job Status.	pyqJobStatus Function (Autonomous Database) NA (on-premises database)
Job result API	NA	GET /api/py-scripts/v1/jobs/{jobId}/result See Retrieve Asynchronous Job Result.	pyqJobResult Function (Autonomous Database) NA (on-premises database)
Script repository	oml.script.dir function See List Available User-Defined Python Functions.	GET /api/py-scripts/v1/scripts See List Scripts.	List the scripts by querying the ALL_PYQ_SCRIPTS View and the USER_PYQ_SCRIPTS View.
Script repository	oml.script.create function See Create and Store a User-Defined Python Function.	NA	pyqScriptCreate Procedure (Autonomous Database) (Autonomous Database) pyqScriptCreate Procedure (On-Premises Database) (on-premises database)
Script repository	oml.script.drop function See Drop a User-Defined Python Function from the Repository.	NA	pyqScriptDrop Procedure (Autonomous Database) (Autonomous Database) pyqScriptDrop Procedure (On-Premises Database) (on-premises database)
Script repository	oml.script.load function See Load a User-Defined Python Function.	NA	NA (Scripts are loaded in the SQL APIs when the function is called.)
Script repository	NA	NA	ALL_PYQ_SCRIPTS View
Script repository	NA	NA	USER_PYQ_SCRIPTS View
Script repository and datastore	oml.grant function See About the Script Repository.	NA	pyqGrant procedure (Oracle Autonomous Database) pyqGrant procedure (on-premises database) See About the SQL API for Embedded Python Execution with On-Premises Database (on-premises database).
Script repository and datastore	oml.revoke function See About the Script Repository.	NA	pyqRevoke procedure (Autonomous Database) pyqRevoke procedure (on-premises database) See About the SQL API for Embedded Python Execution with On-Premises Database (on-premises database).
Datastore	NA	NA	ALL_PYQ_DATASTORES View
Datastore	NA	NA	ALL_PYQ_DATASTORE_CONTENTS View
Datastore	NA	NA	USER_PYQ_DATASTORES View
Authorization - Access Control Lists	NA	NA	pyqAppendHostACE Procedure (Autonomous Database) NA (on-premises database) (On-premises, the authorization is related to logging into the user schema.)
Authorization - Access Control Lists	NA	NA	pyqRemoveHostACE Procedure (Autonomous Database) NA (on-premises database)
Authorization - Access Control Lists	NA	NA	pyqGetHostACE Function (Autonomous Database) NA (on-premises database)
Authorization - Tokens	NA	See Authenticate.	pyqSetAuthToken Procedure (Autonomous Database) NA (on-premises database)
Authorization - Tokens	NA	See Authenticate.	pyqIsTokenSet Function (Autonomous Database) NA (on-premises database)

Note:

An output limit exists on the length function for REST API and SQL APIs for embedded Python execution. A query on the length function with a length of more than 5000 will result in an error with error code 1024 and the error message "Output exceeds maximum length 5000". The limit is set on the len() result of the returning python object. For example, len() of a pandas.DataFrame is the number of rows, len() of a list is the length of the list, etc. If pandas.DataFrame is returned, it cannot have more than 5000 rows. If a list is returned, it should not contain more than 5000 items. This limit can be extended by updating the OML_OUTPUT_SZLIMIT in a %script paragraph:

%script

EXEC sys.pyqconfigset('OML_OUTPUT_SZLIMIT', '8000')