Skip to Main Content
  • Questions

  • Dynamic cursor, Static Cursor, Bind variable etc.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Debasish.

Asked: November 29, 2003 - 10:15 pm UTC

Last updated: December 04, 2003 - 1:25 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi
Tom,
If you clear me about Cursor and which cursor is suitable to use with some example and clear me about bind variable in details then I will be oblige.

Debasish Ghosh

and Tom said...

If you have my new book "Effective Oracle By Design", i cover these topics in large detail.

In short, all SQL in Oracle is dynamic sql under the covers. Therefore, it is a "language thing" in reality (static vs dynamic).

In PLSQL the best, by far (by leaps and bounds) the best is static sql. use dynamic sql only when there is no other way to do it. static sql is more performat, easier to code, easier to maintain, sets up the dependency between the code and the database objects, can "fix" your code as you alter definition of the table in the database. In short, static sql = good, dynamic sql = bad, unless you have to.

In PLSQL if you use static sql -- you don't even need to think about binding -- it is done automagically for you. It is IMPOSSIBLE to not bind correctly and fully in PLSQL with static sql (one of the major benefits i believe!)

In other languages, say java or "VB" -- you have to bind yourself. For that, you refer to your programmers guide for that language (in java and vb, you'd be looking for prepared and/or callable statements)


In PLSQL, binding with dynamic sql is easy, in short:


execute immediate l_statement using variable1, variable2, variable3, ....


refer to the plsql guide for details.

Rating

  (4 ratings)

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

Comments

SQL and PL/SQL boundary

Arun Gupta, December 02, 2003 - 8:56 am UTC

Tom,
Is it right to say that the moment I code something like declare...begin...exception...end, I am using PL/SQL and all the code in packages/stored procedures/functions is PL/SQL, though it might contain SQL statements?

If this is correct, and all the code is in packages in the database, does this imply that I should try to minimize the use of dynamic SQL in packages?

Thanks...

Tom Kyte
December 02, 2003 - 9:26 am UTC

if it starts with declare or begin -- it is plsql.

yes, minimize the use of

o execute immediate
o dbms_sql

maximize the use of STATIC SQL

Arun Gupta, December 03, 2003 - 12:16 pm UTC

Tom,
I want to know more about why is dynamic sql less efficient when called from within PL/SQL. The context switching between SQL and PL/SQL will still take place even when I use static SQL.
Thanks...

Tom Kyte
December 03, 2003 - 5:02 pm UTC

do you have my book "Effective Oracle by Design"?

I go into this in detail but to summarize:

procedure p1
is
l_x number;
begin
execute immediate 'select count(*) from t' into l_x;
end;

procedure p2
is
l_x number;
select count(*) into l_x from t;
end;


Ok -- say we execute p1 and p2 100 times. Answer the following:

a) how many times will select count(*) from t be parsed by p1? 100. by p2? 1

parsing per execute instead of one parse per session. big reason number 1.


b) what procedures access T? I want to modify it. What code will be affected. Well P1 will -- but you HAVE NO CLUE that it will.


c) will p1 run successfully each time? no idea, what of the dynamic sql is bad. compile time static sql will execute. runtime dynamic sql -- who knows? (hard to code, harder to debug, really truly hard to maintain)


Go ahead and benchmark it. run p1 and p2 lots -- see which is "faster"



Arun Gupta, December 04, 2003 - 12:04 pm UTC

I did run the tests for i in 1..1000 loop. Posting the tkprof output would just be a waste of space. The dynamic sql was parsed 1000 times, the static, just once.
a) Why does Oracle parse a dynamic SQL statement every time if nothing has changed? From tkprof, both the statements look the same to Oracle: select count(*) from t
b) Is it a hard parse or a soft parse?

I am also trying to understand how your statement fits in:

In short, all SQL in Oracle is dynamic sql under the covers. Therefore, it is a
"language thing" in reality (static vs dynamic).

Thanks for your patience...

Tom Kyte
December 04, 2003 - 12:30 pm UTC

a) because each time it COULD be different, hence it is not cached.

b) soft



my point was -- in Oracle -- ALL SQL is dynamic (we hard and soft parse all sql, we do not "precompile" access plans in the manner DB2 on the mainframe does -- which is a GOOD thing (that we don't))


It is the language environment that will mandate whether static or dynamic sql is to be used

a) VB -- all about dynamic sql, no such thing as static
b) java -- pick between SQLJ (static) and JDBC (all about dynamic)
c) OCI -- all about dynamic
d) pro*c -- use static until you are forced to use dynamic
e) plsql -- DEFINITELY use static until you are forced at gunpoint to do dynamic

and so on.

Arun Gupta, December 04, 2003 - 1:25 pm UTC

Thanks...it clears the doubts. In addition to everything that you said about dependence and maintainability, the developers coded such convoluted logic using dynamic SQL that there were several (more than 20) possible SQLs generated from one stored procedure. Looking at the SQL from statspack report, it was impossible to tell where they came from. I regretted the day I taught the developers to use dynamic SQL.

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.