CODEWITHSUNDEEP
oracle-databaserestplsqloracle12c
user1009073
user1009073

Reputation: 3238

Oracle - Writing REST services in PLSQL

I have created a REST service (via APEX) which utilizes PL/SQL to build a result set. I loop through a large cursor and do a sys.htp.print(my_data) – (as an aside, is the sys.htp.print the best option here?). Basically, I am dumping a data string, a row at a time, to the http buffer...

My client process receives the data fine, as long as it is not too big a data set. For example, the client process can receive data sets of approximately 35K rows, but larger data sets cause issues.

I do NOT have any value set for Pagination Size. It appears that I will have to implement some type of pagination functionality. My confusion is HOW to do this with PL/SQL.

In looking at the APEX help popup, it refers to :row_offset and :row_count variables. When I have used SQL based queries for REST services (as opposed to PL/SQL) I just set the pagination, and reference the page number that I want. How is this different for PL/SQL? Do I continue using the REST Service URL (which basically adds a 'page=1' type of extension to the service URL) as with SQL based services? (i.e. Source Type = Query), or do I need to adjust my code to reference :row_offset and :row_count? If so, since this is PL/SQL based, will each call have to run the service, and just return the data greater than :row_offset up to :row_count?

Upvotes: 0

Views: 662

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

Consider reworking your PL/SQL to return the data as a pipelined table function. That allows it to be queried through a query like

SELECT columns FROM TABLE(pkg.func(par=> :a)) ORDER BY...

That way it can handle the pagination as though it were any other SQL query.

Upvotes: 2

ArtBajji
ArtBajji

Reputation: 960

With Oracle 12c we have the new Offset and Fetch clauses in SQL.

SELECT (Required Columns) FROM (Tables) (Joins/Predicates) ORDER BY (Required Columns) OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

You may want to check the OracleDocumentation and also some code samples here.

Upvotes: 0

Related Questions