US 12,332,893 B2
Method and system to implement SQL macros
Atif Chaudhry, Fremont, CA (US); Chandrasekharan Iyer, Redwood City, CA (US); Lei Sheng, Redwood City, CA (US); and Agrawal Shashaanka, Hayward, CA (US)
Assigned to Oracle International Corporation, Redwood Shores, CA (US)
Filed by Oracle International Corporation, Redwood Shores, CA (US)
Filed on Sep. 13, 2019, as Appl. No. 16/571,007.
Claims priority of provisional application 62/748,311, filed on Oct. 19, 2018.
Prior Publication US 2020/0125569 A1, Apr. 23, 2020
Int. Cl. G06F 16/2453 (2019.01); G06F 16/242 (2019.01)
CPC G06F 16/24547 (2019.01) [G06F 16/244 (2019.01); G06F 16/2448 (2019.01); G06F 16/24545 (2019.01)] 24 Claims
OG exemplary drawing
 
1. A method, comprising:
maintaining a database system comprising at least a database server and a database, wherein the database system processes SQL queries having SQL statements and one or more non-SQL functions against one or more tables in the database based on at least whether corresponding non-SQL functions are to be converted by the database server into replacement SQL functions, the database server includes at least a query processor, an SQL processing engine that processes SQL statements, and one or more non-SQL processing engines that process one or more non-SQL statements; and
receiving an SQL query to be processed inline at the database server and from a database application connected to the database server over a network, wherein the SQL query includes a non-SQL function that specifies an operation on one or more values in a table of the one or more tables, and the query processor determines that the non-SQL function in the SQL query is to be converted by the query processor into a form that is processable by the SQL processing engine based on at least the non-SQL function comprising a scalar expression or a table expression, and processing the SQL query inline at the database server by:
executing, without performing a cross-engine context switch between the SQL processing engine and the non-SQL processing engine, the SQL query by the SQL processing engine, wherein:
responsive to determining that the non-SQL function corresponds to an SQL macro that pertains to a scalar expression:
retrieving a return string from the non-SQL function corresponding to the SQL macro,
processing the return string to construct at least a select statement that is configured to select referenced content from a corresponding table, and
replacing the non-SQL function in the SQL query with the select statement that is configured to select the referenced content from the corresponding table; and
responsive to determining that the non-SQL function corresponds to an SQL macro that pertains to a table expression:
retrieving a return string from the non-SQL function corresponding to the SQL macro,
processing the return string to construct at least a from statement that is configured to select relevant content from a corresponding table, and
replacing the non-SQL function in the SQL query with a from statement that is configured to select the relevant content from the corresponding table.