OraFAQ Forum: Security » Where to store static SQL (Application or Database) and why?

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Where to store static SQL (Application or Database) and why? [message #576591]

Wed, 06 February 2013 06:59

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

Michel Cadot
Messages: 68757
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

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

Michel Cadot
Messages: 68757
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 12:15:34 CDT 2025