Home » SQL & PL/SQL » SQL & PL/SQL » wanted SELECT Quesry to restrict count of the user selecting the record
wanted SELECT Quesry to restrict count of the user selecting the record [message #264429] Mon, 03 September 2007 05:10 Go to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi experts,

i have created a UI which is used to run a SELECT query(alone) on the oracle DB. tables in that DB has more that 10000 records and so i need to restrict the return of the records by 500 only.

to be specific, the user will give the select query i want to resrit the number of recors it returned to be 500.

how can i do this??
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264432 is a reply to message #264429] Mon, 03 September 2007 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which 500 ones?
Don't you ever hear about WHERE clause?

Regards
Michel
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264435 is a reply to message #264432] Mon, 03 September 2007 05:28 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
if the user types the query we dont know abt the query and because of that we cant insert WHERE at the end at runtime. i need to select the top 500 records irspective of the query given by the user.
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264437 is a reply to message #264429] Mon, 03 September 2007 05:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

In oracle I did not find like this

select top(500)

However I saw in SQL server i could issue to use top.

So you had to use where clause. I don't see any other alternatives.
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264438 is a reply to message #264437] Mon, 03 September 2007 05:43 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

For me you cannot do that.

What you coult look to is:
- limit the quotas of the users to forbid them to run to expensive queries (oracle will NOT run them) so the user HAS TO limit the cost in his WHERE clause.

- Maybe restrict your users to view with limited span of data? Create a connection user with views like:
CREATE OR REPLACE VIEW MY_VIEW_WITH_THE_NAME_OF_THE_TABLE
SELECT * FROM MY_TRUE_SCHEMA.MY_TABLE
WHERE SOME_DATE BETWEEN LOW_SPAN_DATE AND HIGH_SPAN_DATE;

The where clause is just to restrict the number of lines the view can return.
Your user then queries the tables with it's true name (MY_TABLE) and in fact, uses the VIEW called MY_TABLES.
But some queries may return a lot of lines.

Hope this may help,

Regards,

Christian
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264443 is a reply to message #264435] Mon, 03 September 2007 06:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sbadriprasad wrote on Mon, 03 September 2007 12:28
if the user types the query we dont know abt the query and because of that we cant insert WHERE at the end at runtime. i need to select the top 500 records irspective of the query given by the user.

So, you need to modify the query but you can't modify it? That's a bummer. But with a little bit of programming you could use a ref cursor. Here's my test script:
CREATE OR REPLACE FUNCTION return_n( piv_query IN VARCHAR2
                                   , pin_limit IN NUMBER
                                   )
  RETURN SYS_REFCURSOR
IS
  v_refcur SYS_REFCURSOR;
BEGIN
  OPEN v_refcur FOR 'SELECT  * FROM ('||piv_query||') WHERE rownum <= :x' 
  USING  pin_limit;
  
  RETURN v_refcur;
END return_n;
/ 
sho err

VAR mycur REFCURSOR

EXEC :mycur := return_n('SELECT last_name FROM employees', 3);

PRINT mycur
DROP FUNCTION return_n
/
The code runs on the default HR schema.

MHE
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264447 is a reply to message #264443] Mon, 03 September 2007 06:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or maybe you could just "wrap" the user query in something like

select * from (
 [user query]
) where rownum <500;


and execute that.

Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264449 is a reply to message #264438] Mon, 03 September 2007 06:55 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
hi Mr.lecorr,

that was a nice idea, but the DB on which the user can do SELECT has many tables.. so how can you write a view ??

[Updated on: Mon, 03 September 2007 06:57]

Report message to a moderator

Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264451 is a reply to message #264449] Mon, 03 September 2007 06:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The problem is that you don't know in advance what criteria the end user will use. If you limit the output of each table individually, you could end up with missing data... . I'd go for the method proposed by Thomas or the function I showed.

MHE

[Updated on: Mon, 03 September 2007 06:59]

Report message to a moderator

Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264474 is a reply to message #264447] Mon, 03 September 2007 08:23 Go to previous messageGo to next message
sbadriprasad
Messages: 14
Registered: September 2007
Junior Member
thank you MR.Thomas,

it is working as i expected and thanks a lot for this solution Razz
Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264551 is a reply to message #264449] Mon, 03 September 2007 13:45 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

I have done it before...

Write a program which write and execute the views creation.

For me, I wrote a program whic create views, tables and triggers automatically. It worked fine.

So you could easily create 500 views in minutes.

Regards,

Christian

PS: unfortunately, I can't share this code as I sold it to a company (so it is not copyrighted by me...).

Re: wanted SELECT Quesry to restrict count of the user selecting the record [message #264561 is a reply to message #264429] Mon, 03 September 2007 14:18 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>For me, I wrote a program whic create views, tables and triggers automatically. It worked fine.
It may "work fine" for an application with VERY limited number of (ab)users; but such an implementation won't scale well at all.
Use at your own risk!
IMO, creating objects on the fly is a design created by amateur.
AFAIK, such a design would not be approved as being SOX compliant
Previous Topic: ORA-06519:Active autonomous transaction detected and rolled back : Error
Next Topic: Problems with Self_join
Goto Forum:
  


Current Time: Fri Dec 02 20:54:22 CST 2016

Total time taken to generate the page: 0.33849 seconds