Ask the Expert

Dynamic SQL, part 1

I am new to PL/SQL and am trying to do the following. Instead of this stored procedure:
CREATE OR REPLACE PROCEDURE  prc_get_serverid
(
    p_dbid  IN  NUMBER,
    p_svrid OUT NUMBER
)
AS
BEGIN
    SELECT  serverid
    INTO    p_svrid
    FROM    tbldatabases
       WHERE DATABASEID = p_dbid;
END;

I would like to have something like this, where I can pass the SQL statement:

CREATE OR REPLACE PROCEDURE prc_get_serverid
(
    p_sqlstmt  IN  VARCHAR2,
    p_dbid     IN  NUMBER,
    p_svrid    OUT NUMBER
)
AS
BEGIN
    OPEN p_sqlstmt USING p_dbid, psvrid;
END;
...
    sqlstmt := 'SELECT serverid INTO :X FROM tbldatabases WHERE databaseid = :Y';
    prc_get_serverid (sqlstmt, svrid, dbid);

I know the syntax is all wrong, but I am having trouble figuring out the best way to do this.

    Requires Free Membership to View

The buzzword you're looking for is dynamic SQL: a routine that builds and executes a SQL statement at run time is performing dynamic SQL. Dynamic SQL is a silver bullet that can do magical things. With dynamic SQL you can write a single function that returns the answer to any query! It can do anything, for a price. The price is both your time as a developer and the user's time when the program runs. The price is usually so high that you solve the problem some other way.

If you're new to PL/SQL, you may not be aware of some simpler techniques that might help you. Within PL/SQL:

  • creating cursors with parameters
  • passing cursors as parameters to procedures

In regular SQL:

  • CASE statements
  • scalar subquery expressions
  • temporary tables

Or in PL/SQL behind the scenes:

  • policies (dmbs_rls package)
  • SYS_CONTEXT

So if you're "having trouble figuring out the best way to do this," you should start with clearly stating what you're trying to do. Is it look up a value from a table? A join can do that. Do you need to look up a value in any one of three tables, to be determined at run time? Use CASE. Do you need to determine which of several databases you're on? SYS_CONTEXT may give the answer. Are you really trying to get the result from a SQL statement that is completely unknown before run time? Then you may really have to use dynamic SQL, but if that's the case, why are you calling the procedure "prc_get_serverid"?

That's the end of the sermon. Now for the answer to your question in part 2.


Footnote

For a wonderful story involving a silver bullet and how hard it is to make one, see the fairly self-contained chapter "The great bear of Gurlita Cliff" in Selma Lagerlöf's novel The story of Gösta Berling.

This was first published in September 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: