Skip to Main Content
  • Questions

  • pl/sql devloper role in java development project

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, vikas.

Asked: October 09, 2013 - 9:08 am UTC

Last updated: November 07, 2013 - 3:28 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi,

I have previously work in support projects and created the oracle function,procedure,trigger etc.I have good knowledge of oracle sql and pl/sql.

Now i have assigned to a new development project.its a first time i am doing any devlopment project.the fornt end for the application development is Java and backend is oracle 10g.

can you please guide me what are the roles ans responsibility of a pl/sql developer in the development project with example.

your input are much appreciated on this.many Thanks!

Regards,
Vikas

and Tom said...

In my opinion - your role is to champion the use of stored procedures, sql that has more than 'select', 'from' and 'where' in it (using joins, order bys, analytic windowing functions, etc - more than just select * from t where <some simple predicate>)

you'll be championing the use of database security features like fine grained access control, fine grained auditing.

you'll be championing the ability to diagnose performance issues, ensuring things like dbms_monitor can be used to trace applications (ensuring that applications IDENTIFY themselves to the database using properties on the connection to set the session_identifier, client info, module and action attributes!!!). championing the use of dbms_application_info for long running database processes (so we can see what they are doing and where they are in their logic)

you'll be doing this for performance, scalability, security and so on.

you'll meet heavy resistance, big time - i am sure.

so always be able to talk "numerately" - in numbers.

for example:

"if you do this ETL process slow by slow (row by row) and take just 1ms per row (pretty fast don't you think?) and you have to process 5,000,000 rows - it will take almost 1.5 hours. When we get to 50 million rows - it'll be over a half a day. When we get to 500 million rows - it will take a WEEK to process. if I am allowed to do it in the database in a single DDL statement to transform the data, I can be done with 500 million rows in 2 minutes."


meaning - understand how they are going to approach a problem - how you would approach the same problem and prototype out the approaches (benchmark) and explain how slow it can be to do many things outside of the database.

Also, when it comes to security - they'll probably say "that is business logic, it is middle tier", explain to them that security in depth is what we need - many layers of security, so that when one layer is broken through - the others are still intact (and they will be broken through, they will have a SQL injection bug somewhere for example - and it just takes ONE bug). Tell them "go for it, put it into the application if you want, but we'll also want it in the database for when your application is subverted"

see also

https://www.google.com/search?q=site%3Aasktom.oracle.com+business+logic

for many discussions on this.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 09, 2013 - 2:42 pm UTC

very well explained and are real facts... (faced similar situations)

SQL Injection

Rob B, October 15, 2013 - 12:36 pm UTC

"(and they will be broken through, they will have a SQL injection bug somewhere for example - and it just takes ONE bug)"

Totally agree with your answer Tom, but to be fair I've seen just as much SQL Injectable code within PL/SQL in the database as outside of it. In fact in the many different Development environments I've seen J2EE based stuff tends to be at the more secure end I would say. I'm definitely not saying I'm a big fan of it though....
Tom Kyte
November 01, 2013 - 6:31 pm UTC

the only way - repeat - the only way to have sql injectable code in PLSQL is to use dynamic sql.

so, that should preclude the vast majority of plsql code from sql injection - if you use static sql, sql injection is just "not possible".

Now that said, let me wail on about dynamic sql. dynamic sql should hardly ever be used. it is over used and abused (bad developer practices again). but even with dynamic sql - as long as you use bind variables - you are not subject to sql injection.

so - that leaves only dynamic sql that doesn't use bind variables - which is as bad as java/jdbc that doesn't, c# that doesn't, vbasic that doesn't and so on. That code has to be reviewed by an army of people that do not like the coder of that piece of code - that would love to find a bug in their code to make fun of them, that will read that code with a hyper critical eye.

which should make the coder want to avoid dynamic sql, and further really avoid dynamic sql that doesn't use bind variables.

this is where we need to get to. to a point where developers WANT to use bind variables just so they are not subjected to a code microscope. until that happens - here we are :(



I wholly disagree with the "j2ee stuff...." statement. java/jdbc only does dynamic sql, static sql doesn't exist (no one uses sqlj). the lack of bind variable usage is epidemic. the sql injection bugs are *everywhere*.

at least with plsql - MOST of the code is non-sql injectable from the get go with static sql. It is only when a develop gets all cute and fancy and starts using dynamic sql when they probably don't have to that we get into trouble

Dynamic SQL

Rob Burton, November 06, 2013 - 8:59 am UTC

I totally agree with your last reponse about dynamic SQL being the real problem with a lot of development practice. I'm not sure why SQLJ isn't used (I guess is that the database needs to be available at compile time), and as you say all JDBC is dynamic. I never quite understand why a language that cares so much about compilng well is happy to compile, parse and execute all of the database access at runtime.

From my original point though, I see 'Execute Immediate' littered through large amounts of code. Some code ends up doing it around virtually every statement including the Inserts and Updates. As you say, it is a terrible programming practice and virtually all SQL within PL/SQL blocks should be static. Also bind variables are a necessity for the vast security and performance benefits.
Tom Kyte
November 07, 2013 - 3:28 pm UTC

(I guess
is that the database needs to be available at compile time)

that cannot be it - because right after you compile - you run it and the db has to be there then.



if you use bind variables
then
   you are not subject to sql injection (that statement isn't)
else
   you are subject to sql injection
end if

Dynamic sql and parameters

Mark, December 28, 2013 - 4:30 pm UTC

In the event when one must use dynamic SQL, what is the best way to deal with parameters?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.