Home » RDBMS Server » Security » Where to store static SQL (Application or Database) and why? (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Where to store static SQL (Application or Database) and why? [message #576591] Wed, 06 February 2013 06:59 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Hi,

Can anyone help me understanding where to store the static SQL code (Database or Application).

Consider a scenario, that I am executing a simple SQL from Java which is returning some value, and it will not be changed in future.

Can anyone suggest me, should I store this sql in java application code or in database in form of procedure/function and returning the result to Java. Is there any security/performance impact?

Thanks,
Manu

Report message to a moderator

Re: Where to store static SQL (Application or Database) and why? [message #576603 is a reply to message #576591] Wed, 06 February 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The maximum way to secure and restrict the access is to store it in a PL/SQL package and returns a ref cursor but the application must not forgot to close it when it no more needs it.

Regards
Michel

[Updated on: Wed, 06 February 2013 09:01]

Report message to a moderator

Re: Where to store static SQL (Application or Database) and why? [message #577305 is a reply to message #576603] Thu, 14 February 2013 02:39 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member
Got it.

One more question on top of that, once I was facing an interview.
Interviewer asked that we can return REF cursor and Collection both, give me a scenario when REF Cursor should be returned and when Collection should be returned.

I was clueless, and still don't know the answer.

Regards,
Manu

Report message to a moderator

Re: Where to store static SQL (Application or Database) and why? [message #577306 is a reply to message #577305] Thu, 14 February 2013 02:59 Go to previous message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The answer is simple, always use REF CURSOR, never use collection.

Regards
Michel

Report message to a moderator

Previous Topic: Java Critical Patch Update
Next Topic: how to create user under schema (should access all objects of schema)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jan 22 01:40:33 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.