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 last published in September 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close