0

I had to provide data for an application schema, but there is a requirement that the applications connecting using that schema user cannot query more than five rows at a time, the application queries data from views, how can I rewrite all application queries (all this user queries) to have rownum < 5 in the where clause?

I’m using Oracle 12c.

4
  • 1
    BTW, based on your comments it sounds like your goal to limit the data, is for data security purposes against developers. You're better off using a technique designed to accomplish data security such as Data Masking or Row-Level Security (both features of Oracle 12c) for a multitude of reasons. If you want help implementing one of those features, feel free to post a different question.
    – J.D.
    Nov 29 '21 at 12:38
  • @J.D. totally true, if those technologies can be used to implement what I want please inform me on how to do that. I thought those things can be used to hide specific rows from specific users, how ever, I don’t need to hide any thing from the user, I want their results to be restricted only to the top matches of their query, so it they need some other rows they need to write more specific query.
    – alibttb
    Nov 29 '21 at 12:41
  • If you need help with those technologies then please close this question and open a new one instead regarding "how to implement data security from developers" (with the appropriate tags, e.g. oracle-12c). I'm not an expert on Oracle, so you may get better help from other more experienced users that way, but I'll at least provide you relevant documentation on how to implement those features.
    – J.D.
    Nov 29 '21 at 12:47
  • 1
    @J.D. I’ll do some research and if this is the case for me I’ll close the question tonight and add more relevant one, thank you.
    – alibttb
    Nov 29 '21 at 12:48
0

Solution - Stateless App (web)

Fix the application.

The application must add the OFFSET and FETCH NEXT clauses to the SQL since it is the application that defines the ORDER BY and WHERE clauses.

This is what Oracle APEX does for its Interactive Reports.

Solution - Stateful App

Fix the application.

Instead of fetching all of the data at once, leave the statement handle open ( sth ) and fetch 5 rows at a time.

Reason

Databases do not keep things in order. (I'm ignoring IOTs)

If you tell the database "I need 5 rows only", then you are going to get 5 random rows each time you run the query. (Usually, it's the same 5 rows. But, you need to plan as if they ate random)

That makes your request,as written, non deterministic.

If you need "the nth 5 rows", you must first define the order of those rows with an ORDER BY clause. Then (at that level) you can "fetch 5 rows at a time".

5
  • We don’t control the application, it’s another team, and we want their user account to see data in 5 rows chunks only.
    – alibttb
    Nov 29 '21 at 10:44
  • He who controls the ORDER BY clause controls "which 5 rows" to be fetched. If there is a solution, it would be outside of the database. Besides, fetching 5 rows at a time of a 6 row result is stupid. Nov 29 '21 at 11:01
  • This is not about performance of loading time, it’s about restricting the application developer so they can’t have meaningful statistics of the data, they are allowed to query some data but not all data at once, they can query all data one by one, but then this would be logged and can be controlled with alerts.
    – alibttb
    Nov 29 '21 at 11:20
  • Lets say it in another way, I need to add fetch next 5 rows only to every query made by a specific user.
    – alibttb
    Nov 29 '21 at 11:23
  • 1
    This conversation needs to be moved to Chat and I haven't figured out how to do that from a phone. Can someone help? Nov 29 '21 at 12:29
0

You can use the FETCH NEXT clause. Example from the linked tutorial:

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
    USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

In your case since you want to limit the amount of rows returned from an existing view, all you'd have to do is:

SELECT
    YourColumns
FROM
    YourView
ORDER BY
    SomeFieldToOrderBy
FETCH NEXT 5 ROWS ONLY;
2
  • Dosen’t work, I need the fetch next clause to work after the query to the view, creating a view like this would make a view of 5 rows only.
    – alibttb
    Nov 29 '21 at 9:25
  • @alibttb I never said you had to use the FETCH NEXT clause inside your view. You can add it to the end of your query to the view (so it's outside the view).
    – J.D.
    Nov 29 '21 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.