Q

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close