Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: static/dynamic/embedded SQL distinctions

Re: static/dynamic/embedded SQL distinctions

From: Malcolm Dew-Jones <>
Date: 22 Aug 2005 13:25:07 -0800
Message-ID: <> wrote:
: I want to distinguish between static SQL, dynamic SQL, and embedded
: SQL, but couldn't find too much useful resources in the web.

: For example, if we put SQL statements (SELECT, INSERT, UPDATE, etc...)
: inside an application (e.g. Java application, VB application, etc...),
: do we consider those SQL statements as static SQL? or embedded SQL?

: How about dynamic SQL? any practical examples?

: Please advise. thanks!!

I think you are playing with words, to a large extent. However, for the sake of helping clarify things, I might put the following

"static SQL" would the opposite of "dynamic SQL"

For dynamic SQL in Oracle PL/SQL lookup "EXECUTE IMMEDIATE" (or "DBMS_SQL" for older Oracles). Static SQL is then the stuff you write that doesn't need the execute immediate statement to make it work. Most SQL in PL/SQL is static.

In languages other than PL/SQL all sql is ultimately dynamic. The program builds a string containing an SQL statement at runtime and passes it to the database server which compiles it and runs it. Various preprocessing tools make it easier to use SQL within the language, and it may make the SQL look the same as static SQL within PL/SQL, but it isn't. In fact the preprocessor simply converts the SQL into function calls that will end up manipulating a string at runtime, just as if you did that yourself by directly coding the necessary function calls.

As for embedded SQL, I would call that the SQL statements that you write directly into a language that doesn't normally support SQL as part of the language. To make that work you use a preprocessor, as mentioned above.

Someone else might use the term "embedded" when ever your program stores SQL statements within text strings that will be sent to the server to be used as SQl statements.

I would also say that SQL statements in PL/SQL are "embedded", but that is probably not what most people care to mean by it.



This space not for rent.
Received on Mon Aug 22 2005 - 16:25:07 CDT

Original text of this message